WAL & durabilityseniorFree end to end

WAL records: what an UPDATE writes, and the WAL-before-data rule

Simple terms

The rule is simple and it never bends: the description of a change reaches disk before the changed page does. Write ahead. That is what lets a crash be replayed from the log instead of leaving you with a torn data file. Each record is a small header plus the change itself, usually tiny, except for the first change to a page after a checkpoint, which drags a full copy of the page along with it. Which is why the same UPDATE can cost wildly different amounts of WAL depending on when it lands.

You might be asked

Walk me through what PostgreSQL writes to the WAL when I run a single UPDATE. What is a full-page image, why does the *first* write to a page after a checkpoint cost so much more WAL than the next one, and what is the durability rule that ties WAL flushing to data-page writes?

TopicWrite-Ahead Log: what each operation emits, full-page images, and the WAL-before-data rule
PostgreSQL12-17 (measured on 17.10; pg_walinspect requires 15+)
Tools usedDocker pgi17 (PostgreSQL 17.10, wal_level=replica), pg_walinspect, source @REL_17_10
Last reviewed2026-06-19

How you’d answer it

Every change is described by one or more WAL records before the modified data page is allowed to reach disk. The source states it plainly: 'log updates must hit disk before any of the data-file changes they describe do'.

A record itself is small. It carries a header, resource manager id, xid, length, CRC, plus an rmgr-specific payload. One autocommit UPDATE produces a Heap/HOT_UPDATE record and a Transaction/COMMIT record.

The size surprise comes from checkpoints. The first write to a given page after a checkpoint also embeds a full-page image (FPI): the entire 8 KB page, so recovery can repair a torn page rather than try to patch a half-written one with a delta.

You can watch the cost collapse. On pgi17 the first UPDATE logged 7229 bytes (fpi_length=7164); the very next UPDATE to the same page logged 168 (fpi_length=0).

What the docs say

WAL is an append-only stream of records, and every record starts with the same small header before its payload: a total length, the transaction id, a back-pointer to the previous record, some flag bits, a CRC for integrity, and, the important field, a resource-manager id. That id says which subsystem owns the record. Heap changes belong to the "Heap" resource manager, commits to "Transaction," B-tree changes to "Btree," and so on; each resource manager knows how to both describe its records and replay them.

So "what does a single UPDATE write?" has a concrete answer. An autocommit UPDATE produces a Heap record describing the row change, followed by a Transaction COMMIT record that makes the change durable and visible after replay. Two records, two resource managers, for one statement.

The reason the first write to a page after a checkpoint costs so much more WAL is full-page images. The on-disk page could be torn, partially written, if the machine crashes mid-write, and an ordinary delta record assumes the page underneath it is intact. To keep recovery safe, the first modification of a page after each checkpoint carries the entire 8 KB page in its WAL record (a flag on the record marks that it has an image).

During replay PostgreSQL simply stamps that whole page down, sidestepping any tear, and later delta records apply on top. After the next checkpoint the cycle resets. This is the dominant source of WAL volume, which is why full_page_writes and checkpoint frequency are the two dials that drive WAL throughput.

Underneath all of it is one durability rule that ties WAL to data pages: a change's WAL record must reach disk before the dirty data page it describes is allowed to. When PostgreSQL flushes a data page it first reads that page's LSN and forces the WAL out to at least that point, so the log always leads the data files. The manual states it plainly, log updates must hit disk before any of the data-file changes they describe do.

That ordering is what makes crash recovery work: replay the log forward from the last checkpoint and the data files are reconstructed deterministically, every time.

References

  • PostgreSQL 17 docs: 'Reliability and the Write-Ahead Log' (WAL Configuration, full_page_writes, wal_compression); pg_walinspect module; pg_waldump. Source @REL_17_10: src/include/access/rmgrlist.h (resource manager table), src/include/access/xlogrecord.h (XLogRecord + block/image headers, BKPBLOCK_HAS_IMAGE), src/backend/storage/buffer/bufmgr.c FlushBuffer (the WAL rule + XLogFlush interlock). Reproduce locally: concepts/wal-records/demo.sh on a 15+ instance with pg_walinspect.

What the code does

REL_17_103 files · 4 line references, read at the pinned tag
rmgrlist.h49 lines
  • L43the PG_RMGR table, XLOG L28, Transaction L29, Heap2 L37, Heap L38, Btree L39, Sequence
xlogrecord.h248 lines
  • L47XLogRecord struct L41 with xl_tot_len L43, xl_xid L44, xl_prev L45, xl_info L46, xl_rmid
  • L198full-page-image machinery, XLogRecordBlockImageHeader L141 and BKPBLOCK_HAS_IMAGE 0x10
bufmgr.c6181 lines
  • L3903-3904FlushBuffer L3840, recptr = BufferGetLSN(buf) L3880, the WAL-rule comment L3887-3889 ('log updates must hit disk before any of the data-file changes they describe do'), guarded XLogFlush(recptr) under BM_PERMANENT

Proof from a real run

Captured run summary

Box pgi17 / PostgreSQL 17.10, wal_level=replica, via pg_walinspect (concepts/wal-records/demo.sh). Preload wal_test(id,pad) with 50 rows, CHECKPOINT (so the next write must carry an FPI), then capture pg_current_wal_lsn() around each step. STAGE A, first UPDATE of id=1 after the checkpoint: pg_get_wal_records_info shows ONE Heap/HOT_UPDATE record, record_length=7229, main_data_length=14, fpi_length=7164 (carried_full_page_image = t). STAGE B, second UPDATE of the same row, no checkpoint between: Heap/HOT_UPDATE, record_length=168, fpi_length=0 (f). Same logical change, ~43x less WAL, purely because the page no longer needs a full-page image.

STAGE C, BEGIN; INSERT; DELETE; COMMIT: the window contains Heap/INSERT (160 B), Heap2/PRUNE_ON_ACCESS (62 B, opportunistic page pruning piggy-backed on access), Heap/DELETE (54 B), Transaction/COMMIT (34 B). STAGE D, pg_get_wal_stats over the whole window: Heap 4 records, record_size=447, fpi_size=7164, combined=7611; Transaction 3 records 102 B; Heap2 1 record 62 B, the lone FPI dwarfs every delta combined. STAGE E, CHECKPOINT then inspect: the checkpoint itself emits XLOG/CHECKPOINT_REDO and XLOG/CHECKPOINT_ONLINE records.

Using it under pressure

A situation where it matters

A team complains that WAL volume (and therefore archive/replication bandwidth and pg_wal size) spikes hard right after each checkpoint and then tapers, even under steady write load. That sawtooth is full-page images: immediately after a checkpoint, every page touched for the first time logs its full 8 KB; as the same hot pages get re-touched within the checkpoint interval, later writes are cheap deltas.

Spreading checkpoints further apart (larger max_wal_size, higher checkpoint_timeout, checkpoint_completion_target near 0.9) means each page pays the FPI tax less often, lowering total WAL, at the cost of longer crash recovery and a bigger pg_wal. wal_compression=on (or a column/zstd method) shrinks those FPIs directly. The demo's 7164-byte FPI vs 0-byte second write is exactly this effect in miniature.

The call you would make

Three design choices show up in the measurements.

  1. 1

    FPIs trade WAL volume for torn-page safety: rather than depend on the storage layer to never tear an 8 KB write, PostgreSQL logs the whole page once per checkpoint so replay can always start from a known-good image.

  2. 2

    The WAL-before-data interlock (FlushBuffer → XLogFlush before writing the page) is what lets recovery be 'redo-only from the last checkpoint', if a data page made it to disk, its WAL is guaranteed already there.

  3. 3

    Resource managers modularize the log: heap, btree, gin, sequence, etc. each define their own record formats and redo handlers, so adding an access method doesn't change the WAL framework. Note Heap2/PRUNE_ON_ACCESS in STAGE C: even a plain SELECT/DML touch can opportunistically prune a page and that pruning is itself logged, reads are not always WAL-free.

When it goes wrong

To attribute WAL growth, use pg_walinspect: pg_get_wal_stats(start,end) breaks the stream down by resource manager with record_size vs fpi_size, so you can see whether bytes are real row changes or full-page images (in the demo, fpi_size 7164 vs record_size 447 = 94% of WAL was one FPI).

If fpi_size dominates, the levers are checkpoint spacing and wal_compression, not query tuning. For a single suspicious operation, pg_get_wal_records_info between two pg_current_wal_lsn() snapshots lists every record it produced (note: the start LSN must sit on a record boundary, a value returned by pg_current_wal_lsn() always does). Unexpected Transaction/COMMIT records per statement usually mean autocommit; batching into one transaction collapses N commit records into one. On a replica lagging, the same per-rmgr view tells you whether the master is shipping deltas or FPI-heavy WAL.

What people get wrong

The big trap: 'the same UPDATE always costs the same WAL.' It does not, the first write to a page after a checkpoint embeds a full-page image and can be 40x+ larger than an identical write moments later (7229 B vs 168 B here). Benchmarks that ignore checkpoint timing will mis-measure WAL. Second trap: people assume reads never write WAL, but opportunistic pruning and hint-bit/visibility maintenance can emit records (STAGE C's Heap2/PRUNE_ON_ACCESS).

Third: pg_get_wal_records_info errors if start_lsn isn't a record boundary; feed it values from pg_current_wal_lsn() (or a known record start), not arbitrary offsets. Fourth: FPIs are tied to full_page_writes=on (the safe default), turning it off shrinks WAL but is only safe on storage that guarantees atomic 8 KB writes.

Version notes

pg_walinspect (the pg_get_wal_records_info / pg_get_wal_stats functions used here) was added in PostgreSQL 15; before that you used the external pg_waldump CLI for the same information. The WAL record format shown (XLogRecord + block headers + FPI flag) has been stable since the rewrite in 9.5. Record-type *names* evolve: in 17 you see Heap2/PRUNE_ON_ACCESS (the on-access page-pruning record was renamed/reworked around 16-17; older majors logged pruning differently). The WAL-before-data interlock in FlushBuffer is long-standing. wal_compression gained per-method options (pglz/lz4/zstd) in 15. Always check the running major's pg_walinspect docs for exact column names, e.g. the stats grouping column is literally 'resource_manager/record_type'.

How this was verified

This concept is free end to end. The manual section is grounded in the official PostgreSQL documentation, the source walk was navigated in postgres/postgres at a version-pinned tag, and the evidence is labeled as raw output or a captured run summary. Where the text distinguishes mechanism (from docs/source) from consequence (measured), that boundary is kept explicit.

Connected

Where this concept connects

How this concept links across the library, the interview questions that test it, its plain-English glossary definition, and the guided pathways it belongs to. Open the full map to explore further.

Open in the interactive map →
← All interview concepts