The path to using AIO in postgres
summary
Work toward using asynchronous IO and direct IO in Postgres is already in code: 3x speedup for concurrent COPY in 16, a new streaming read interface for easy prefetching, bulk relation extension to avoid extreme fragmentation, and early direct I/O support in v16 (not recommended for general use). Linux can use io_uring for many in-flight requests; posix AIO and Windows support are partial; a worker-based fallback simulates AIO. Sequential scans, checkpoints, VACUUM show large wins (VACUUM examples: 94s→12.3s and 33s→7.7s) and checkpoint/direct I/O moves writes from kernel limits (~2–2.5 GB/s) to much higher throughput.
AI-written summary · about
chapters (15)
- 0:00Introduction to Postgres IO
- 1:01Understanding IO mechanisms
- 4:13Implemented infrastructure
- 6:41Bulk relation extension
- 10:37Experimental direct IO
- 11:58Streaming IO abstraction
- 14:23Asynchronous IO framework
- 16:48Sequential scan performance
- 21:12Checkpoint and bgwriter
- 23:47WAL writing optimizations
- 30:04Vacuum performance gains
- 32:49Fsync and buffer management
- 35:54Future work and constraints
- 38:26Community contributions
- 41:11Q&A session
related talks
49mAIO in PG 18 and beyond
48mStreaming I/O and vectored I/O
01h 06mIO in PostgreSQL: Past, Present, Future
49mStreaming I/O and vectored I/O
51mWhat went wrong with AIO
30mDeveloping IO Prefetching for Postgres
26mAdditional IO Observability in Postgres with pg_stat_io
17mThe Evolution of Postgres Performance