High CPU and I/O

Symptom: CPU is pinned, disk is saturated, or latency rises under normal load.

What are sessions doing right now

select wait_event_type, wait_event, count(*) from pg_stat_activity
where state = 'active' and backend_type = 'client backend' group by 1, 2 order by 3 desc;

No wait event means the sessions are burning CPU. IO waits mean disk. Lock waits mean Locks and blocked queries. LWLock waits usually mean too many busy connections.

CPU

Find the queries: Slow queries, sorted by total_exec_time. Common causes are sequential scans on large tables, missing indexes, and thousands of connections context-switching. Put a pooler in front and cap active connections near the core count.

Disk

select datname, blks_hit, blks_read,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) as cache_hit_pct
from pg_stat_database where datname = current_database();

Under 99% on an OLTP workload means the working set does not fit in memory. Add RAM, add indexes so fewer pages are touched, or find the queries reading the most from disk (shared_blks_read in pg_stat_statements). On PostgreSQL 16+, pg_stat_io splits reads and writes by backend type.

Checkpoints

Frequent requested checkpoints mean max_wal_size is too small for your write rate, which causes I/O spikes:

select * from pg_stat_bgwriter;      -- PostgreSQL 16 and earlier
select * from pg_stat_checkpointer;  -- PostgreSQL 17+

Raise max_wal_size (for example 8 to 16 GB) and set checkpoint_timeout = 15min with checkpoint_completion_target = 0.9.

Autovacuum

A vacuum running on a very large table can dominate I/O. Throttle it with autovacuum_vacuum_cost_delay rather than disabling it.