AIO in PG 18 and beyond
summary
PostgreSQL 18 adds AIO for reads only via a readstreams layer that currently uses either worker mode (process-based workers) or Linux io_uring; buffered reads gain most now, writes aren’t yet supported. Benchmarks (with DM delay) show small gains for sequential scans unless storage latency is high, vacuum can speed 2–3x (and much more with high latency), random index-heavy lookups show ~7–8x improvements, and backward scans jump multiplex. Limits: global worker count, ulimit/file-descriptor constraints, and need for index-TID prefetching for wider benefits.
AI-written summary · about
chapters (13)
- 0:00Introduction to AIO in PostgreSQL
- 1:29What is asynchronous IO
- 4:16Predicting IO patterns for AIO
- 7:47IO methods in PostgreSQL 18
- 11:14When AIO helps workloads
- 13:02Benchmark setup and results
- 17:37Sequential scan performance gains
- 18:21Vacuum performance improvements
- 19:45Bitmap heap scan benefits
- 21:54Index read ahead in development
- 26:07Future AIO features and rights
- 32:41Cross-platform AIO support
- 34:32Q&A session
related talks
48mThe path to using AIO in postgres
49mStreaming I/O and vectored I/O
01h 06mIO in PostgreSQL: Past, Present, Future
48mStreaming I/O and vectored I/O
51mWhat went wrong with AIO
41mLinux IO internals for PostgreSQL administrators in 2023
26mAdditional IO Observability in Postgres with pg_stat_io
17mThe Evolution of Postgres Performance