pg_stat_io and the end of I/O by subtraction

Reads that arrived with nobody’s name on them

Until PostgreSQL 16, the server’s answer to “who is doing the physical I/O here” was partial in a specific and frustrating way. Writes could be attributed, roughly: the background writer view counted buffers written by the checkpointer, buffers written by the background writer, and buffers that ordinary backends had been forced to write themselves. Reads could not be attributed at all. The per-database counters gave a cluster-wide block count, the per-table counters gave the same thing sliced by relation, and neither knew whether the process doing the reading was a client backend, autovacuum, or a parallel worker three levels down a plan.

So the standard technique was arithmetic. Take a total from one view, subtract what two named processes admit to from another, and attribute the remainder to backends by inference. The remainder is not a measurement. It absorbs every process the accounting did not name and every write that went to a relation the accounting did not cover, and it silently changes meaning when a release adds a new kind of auxiliary process.

PostgreSQL 16 replaced the inference with a table. The new view reports one row per combination of process type, object type and context, and each row carries counts of reads, writes, extends, evictions, reuses and fsyncs, with timings when timing is enabled. The context column is the part that does not appear in any earlier view and is the reason the view is worth learning rather than merely adopting: it separates ordinary buffer pool traffic from bulk reads, bulk writes, and vacuum’s own ring buffer, so a large sequential scan and a query thrashing the cache stop looking alike.

The Monitoring section of the PostgreSQL 16 release notes records it in one line. This is an addition rather than a move, so nothing that worked on 15 stops working on 16; what changes is that a whole class of dashboards can be thrown away.

On 15, the arithmetic

The fixture is fifty thousand short rows and a vacuum, so there is genuine read and write traffic of more than one kind to attribute.

CREATE TABLE io_probe (id integer PRIMARY KEY, body text);
INSERT INTO io_probe SELECT g, md5(g::text) FROM generate_series(1, 50000) AS g;
VACUUM ANALYZE io_probe;

CREATE TABLE
INSERT 0 50000
VACUUM

Asking a 15 server the question directly gets you nothing, because the relation is not there to ask:

SELECT backend_type, object, context, reads
FROM pg_stat_io
WHERE reads > 0;

ERROR:  relation "pg_stat_io" does not exist
LINE 2: FROM pg_stat_io
             ^

What 15 can tell you is a cluster-wide read total, and a write breakdown across exactly three named producers. The last column is the inference.

SELECT (SELECT sum(blks_read) FROM pg_stat_database)     AS blocks_read_by_somebody,
       (SELECT buffers_checkpoint FROM pg_stat_bgwriter) AS written_by_checkpointer,
       (SELECT buffers_clean FROM pg_stat_bgwriter)      AS written_by_bgwriter,
       (SELECT buffers_backend FROM pg_stat_bgwriter)    AS written_by_backends;

 blocks_read_by_somebody | written_by_checkpointer | written_by_bgwriter | written_by_backends 
-------------------------+-------------------------+---------------------+---------------------
                    1861 |                       0 |                   0 |                   0
(1 row)

One number for every read the cluster has ever done, and no way to divide it. The three write columns are all zero, and that is accurate rather than broken: no checkpoint has run yet, so nothing has been written out of the buffer pool by any of the three producers that view knows about. Whether the cluster has done any writing at all, 15 cannot tell you, because the only writes it counts are the ones those three do.

On 16, the rows

The same question, asked of the view that exists to answer it:

SELECT backend_type, object, context, reads, writes, extends
FROM pg_stat_io
WHERE reads > 0 OR writes > 0 OR extends > 0
ORDER BY backend_type, context;

    backend_type     |  object  |  context  | reads | writes | extends 
---------------------+----------+-----------+-------+--------+---------
 autovacuum launcher | relation | normal    |     1 |      0 |        
 client backend      | relation | bulkread  |   920 |      0 |        
 client backend      | relation | normal    |   204 |      0 |     559
 standalone backend  | relation | bulkwrite |     0 |      0 |       8
 standalone backend  | relation | normal    |   535 |   1013 |     667
 standalone backend  | relation | vacuum    |    10 |      0 |       0
(6 rows)

Every row names its producer, including one the older view has no row for at all. The standalone backend lines are the bootstrap process that built the cluster before it started serving, and they carry a write count on 16 while the background writer view on 15 reported zero writes for the same work. The bulk-write context is where the initial load went; the vacuum context is the ring buffer, kept deliberately small so a vacuum cannot evict the working set of a busy table; the normal context is everything else. On 15 all of that arrived as one number with a plus sign in front of it.

One detail in that output is worth more than the counts, and it is not in the release note. Some cells are blank rather than zero. A blank is not “this has not happened yet”, it is “this combination cannot happen”, and the view uses the distinction deliberately: a backend reading in the bulk-read context never extends a relation, so that cell is null rather than a zero waiting to increment. Any collection layer that coerces nulls to zero on the way into a time series throws that distinction away, and the graph that results says a thing is happening at a rate of zero when in fact the thing is not a thing. Check what your exporter does with nulls before you trust a flat line here.

The object column is the other axis worth using. It separates ordinary relations from temporary ones, so a session spilling a sort to disk stops being indistinguishable from a session reading a table, which on 15 it was.

Timing is a separate decision and it is off by default. With track_io_timing on, the same rows carry how long the I/O took rather than only how much of it there was, which is what turns the view from an accounting of blocks into a latency signal.

SELECT backend_type, context,
       round(read_time::numeric, 1)  AS read_ms,
       round(write_time::numeric, 1) AS write_ms
FROM pg_stat_io
WHERE read_time > 0 OR write_time > 0
ORDER BY backend_type, context;

    backend_type     | context  | read_ms | write_ms 
---------------------+----------+---------+----------
 autovacuum launcher | normal   |     0.0 |      0.0
 client backend      | bulkread |     9.0 |      0.0
 client backend      | normal   |     1.4 |      0.0
(3 rows)

There is also a hits column, and it is the one that quietly retires a metric most fleets still graph. The familiar cache hit ratio is computed from the per-database block counters as hits over hits plus reads, and it has two well-known problems: a block the kernel served from its own page cache counts as a read, so the ratio understates the cache on a machine with plenty of memory, and the ratio is an average over every kind of work the cluster does, so a single large sequential scan drags it down and looks like a regression. The new view splits hits by process type and by context, which means a bulk read can be excluded from the number rather than apologised for afterwards. The ratio still does not know what the kernel did, and nothing in 16 fixes that.

What else arrived in the same release

Two smaller additions in 16 belong to the same argument and are easy to miss beside the new view. The per-table statistics gained the time of the last sequential scan and the time of the last index scan, which turns “is anything using this index” from a counter you have to baseline and compare into a timestamp you can simply read. And a column arrived counting updates that could not find room on the page holding the old row version, which is the direct measurement of the thing fillfactor exists to prevent and which previously had to be inferred from bloat growth.

Neither is an I/O counter, and both answer the same kind of question the new view answers: something the server had always known and had never been asked to say out loud.

Worth graphing before it is worth alerting on

This view is an addition, so nothing breaks and nothing needs a new page rule. It also has no obvious threshold, and inventing one would be worse than admitting that. Two things are worth putting on a graph and watching for a change in shape rather than a crossing of a line.

• Writes attributed to client backends, in the normal context. A cluster where backends are doing their own writing is a cluster whose checkpointer is behind, and that is a symptom of checkpoint spacing rather than of the query that happened to be unlucky.
• Read time per read in the vacuum context, once timing is on. Vacuum reading slowly is how a maintenance window turns into a morning, and it is invisible in any per-table counter.

The reuses column is the one most likely to be misread. A high reuse count in the vacuum context is the ring buffer doing its job, not pressure.

What it costs to observe

The counts themselves cost nothing beyond the counters the server already maintains, and the view needs no extension and no restart.

Timing is different and is worth a measured decision. track_io_timing asks the operating system for a clock reading around every I/O operation, and on a machine whose clock source is slow, that overhead is real rather than theoretical. PostgreSQL ships a way to measure it directly, pg_test_timing, and running it on the host before switching timing on in production is twenty seconds that occasionally saves an incident. It can be changed without a restart, which means it can also be switched back off without one.

A cluster running a supported version before 16 has no path to this view at all, which is the strongest argument for the upgrade that most fleets meet. PostgreSQL 17 leaned on it further by deleting the backend-write columns from the old view on the grounds that this one reports the same thing more precisely, so a fleet that skipped 16 meets both changes at once.