Daniel Westermann: Getting the most out of pg_stat_io (PGConf.EU 2023)

Explore the intricacies of PostgreSQL's disk I/O with Daniel Westermann in this talk, covering evictions, direct I/O, debugging, and performance optimization.

Key takeaways

Evictions in pg_stat_io track blocks kicked out of the shared buffer when Postgres needs to make room for new blocks
To simulate evictions, use pgbuffercache extension to check current cache contents and pg_test_timing to test I/O timing
Direct I/O means bypassing the OS cache, achieved with debug_iodirect option in Postgres 16 and later
Default block size in Postgres is 8KB, but can be changed at compilation time
File flag and buffering can be enabled with pg_prevo extension to force evictions
pg_stat_io provides metrics on disk I/O, including read and write times, and hit and miss rates
High number of fsyncs may indicate incorrect checkpointer configuration
pgstat.reset_shared can be used to reset I/O-related statistics
pgbench can be used to populate the database with a sample schema for testing
Direct I/O may be faster than default configuration in some cases
Evictions may occur when the cache is full and Postgres needs to make room for new data
track_io_timing option in Postgres 17 and later can be used to track I/O timing
pg_stat_statements provides similar statistics to pg_stat_io, but for overall database performance

Daniel Westermann: Getting the most out of pg_stat_io (PGConf.EU 2023)

More talks