Comment by atombender | Hacker News Reader

Comment by atombender | Hacker News Reader

> PostgreSQL's query planner/optimizer is decidedly state-of-the-art

Postgres's cost-based planner is good, but it's a decidedly 1980s design, predating the famous but also outdated Volcano/Cascades systems (used by Microsoft SQL Server and CockroachDB and others).

So much has happened in the field of query optimization in the last 30 years, very little of which has ended up in Postgres, I think. Postgres has gotten parallel workers and a JIT, but the fundamental design is largely unchanged. It's also quite conservative about adding improvements; other databases has had some variation of index skip scans for ages (Oracle has probably had it for 20 years now, and you can get it through the TimeScale extension), but Postgres is still working on supporting it natively.

The state of the art is arguably Umbra [1], a research project by Thomas Neumann's group at the university of Munich, the successor to HyPer, which is now being commercialized as CedarDB. Their analysis of the Postgres query planner is an interesting read [2].

[1] https://umbra-db.com/

[2] https://www.vldb.org/pvldb/vol9/p204-leis.pdf

Also there are now databases purpose built for specific domains. E.g. if you are building a financial ledger I would way rather be interfacing with TigerBeetle than scaffolding an application-driven ledger around Postgres. https://tigerbeetle.com/.

If I am scraping giant amounts of data I would run far away from Postgres for other databases like Amazon Redshift.

Just wonder what is the advantage of Redshift vs using something such as https://github.com/hydradatabase/columnar, if we are already using postgres?

Perhaps. The trouble usually arrives when you need functionality that your specialized database doesn’t have. Then you end up maintaining two databases and spreading your data, business logic, and resources between them. To each their own, though... there are certainly valid use cases for this.

Tbf, at least for TigerBeetle, they make no bones about the fact that they are hyper-specialized, and they recommend using a general OLTP DB alongside it for the rest of your business logic. It’s purely a ledger DB.

Postgres is a decent, acceptable _anything_ database.

Bisecant fractal databases like HyperKlingonDB are excellent at storing bisecant fractal data, but terrible at anything else (often, just plain terrible overall due to being immature).

These days if you want a PostgreSQL based Data Warehouse both Citus and Timescale are extensions/PostgreSQL based databases I would consider before Redshift.

But even in the 9.4 days (~a decade ago) I was pushing Terabytes worth of analytics data daily through a manually managed Postgres cluster with a team of <=5 (so not that difficult). Since then there have been numerous improvements which make scaling beyond this level even easier (parallel query execution, better predicate push down by the query planner, and declarative partitioning to name a few). Throw something like Citus (extension) into the mix for easy access to clustering and (nearly) transparent table sharding and you can go quite far without reaching for specialized data storage solutions.

TigerBeetle is impressive, but correct me if I'm wrong the schema can't be changed. To me, that that's like a single use DB, when often people don't know what they need.

The paper seems to mostly focus on the quality of cardinality estimation (mostly driven by statistics) which is admittedly one of the frequent sore points in Postgres. There's been some progress in that area though (CREATE STATISTICS being a highlight).

Which is arguably the most important part of a planner. If you don't have good cardinality information, it doesn't matter if you have fancy planner strategies. They'll be employed in the wrong situation, and won't produce the good plans we all want.

And JIT is something you would disable, because often it makes queries slower or jus unpredictable (and results are not cached AFAIK).

How does MySQL compare? I get the sense that innovations land there sooner because of all the mega corps that use it.

A lot of the work is now part of MySQL Heatwave, the paid Oracle / cloud edition.

MySQL hasn’t had nearly as many innovations. That said, for a long time, it was often faster for simpler queries, and especially range queries, IFF your schema was designed to exploit the fact that it’s a clustering index, so rows are physically located next to the PK. If you’re using UUIDv4 PKs, you’re throwing away its entire advantage, and Postgres will almost certainly be faster.

One notable exception for MySQL is INSTANT DDL in 8.x, thanks to contributions from Tencent, which is an extremely nice QoL upgrade that Postgres doesn’t have. The other is logical replication, something Postgres does support now (and has for several years), but didn’t for a long time.

> So much has happened in the field of query optimization in the last 30 years, very little of which has ended up in Postgres, I think. Postgres has gotten parallel workers and a JIT, but the fundamental design is largely unchanged.

Guess I'll keep using Postgres.

Reply on news.ycombinator.com