pg_stat_wal, and the columns 18 takes back

Log volume stopped being a thing you measured at the filesystem

For most of PostgreSQL’s history the only honest way to find out how much write-ahead log a workload generated was to watch the directory. Count segments, or subtract two log positions, and divide by elapsed time. That gives a rate and nothing else. It cannot tell you whether the volume is records or full-page images, it cannot tell you whether a backend had to wait for the log buffer, and it attributes nothing, because the archive directory does not know which statement filled it.

PostgreSQL 14 added a view with the counters the server had been keeping internally. Records written, full-page images among them, total bytes, and a count of the times a backend found the log buffer full and had to write it out itself before it could continue. With timing enabled it also carried how long those writes and the subsequent syncs took. The System Views section of the release notes records it in a single line, beside the replication slot and copy progress views that arrived with it.

The counter people misread is the full-page image one. A full-page image is written the first time a page is touched after a checkpoint, so its share of your log volume is a function of checkpoint spacing rather than of how much data changed. A workload that looks like it writes a lot is often a workload that checkpoints too often, and the view is where that distinction becomes visible instead of inferred.

What a load of twenty thousand rows costs in log

The fixture is an append-only audit table, the counters reset first so the numbers belong to the load rather than to the life of the cluster.

CREATE TABLE audit_trail (id bigserial PRIMARY KEY, body text);
SELECT pg_stat_reset_shared('wal');
SELECT pg_sleep(1);
INSERT INTO audit_trail (body) SELECT repeat('a', 300) FROM generate_series(1, 20000);
CHECKPOINT;
SELECT pg_sleep(1);
SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) AS wal_written, wal_buffers_full,
       wal_write, wal_sync,
       round(wal_write_time::numeric, 1) AS write_ms,
       round(wal_sync_time::numeric, 1)  AS sync_ms
FROM pg_stat_wal;

CREATE TABLE
 pg_stat_reset_shared 
----------------------
 
(1 row)

 pg_sleep 
----------
 
(1 row)

INSERT 0 20000
CHECKPOINT
 pg_sleep 
----------
 
(1 row)

 wal_records | wal_fpi | wal_written | wal_buffers_full | wal_write | wal_sync | write_ms | sync_ms 
-------------+---------+-------------+------------------+-----------+----------+----------+---------
       40839 |      33 | 8674 kB     |              520 |       525 |        5 |     14.0 |    13.0
(1 row)

Read the last four columns together, because they are the operational content of this view and the counts on their own are not. The number of writes and the number of times the buffer filled are almost the same number, which says that nearly every write happened because the log buffer ran out of room rather than because a commit asked for one. That is the signature of a log buffer too small for the write rate, and it is the one tuning conclusion this view supports directly.

The sync count is much smaller than the write count for the opposite reason: these inserts ran inside one implicit transaction each, and the group commit machinery batches the syncs. A workload with many small committing transactions inverts that ratio, and the sync time becomes the number that matters because it is the one the storage device controls.

The four columns 18 takes away

On PostgreSQL 18 the write-ahead log was brought into the same I/O accounting as everything else, and the four columns describing writes and syncs left this view. The same query does not degrade; it stops.

SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) AS wal_written, wal_buffers_full,
       wal_write, wal_sync,
       round(wal_write_time::numeric, 1) AS write_ms,
       round(wal_sync_time::numeric, 1)  AS sync_ms
FROM pg_stat_wal;

ERROR:  column "wal_write" does not exist
LINE 2:        wal_write, wal_sync,
               ^

The error names wal_write because that is the first missing column in the list, which is an accident of the order you wrote them in rather than a statement about what moved. All four went together.

What survives on 18 is the part nobody needs to change: records, full-page images, bytes and the buffer-full counter. The same load, on the newer server, with both sets of shared counters cleared first so the figures belong to it.

CREATE TABLE audit_trail (id bigserial PRIMARY KEY, body text);
SELECT pg_stat_reset_shared('wal');
SELECT pg_stat_reset_shared('io');
SELECT pg_sleep(1);
INSERT INTO audit_trail (body) SELECT repeat('a', 300) FROM generate_series(1, 20000);
CHECKPOINT;
SELECT pg_sleep(1);
SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) AS wal_written, wal_buffers_full
FROM pg_stat_wal;

CREATE TABLE
 pg_stat_reset_shared 
----------------------
 
(1 row)

 pg_stat_reset_shared 
----------------------
 
(1 row)

 pg_sleep 
----------
 
(1 row)

INSERT 0 20000
CHECKPOINT
 pg_sleep 
----------
 
(1 row)

 wal_records | wal_fpi | wal_written | wal_buffers_full 
-------------+---------+-------------+------------------
       40848 |       2 | 8537 kB     |              202
(1 row)

The writes and syncs are still counted. They are rows now, in the I/O view PostgreSQL 16 added, with the log as one kind of object among the others and one row per process type that touches it.

SELECT backend_type, context, writes, round(write_time::numeric, 2) AS write_ms, fsyncs
FROM pg_stat_io
WHERE object = 'wal' AND (writes > 0 OR fsyncs > 0)
ORDER BY backend_type, context;

  backend_type  | context | writes | write_ms | fsyncs 
----------------+---------+--------+----------+--------
 checkpointer   | normal  |      1 |     0.03 |      1
 client backend | normal  |    205 |     4.73 |      2
 walwriter      | normal  |      1 |     0.98 |      1
(3 rows)

That is more information than the old columns carried, not less, and it is the reason the change was made: the log writes a checkpointer does, the ones the log writer does on its own schedule, and the ones a client backend is forced into are three different events with three different remedies, and a single pair of counters could not separate them. The client backend row is the one that maps onto the diagnosis above, and its count sits just above the buffer-full count from the same load, which is the same story told by a row instead of a column.

Compare the two loads rather than the two totals, because both servers ran the same fixture with the same log buffer size. Records and bytes come out within a percent of each other, which is what you would hope: the workload generates the log, and the version does not change how much of it there is. The buffer-full count does not come out the same, and it is not close. Whatever the cause, the practical consequence is that the one column on this view which is both a tuning signal and still present after the upgrade needs a fresh baseline rather than a translated one, and a panel that carries it across the boundary unchanged will report an improvement nobody made.

The move itself is a break in the series rather than a rename, and the sum of the new rows is not the old counter. Reset the baseline; do not splice it.

Why this number and the log directory disagree

An obvious sanity check is to compare the bytes this view reports against the size of the write-ahead log directory, and it will not match, by a lot, in either direction. The two measure different things and the difference is not an error.

Segments are a fixed size and they are recycled rather than deleted. A cluster that has written eight megabytes of log has a directory containing whole segments, several of them, because the server keeps a supply ready so that a busy moment never has to wait for a file to be created. Once a segment is no longer needed it is renamed and reused rather than removed, so the directory settles at a size decided by the checkpoint settings and the retention requirements and then stops tracking the workload at all.

In the other direction, the directory holds segments the counters have long finished with: everything a replication slot is holding back, everything within the configured retention, and everything the archiver has not yet dealt with. On a cluster with a stuck slot the directory grows without bound while these counters report an ordinary rate, which is exactly the case where somebody checks the wrong number and concludes the workload changed.

So use them for different questions. This view answers how much log the workload generates, which is a property of the application and the checkpoint settings. The directory answers how much disk the server currently needs, which is a property of retention, archiving and whoever is consuming the stream. Alerting on the first and not the second means a full disk arrives unannounced; alerting on the second and not the first means a workload that doubled its log volume is invisible until it does.

What to alert on, and the one that moves

• Bytes of log per unit of business work, rather than per second. A rate tells you the disks are busy. Bytes per order, or per batch, tells you whether a deploy changed the cost of the workload.
• Buffer-full events as a share of writes. Sustained near parity, as above, is the case for a larger log buffer, and that is a restart.
• Full-page images as a share of records, watched across a checkpoint interval change. This is how you find out whether shortening the interval to smooth writes has quietly multiplied the volume.

Only the third of those survives the upgrade unchanged. The first is computed from bytes, which survives. The second is computed from the write count, which does not, and its replacement is a sum over the I/O view rows for the log object.

What it costs to collect

The counts cost nothing: the server maintains them regardless. Timing is gated by track_wal_io_timing, it is off by default, and it can be changed with a reload. Unlike the general I/O timing setting it covers only the log, so the overhead is bounded by how often the server writes the log rather than by how much the workload reads.

One caveat that applies to every counter on this page while you are still on 14. These numbers travel through the statistics collector, so a value read immediately after a reset can still be the pre-reset value for up to half a second, which is a property of the version rather than of the view. It matters here more than elsewhere because resetting the shared counters before a measured load is exactly the workflow this view invites.