3 ms·

> 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 fa

> 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/ https://umbra-db.com/ [2] https://www.vldb.org/pvldb/vol9/p204-leis.pdf https://www.vldb.org/pvldb/vol9/p204-leis.pdf

paulryanrogers 2y ago
How does MySQL compare? I get the sense that innovations land there sooner because of all the mega corps that use it.
- re-thc 2y ago
  A lot of the work is now part of MySQL Heatwave, the paid Oracle / cloud edition.
- sgarland 2y ago
  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.
RedCrowbar 2y ago
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).
- systay 2y ago
  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.
kelseydh 2y ago
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/ https://tigerbeetle.com/. If I am scraping giant amounts of data I would run far away from Postgres for other databases like Amazon Redshift.
- freeamz 2y ago
  Just wonder what is the advantage of Redshift vs using something such as https://github.com/hydradatabase/columnar https://github.com/hydradatabase/columnar, if we are already using postgres?
- 1st1 2y ago
  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.
  - sgarland 2y ago
    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.
- pgmeanspostgres 2y ago
  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).
- gshulegaard 2y ago
  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.
celeryd 2y ago
> 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.
ahoka 2y ago
And JIT is something you would disable, because often it makes queries slower or jus unpredictable (and results are not cached AFAIK).

paulryanrogers 2y ago

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

re-thc 2y ago
A lot of the work is now part of MySQL Heatwave, the paid Oracle / cloud edition.
sgarland 2y ago
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.

RedCrowbar 2y ago

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).

systay 2y ago
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.

kelseydh 2y ago

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/ https://tigerbeetle.com/. If I am scraping giant amounts of data I would run far away from Postgres for other databases like Amazon Redshift.

freeamz 2y ago
Just wonder what is the advantage of Redshift vs using something such as https://github.com/hydradatabase/columnar https://github.com/hydradatabase/columnar, if we are already using postgres?
1st1 2y ago
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.
- sgarland 2y ago
  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.
pgmeanspostgres 2y ago
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).
gshulegaard 2y ago
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.

celeryd 2y ago

> 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.

ahoka 2y ago

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