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.
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.
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.
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.
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.
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.
... 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
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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
> 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.
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.
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.
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.
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.
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.
> 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.
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.
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?
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'?
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.
It's also worth noting that the default for data checksums has changed, with some overhead due to that.
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.
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.
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.
Am I correct in that using local disk on any VPS has durability concerns?
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
(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.
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.
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.
not to mention then you have all the issues people are discussing managing your own backups, snapshots and replication and etc
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.
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...
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.
See e.g. here: https://www.cybertec-postgresql.com/en/postgresql-18-and-bey...
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.
[1] https://hub.docker.com/_/postgres#pgdata
[2] https://www.postgresql.org/docs/current/upgrading.html#UPGRA...