Diagnosing I/O stalls with pg_stat_io

pg_stat_io (PostgreSQL 16+) breaks down I/O by backend type and operation, letting you pinpoint which workload component is causing storage pressure.

Diagnose it

Requires PostgreSQL 16 or later.

-- Top I/O consumers by read time:
SELECT backend_type,
       object,
       context,
       reads,
       pg_size_pretty(read_bytes)          AS read_bytes,
       round(read_time  / 1000.0, 2)       AS read_s,
       writes,
       pg_size_pretty(write_bytes)         AS write_bytes,
       round(write_time / 1000.0, 2)       AS write_s,
       evictions,
       hits
FROM pg_stat_io
WHERE reads > 0 OR writes > 0
ORDER BY read_time + write_time DESC NULLS LAST;

-- Cache effectiveness:
SELECT backend_type, context, object,
       round(100.0 * hits / NULLIF(hits + reads, 0), 1) AS hit_pct,
       reads, hits
FROM pg_stat_io
WHERE hits + reads > 0
ORDER BY reads DESC NULLS LAST;

Why it happens

pg_stat_io aggregates I/O per (backend_type, object,
context) triple. context = 'normal' is regular table/index access;
context = 'bulkread' is sequential scans; context = 'bulkwrite'
is COPY and similar. High evictions in the normal context means
pages are being pushed out of shared_buffers faster than they can be reused —
the cache is too small or the working set is too large.