The idea: log the change before the page
PostgreSQL never writes a changed data page at commit. It writes a short description of the change, a WAL record, to the write-ahead log and makes only the log durable. The 8 KB data pages are written later, in the background, in any order. After a crash, the log is replayed to bring the pages up to date. Two rules make this safe: a transaction's commit record is on disk before the client hears "COMMIT", and a page is never written before the WAL that describes its changes.
This page shows the PostgreSQL machinery. For the textbook version with an undo pass (ARIES, steal / no-force, CLRs), see Write-Ahead Logging and ARIES Crash Recovery.
WAL records and LSNs
A backend that changes a heap page does three things inside one critical section: it changes the page in shared buffers, calls XLogInsert to copy a record into the WAL buffers, and stamps the page with the record's position. A record has a header (resource manager such as Heap, Btree, Transaction or XLOG; the xid; the length; a CRC) and one or more block references: which relation, fork and block it changes. pg_waldump prints them like this:
rmgr: Heap len (rec/tot): 72/72, tx: 731, lsn: 0/010000A0, desc: UPDATE ..., blkref #0: rel 1663/5/16384 blk 0 FPW rmgr: Transaction len (rec/tot): 40/40, tx: 731, lsn: 0/010020E8, desc: COMMIT ...
An LSN (log sequence number) is a byte position in the WAL, printed as two hex halves, 0/10000A0. Each page header has a pd_lsn: the end of the last record that changed the page. Two other LSNs matter: the insert LSN (where the next record goes) and the flushed LSN (how far the WAL is safely on disk). On disk the WAL is a series of 16 MB segment files in pg_wal, named by timeline and position: 000000010000000000000001 holds LSNs 0/1000000 to 0/1FFFFFF.
The WAL buffers (wal_buffers, by default 1/32 of shared_buffers, up to 16 MB) are a ring in shared memory. Many backends insert into them at once: each reserves its byte range with a short spinlock and copies its record in parallel.
COMMIT means a flush
COMMIT inserts a Transaction COMMIT record and calls XLogFlush with its LSN. That takes WALWriteLock, write()s the WAL buffers up to the insert LSN into the segment file and calls fsync() (or whatever wal_sync_method says). Only then is the xid marked committed in pg_xact and the client told. The data page is still dirty in memory; no data file was touched. A COMMIT costs one sequential log write instead of many random page writes. Demo: UPDATE and COMMIT.
Group commit and commit_delay
An fsync takes from a few microseconds (battery-backed cache) to milliseconds (a plain disk). If every commit needed its own fsync, that would cap the commit rate. But a flush writes everything inserted so far, so one fsync can carry many backends' commit records. A backend that was waiting for WALWriteLock often finds, once it gets the lock, that its record has already been flushed by someone else. That is group commit, and it happens by itself under load.
commit_delay (microseconds, default 0) makes the leader sleep before flushing, so that more commit records join the same write. It only sleeps when at least commit_siblings (default 5) other transactions are active. The demo sets it to 1 because there are only three backends. Demo: group commit makes three commits durable with one fsync; with commit_delay = 0 the same thing takes two.
The WAL-before-data rule
Before the buffer manager writes a dirty page, it calls XLogFlush(pd_lsn). If the WAL is not yet flushed that far, the background writer, the checkpointer or a backend evicting the page must flush it first. So a page on disk never contains a change the log does not know about. The page may contain changes of a transaction that has not committed (PostgreSQL "steals" freely), and that is fine: after a crash that transaction has no COMMIT record and counts as aborted. Demo: WAL before data, crash.
Full-page writes and torn pages
PostgreSQL pages are 8 KB but the disk and the OS write in 4 KB (or 512-byte) units. A power failure in the middle of a page write can leave a torn page: half new, half old. A WAL record such as "add this tuple at line pointer 5" cannot be applied to a page whose header and line pointers are garbage.
So with full_page_writes = on (the default), the first change to each page after a checkpoint's REDO point logs a copy of the whole page, a full-page image (FPI). The test is simple: if the page's pd_lsn is not after RedoRecPtr, attach the image. Replay starts at the REDO point, so for every page it touches the first record carries an image, which is restored as is without even reading the torn copy; later records apply on top of it. Demo: torn page repaired by the FPI.
This is why the WAL volume jumps right after every checkpoint, and why a longer checkpoint_timeout means less WAL. wal_compression shrinks the images. Demo: full-page writes after a checkpoint shows the first UPDATE of block 0 after the checkpoint with +FPI and the second without. InnoDB solves the same problem with a doublewrite buffer instead (see How MySQL Runs a Query).
synchronous_commit = off
With synchronous_commit = off COMMIT inserts its record, marks the xid committed and returns without waiting for the flush. The walwriter flushes the WAL buffers every wal_writer_delay (200 ms), so a commit is durable after at most about three times that. A crash in between loses the last commits the clients were told about. But the database stays consistent: those transactions simply count as aborted, because the WAL-before-data rule (and a matching rule for pg_xact pages) stops any of their effects reaching disk without their log. It can be set per transaction, for writes that can be redone. Demo: synchronous_commit = off, crash.
| synchronous_commit | COMMIT returns after |
|---|---|
off | the record is in the WAL buffers (local flush happens later) |
local | the local fsync |
on (default) | the local fsync, and the synchronous standby's fsync if one is configured |
remote_write / remote_apply | the standby has written / replayed it |
fsync = off is a different thing: it skips the fsyncs entirely, and a crash can then corrupt the database. Never use it for data you want to keep.
Crash recovery: redo only
A checkpoint writes all dirty pages and then logs a checkpoint record; pg_control remembers its location and its REDO point (see PostgreSQL Checkpoints). After a crash the startup process reads pg_control and replays every record from the REDO point to the end of the valid WAL:
- a record with a full-page image restores the page;
- otherwise the page is read, and the record is applied only if the page's
pd_lsnis before the record (else the change is already there and is skipped); - COMMIT and ABORT records set the xid's state in pg_xact.
There is no undo phase, unlike ARIES. MVCC keeps old row versions in the heap and visibility comes from pg_xact, so a transaction with no COMMIT record is just treated as aborted: its new row versions stay in the pages, invisible, until VACUUM removes them (see pg_xact and Hint Bits and the heap page). Recovery ends with a checkpoint. The same replay code runs on a streaming standby, forever.
| PostgreSQL | ARIES (generic, InnoDB-like) | |
|---|---|---|
| Recovery passes | redo from the REDO point | analysis, redo, undo |
| Uncommitted changes on disk | left in place, invisible (xid aborted) | rolled back with undo records and CLRs |
| Torn pages | full-page images in the WAL | doublewrite buffer (InnoDB) |
| Cleanup of aborted versions | VACUUM, later | at once, by undo |
InnoDB solves the same problem with a doublewrite buffer instead: see InnoDB Doublewrite Buffer and Torn Pages. InnoDB's equivalent, with innodb_flush_log_at_trx_commit instead of synchronous_commit, is shown in InnoDB Redo Log Internals. MySQL needs two logs to agree, so its group commit is a three-stage pipeline: see MySQL Binlog Group Commit. InnoDB recovers differently: redo, then undo of unfinished transactions, with PREPARED ones settled by the binlog (see InnoDB Crash Recovery).
SQLite's WAL mode appends whole pages as frames and copies them back at checkpoints; see SQLite internals.
What the page leaves out
WAL page headers, records that cross WAL pages, record CRCs and the WAL insertion locks; segment switching, recycling and archiving (archive_command, PITR); wal_level and the extra information logical decoding needs (see Logical Decoding); the XLOG_CHECKPOINT_REDO record of PostgreSQL 17; index records, multi-block records and hint-bit FPIs with checksums on; the clog's "async commit LSN" guard; streaming replication and synchronous standbys; wal_compression. Here there is one table of three single-row blocks.