Write-ahead logging (WAL)

Why the log is written before the data pages

Write-ahead logging turns every change into two writes: a small record appended to a log, and the update to the data page where the row lives. The rule that gives WAL its name orders them. A change to a data file may be written to disk only after the log record describing it has been flushed to durable storage. The log goes ahead of the data.

Each log record carries a monotonically increasing Log Sequence Number (LSN), and each data page remembers the LSN of the last change applied to it. Before writing a page out, the engine flushes the log at least up to that page's LSN.

Wrong "The engine writes the row to its page, then logs that it did." A crash between those steps leaves a page change on disk with no log record behind it, and recovery can neither confirm a committed change nor reverse an uncommitted one. Right log record durable first, data page later or not until a checkpoint.

Why the commit fsync is what makes data durable

Durability, the D in ACID, means a committed change survives power loss and OS failure. A plain write() does not deliver that. It hands bytes to the operating system's page cache, which a power cut discards. Only fsync() forces those bytes past the OS and device caches onto stable media.

So a commit is not acknowledged until the log has been flushed through the transaction's COMMIT record:

append COMMIT record to the in-memory log
flush (fsync) the log up to and including that record
only now return success to the client

MySQL's innodb_flush_log_at_trx_commit exposes this. The default 1 fsyncs the redo log at every commit for full durability. Values 2 and 0 skip the per-commit fsync and can lose about a second of committed work on a power or OS crash. Faster commits, weaker guarantee.

Why WAL speeds up commits instead of slowing them

A common worry is that logging every change writes the data twice and must be slower. Commit throughput improves. The data pages a transaction touches are scattered across the disk, so forcing them all at commit would be many random writes. The log is one file written sequentially, so a commit forces only that append and defers the random data-page writes to be batched later. Sequential I/O is far cheaper than random I/O, and one fsync of the log can commit many concurrent transactions at once (group commit).

How crash recovery replays the log

On restart the engine walks the log to rebuild a consistent state. Committed changes that never reached their data pages are re-applied (redo). Changes from transactions that never committed but whose pages did reach disk are rolled back (undo). Both are possible because the log was made durable ahead of the pages, so it records what to replay and what to reverse.

Why crash recovery needs both redo and undo

The two recovery directions fall out of how the buffer pool behaves. WAL makes a STEAL + NO-FORCE buffer pool safe, and each half creates one obligation. NO-FORCE means a committing transaction does not force its dirty pages to disk, only its log, so committed changes may be missing from the data files and must be redone from the log. STEAL means the pool may evict an uncommitted transaction's dirty page to reclaim a frame, so a change that never committed may already sit on disk and must be undone. STEAL + NO-FORCE buys the best runtime throughput and pays for it with both directions.

How ARIES recovers in three passes

ARIES (the recovery model most engines follow) runs analysis, redo, undo. Analysis starts at the last checkpoint and rebuilds the in-flight transactions and dirty pages. Redo repeats history, reapplying logged changes from the earliest dirty-page LSN forward, including those of transactions that will later be undone. Undo rolls back the losers, walking each one's records backward via prevLSN.

Two details keep recovery restartable if it crashes mid-run. Redo is idempotent: each page stores the LSN of its last change, so a record already reflected there is skipped and nothing applies twice. Undo logs a compensation record per reversal, and those are never themselves undone, so a second crash does not re-undo reversed work.

Checkpoints and the recovery-time trade-off

A checkpoint flushes dirty pages and records a log position so recovery starts there, not at the log's beginning. That bound is a trade-off. Postgres checkpoints fire every checkpoint_timeout (5 min) or when max_wal_size (1 GB) is about to be exceeded, whichever comes first; frequent checkpoints shrink recovery time but raise steady-state I/O, and checkpoint_completion_target (0.9) spreads the flush across most of the interval. InnoDB's redo log is a fixed-capacity ring whose checkpoint advances as pages flush; if it fills faster than checkpoints free it, InnoDB throttles by flushing harder (furious flushing).

Two failure modes hide here. A torn page write is guarded differently by each engine: Postgres logs a full page image to the WAL on the first change to a page after a checkpoint (full_page_writes), while InnoDB writes each page to a separate doublewrite buffer before its data file and recovers the clean copy from there. (Default page size is 8 KB in Postgres, 16 KB in InnoDB.) And a drive that lies about its write cache without a battery-backed unit can drop an fsync'd record on power loss, so honest fsync is a hardware assumption.

Postgres WAL vs MySQL binlog

The headline difference is one log versus two. Postgres uses a single WAL for both crash recovery and physical replication: streaming replication ships the same byte-level WAL a standby replays continuously, so primary and standby must share major version and architecture (a hot standby also serves reads). That same WAL can be decoded into row-level events for logical replication.

MySQL splits the roles. The InnoDB redo log is physical, storage-engine-internal, fixed-size, and used only for crash recovery; it is never shipped. Replication and point-in-time recovery use the binlog, a logical server-layer log of change events (ROW format by default, plus STATEMENT and MIXED). Internal two-phase commit ties the two so a commit lands in both logs or neither, and sync_binlog=1 fsyncs the binlog per commit.

Wrong "InnoDB recovers from the binlog." Crash recovery is the redo log's job; the binlog carries replication and PITR. Conflating the recovery log with the replication log is the WAL mistake interviewers probe most.