Write-ahead logging

Why a database survives a power cut in the middle of a save

With write-ahead logging (WAL), the database appends a note of each change to a separate log file, and that note reaches disk before the data page does. So a crash between a transfer's two page writes can't leave a mess.

Each log record gets a rising number, its log sequence number (LSN), and each page stores the LSN of its last change. A transaction is committed once its commit record is flushed, meaning confirmed as written to disk.

The changed pages themselves can stay in memory and be written out later.

Write-ahead logging

PostgreSQL's pg_wal folder and SQLite's .db-wal file

PostgreSQL writes every change to WAL files in its pg_wal directory before it updates its table files. After a crash it replays them on startup, and it streams the same records to replica servers.

SQLite, built into many phone and desktop apps, has a WAL mode. Turn it on and app.db-wal appears next to app.db. Changes are appended there and copied into app.db later, in a step called a checkpoint.

Write-ahead logging

Jot the change in a notebook first, tidy the shelves later

Commits are fast because each needs one flush of records added to the log's end, not many scattered page writes. Several transactions can share one flush. Pages are written lazily later, and the LSN rule keeps that safe.

After a crash, recovery starts at the last checkpoint, a marker meaning everything logged before it is already in the data files. It redoes logged changes missing from pages, then undoes transactions with no commit record.

So committed work survives (durability) and half-finished work disappears (atomicity). Checkpoints also let old log files be deleted, which keeps the replay short.