Benchmarking Postgres 17 vs. 18

[anarazel]

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]

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/) showed some significant differences.

[ozgune]

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.

[nopurpose]

Then io_uring AIO mode underperformance is even more curious.

[cowsandmilk]

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.

[cheema33]

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]

But ephemeral and non-redundant.

Am I correct in that using local disk on any VPS has durability concerns?

[samlambert]

Correct. Network storage is flexible for a variety of use cases that's why PlanetScale supports both.

[alberth]

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.

[cientifico]

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.

[6r17]

typo *interpreting i guess ?

[jackdoe]

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

[jaza]

You don't pay for RDS because you care about IOPS. You pay for it because you want backups and replication to be somebody else's problem. And because you (by which I mean probably the MBA management above you, rather than you yourself) care about it being an opex rather than capex cost, a lot more than you care about how much the cost is. And because ISO audit boxes get ticked.

[makkes]

Comparing monthly cloud cost with one-time hardware purchasing cost completely dismisses the latter's long-time cost like people, replacement parts, power, housing, accessories. While I do believe you can run your own hardware much cheaper, there's a lot to consider before making the decision.

[Hrun0]

But now you need someone to deal with the hardware.

[vbezhenar]

Most clouds I've used allow you to create VM with local disk, and that might be cheaper that network disk.

[DicIfTEx]

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

[1]: https://www.postgresql.org/docs/devel/app-pgdump.html#:~:tex...

[anarazel]

The docs for 18 also show it, where do you get from that it's not available for 18?

[rastignack]

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]

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

[samlambert]

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

[nodesocket]

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]

Either do a proper upgrade with backup/restore or use `PGDATA`[1] and `pg_upgrade`[2].

[1] https://hub.docker.com/_/postgres#pgdata

[2] https://www.postgresql.org/docs/current/upgrading.html#UPGRA...

[fourseventy]

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]

Why would you migrate your prod db if you aren't sure of all the changes and which config params to use?

[spprashant]

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.

[p_zuckerman]

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

[samlambert]

We are working on it.

[travisgriggs]

As in timescaledb? Or something else…?

[novoreorx]

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

[miklosz]

Seems it's Recharts.