[HN Gopher] Benchmarking Postgres 17 vs. 18
___________________________________________________________________
Benchmarking Postgres 17 vs. 18
Author : bddicken
Score : 177 points
Date : 2025-10-14 15:35 UTC (10 days ago)
(HTM) web link (planetscale.com)
(TXT) w3m dump (planetscale.com)
| alberth wrote:
| 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.
| 6r17 wrote:
| typo *interpreting i guess ?
| cientifico wrote:
| 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 wrote:
| 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 wrote:
| 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.
| nopurpose wrote:
| Then io_uring AIO mode underperformance is even more curious.
| anarazel wrote:
| 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 wrote:
| 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 wrote:
| Thanks for posting this interesting article! Do we know if
| timescale extension is available as well?
| travisgriggs wrote:
| As in timescaledb? Or something else...?
| p_zuckerman wrote:
| Yes, as in timescaledb. Sorry for not be specific.
| samlambert wrote:
| We are working on it.
| rastignack wrote:
| 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 wrote:
| 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...
| DicIfTEx wrote:
| 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 wrote:
| The docs for 18 also show it, where do you get from that it's
| not available for 18?
| DicIfTEx wrote:
| 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/202
| 50624230110/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.
| cheema33 wrote:
| 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 wrote:
| But ephemeral and non-redundant.
|
| Am I correct in that using local disk on any VPS has durability
| concerns?
| CodesInChaos wrote:
| Using a single disk has durability concerns. But I don't see
| why VPS vs dedicated server should matter much.
| inapis wrote:
| 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 wrote:
| 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.
| XCSme wrote:
| 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).
| whizzter wrote:
| You have a call from France, some company called OVH on the
| line!
| BonoboIO wrote:
| And your backup goes up in flames too.
|
| I would never ever trust OVH with any important data or
| servers, I mean we saw how they secured their datacenters
| where it took 3h to cut the power while the datacenter
| was burning.
| jascha_eng wrote:
| yeh planetscale loves to flex how fast they are but the main
| reason they are fast is because they run a full abstraction
| less than any other cloud provider and this does in fact have
| trade-offs.
| samlambert wrote:
| What is wrong with running without lots of abstractions? We
| are clear about the downsides. The results are clear, you
| can see the customers love it. We run insane amounts of
| state safely on ephemeral compute. It's a flex. All I've
| seen from Timescale people is qqing. Write some code or be
| quiet.
| jascha_eng wrote:
| I'm not criticizing your engineering approach at all.
| Running everything in one box has its merits as your
| benchmarks show but it is also just not apples to apples
| there are other trade-offs and I am just appreciating
| that the community calls that out.
|
| Also hey this is HN not Twitter I think we can be a bit
| more civilized. Not a good look imo for a CEO to get that
| upset over a harmless comment.
| samlambert wrote:
| We run 3 nodes not 1. Your comment is not in isolation we
| get constant shade from Timescale people when we don't
| even think about you.
| samlambert wrote:
| we have mitigated the durability concerns in multiple ways.
| sgarland wrote:
| Yes, it's the ephemerality that's the biggest issue.
| Enterprise-grade SSDs are quite reliable, and typically have
| PLP so even in the event of a sudden power loss, any queued
| writes that the drive has accepted - and thus ack'd the
| fsync() - will be written. Presumably you'd be running some
| kind of redundancy, likely some flavor of RAID or zRAID
| (assuming purely local storage here, not a distributed system
| like Ceph, nor synchronous replication).
|
| But in the cloud, if the physical server backing your
| instance dies, or even if someone accidentally issues a
| shutdown command, you don't get that same drive back when the
| new instance comes up. So a problem that is normally solved
| by basic local redundancy suddenly becomes impossible, and
| thus you must either run synchronous or semi-sync replication
| (the latter is what PlanetScale Metal does), accepting the
| latency hit from distributed storage, or asynchronous
| replication and accept some amount of data loss, which is
| rarely acceptable.
| samlambert wrote:
| Agreed on these trade offs. We do both synchronous and
| semi-synchronous depending on Postgres or MySQL.
| pas wrote:
| ... sounds like a trivial job for bare metal instances
|
| and that EC2 local NVMe encryption keys are ephemeral is
| nice against leaks, but not a necessity for other clouds
| (and not great for resumability, which can really downgrade
| business continuity scores), and I expect for all the money
| they ask for it, to be able to keep it relatively secure
| even across reboots
| BonoboIO wrote:
| Or even a bare metal simple server that just does
| databases with redundant nvme ssd
| rcrowley wrote:
| Yes, a single disk in a VPS or cloud provider has durability
| concerns. That's why EBS and products like it that pretend to
| be a single disk are actually several. Instead of relying on
| multiple block devices, though, we create that redundancy at
| a higher level by relying on multiple MySQL or Postgres
| servers for durability, each with a local NVMe drive for
| performance.
| rcrowley wrote:
| RAID isn't the answer, either, for the record. In AWS and
| GCP, the CPU or RAM blowing up will cost you access to that
| local NVMe drive, too, no matter how much RAID you throw at
| it.
| samlambert wrote:
| Correct. Network storage is flexible for a variety of use cases
| that's why PlanetScale supports both.
| jackdoe wrote:
| > 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 wrote:
| But now you need someone to deal with the hardware.
| jackdoe wrote:
| 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 wrote:
| 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 wrote:
| 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.
| hylaride wrote:
| There is currently a bit of an early shift back to physical
| infra. Some of this is driven by costs(1), some by
| geopolitical concerns, and some by performance. However,
| dealing with physical equipment does introduce a different
| set (old fashioned, but somewhat atrophied) set of skills
| and costs that companies need to deal with.
|
| (1) It is shocking how much of a move to the cloud was
| driven by accountants wanting opex instead of capex, but
| are now concerned with actual cashflow and are thinking of
| going back. The cloud is really good at serving web content
| and storing gobs of data, but once you start wanting to
| crunch numbers or move that data, it gets expensive fast.
| unregistereddev wrote:
| In some orgs the move to the cloud was driven by
| accountants. In my org it was driven by lawyers. With
| GDPR on the horizon and murmurs of other data privacy
| laws that might (but didn't) require data to be stored in
| that customer's jurisdiction, we needed to host in
| additional regions.
|
| We had a couple rather large datacenters, but both were
| in the US. The only infrastructure we had in the EU was
| one small server closet. We had no hosting capacity in
| Brazil, China, etc. Multi-region availability drove us to
| the cloud - just not in the "high availability" sense of
| the term.
| mbesto wrote:
| > I can only see cloud getting even more expensive, not
| less.
|
| When you have three major hyperscalers competing for your
| dollars this is basically not true and not how markets
| work...unless they start colluding on prices.
|
| We've already seen reduction in prices of web services
| costs across the three major providers due to this
| competitive nature.
| lossolo wrote:
| You don't need to, just rent dedicated servers, still 20-50x
| cheaper, problem solved.
| jaza wrote:
| 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.
| thyristan wrote:
| If you want your own hardware to be OPEX, just do leasing.
| Every enterprise hardware seller will make you an offer for
| that.
| benjiro wrote:
| > You pay for it because you want backups and replication to
| be somebody else's problem.
|
| Or you just use something like CockroachDB, YugabyteDB etc
| that auto replicate, auto rebalance if a node goes down, and
| have build in support for backups to and from S3...
|
| Or if your a bit more hands on, multigress seems to be
| closing to completion (
| https://github.com/multigres/multigres ) from the guy that
| make Vitess for Mysql.
|
| The idea that managing hardware and software is hard, is
| silly yet, people (mostly managers it seems ) think its the
| best solution.
| manacit wrote:
| I wouldn't say it's closing to completion - it looks like
| it's in the very early stages development according to
| their repo. I don't see any evidence they've gotten as far
| as even running a single query through it.
|
| Even when it's done, it's going to be a lot of work to run.
| sure, it's not guaranteed to be hard, but if it's not your
| core business and you're making money, having someone else
| do it gives you time to focus on what matters.
| makkes wrote:
| 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.
| vbezhenar wrote:
| Most clouds I've used allow you to create VM with local disk,
| and that might be cheaper that network disk.
| jackdoe wrote:
| This is the 500$ per month option they are describing in the
| post, the magnificent 300,000 IOPS, the network disk is 1500$
| for 16000 IOPS
|
| not to mention then you have all the issues people are
| discussing managing your own backups, snapshots and
| replication and etc
| cowsandmilk wrote:
| 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 wrote:
| The charts looks beautiful, I wonder which library it uses.
| miklosz wrote:
| Seems it's Recharts.
| nodesocket wrote:
| 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 wrote:
| 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...
| samlambert wrote:
| While this post is here I'd like to call out that Vitess for
| Postgres is coming https://www.neki.dev/
| fourseventy wrote:
| 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 wrote:
| Why would you migrate your prod db if you aren't sure of all
| the changes and which config params to use?
| spprashant wrote:
| 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.
___________________________________________________________________
(page generated 2025-10-24 23:01 UTC)