postgres pg_stat_io - ghdrako/doc_snipets GitHub Wiki

pg_stat_io - Postgres 16+

• Pinpoint which processes are I/O-heavy
• Understand how autovacuum and background writer contribute to disk load
• Correlate slow queries with specific I/O patterns

Using pg_stat_io, you can track the I/O load from autovacuum processes:

SELECT backend_type, sum(reads) as total_reads, sum(writes) as total_writes
FROM pg_stat_io
WHERE backend_type = 'autovacuum worker'
GROUP BY backend_type;

Diagnosing Slow Queries Due to I/O

SELECT backend_type, reads, writes, fsyncs, read_time, write_time
FROM pg_stat_io
WHERE backend_type = 'client backend';

select pg_stat_reset_shared('io');

select * from pg_stat_io order by reads desc nulls last limit 10;
select * from pg_stat_io order by writes desc nulls last limit 10;