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

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.

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.

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.

Is this new async. I&#x2F;O feature for Linux only?<p>I know Windows has IOCP and also now an IORing implementation of its own (Less familiar with macOS capabilities other than POSIX AIO).<p><a href="https:&#x2F;&#x2F;learn.microsoft.com&#x2F;en-us&#x2F;windows&#x2F;win32&#x2F;api&#x2F;ioringapi&#x2F;" rel="nofollow">https:&#x2F;&#x2F;learn.microsoft.com&#x2F;en-us&#x2F;windows&#x2F;win32&#x2F;api&#x2F;ioringap...</a><p>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:<p><a href="https:&#x2F;&#x2F;windows-internals.com&#x2F;ioring-vs-io_uring-a-comparison-of-windows-and-linux-implementations&#x2F;" rel="nofollow">https:&#x2F;&#x2F;windows-internals.com&#x2F;ioring-vs-io_uring-a-compariso...</a>

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

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

Very nicely written post! I&#x27;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

I recently deployed Postgres on a dedicated Hetzner EX-44 server (20 cores, 64GB RAM, 2x 512GB NVMe SSDs in RAID 1) for €39&#x2F;month. The price-to-performance ratio is exceptional, providing enterprise-level capacity at a fraction of typical cloud costs.<p>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.<p>My optimization approach includes:<p>- Workload-specific configuration generated via PGTune (<a href="https:&#x2F;&#x2F;pgtune.leopard.in.ua&#x2F;" rel="nofollow">https:&#x2F;&#x2F;pgtune.leopard.in.ua&#x2F;</a>)<p>- Real-time performance monitoring with PgHero for identifying bottlenecks<p>- Automated VACUUM ANALYZE operations scheduled via pgcron targeting write-heavy tables, which prevents performance degradation and helps me sleep soundly<p>- A custom CLI utility I built for ZSTD-compressed backups that achieves impressive compression ratios while maintaining high throughput, with automatic S3 uploading: <a href="https:&#x2F;&#x2F;github.com&#x2F;overflowy&#x2F;pgbackup">https:&#x2F;&#x2F;github.com&#x2F;overflowy&#x2F;pgbackup</a><p>This setup has been remarkably stable and performant, handling our workloads with substantial headroom for growth.

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<p>I always do wonder how much &quot;arbitrary&quot; cloud limits on things like this cause so many issues. I&#x27;m sure that async IO is very helpful anyway, but I bet on a 1million IOPS NVMe it is nowhere near as important.<p>We&#x27;re effectively optimising critical infrastructure tech for ~2010 hardware because that&#x27;s when big cloud got going and there has been so few price reductions on things since then vs the underlying hardware costs.<p>Obviously a consumer NVMe is not &quot;enterprise&quot; but my point is we are 3+ orders of magnitude off performance on cheap consumer hardware vs very expensive &#x27;enterprise&#x27; AWS&#x2F;big cloud costs.

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

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

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

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

Thank you for the effort that went into getting this committed. I remember seeing the first discussions about async I&#x2F;O (and using io_uring) like 6 or 7 years ago. Amazing amount of work to get the design right.<p>Looking forward to the other places that async I&#x2F;O can be used in future Postgres releases now that the groundwork is done.

This looks promising! Wonder if it&#x27;s coming to Neon

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

insane how long it took postgres to get async i&#x2F;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