Write-Ahead Log Replication

Your database is updating a row. It is halfway through modifying a B-tree index page and the data page when the server's power supply dies. The machine goes dark instantly. No graceful shutdown, no flushing buffers, just an immediate halt.

When the server reboots, the database's files are in an inconsistent state. The index page was modified but the data page was not. Or maybe both were partially modified. Reading them would return garbage. Your B-tree index now points to a row that does not exist, or worse, to a row that belongs to a different table.

The Write-Ahead Log (WAL) prevents this disaster. Before modifying any data page, Postgres first writes a description of the change to the WAL, a sequential, append-only log on disk. Only after the WAL record is safely on disk does Postgres modify the actual data pages. This principle was formalized by C. Mohan at IBM Research in the ARIES paper (1992), and every modern database implements some variant of it.

If the server crashes, Postgres replays the WAL on startup. Changes written to the WAL but not yet applied to data pages get replayed. Changes not in the WAL were never committed, so they are discarded. The database is always recoverable to a consistent state. Always.

This same WAL is what powers Postgres replication. The primary streams its WAL to replicas, and replicas replay the same records against their own data files. Crash recovery and replication are literally the same mechanism. That is a clean design, and it is the reason Postgres replication is so reliable.

The WAL is a sequence of records, each describing a low-level modification to a database page. It operates at the physical level. Not "UPDATE users SET name = 'Alice'" but "write these 42 bytes at offset 2048 in file base/16384/16389."

WAL record structure:

• LSN (Log Sequence Number). A monotonically increasing 64-bit number that uniquely identifies the record's position in the WAL. Used to track replication progress, checkpoint positions, and recovery targets.
• Transaction ID. Which transaction produced this change. Used during recovery to determine which transactions committed and which should be rolled back.
• Resource manager. Which subsystem (heap, btree, hash, sequence, transaction) knows how to replay this record. There are about 20 resource managers in Postgres, each handling its own record types.
• Record data. The actual bytes to write, the page they apply to, and enough information to redo the operation from scratch.

WAL records are written to segment files, each 16MB by default (configurable at initdb time). Files are named with their LSN range: 000000010000000000000001, 000000010000000000000002, etc. The pg_wal directory on a busy Postgres instance might have hundreds of these files.

The WAL guarantee: A transaction is considered committed when its COMMIT record is flushed to the WAL on disk via fsync. At that point, even if the server loses power one nanosecond later, the transaction's changes can be recovered by replaying the WAL.

How WAL interacts with data pages:

1. Transaction modifies rows in shared memory (buffer cache)
2. WAL records are written to WAL buffers
3. On COMMIT, WAL buffers are flushed to disk (fsync)
4. Dirty data pages in buffer cache are written to disk later (by the background writer or checkpointer)
5. If crash happens between steps 3 and 4, WAL replay reconstructs the dirty pages

This "write the log first, data later" pattern is why it is called Write-AHEAD Log. The log is always ahead of the data.