from Hacker News

Waiting for Postgres 18: Accelerating Disk Reads with Asynchronous I/O

by lfittl on 5/7/25, 2:57 PM with 154 comments

• by drdrek on 5/8/25, 7:52 AM

  Postgres is such a cool project, I have so much respect for its maintainers and community! For me its the second most impactful OSS project in the business tech world behind Linux itself. A real public good to be cherished and praised.

• by the8472 on 5/7/25, 4:11 PM

  On linux there also is preadv2(..., RWF_NOWAIT) which can be used to do optimistic non-blocking read from the page cache. That might be useful for io_method = worker to shave off a bit of latency. Try reading on the main thread with NOWAIT and only offload to a worker thread when that fails.

• by nu11ptr on 5/7/25, 4:03 PM

  Is this new async. I/O feature for Linux only?

  I know Windows has IOCP and also now an IORing implementation of its own (Less familiar with macOS capabilities other than POSIX AIO).

  https://learn.microsoft.com/en-us/windows/win32/api/ioringap...

  Update: Most of the comments below seem to be missing the fact that Windows now also has an IORing implementation, as I mentioned above. Comparison article here:

  https://windows-internals.com/ioring-vs-io_uring-a-compariso...

• by kev009 on 5/7/25, 7:31 PM

  A lot of work has gone into FreeBSD's aio(4) so it will be interesting to see how that works, because it doesn't have the drawbacks of Linux/glibc aio.

• by skeptrune on 5/7/25, 4:18 PM

  How close is this to the way MySQL does it with InnoDB? It appears to be about the same.

• by Tostino on 5/7/25, 4:30 PM

  Thank you for the effort that went into getting this committed. I remember seeing the first discussions about async I/O (and using io_uring) like 6 or 7 years ago. Amazing amount of work to get the design right.

  Looking forward to the other places that async I/O can be used in future Postgres releases now that the groundwork is done.

• by shayonj on 5/7/25, 4:22 PM

  Very nicely written post! I'd love to start running these in production on NVMe and hope its something major cloud providers start to offer ASAP. The performance gains are _extremely_ attractive

• by niux on 5/7/25, 4:04 PM

  I recently deployed Postgres on a dedicated Hetzner EX-44 server (20 cores, 64GB RAM, 2x 512GB NVMe SSDs in RAID 1) for €39/month. The price-to-performance ratio is exceptional, providing enterprise-level capacity at a fraction of typical cloud costs.

  For security, I implemented TailScale which adds only ~5ms of latency while completely eliminating public network exposure - a worthwhile tradeoff for the significant security benefits.

  My optimization approach includes:

  - Workload-specific configuration generated via PGTune (https://pgtune.leopard.in.ua/)

  - Real-time performance monitoring with PgHero for identifying bottlenecks

  - Automated VACUUM ANALYZE operations scheduled via pgcron targeting write-heavy tables, which prevents performance degradation and helps me sleep soundly

  - A custom CLI utility I built for ZSTD-compressed backups that achieves impressive compression ratios while maintaining high throughput, with automatic S3 uploading: https://github.com/overflowy/pgbackup

  This setup has been remarkably stable and performant, handling our workloads with substantial headroom for growth.

• by martinald on 5/7/25, 4:23 PM

  I sort of had to chuckle at the 20k IOPS AWS instance, given even a consumer $100-200 NVMe gives ~1million+ IOPS these days. I suspect now we have PCIe 5.0 NVMes this will go up to

  I always do wonder how much "arbitrary" cloud limits on things like this cause so many issues. I'm sure that async IO is very helpful anyway, but I bet on a 1million IOPS NVMe it is nowhere near as important.

  We're effectively optimising critical infrastructure tech for ~2010 hardware because that's when big cloud got going and there has been so few price reductions on things since then vs the underlying hardware costs.

  Obviously a consumer NVMe is not "enterprise" but my point is we are 3+ orders of magnitude off performance on cheap consumer hardware vs very expensive 'enterprise' AWS/big cloud costs.

• by p_ing on 5/7/25, 4:52 PM

  Is io_uring still plagued by security issues enabled by it's use? Or have those largely been fixed? My understanding was many Linux admins (or even distros by default?) were disabling io_uring.

• by song on 5/7/25, 5:53 PM

  Are there good performance comparisons between postgres, mariadb and percona? I'm really curious at this point in which case each of those database shine.

• by pseudopersonal on 5/7/25, 4:16 PM

  Does anyone know when the update allowing more concurrent connections is dropping, so we can stop using pgbouncer?

• by rks404 on 5/8/25, 4:48 PM

  I remember back in the day when mysql vs postgres was a real debate with mysql being the popular fave. Genuinely interesting to see that postgres eventually won.

• by cryptonector on 5/7/25, 11:27 PM

  I've been following the `AIO 2.5` thread on the PG mailing lists, and I've been salivating at the thought of this being released.

• by dbbk on 5/7/25, 9:34 PM

  This looks promising! Wonder if it's coming to Neon

• by WhyNotHugo on 5/7/25, 9:22 PM

  It's pretty disappointing that simply using O_NONBLOCK doesn't work as expected on regular files. It would be such a simple and portable mechanism to do async I/O using the same interfaces that we already use for networking.

• by gitroom on 5/7/25, 9:40 PM

  insane how long it took postgres to get async i/o right - feels like all the big changes spark a million little tradeoffs, right? you think stuff like io_uring is finally gonna push postgres to catch up with the clouds