Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Looking Ahead to Postgres 19

snowflake.com

Post reply on HN
Print view View original

81–90 of 138 posts

Re: Looking Ahead to Postgres 19

#81

Post by sega_sai »

chucksmash wrote: post #61
sega_sai wrote: post #53

Earlier quoted context omitted.

PostgreSQL was a right tool for my task for many years. It is a question for PostgreSQL can adapt to a new reality of much bigger datasets or I have to switch to a new tool. And I am not the only user of Postgresql in this context. So it is easy to say in vacuum 'you are using the wrong database', but it's not something that can be easily changed with 100s of Tb of data, existing user workflows etc.

Just because it's easy to say doesn't mean it's wrong. By the same logic, you could say Microsoft Access should have all the capabilities of Postgres because it's painful for small businesses to move off of it when it's no longer a good fit for their needs.

The thing is that PG had introduced pluggable storage engines exactly for the reason I am talking about, and there have been few implementations of columnar storage using PG functionality, it's just they always stayed out of the tree. So I wasn't talking about some functionality that is completely out of scope.

But I agree in the end I may be forced to move elsewhere...

Re: Looking Ahead to Postgres 19

#82

Post by khurs »

sgt wrote: post #72
khurs wrote: post #69

Earlier quoted context omitted.

"As the datasets become bigger and bigger," I think the better way is to use Postgresql for new data and routinely archive off older data to data warehouse type database, to keep the Postgres one small. (Many companies also now use a RDBMS alongside either a KV database or document store in main app)

Which document store do you prefer? And do you use it via Postgres using a foreign wrapper?

Two most recent companies:

The first was using SQL Server alongside CouchDB, an older company. CRUD Data into SQL, documents into Couch. (used separately, document Url in Couch stored in SQL and brought in by front end)

The second, startup with funding, was using Azure SQL Server (and complaining of the cost) and using Azure Cosmo (Key Value NoSQL) alongside it. Same relationship, no link between the two, it's the front end that drew data from both.

Just look up the pro and cons of each database, some are insert only, some are distributed, some are faster read then write etc. LLM will give you a quick comparison.

Also: https://db-engines.com/en/ranking

Re: Looking Ahead to Postgres 19

#83

Post by bob1029 »

colechristensen wrote: post #64
bob1029 wrote: post #31

Earlier quoted context omitted.

SQLite and MSSQL are my two solutions for relational storage problems. If I am going to use a "free" provider, SQLite is impossible to beat. They cover a majority of use cases today. SQLite starts to fall apart with backup, replication and tooling. If I am on the hook for things like system availability and disaster recovery, I don't have a problem spending money to cover my ass. If I am going to pay any amount of mo…

Can you expand on what is better with MSSQL?

One of the biggest advantages is that a lot of people in the business are already comfortable with the ecosystem. I know that from the perspective of HN that SSMS, pgAdmin, DBeaver, DB Browser for SQLite, et. al. are mostly isomorphic, but from the perspective of everyone else in the business, these are substantially different things to think about.

Whether the popularity of the MS ecosystem is good or not is a separate problem. If we are solving for "make the business go well and I get paid more", the strategy is usually obvious. We can still advocate for OSS and not-so-many-eggs in the Microsoft basket while we get paid for using these technologies. Again, SQLite is the preferred engine in my tool belt. I don't want to have to manage a hosted sql machine. But, sometimes the problem absolutely insists upon it.

Also, if you are using C#/.NET, integrating with MSSQL is always a little bit easier than the other providers. SQLite (and some others) have lackluster types for things like time. MSSQL has DateTime2 and DateTimeOffset that map exactly into the CLR types.

Re: Looking Ahead to Postgres 19

#84

Post by mickeyp »

hobofan wrote: post #67
mickeyp wrote: post #44

The graph database feature looks interesting, but I wonder... SELECT customer_name FROM GRAPH_TABLE (myshop MATCH (c IS customers)-[IS customer_orders]->(o IS orders WHERE o.ordered_when = current_date) COLUMNS (c.name AS customer_name)); That is _awful_ syntax; it is reminiscent of neo4j, which is surely not a tool anyone serious should copy from outright in 2026. And of course the final thing I am left wondering is…

That is not a homebrew sytax that Postgres came up with themselves. It's SQL/PGQ, which was derived from the Cypher language for Neo4J and now is part of the SQL standard.

Fair --- but I feel my criticism stands. It's awful. Don't get me started on 80% of all deployment uses of neo4j...

Re: Looking Ahead to Postgres 19

#85

Post by Macha »

ksec wrote: post #29
aljgz wrote: post #14

I've used Postgres, Oracle, MsSql Server, and MySql in serious projects, no extensive experience with Sqlite, which I know is an amazing player. These days, I do myself a favor and always avoid Oracle and MySql/MariaDB. Postgres is amazing, and the two big things I wished it had: 1. lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurre…

>I do myself a favor and always avoid Oracle and MySql/MariaDB. So what's wrong with MySQL or MariaDB?

1. No transactional DDL

2. No MERGE statement

3. No partial indices

4. Many ways to lock out instant add table, meaning you can’t add a column without a full table write, which can lock the table for minutes at a time in even moderately sized tables.

5. Dealing with legacy mysql databases often means dealing with utf8mb3, which used to be the default utf8 data type despite not storing all utf8

6. Dealing with all but the most recent mysql databases means dealing with non strict mode which means your NOT NULL column won’t require a value.

Re: Looking Ahead to Postgres 19

#86

Post by switchbak »

sega_sai wrote: post #53
tyre wrote: post #38

Earlier quoted context omitted.

You might be using the wrong database if that’s what you’re hoping for. Columnar databases are a separate category. It’s like saying that you’re getting worried Apple doesn’t sell washing machines.

PostgreSQL was a right tool for my task for many years. It is a question for PostgreSQL can adapt to a new reality of much bigger datasets or I have to switch to a new tool. And I am not the only user of Postgresql in this context. So it is easy to say in vacuum 'you are using the wrong database', but it's not something that can be easily changed with 100s of Tb of data, existing user workflows etc.

Columnar vs row based is a large architectural distinction. In theory it'd be great to support both, but this has also been an elusive and complex problem to solve. You're not going to see a simple "columnar type" on a row based system like this any time soon, that's trivializing the situation.

Andy Pavlo teaches about this and has done real work in this space, and certainly considers it to be non-trivial. His YouTube channel is spicy and worth looking into if you want to dive deep on the topic.

Re: Looking Ahead to Postgres 19

#87

Post by breakingcups »

bspammer wrote: post #42
horsawlarway wrote: post #22

Earlier quoted context omitted.

These comments are worse than the slop.

They aren't actually. They make me feel like I'm not going crazy - that I'm not the only person noticing that the quality of the average article on hacker news has dropped off a cliff in the last 6 months. Links from different people with different cultures, life experiences, and languages have the same tone of voice, the same sentence structure, and the same breathless, boring, staccato yet arrhythmic, emotive yet s…

Hear hear. I can't believe people are content to consume slop like this.

Re: Looking Ahead to Postgres 19

#88

Post by khurs »

aljgz wrote: post #14

I've used Postgres, Oracle, MsSql Server, and MySql in serious projects, no extensive experience with Sqlite, which I know is an amazing player. These days, I do myself a favor and always avoid Oracle and MySql/MariaDB. Postgres is amazing, and the two big things I wished it had: 1. lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurre…

Postgresql is the better product, but doesn't have the horizontal scaling of MySQL/Maria though, so if you want an easy to setup cluster MySQL for high volume online retail store or similar has a use case still.

Re: Looking Ahead to Postgres 19

#89

Post by kjs3 »

sega_sai wrote: post #53
tyre wrote: post #38

Earlier quoted context omitted.

You might be using the wrong database if that’s what you’re hoping for. Columnar databases are a separate category. It’s like saying that you’re getting worried Apple doesn’t sell washing machines.

PostgreSQL was a right tool for my task for many years. It is a question for PostgreSQL can adapt to a new reality of much bigger datasets or I have to switch to a new tool. And I am not the only user of Postgresql in this context. So it is easy to say in vacuum 'you are using the wrong database', but it's not something that can be easily changed with 100s of Tb of data, existing user workflows etc.

"Not something that can be easily changed" is not a valid rebuttal to "the tool no longer supports how the task I am working on has evolved over many years". This isn't 'saying in a vacuum'; 'has this tool ceased to be the right answer and how do I move to a better solution' is an exercise responsible app owners should engage in all the time. Shouldn't be different than things I presume your job touches on: if your research evolves to need to see in radio waves, do you complain the optical telescope you started with back in the day doesn't see radio waves and expect someone else to make that happen, or do you find a radio telescope?

You can either 1) accept the limits of the tool, at least until it becomes a priority for the tool developer to add whatever feature you want, 2) pay someone to develop the features you want, or 3) buck up and do what vast numbers of ops folks have done before you and move to something that does what you need.

Re: Looking Ahead to Postgres 19

#90

Post by khurs »

bob1029 wrote: post #31
aljgz wrote: post #14

I've used Postgres, Oracle, MsSql Server, and MySql in serious projects, no extensive experience with Sqlite, which I know is an amazing player. These days, I do myself a favor and always avoid Oracle and MySql/MariaDB. Postgres is amazing, and the two big things I wished it had: 1. lightweight connection; connection bouncers improve the situation, but you still have an unreasonably high memory footprint per concurre…

SQLite and MSSQL are my two solutions for relational storage problems. If I am going to use a "free" provider, SQLite is impossible to beat. They cover a majority of use cases today. SQLite starts to fall apart with backup, replication and tooling. If I am on the hook for things like system availability and disaster recovery, I don't have a problem spending money to cover my ass. If I am going to pay any amount of mo…

>I am going all the way. The developer experience around MSSQL is untouchable. SSMS and VS with sql projects runs circles around contemporary entity framework crap. Sprinkle in 3rd party tools from vendors like RedGate and you can replace multi-million dollar consulting packages.

Try Postgresql. I was previously SQL Server and the move has been great.

SSMS doesn't offer much over alternatives, both PGAdmin and others. VS is dying, VSCode is the future.

Redgate doesn't offer anything that is essential or not available elsewhere for postgresql

The money you spend on Microsoft licenses could be put towards more ram on the server.

Post reply on HN