WAL and recovery

Full-page writes

A crash in the middle of writing an 8 kB page can leave it half old and half new. To repair such a "torn page", PostgreSQL logs a full copy of each page the first time it changes after a checkpoint.

In plain words

PostgreSQL stores tables in 8 kB pages, but the operating system and disk usually write in smaller pieces (4 kB or 512 bytes). If the power fails in the middle of writing a page, the file can end up with a page that is half new and half old: a torn page. The small "change notes" in the WAL cannot repair it, because they assume the page they are applied to is intact.

The fix is simple: the first time a page is changed after a checkpoint, the WAL record carries a complete photo of the page (a full-page image). During crash recovery that photo is laid down first, so whatever state the disk copy was in no longer matters, and the later small notes are replayed on top.

The price is extra WAL: right after every checkpoint, many pages are touched for the first time, so the amount of WAL written jumps and then settles. Fewer, longer-spaced checkpoints and wal_compression reduce that cost. Turning full_page_writes off is only safe on storage that guarantees 8 kB atomic writes.

For DBAs and operators

When an image is logged

When a backend modifies a buffer, it compares the page LSN with the current checkpoint's REDO pointer. If the page has not been WAL-logged since then, the record includes a backup block (full-page image, FPI). pg_waldump marks it with FPW; the hole between pd_lower and pd_upper is skipped, so a half-empty page costs less than 8 kB.

rmgr: Heap  len (rec/tot): 73/7045, tx: 753, lsn: 0/017AEF08, desc: UPDATE old_xmax: 753, old_off: 1, …,
      new_off: 193, blkref #0: rel 1663/5/16384 blk 8 FPW, blkref #1: rel 1663/5/16384 blk 0
rmgr: XLOG  len (rec/tot): 49/8209, tx: 753, lsn: 0/017B8290, desc: FPI_FOR_HINT ,
      blkref #0: rel 1663/5/16384 blk 1 FPW

Measuring it

SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) FROM pg_stat_wal;
-- per statement:
EXPLAIN (ANALYZE, WAL) UPDATE t SET v = v WHERE id < 1000;
--   WAL: records=3014 fpi=15 bytes=318717
-- per record type:
pg_waldump --stats=record -p $PGDATA/pg_wal 000000010000000000000014

A high wal_fpi share is normal after checkpoints. If WAL volume is a problem, first lengthen checkpoint_timeout/max_wal_size (fewer first-touches), then enable wal_compression (lz4 or zstd since PostgreSQL 15; on means pglz). Compression applies only to FPIs and costs some CPU.

Hint bits, checksums and wal_log_hints

Setting a hint bit (for example "this inserting transaction committed") dirties a page without a WAL record. With data checksums that is dangerous: a torn hint-bit write would fail verification. So when checksums or wal_log_hints are on, the first hint-bit-only change after a checkpoint logs an FPI_FOR_HINT record. Since PostgreSQL 18, initdb enables data checksums by default (opt out with --no-data-checksums); pg_upgrade needs both clusters to match, so upgrades from older non-checksum clusters must use --no-data-checksums or enable checksums offline with pg_checksums. pg_rewind requires one of the two.

What a torn or corrupt page looks like

LOG: page verification failed, calculated checksum 20919 but expected 15254ERROR: invalid page in block 7 of relation "base/16384/16385"

(In PostgreSQL 18 the checksum mismatch goes to the server log at level LOG; the client sees the ERROR.)

With full_page_writes = on, crash recovery repairs torn pages silently. Turning it off makes undetected corruption after an OS crash or power loss possible; it is only reasonable on filesystems that write 8 kB atomically (for example ZFS with copy-on-write). Base backups force full-page writes on while they run, regardless of the setting.

On standbys and in PITR the same images are what let recovery start from a base backup taken while pages were being modified.

For developers: inside the source

Where it lives

xloginsert.c: XLogRecordAssemble() makes the decision and builds the image; XLogCompressBackupBlock() applies wal_compression; XLogSaveBufferForHint() and log_newpage() are the special cases.
xlog.c: GetFullPageWriteInfo(), the recheck in XLogInsertRecord(), Insert->fullPageWrites / runningBackups, and UpdateFullPageWrites(), which logs XLOG_FPW_CHANGE when the setting changes.
xlogrecord.h: XLogRecordBlockImageHeader (length, hole_offset, bimg_info with BKPIMAGE_HAS_HOLE, BKPIMAGE_APPLY, BKPIMAGE_COMPRESS_PGLZ/LZ4/ZSTD).
xlogutils.c and xlogreader.c: replay restores the image with RestoreBlockImage().

The decision, per registered buffer

if (regbuf->flags & REGBUF_FORCE_IMAGE)     needs_backup = true;
else if (regbuf->flags & REGBUF_NO_IMAGE)   needs_backup = false;
else if (!doPageWrites)                      needs_backup = false;
else needs_backup = (PageGetLSN(page) <= RedoRecPtr);

doPageWrites is fullPageWrites || runningBackups > 0, which is why base backups force images regardless of the setting. RedoRecPtr is read without a lock in XLogInsert(); once XLogInsertRecord() holds a WAL insertion lock it re-checks, and if a checkpoint started in between and a page without an image is now "first touch" (fpw_lsn <= RedoRecPtr), it returns InvalidXLogRecPtr and the record is rebuilt. With REGBUF_STANDARD the free space between pd_lower and pd_upper is cut out as the "hole". The image only replaces the page at replay when BKPIMAGE_APPLY is set; images taken only for wal_consistency_checking are not applied (pg_waldump shows them as FPW for WAL verification).

Hint bits

bufmgr.c: MarkBufferDirtyHint() calls XLogSaveBufferForHint() (record XLOG_FPI_FOR_HINT) when XLogHintBitIsNeeded(), i.e. DataChecksumsEnabled() || wal_log_hints, for permanent buffers outside recovery. The backend sets DELAY_CHKPT_START around it, so a checkpoint cannot complete between writing that record and marking the buffer dirty. Checksums are computed at write time by PageSetChecksumCopy() in FlushBuffer() and verified by PageIsVerified() (bufpage.c, algorithm in checksum_impl.h).

Observe and experiment

pg_waldump --save-fullpage=DIR writes every image to a file (test: src/bin/pg_waldump/t/002_save_fullpage.pl); pg_get_wal_block_info() in pg_walinspect returns block_fpi_length, block_fpi_info and block_fpi_data.
EXPLAIN (ANALYZE, WAL) and pg_stat_wal.wal_fpi read the WalUsage.wal_fpi counter.
pageinspect's page_checksum() computes a page's checksum; amcheck and pg_checksums --check verify relations.

Parameters that shape this

full_page_writes wal_compression wal_log_hints data_checksums checkpoint_timeout max_wal_size