A database that updated pages in place on every commit would be both slow and unsafe. Slow, because the pages a transaction touches are scattered across storage. Unsafe, because a crash in the middle of that scatter leaves some pages updated and others not, with no record of which.

The write-ahead log removes both problems with one rule: before a change is applied to the data pages, a description of that change is appended to a sequential log and made durable. Commit means the log record is safe, not that the data pages are up to date.

Why appending is cheaper

  • Sequential writes avoid the seek and write-amplification costs of scattered page updates.
  • One flush can cover many transactions, so commits can be grouped.
  • Recovery only has to read forward, never search.

Recovery in three passes

After a crash, the engine reads the log from the last checkpoint. It works out which transactions were in flight, replays every logged change to bring pages back to the state at the moment of the crash, then undoes anything that belonged to a transaction that never committed. Replay has to be idempotent, because a crash during recovery means doing it all again.

text
analysis  find in-flight transactions from the last checkpoint
redo      replay all logged changes, committed or not
undo      roll back whatever never committed
Checkpoints do not make the log unnecessary. They bound how much of it recovery has to read.

The fsync question

The log is only durable once the operating system and the device have actually persisted it. A write that returns after landing in a page cache has not survived power loss. This is the honest cost of a commit, and it is why engines offer group commit, why synchronous commit is sometimes relaxed for throughput, and why that relaxation is a data-loss window measured in milliseconds, not a free win.

The same idea, everywhere

Once you recognise the shape, it appears far outside databases. Journaling filesystems log metadata before applying it. Replicated systems ship the log itself and let followers rebuild state by replaying it. Event-sourced applications treat the log as the system of record and the tables as a cache. In each case the log is the truth and the structure you query is a derived, rebuildable view of it.