Write-ahead logging, recovery and durability

Why it matters

"Committed" is a promise that must survive a power cut. Keeping it cheaply is the job of the log, and the same log also feeds replication, change-data capture and point-in-time recovery. Understanding it explains everyday operational facts: commit latency follows disk flush latency, restarting after a crash can take minutes, and turning off fsync makes benchmarks fast and data unsafe.

Core ideas

Buffer policy. Pages are changed in the buffer pool and written back later. A steal policy lets the buffer manager write a page containing uncommitted changes, so recovery needs undo. A no-force policy lets a transaction commit without writing its dirty pages, so recovery needs redo. Steal plus no-force gives the buffer manager freedom and turns each commit into a small sequential log write, at the price of supporting both.

Two rules. Write-ahead: the log record describing a change must be durable before the changed page may overwrite its on-disk version. Commit: all of a transaction's log records, including the commit record, must be durable before commit is acknowledged. Every record has a log sequence number (LSN); every page stores the LSN of the last change applied to it (pageLSN), and the buffer manager flushes the log up to pageLSN before writing the page.

Checkpoints. Without them recovery would scan the whole log. A fuzzy checkpoint records the active transactions and the dirty pages, with the earliest LSN that might still need redo, without stopping normal work. Recovery starts from there, and older log segments can be recycled or archived. Frequent checkpoints shorten recovery but write more. PostgreSQL also logs a full image of each page on its first change after a checkpoint, so a torn, half-written page can be repaired from the log.

ARIES at intuition level.

1. Analysis: starting at the last checkpoint, rebuild the tables of active transactions and dirty pages.
2. Redo: from the smallest recovery LSN, reapply every logged change whose page does not yet reflect it (pageLSN smaller than the record's LSN), including changes of transactions that will be rolled back. This "repeats history".
3. Undo: roll back transactions without a commit record, newest change first, writing compensation log records (CLRs) so that a crash during recovery never undoes the same change twice. Repeating history first means undo always operates on a consistent state, which is what allows record-level locking and logical undo.

Durability assumptions. fsync (or fdatasync, or O_DSYNC writes) asks the OS to push data and required metadata to the device, and the device to make it persistent. Each link can fail: drives with volatile write caches that do not honor flush commands, file systems that reorder writes, and fsync errors after which the kernel has already dropped the dirty pages, so that a retried fsync "succeeds" without the data. PostgreSQL responded to the last case by treating an fsync failure as a crash (PANIC) and recovering from WAL instead of retrying. Replication changes the question from "on this disk" to "on how many machines", and waiting for a synchronous replica adds a network round trip to every commit.

Throughput knobs. Group commit flushes several transactions' commit records together, raising throughput while each transaction still waits for a flush. Asynchronous commit (synchronous_commit = off in PostgreSQL) acknowledges before the flush: the database stays consistent after a crash, but a short window of acknowledged transactions can be lost. fsync = off is different and dangerous: a crash can leave the database corrupted.

Other designs. SQLite's default rollback journal saves original page contents to a journal before changing the database file in place (undo logging). Its WAL mode appends new page versions to a separate file and copies them back at checkpoints (redo logging), so readers continue while one writer appends. LSM-tree engines log memtable inserts and treat their immutable sorted files as the durable state.

Common misconceptions

• "COMMIT returned, so the data is on disk." Only if the log was flushed and the device honored the flush; asynchronous commit and lying caches break this.
• "The WAL exists only for crash recovery." The same stream drives physical replication, point-in-time recovery and logical decoding.
• "Checkpoints make commits durable." Log records make commits durable; checkpoints bound recovery work and allow log recycling.
• "Retrying fsync after an error is safe." On several systems the failed write-back is forgotten and the retry reports success.
• "Recovery undoes losers first, then redoes winners." ARIES redoes everything first, then undoes losers.

How to approach it

Build the model on paper: a buffer pool, a log with LSNs and a crash at an awkward moment, then recover by hand. Read SQLite's atomic-commit page, which walks through every step and names each failure point. In interviews, answer durability questions along the whole chain (application, database log, OS, device, replicas) and name the setting you would change and what it costs. Storage and research roles may ask you to justify each LSN check or to evaluate a new logging scheme, so practice explaining why each rule exists.