pg_stat_io: I/O Breakdown

Break disk I/O down to the exact operation — heap writes, WAL, and VACUUM — instead of one undifferentiated number

Every earlier lab in this block measured I/O indirectly — cache hit ratios from pg_statio_user_tables, file sizes from pg_total_relation_size. pg_stat_io, introduced in PostgreSQL 16, breaks I/O down to the operation level: which backend type performed it, what kind of object it touched — relation data or WAL — and in what context: an ordinary read, a VACUUM pass, a bulk read, or a bulk write.\n\nThis lab resets the view for a clean baseline, generates a real workload, and reads the breakdown after each phase — INSERT, then VACUUM — to see exactly which context each one shows up under, and why the WAL and the table's own heap are tracked as two completely separate objects even though a single INSERT writes to both.

Turning On Timing (Optional, Session-Scoped)

SET track_io_timing = on;

Without this, pg_stat_io still reports accurate read/write/extend counts — it just leaves the timing columns (read_time, write_time) at zero. track_io_timing is superuser-context, so a single SET is enough for the current session.

A Clean Baseline

SELECT pg_stat_reset_shared('io');

Unlike the per-table resets in Lab 2.4.1, this resets the shared, cluster-wide pg_stat_io view in one call.

Reading the Breakdown

SELECT backend_type, object, context, reads, writes, extends, hits
FROM pg_stat_io
WHERE backend_type = 'client backend' AND object = 'relation'
ORDER BY context;

object = 'relation' is heap and index data; object = 'wal' is the write-ahead log — a single INSERT touches both, but they are tracked as entirely separate rows. context further splits relation I/O into normal (ordinary access), vacuum (VACUUM's own reads/writes), bulkread/bulkwrite (the ring-buffer-bounded operations from Lab 2.4.6), and init (extending a relation for the first time).

object: relation vs wal

pg_stat_io tracks I/O against relation data (table and index heap pages) completely separately from I/O against the write-ahead log, even within the same statement. An INSERT generates both — a relation write for the new row's page, and a WAL write to make that change durable — and pg_stat_io reports them as two independent rows rather than one combined number.

context: normal vs vacuum vs bulkread/bulkwrite

Within relation I/O, context distinguishes ordinary query access (normal) from VACUUM's own reads and writes (vacuum) from the ring-buffer-bounded bulk operations covered in Lab 2.4.6 (bulkread, bulkwrite) from a relation's very first extension (init). The same physical disk, four different reasons something touched it.

⏱️ Enable I/O Timing

Turn on track_io_timing for this session so pg_stat_io's timing columns are populated, not just its counts.

psql -U postgres -d beer_db -c "SET track_io_timing = on;" -c "SELECT current_setting('track_io_timing');"

student@lab:~$ psql -U postgres -d beer_db -c "SET track_io_timing = on;" -c "SELECT current_setting('track_io_timing');" SET SET current_setting ------------------ on (1 row)

🔄 Reset for a Clean Baseline

Reset the shared I/O statistics view before generating any workload.

psql -U postgres -d beer_db -c "SELECT pg_stat_reset_shared('io');"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_stat_reset_shared('io');" SET pg_stat_reset_shared ----------------------- (1 row)

✍️ Generate a Write Workload

Insert 50,000 rows, then check the I/O breakdown by object — relation data versus WAL are tracked separately.

psql -U postgres -d beer_db -c "INSERT INTO monitoring.io_demo (payload) SELECT repeat('z',100) FROM generate_series(1,50000) g;"

psql -U postgres -d beer_db -c "SELECT backend_type, object, context, reads, writes, extends, hits FROM pg_stat_io WHERE backend_type = 'client backend' AND (reads > 0 OR writes > 0 OR extends > 0) ORDER BY object, context;"

🧹 Run VACUUM and Check the Context Breakdown

VACUUM the table and see a new context appear in the relation I/O breakdown.

psql -U postgres -d beer_db -c "VACUUM monitoring.io_demo;"

psql -U postgres -d beer_db -c "SELECT backend_type, object, context, reads, writes, extends, hits FROM pg_stat_io WHERE backend_type = 'client backend' AND object = 'relation' ORDER BY context;"

🔄 Reset Again — and See It Does Not Stay at Zero

Reset the I/O stats one more time, then immediately check whether anything is already non-zero again.

psql -U postgres -d beer_db -c "SELECT pg_stat_reset_shared('io');"

psql -U postgres -d beer_db -c "SELECT count(*) FROM pg_stat_io WHERE reads > 0 OR writes > 0;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_stat_reset_shared('io');" SET pg_stat_reset_shared ----------------------- (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM pg_stat_io WHERE reads > 0 OR writes > 0;" SET count ------- 3 (1 row)

Lab 2.4.7 complete — and Block 2.4 complete. You can now attribute disk I/O to the exact operation that caused it:\n\n\n track_io_timing : ✅ enabled at session level\n pg_stat_reset_shared('io') : ✅ clean measurement windows, twice\n relation vs wal : ✅ told apart on the same INSERT\n normal vs vacuum context : ✅ watched VACUUM's own footprint appear\n

Enable JavaScript to run the live terminal and track your progress.