IO & Memory Tools

iostat, vmstat, pidstat on PostgreSQL 18.4 Linux hosts - tie host metrics to postgres PIDs during IO and memory incidents.

Recipe

# IO snapshot (sysstat package)
iostat -xz 1 5
 
# Memory and CPU scheduler view
vmstat 1 5
 
# Per-process RSS for postgres PIDs
pidstat -r -p $(pgrep -d, -f 'postgres:') 1 5

SELECT pid, wait_event_type, wait_event, state, left(query, 60)
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND state = 'active';

When to reach for this:

• p95 latency up but CPU percent looks idle (IO wait)
• OOM or swap activity during large sort/hash aggregate queries
• Autovacuum or checkpoint suspected of saturating disk

Working Example

Symptom: API timeouts; Postgres CPU 30% but latency high.

iostat -xz 1 3

Device  r/s   w/s  rkB/s  wkB/s  %util
nvme0n1 4500  800  72000  12000  98.5

SELECT queryid, calls, mean_exec_time, shared_blks_read, temp_blks_written
FROM pg_stat_statements
ORDER BY shared_blks_read DESC NULLS LAST LIMIT 5;

pidstat -u -p $(pgrep -n -f 'postgres:') 1 3

Interpretation:

• %util near 100% → disk saturated
• Top query high shared_blks_read → seq scan or cold cache
• Specific PID high CPU in pidstat → correlate to pg_stat_activity.pid

Deep Dive

iostat Columns

vmstat Columns

pidstat on Postgres

# All postgres backends RSS every second
pidstat -r -C postgres 1 5
 
# Single backend from pg_stat_activity
pidstat -r -p 284719 1 10

SELECT pid, query, work_mem_needed FROM (
  SELECT pid, left(query,40) AS query
  FROM pg_stat_activity WHERE state = 'active'
) q;

• Sort/hash operations show RSS climb on one PID before spill to disk (temp_blks_written)

Checkpoint vs Query IO

SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, buffers_checkpoint
FROM pg_stat_bgwriter;

• Checkpoint spike: write rate high, fewer active query rows in pg_stat_activity
• Query storm: many active backends with DataFileRead wait events

Gotchas

• Blaming Postgres without iostat - EBS/NFS throttle looks like slow queries. Fix: measure %util first.
• Single vmstat sample - misleading spike. Fix: 1-second interval, 5+ samples.
• pidstat on wrong PID - postmaster vs backend. Fix: use PID from pg_stat_activity.
• Ignoring connection count - 200 sorts x work_mem exhaust RAM. Fix: pool connections, lower per-query work_mem role.
• Tuning shared_buffers during swap storm - does not fix swap. Fix: reduce memory consumers, add RAM, fix queries.

Alternatives

FAQs

Related

• Linux CLI for DBAs Basics - disk and service health
• CLI Best Practices - scripting policy
• Work Mem and Sort Hash Tuning - memory GUCs
• Seq Scan vs Index Scan - read amplification
• Incident Triage Skill - IO branch

Stack versions: This page was written for PostgreSQL 18.4 on Linux with sysstat 12+ (iostat, pidstat, vmstat).

cGJxcnRodnFyZi52YnxwdHZiMTAyNnwyMDI2MDc=