5 ms·

Benchmarking Postgres 17 vs. 18

184 points by bddicken 1y ago

alberth 1y ago
Am I interrupting the data correctly in that, if you’re running on NVMe - it’s just so fast, that it doesn’t make a difference what mode you pick.
- deleted 1y ago
  [deleted]
- 6r17 1y ago
  typo *interpreting i guess ?
- cientifico 1y ago
  That was the same conclusion I got by playing with the graphs. I concluded that better IO planning it's only worth it for "slow" I/O in 18. Pretty sure it will bring a lot of learnings. Postgress devs are pretty awesome.
anarazel 1y ago
Afaict nothing in this benchmark will actually use AIO in 18. As of 18 there is aio reads for seq scans, bitmap scans, vacuum, and a few other utility commands. But the queries being run should normally be planned as index range scans. We're hoping to the the work for using AIO for index scans into 19, but it could work end up in 20, it's nontrivial. It's also worth noting that the default for data checksums has changed, with some overhead due to that.
- mebcitto 1y ago
  That explains why `sync` and `worker` have so similar results in almost all runs. The benchmarks from Tomas Vondra (https://vondra.me/posts/tuning-aio-in-postgresql-18/ https://vondra.me/posts/tuning-aio-in-postgresql-18/) showed some significant differences.
- nopurpose 1y ago
  Then io_uring AIO mode underperformance is even more curious.
  - anarazel 1y ago
    It is. I tried to repro it without success. I wonder if it's just being executed on a different VMs with slightly different performance characteristics. I can't tell based on the formulation in the post whether all the runs for one test are executed on the same VM or not.
- ozgune 1y ago
  If the benchmark doesn’t use AIO, why the performance difference between PG 17 and 18 in the blog post (sync, worker, and io_uring)? Is it because remote storage in the cloud always introduces some variance & the benchmark just picks that up? For reference, anarazel had a presentation at pgconf.eu yesterday about AIO. anarazel mentioned that remote cloud storage always introduced variance making the benchmark results hard to interpret. His solution was to introduce synthetic latency on local NVMes for benchmarks.
p_zuckerman 1y ago
Thanks for posting this interesting article! Do we know if timescale extension is available as well?
- travisgriggs 1y ago
  As in timescaledb? Or something else…?
  - p_zuckerman 1y ago
    Yes, as in timescaledb. Sorry for not be specific.
- samlambert 1y ago
  We are working on it.
rastignack 1y ago
Is there now a way to avoid double buffering and use direct IO in postgresql ? Has anybody seriously benchmarked this ? I don’t think io uring would make a difference with this setting but I’m curious, as it’s the default for oracle and sybase.
- hans_castorp 1y ago
  Direct I/O is being worked on, but is not yet available. See e.g. here: https://www.cybertec-postgresql.com/en/postgresql-18-and-beyond-from-aio-to-direct-io/ https://www.cybertec-postgresql.com/en/postgresql-18-and-bey...
DicIfTEx 1y ago
I was expecting `pg_dumpall` to get the `--format` option in v18,[0] but at the moment the docs say it's still only available in the development branch.[1] Is anyone familiar with Postgres development able to give an update on the state of the feature? Is it planned for a future (18 or 19) release? [0]: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1495eff7bdb0779cc54ca04f3bd768f647240df2 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... [1]: https://www.postgresql.org/docs/devel/app-pgdump.html#:~:text=%2DF%20format,cannot%20be%20changed%20during%20restore. https://www.postgresql.org/docs/devel/app-pgdump.html#:~:tex...
- anarazel 1y ago
  The docs for 18 also show it, where do you get from that it's not available for 18?
  - DicIfTEx 1y ago
    Ah my mistake, I linked to the docs for `pg_dump` (which has long had the `format` option) rather than `pg_dumpall` (which lacks it). Before Postgres 18 was released, the docs listed `format` as an option for `pg_dumpall` in the upcoming version 18 (e.g. Wayback Machine from Jun 2025 https://web.archive.org/web/20250624230110/https://www.postgresql.org/docs/18/app-pg-dumpall.html#:~:text=%2D%2Dformat%20is%20plain-,%2DF%20format,the%20oid%20of%20the%20database.,-Note:%20see%20pg_dump https://web.archive.org/web/20250624230110/https://www.postg... ). The relevant commit is from Apr 2025 (see link #0 in my original comment). But now all mention has been scrubbed, even from the Devel branch docs.
    - anarazel 1y ago
      It got reverted for now: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ce9a6244b5b4ce1df71611512a757353803404a5 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...
cheema33 1y ago
The primary lesson I learned here was this: If you care about performance, don't use network storage. If you are using local nvme disk, then it does not matter if you are using Postgres 17 or 18. Performance is about the same. And significantly faster than network storage.
- saxenaabhi 1y ago
  But ephemeral and non-redundant. Am I correct in that using local disk on any VPS has durability concerns?
  - CodesInChaos 1y ago
    Using a single disk has durability concerns. But I don't see why VPS vs dedicated server should matter much.
  - inapis 1y ago
    Sure. Till an extent. And if you run some mission-critical application, definitely. But most applications run fine from local storage and can tolerate some downtime. They might even benefit from the improved performance. You can also fix the durability and disaster recovery concerns by setting up on RAID/ZFS and maintaining proper backups.
  - fabian2k 1y ago
    Databases like Postgres have well established ways to handle that. And if you're setting up the DB yourself, you absolutely need to do backups anyway. And a replica on a different server.
    - saxenaabhi 1y ago
      Backups don't alleviate durability concerns. Read replicas(async) neither. I think only way it could work was if I implemented sync replication like planetscale, but that arduous.
  - XCSme 1y ago
    On some providers (e.g. Hetzner), the dedicated servers come by default with 2x RAID 1 disks, so it's a lot less likely to fail (unless the datacenter burns down).
jackdoe 1y ago
> IOPS: 3,000 > IOPS: 300,000 for 551$ per month the cloud is ridiculous. just for reference with 4 consumer nvmes and raid10 and pciex16 you can easily do 3m IOPS for one time cost of like 1000$ in my current job we constantly have to rethink db queries/design because of cloud IOPS, and of course not having control over RDS page cache and numa. every time I am woken up at night because a seemingly normal query all of the sudden goes beyond our IOPS budget and the WAL starts trashing, I seriously question my choices. the whole cloud situation is just ridiculous.
- Hrun0 1y ago
  But now you need someone to deal with the hardware.
  - jackdoe 1y ago
    oh no! this is proven to be impossible, no man can tell a computer what to do lspci is only written in the old alchemy books, in the whispers of the thrice great Hermes. PS: I have personally put down actual fires in a datacenter, and I prefer it to this 3000 IOPS crap.
  - layoric 1y ago
    Working at IT places in the late 2000s, it was still pretty common place for there to be a server rooms. Even for a large org with multiple sites 100s of kms a part, you could manage it with a pretty small team. And it is a lot easier to build resilient applications now than it was back then from what I remember. Cloud costs are getting large enough that I know I’ve got one foot out the door and a long term plan to move back to having our own servers and spend the money we save on people. I can only see cloud getting even more expensive, not less.
    - ralusek 1y ago
      And it’ll be so good and cheap that you’ll figure “hell, I could sell our excess compute resources for a fraction of AWS.” And then I’ll buy them, you’ll be the new cloud. And then more people will, and eventually this server infrastructure business will dwarf your actual business. And then some person in 10 years will complain about your IOPS pricing, and start their own server room.
deleted 1y ago
[deleted]
cowsandmilk 1y ago
Where are the error bars? I don’t get why people run all these tests and don’t give me an idea of standard deviation or whether the differences are actually statistically significant.
novoreorx 1y ago
The charts looks beautiful, I wonder which library it uses.
- miklosz 1y ago
  Seems it's Recharts.
nodesocket 1y ago
I'm currently running PostgreSQL in docker containers using bitnami/postgresql:17.6.0-debian-12-r4. As I understand it, Bitnami is no longer supporting or updating their Docker containers. Any recommendations on a upgrade path to PostgreSQL 18 in Docker? A quick glance of swapping to the official postgres container shows POSTGRESQL_DATABASE is renamed to POSTGRESQL_DB. The other issue is the volume mount path is currently /bitnami/postgresql.
- makkes 1y ago
  Either do a proper upgrade with backup/restore or use `PGDATA`[1] and `pg_upgrade`[2]. [1] https://hub.docker.com/_/postgres#pgdata https://hub.docker.com/_/postgres#pgdata [2] https://www.postgresql.org/docs/current/upgrading.html#UPGRADING https://www.postgresql.org/docs/current/upgrading.html#UPGRA...
samlambert 1y ago
While this post is here I'd like to call out that Vitess for Postgres is coming https://www.neki.dev/ https://www.neki.dev/
- deleted 1y ago
  [deleted]
fourseventy 1y ago
I'm literally in the middle of upgrading my prod db to pg18. Its about 6tb, has a few thousand queries per second, should I be considering running in 'worker' mode instead of 'io_uring'?
- parthdesai 1y ago
  Why would you migrate your prod db if you aren't sure of all the changes and which config params to use?
- spprashant 1y ago
  For upgrades which have enough risks as it is, I would keep the number of variables low. Once upgraded and stable, you can replicate to a secondary instance with io_method switched and test on it before switching over.

184 points by bddicken 1y ago

Am I interrupting the data correctly in that, if you’re running on NVMe - it’s just so fast, that it doesn’t make a difference what mode you pick.

deleted 1y ago
[deleted]
6r17 1y ago
typo *interpreting i guess ?
cientifico 1y ago
That was the same conclusion I got by playing with the graphs. I concluded that better IO planning it's only worth it for "slow" I/O in 18. Pretty sure it will bring a lot of learnings. Postgress devs are pretty awesome.

anarazel 1y ago

Afaict nothing in this benchmark will actually use AIO in 18. As of 18 there is aio reads for seq scans, bitmap scans, vacuum, and a few other utility commands. But the queries being run should normally be planned as index range scans. We're hoping to the the work for using AIO for index scans into 19, but it could work end up in 20, it's nontrivial. It's also worth noting that the default for data checksums has changed, with some overhead due to that.

mebcitto 1y ago
That explains why `sync` and `worker` have so similar results in almost all runs. The benchmarks from Tomas Vondra (https://vondra.me/posts/tuning-aio-in-postgresql-18/ https://vondra.me/posts/tuning-aio-in-postgresql-18/) showed some significant differences.
nopurpose 1y ago
Then io_uring AIO mode underperformance is even more curious.
- anarazel 1y ago
  It is. I tried to repro it without success. I wonder if it's just being executed on a different VMs with slightly different performance characteristics. I can't tell based on the formulation in the post whether all the runs for one test are executed on the same VM or not.
ozgune 1y ago
If the benchmark doesn’t use AIO, why the performance difference between PG 17 and 18 in the blog post (sync, worker, and io_uring)? Is it because remote storage in the cloud always introduces some variance & the benchmark just picks that up? For reference, anarazel had a presentation at pgconf.eu yesterday about AIO. anarazel mentioned that remote cloud storage always introduced variance making the benchmark results hard to interpret. His solution was to introduce synthetic latency on local NVMes for benchmarks.

p_zuckerman 1y ago

Thanks for posting this interesting article! Do we know if timescale extension is available as well?

travisgriggs 1y ago
As in timescaledb? Or something else…?
- p_zuckerman 1y ago
  Yes, as in timescaledb. Sorry for not be specific.
samlambert 1y ago
We are working on it.

rastignack 1y ago

Is there now a way to avoid double buffering and use direct IO in postgresql ? Has anybody seriously benchmarked this ? I don’t think io uring would make a difference with this setting but I’m curious, as it’s the default for oracle and sybase.

hans_castorp 1y ago
Direct I/O is being worked on, but is not yet available. See e.g. here: https://www.cybertec-postgresql.com/en/postgresql-18-and-beyond-from-aio-to-direct-io/ https://www.cybertec-postgresql.com/en/postgresql-18-and-bey...

DicIfTEx 1y ago

I was expecting `pg_dumpall` to get the `--format` option in v18,[0] but at the moment the docs say it's still only available in the development branch.[1] Is anyone familiar with Postgres development able to give an update on the state of the feature? Is it planned for a future (18 or 19) release? [0]: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1495eff7bdb0779cc54ca04f3bd768f647240df2 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... [1]: https://www.postgresql.org/docs/devel/app-pgdump.html#:~:text=%2DF%20format,cannot%20be%20changed%20during%20restore. https://www.postgresql.org/docs/devel/app-pgdump.html#:~:tex...

anarazel 1y ago
The docs for 18 also show it, where do you get from that it's not available for 18?
- DicIfTEx 1y ago
  Ah my mistake, I linked to the docs for `pg_dump` (which has long had the `format` option) rather than `pg_dumpall` (which lacks it). Before Postgres 18 was released, the docs listed `format` as an option for `pg_dumpall` in the upcoming version 18 (e.g. Wayback Machine from Jun 2025 https://web.archive.org/web/20250624230110/https://www.postgresql.org/docs/18/app-pg-dumpall.html#:~:text=%2D%2Dformat%20is%20plain-,%2DF%20format,the%20oid%20of%20the%20database.,-Note:%20see%20pg_dump https://web.archive.org/web/20250624230110/https://www.postg... ). The relevant commit is from Apr 2025 (see link #0 in my original comment). But now all mention has been scrubbed, even from the Devel branch docs.
  - anarazel 1y ago
    It got reverted for now: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ce9a6244b5b4ce1df71611512a757353803404a5 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

cheema33 1y ago

The primary lesson I learned here was this: If you care about performance, don't use network storage. If you are using local nvme disk, then it does not matter if you are using Postgres 17 or 18. Performance is about the same. And significantly faster than network storage.

saxenaabhi 1y ago
But ephemeral and non-redundant. Am I correct in that using local disk on any VPS has durability concerns?
- CodesInChaos 1y ago
  Using a single disk has durability concerns. But I don't see why VPS vs dedicated server should matter much.
- inapis 1y ago
  Sure. Till an extent. And if you run some mission-critical application, definitely. But most applications run fine from local storage and can tolerate some downtime. They might even benefit from the improved performance. You can also fix the durability and disaster recovery concerns by setting up on RAID/ZFS and maintaining proper backups.
- fabian2k 1y ago
  Databases like Postgres have well established ways to handle that. And if you're setting up the DB yourself, you absolutely need to do backups anyway. And a replica on a different server.
  - saxenaabhi 1y ago
    Backups don't alleviate durability concerns. Read replicas(async) neither. I think only way it could work was if I implemented sync replication like planetscale, but that arduous.
- XCSme 1y ago
  On some providers (e.g. Hetzner), the dedicated servers come by default with 2x RAID 1 disks, so it's a lot less likely to fail (unless the datacenter burns down).

jackdoe 1y ago

> IOPS: 3,000 > IOPS: 300,000 for 551$ per month the cloud is ridiculous. just for reference with 4 consumer nvmes and raid10 and pciex16 you can easily do 3m IOPS for one time cost of like 1000$ in my current job we constantly have to rethink db queries/design because of cloud IOPS, and of course not having control over RDS page cache and numa. every time I am woken up at night because a seemingly normal query all of the sudden goes beyond our IOPS budget and the WAL starts trashing, I seriously question my choices. the whole cloud situation is just ridiculous.

Hrun0 1y ago
But now you need someone to deal with the hardware.
- jackdoe 1y ago
  oh no! this is proven to be impossible, no man can tell a computer what to do lspci is only written in the old alchemy books, in the whispers of the thrice great Hermes. PS: I have personally put down actual fires in a datacenter, and I prefer it to this 3000 IOPS crap.
- layoric 1y ago
  Working at IT places in the late 2000s, it was still pretty common place for there to be a server rooms. Even for a large org with multiple sites 100s of kms a part, you could manage it with a pretty small team. And it is a lot easier to build resilient applications now than it was back then from what I remember. Cloud costs are getting large enough that I know I’ve got one foot out the door and a long term plan to move back to having our own servers and spend the money we save on people. I can only see cloud getting even more expensive, not less.
  - ralusek 1y ago
    And it’ll be so good and cheap that you’ll figure “hell, I could sell our excess compute resources for a fraction of AWS.” And then I’ll buy them, you’ll be the new cloud. And then more people will, and eventually this server infrastructure business will dwarf your actual business. And then some person in 10 years will complain about your IOPS pricing, and start their own server room.

deleted 1y ago

[deleted]

cowsandmilk 1y ago

Where are the error bars? I don’t get why people run all these tests and don’t give me an idea of standard deviation or whether the differences are actually statistically significant.

novoreorx 1y ago

The charts looks beautiful, I wonder which library it uses.

miklosz 1y ago
Seems it's Recharts.

nodesocket 1y ago

I'm currently running PostgreSQL in docker containers using bitnami/postgresql:17.6.0-debian-12-r4. As I understand it, Bitnami is no longer supporting or updating their Docker containers. Any recommendations on a upgrade path to PostgreSQL 18 in Docker? A quick glance of swapping to the official postgres container shows POSTGRESQL_DATABASE is renamed to POSTGRESQL_DB. The other issue is the volume mount path is currently /bitnami/postgresql.

makkes 1y ago
Either do a proper upgrade with backup/restore or use `PGDATA`[1] and `pg_upgrade`[2]. [1] https://hub.docker.com/_/postgres#pgdata https://hub.docker.com/_/postgres#pgdata [2] https://www.postgresql.org/docs/current/upgrading.html#UPGRADING https://www.postgresql.org/docs/current/upgrading.html#UPGRA...

samlambert 1y ago

While this post is here I'd like to call out that Vitess for Postgres is coming https://www.neki.dev/ https://www.neki.dev/

deleted 1y ago
[deleted]

fourseventy 1y ago

I'm literally in the middle of upgrading my prod db to pg18. Its about 6tb, has a few thousand queries per second, should I be considering running in 'worker' mode instead of 'io_uring'?

parthdesai 1y ago
Why would you migrate your prod db if you aren't sure of all the changes and which config params to use?
spprashant 1y ago
For upgrades which have enough risks as it is, I would keep the number of variables low. Once upgraded and stable, you can replicate to a secondary instance with io_method switched and test on it before switching over.