--- title: "Write-ahead log explained through one crash" description: "Follow one transfer through WAL records, dirty pages, commit, crash recovery, redo, undo, checkpoints, and group commit." canonical_url: "https://fanout.sh/blog/write-ahead-log-explained" md_url: "https://fanout.sh/blog/write-ahead-log-explained.md" last_updated: "2026-08-16" access: "public" --- # Write-ahead log explained through one crash Follow one transfer through WAL records, dirty pages, commit, crash recovery, redo, undo, checkpoints, and group commit. - Author: Suraj Gaud - Published: 2026-08-16 - Track: System design - Tags: write-ahead log, WAL, database recovery, transactions, ARIES, checkpoints, durability, system design A write-ahead log explained as "write to a log before the database" leaves out the two ordering rules that make recovery work. The database must make an update's log record durable before it flushes the changed data page. It must also make the transaction's commit record durable before telling the client that the commit succeeded. A transfer between two accounts exposes what each rule protects at four possible crash points. ## Write-ahead log explained by two durable boundaries Suppose account A has 500 credits and account B has 500. Transaction T moves 100 credits from A to B. The database changes cached copies of two pages in memory. It also appends log records describing A becoming 400 and B becoming 600, followed by a commit record. Appending is not the same as making durable. A log buffer in RAM and bytes accepted by the operating system can still disappear after a power loss. The first WAL boundary applies when a dirty data page leaves memory. The log must be durable through that page's update record before the page reaches persistent storage. The second boundary applies when the database answers the client. The log must be durable through T's commit record before the database reports success. The[PostgreSQL WAL introduction](https://www.postgresql.org/docs/current/wal-intro.html)states the first rule directly and explains why committed data pages need not be forced to disk at commit. These boundaries let the log and the data files advance at different speeds without making a successful commit disappear. ## Give every update a log sequence number Use a small ARIES-style example. The exact record format differs by engine, but the ordering idea is common. - LSN 10 records T changing A from 500 to 400. - LSN 20 records T changing B from 500 to 600. - LSN 30 records that T committed. Each data page stores a pageLSN for the newest logged update reflected on that page. After the in-memory changes, page A has pageLSN 10 and page B has pageLSN 20. The[ARIES paper](https://www.cs.cmu.edu/~15849g/readings/mohan92.pdf)uses these LSNs to compare a page on disk with the update records in the log. If the buffer manager wants to flush page B, durable WAL must already reach at least LSN 20. Flushing B while the log is durable only through LSN 10 would violate write-ahead logging. That restriction concerns page output. T can keep running while its update records remain in a memory log buffer, provided no dependent data page reaches disk first. ## Crash before the update log is durable First place the crash before LSN 10 reaches durable storage. The memory copies of A and B disappear with the process. Recovery finds no durable evidence of T. The data files still contain A=500 and B=500 because WAL prevented either changed page from being flushed first. T did not commit, so returning to the original balances is correct. The client must not have received a successful commit response because LSN 30 never became durable. A memory log buffer is useful for speed but cannot establish durability by itself. Without the page-flush rule, page A might contain 400 on disk while the only record needed to understand or reverse that change vanished in the crash. ## Crash after update records but before commit Now assume durable WAL reaches LSN 20, but the crash happens before a durable commit record. The buffer manager may already have flushed page A because its record at LSN 10 is safe. Page B may still contain 500 on disk. An ARIES-style recovery first repeats history. It can redo missing effects, including effects from transactions that were incomplete at the crash, until pages match the logged history. It then undoes loser transactions. T has no durable commit record, so recovery restores A=500 and B=500 and logs the compensation work. Redo before undo can sound wasteful. It reconstructs the exact pre-crash state first, which gives recovery one consistent basis for rolling incomplete transactions back. Not every database uses ARIES or stores both before and after values in one log. The conclusion is narrower: durable update records do not by themselves mean the client transaction committed. ## Crash after commit but before data pages Next let durable WAL reach LSN 30 before either changed data page is flushed. The database may now acknowledge T. A crash leaves A=500 and B=500 in the data files, but recovery sees the durable commit and replays both updates. The recovered state becomes A=400 and B=600. This is the main performance benefit of WAL. Commit waits for a compact sequential log flush rather than random writes for every table and index page touched by the transaction. PostgreSQL notes that one WAL sync can also commit several concurrent transactions. That technique is group commit: share one durable flush across multiple commit records. The acknowledgement rule is still exact. A server that answers before the commit record is durable has chosen weaker durability, regardless of whether it calls the operation a commit. ## Crash after only one data page is flushed Finally, let WAL be durable through LSN 30 and let page A reach disk with pageLSN 10. Page B remains at its older version. Recovery compares log records with pageLSNs. It skips the A update already present and reapplies the B update, producing A=400 and B=600. Redo must be safe to repeat. A physical after-image can be installed again, while a physiological operation is guarded by its page and LSN context. Blindly executing the business command "subtract 100" on every restart would be wrong. A second crash during recovery could subtract twice. ARIES records compensation actions during undo for the same reason. A later restart can tell which rollback work already happened instead of undoing it again. The log is an ordered recovery protocol, not merely an audit trail of application commands. ## Redo and undo depend on buffer policy Two buffer-management choices explain why recovery systems need redo, undo, or both. No-force means commit does not force every changed data page to storage. It improves commit latency but requires redo because committed changes may exist only in WAL at a crash. Steal means the buffer manager may flush a page changed by an uncommitted transaction to free memory. It improves buffer use but requires a way to remove uncommitted effects. ARIES supports steal plus no-force and therefore performs both redo and undo. PostgreSQL has a different transaction and storage design. Its[MVCC model](https://www.postgresql.org/docs/current/mvcc-intro.html)keeps row versions visible or invisible by transaction state. It does not follow this simplified ARIES cash-transfer sequence. SQLite's[WAL mode documentation](https://www.sqlite.org/wal.html)makes another distinction. Commits append to a separate WAL file, and checkpoints later copy pages back to the database file. The phrase "uses WAL" does not identify one universal recovery algorithm. Ask what reaches disk before acknowledgement, what can be flushed before commit, and how uncommitted state becomes harmless. ## Checkpoints bound replay work A log cannot grow forever if recovery must scan from its first record on every restart. A checkpoint records enough progress for recovery to begin from a later position. PostgreSQL describes a checkpoint as a point before which heap and index changes are guaranteed to be on disk. That does not mean normal processing stops until every later page is clean. Modern systems use fuzzy checkpoints that coexist with updates and record the dirty-page and transaction state recovery needs. The[PostgreSQL checkpoint documentation](https://www.postgresql.org/docs/current/wal-configuration.html)exposes the tradeoff. More frequent checkpoints reduce potential replay but create more page-write pressure. Full-page writes add another cost. The first change to a page after a checkpoint may log the whole page so recovery can repair a torn page after a system failure. Checkpoint tuning therefore connects recovery time, WAL volume, foreground latency, and storage headroom. "Checkpoint more often" is not a free reliability improvement. ## WAL does not replace every durability mechanism WAL handles the ordering between recovery records and data pages. It does not make one disk infallible or replicate a commit to another machine. A local fsync policy decides when the log is considered persistent. Replication policy decides whether another failure domain must confirm the record before the client receives success. Archiving the log supports point-in-time recovery only when the required base backup and every needed log segment remain available. Logical change data capture can read a database log, but a recovery log and an application event stream have different retention, schema, and consumer contracts. Fanout's[system design course](/system)treats logging alongside replication and failure recovery rather than as a substitute for them. The[systems paper reading list](/blog/100-papers-to-understand-software-and-computing)is a useful continuation for ARIES and other storage designs. ## Review a write path in this order Start with the acknowledgement point. Identify the exact record and storage boundary that must be durable before success is returned. Then inspect page flushing. For every dirty page, verify that WAL is durable through the page's newest logged change before that page can reach storage. Next, crash the sequence before the update log, before commit, after commit, and after a partial page flush. State the recovered result for each point. Name the redo and undo mechanism instead of assuming every WAL behaves like ARIES. Include pageLSNs, transaction status, MVCC visibility, or another engine-specific rule. Finally, account for checkpoints, log recycling, replicas, and archives. A correct local recovery sequence can still lose data through an early acknowledgement or missing retained segment. Operationally, a write-ahead log preserves enough ordered, durable evidence to make every allowed data-page state recoverable after a crash. --- This representation contains public Fanout content only. Protected Pro lessons, account data, billing, checkout, and pricing are not included. Browse the public content map: https://fanout.sh/sitemap.md