Write-ahead logging: the durability contract · PostgreSQL

Part VI established that a commit waits for one fsync. This part is about what that fsync is writing and why it is enough.

The rule

A change is written to durable WAL before the data page it describes is written to disk.

That is the whole contract, and everything else in this part is a consequence of it.

It works because the WAL is sequential and the data pages are not. One sequential write, synced once, records what happened to a hundred scattered pages. Those pages can then be written whenever it is convenient — by a checkpoint, by the background writer, or by a backend that needs the buffer — because if the server dies first, the WAL contains everything needed to redo them.

Read-only / Safethe contract, tested— Captured on 18.6. 300,000 rows inserted after a CHECKPOINT, then the container killed with SIGKILL — no clean shutdown, nothing flushed on the way down.

$ docker kill --signal=KILL rbpg-stor && docker start rbpg-stor

LOG:  database system was interrupted; last known up at 2026-08-27 21:01:01 UTC
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 7/CB35CEA8
LOG:  invalid record length at 7/CF4461A0: expected at least 24, got 0
LOG:  redo done at 7/CF446178 system usage: CPU: user: 0.09 s, system: 0.03 s, elapsed: 0.12 s
LOG:  database system is ready to accept connections

-- SELECT count(*) FROM crashtest;
300000

Every committed row survived. 65 MB of WAL was replayed in 0.12 seconds.

The line that looks like an error and is not

LOG:  invalid record length at 7/CF4461A0: expected at least 24, got 0

This is the normal end-of-WAL marker. Recovery reads forward until it finds something that is not a valid record, which is how it knows where the log ends. It appears in every crash recovery, it is logged at LOG rather than ERROR, and it means recovery completed correctly.

It is also the single most misread line in the PostgreSQL log. During an incident it looks exactly like WAL corruption, and people have restored from backup rather than let a working recovery finish.

The line that would indicate a real problem is one at PANIC or FATAL, or a redo that never reports redo done.

What full_page_writes protects against

A PostgreSQL page is 8 KiB. Storage typically writes in 512-byte or 4 KiB units. If the power fails mid-write, the page on disk can be part old and part new — a torn page.

WAL cannot repair that, because a WAL record describes a change to a page and assumes the rest of the page is intact. Applying “set byte 1200 to X” to a half-written page produces something worse.

full_page_writes solves it: the first modification of a page after each checkpoint writes the entire page to WAL. Recovery can then restore the whole page rather than patching it.

Read-only / Safewhat that costs, measured across a checkpoint boundary— Captured on 18.6. Three identical UPDATE statements over 20,000 rows, the first immediately after a CHECKPOINT.

$ psql -U postgres -c "CHECKPOINT" -c "UPDATE churn_default SET v=v+1 WHERE id BETWEEN 1 AND 20000"   # repeated, measuring pg_current_wal_lsn and wal_fpi

  round 1: WAL 9217 kB   full page images: 473
round 2: WAL 6123 kB   full page images: 2
round 3: WAL 6138 kB   full page images: 2

50% more WAL in the first round, and 473 full page images against 2.

Three consequences that come up repeatedly:

WAL volume is not a flat function of write volume. It spikes after every checkpoint. A monitoring alert on WAL rate will fire on the spike, not on a problem.

Shorter checkpoint intervals mean more spikes. This is the largest argument against setting checkpoint_timeout very low, and lesson XII-04 develops the trade.

Freezing is disproportionately expensive, because it modifies every page. Lesson VIII-05 measured a first freeze generating roughly the table’s own size in WAL, and this is the mechanism.

Turning off full_page_writes

It is legitimate only where the storage guarantees atomic 8 KiB writes — some enterprise arrays, some filesystems with data journalling, and ZFS with a matching record size. The PostgreSQL documentation is explicit that turning it off on ordinary storage risks unrecoverable corruption after a crash.

The failure mode is what makes this dangerous rather than merely risky: nothing goes wrong until a power failure, and then recovery produces a corrupt page rather than an error. There is no warning and no way to detect the exposure in advance.

If you believe your storage qualifies, the burden of proof is on the storage vendor’s documentation, in writing, naming 8 KiB atomicity. “It’s enterprise SAN” is not that.

wal_compression is the supported way to reduce the same cost:

ALTER SYSTEM SET wal_compression = 'lz4';   -- or pglz, or zstd

It compresses full page images specifically, trading CPU for WAL volume, and it does not touch the guarantee. On a workload dominated by full page writes it is often a large reduction for a small CPU cost.

The settings, and which ones void the contract

Setting	Trades	Voids the contract?
`synchronous_commit = off`	Loses recent commits on crash	No — bounded, recoverable
`wal_compression`	CPU for WAL volume	No
`wal_buffers`	Memory for fewer flushes	No
`commit_delay`	Latency for batching	No
`full_page_writes = off`	Torn page protection	Yes, on ordinary storage
`fsync = off`	The write-ahead rule itself	Yes

The first four are engineering decisions with understood costs. The last two are the removal of the property that makes recovery work, and lesson VI-06 made the distinction: synchronous_commit = off risks a bounded number of recent transactions, fsync = off risks unbounded corruption.

`wal_level`

Level	Records enough for	Cost
`minimal`	Crash recovery only	Least WAL
`replica` (default)	Crash recovery, archiving, physical replication	Moderate
`logical`	All of the above, plus logical decoding	Most

minimal is a genuine optimisation for a cluster that will never be replicated, archived or backed up physically — a build-time data transformation, a scratch instance. It also disallows WAL archiving and replication entirely, so a cluster set to minimal cannot have a standby added without a restart, and cannot be backed up with pg_basebackup.

logical is required for logical replication and for change-data-capture tools. It adds row-level detail to WAL and therefore volume, so setting it “just in case” is a real, ongoing cost.

Changing wal_level requires a restart. Choose it when the cluster is built, and revisit it only with a maintenance window.

What an LSN is, and the arithmetic worth knowing

A Log Sequence Number is a 64-bit byte offset into the notional infinite WAL stream, printed as two hexadecimal halves separated by a slash:

7/CF446178

The left half is the high 32 bits, the right the low 32 bits. They are not “file and offset” — that is a common misreading — although the file name is derivable from the value.

LSNs subtract, and the result is a byte count:

SELECT pg_size_pretty('7/CF446178'::pg_lsn - '7/CB35CEA8'::pg_lsn);
--  65 MB

That single expression answers most WAL questions:

-- how much WAL has this cluster generated since a point
SELECT pg_size_pretty(pg_current_wal_lsn() - '7/CB35CEA8'::pg_lsn);

-- how far behind is a replica
SELECT application_name,
       pg_size_pretty(pg_current_wal_lsn() - replay_lsn) AS replay_lag_bytes
  FROM pg_stat_replication;

-- how much WAL is a slot retaining
SELECT slot_name,
       pg_size_pretty(pg_current_wal_lsn() - restart_lsn) AS retained
  FROM pg_replication_slots;

The segment file containing an LSN follows from the WAL segment size, 16 MB by default. The mapping is available directly rather than by hand:

SELECT pg_walfile_name('7/CF446178');
--  0000000100000007000000CF

The name is three 8-character hex fields: timeline, high 32 bits of the LSN, and segment number within that. pg_controldata reports the same form for the segment recovery would start from:

Latest checkpoint's REDO WAL file:    0000000100000007000000D3

That file, and everything after it, is what must exist for the cluster to recover. It is the precise answer to “which WAL files can I delete”, and the answer is none of them without also accounting for replication slots and archiving, which lesson XII-03 covers.

What to take from this

The rule: WAL is durable before the data page it describes. That is what makes crash recovery possible.
Measured: 300,000 committed rows survived a SIGKILL; 65 MB replayed in 0.12 s.
invalid record length at the end of redo is normal, not corruption.
full_page_writes costs 50% more WAL in the round after a checkpoint. Use wal_compression rather than turning it off.
fsync = off and full_page_writes = off remove the guarantee. Everything else on the list trades latency or CPU.
An LSN is a byte offset. Subtracting two of them answers most WAL questions.

Cross-course references

Linux for Production Sysadmins — Part XIII (Disks and block devices) covers whether a flush reaches durable media, which is the assumption the whole contract rests on.
Ceph & Distributed Storage — Part XII (BlueStore) covers the same write-ahead idea implemented one layer down, and Part II (Storage performance fundamentals) covers measuring the flush latency it costs.