Getting the most out of pg_stat_io
summary
PostgreSQL 16 adds pg_stat_io and it exposes concrete I/O counters: extends, evictions, hits, fsyncs, read/write counts and timings, but track_io_timing is off by default so enable it and use pg_test_timing utility and pg_reset_shared('io') to validate before measuring. Extends match 8KB blocks (example showed 8 extends → 8×8KB), evictions can hit ~150k when preloading large relations, and a developer-only direct I/O option shows ~5–10% speedup in simple benchmarks (requires restart and careful workload testing).
AI-written summary · about
chapters (13)
- 0:00Introduction to pg_stat_io
- 1:08Default zero IO timings
- 4:02Tracking IO timing settings
- 4:23Understanding extends in PostgreSQL
- 10:19Evictions and cache behavior
- 17:45Interpreting eviction metrics
- 18:06Cache hits explained
- 22:04Hits vs evictions analysis
- 22:25Fsync operations overview
- 23:53Other pg_stat_io metrics
- 24:14Direct IO in PostgreSQL
- 26:17Summary and acknowledgments
- 27:27Q&A session
related talks
26mAdditional IO Observability in Postgres with pg_stat_io
01h 06mIO in PostgreSQL: Past, Present, Future
53mVisualizing Postgres I O Performance for Development
52mPostgreSQL Index Strategy Guide
49mStreaming I/O and vectored I/O
48mStreaming I/O and vectored I/O
49mAIO in PG 18 and beyond
39mDeep dive into PostgreSQL internal statistics