Full-page image, and WAL amplification

A full-page image is a complete copy of an 8 kB page written into the write-ahead log instead of a description of the change. PostgreSQL writes one the first time a page is modified after each checkpoint, under full_page_writes. The reason is torn pages: if a page is half written when the machine loses power, recovery cannot apply an incremental change to it, but it can replace the whole thing. WAL amplification is the gap between the logical change and the volume this produces.

Why write volume is a sawtooth

The behaviour is per page and per checkpoint, so the cost is front-loaded. Immediately after a checkpoint every page a transaction touches is being touched for the first time, and each one costs a full page in the log. Minutes later the same workload writes a small fraction of that, because those pages have already been imaged. Graph write-ahead log bytes on a busy server and the sawtooth is visible without any other instrumentation.

This is the mechanism behind an unintuitive tuning result: making checkpoints less frequent reduces total write volume, sometimes dramatically, because it reduces how often the counter of first-touches resets. It also lengthens recovery. That trade is the real content of checkpoint_timeout and max_wal_size, and it is more important on a workload that updates scattered rows across many pages than on one that appends.

It also explains why random updates cost so much more than their size suggests. Changing one integer on a thousand different pages just after a checkpoint writes several megabytes; changing a thousand integers on one page writes a few kilobytes plus one image. The unit of cost is the page, which is the same reason a HOT update is cheap.

Measuring the gap on one statement

EXPLAIN reports the log a statement generated, and running the same update twice shows the difference the checkpoint makes.

CREATE TABLE ledger_entry (id int PRIMARY KEY, amount numeric);
INSERT INTO ledger_entry SELECT g, g FROM generate_series(1, 5000) g;
CHECKPOINT;
EXPLAIN (ANALYZE, WAL, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
  UPDATE ledger_entry SET amount = amount + 1;
EXPLAIN (ANALYZE, WAL, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
  UPDATE ledger_entry SET amount = amount + 1;

CREATE TABLE
INSERT 0 5000
CHECKPOINT
                        QUERY PLAN                         
-----------------------------------------------------------
 Update on ledger_entry (actual rows=0 loops=1)
   WAL: records=15070 fpi=43 bytes=1388544
   ->  Seq Scan on ledger_entry (actual rows=5000 loops=1)
(3 rows)

                        QUERY PLAN                         
-----------------------------------------------------------
 Update on ledger_entry (actual rows=0 loops=1)
   WAL: records=15124 bytes=1048223
   ->  Seq Scan on ledger_entry (actual rows=5000 loops=1)
         WAL: records=28 bytes=11512
(4 rows)

The same statement, run twice, against the same rows. The first logged 43 full pages and about a third of a megabyte more than the second, which logged none because every page it touched had already been imaged. That surcharge is what a checkpoint costs a workload, paid in the log rather than at the checkpoint itself, and it is invisible to anyone measuring only checkpoint duration. The capture is from 14.24; later majors print the same WAL: line with extra counters around it.

Deciding what to change

Reducing this volume is a checkpoint tuning problem, and the thing to establish before touching anything is whether your checkpoints are on the schedule or being forced early, which is what timed versus requested checkpoints separates. For where the write path counters live in the newer majors and which of them stopped being reported where a dashboard expects, read what PostgreSQL 18 changed in monitoring.