Database Write-Ahead Logs and Crash Recovery

23 Oct 2026 databases

A write-ahead log (WAL) is a sequential record of database changes that is written to durable storage before the changed data pages are allowed to be written there. After a crash, the database reads this log to determine which changes to redo and which incomplete changes to undo.

The WAL is the database's durable history of change. The data files hold the current pages; the log explains how those pages got there and how to repair them if a failure interrupts that process.

The problem: data pages do not reach disk atomically

A database usually does not update a table's disk page immediately for every UPDATE. It changes a copy of the page in memory, called a buffer page, and writes that page back later. Batching writes is much faster than performing a storage operation for every row change.

That creates several failure cases:

• The process may crash after changing a page in memory but before writing it to disk.
• It may write some changed pages but not others.
• A machine may lose power while storage is writing a page, leaving a partially written or otherwise damaged page.
• A transaction may have changed many pages when only some of them have reached storage.

Without a log, the database cannot reliably tell which changes happened, which were committed, or how to finish or reverse a partly applied operation. A committed payment could disappear, or one side of a transfer could remain without the other.

The WAL turns this into a recoverable ordering problem: record the intended change first, then allow the data page to be written.

Write-ahead rule: before a modified data page is flushed to durable storage, every log record needed to describe that page's changes must already be durable.

“Durable” here means that the storage system has acknowledged the data in a way that survives the failure model the database promises to handle. A write that exists only in the process, operating-system cache, or a storage device's volatile cache is not necessarily durable. Database systems therefore use a flush or synchronization operation at important points, although the exact mechanism depends on the operating system and storage hardware.

What a log record contains

A log record identifies enough information to reconstruct or reverse a change. It commonly includes a transaction identifier, the affected page or record, and either the new value, the old value, or both. A record may also carry a log sequence number (LSN), a monotonically increasing position that lets the database compare the age of log records and pages.

For example, an update might be represented conceptually as:

transaction: 42
page: accounts page 817
row: account 7
old balance: 100
new balance: 70

Real systems often use compact, page-oriented records rather than this readable form. The important property is not the format; it is that recovery can interpret the record after the original process is gone.

A typical transaction follows this sequence:

1. The database changes a page in memory.
2. It creates a WAL record describing that change.
3. It assigns the record an LSN and appends it to the log buffer.
4. Before flushing the changed page, it flushes the relevant log records to durable storage.
5. When the transaction commits, the database usually flushes its commit record too.
6. The changed data pages can be written later, in batches.

The second rule is especially important: a commit acknowledgement normally means the commit record itself has reached durable storage, not necessarily that every modified table page has. This is why a database can acknowledge a transaction quickly while postponing scattered data-page writes.

Why the ordering matters

Suppose a transaction changes a balance from 100 to 70. The database may have the new value in memory while the disk still contains 100. If power fails, that is safe only if the WAL already contains a durable description of “change 100 to 70.” Recovery can then apply that change again.

If the disk page containing 70 is written first and the machine fails before the log record is durable, recovery has no dependable explanation for the page. The database has violated write-ahead logging and may be unable to establish whether the value is valid, committed, or part of an abandoned transaction.

The log is also what permits a steal policy: the database may flush a page containing changes from a transaction that has not committed yet. Stealing memory pages improves buffer use, but it means recovery must later undo uncommitted changes. WAL makes that possible because the old values or an equivalent undo description are preserved.

Recovery after a crash

On startup, the database finds the relevant portion of the log, often using a checkpoint, which is a saved recovery position and summary that reduces how far back scanning must begin. It then determines the state of transactions and pages at the time of failure.

Recovery commonly has three conceptual phases, though implementations differ:

1. Analysis: identify transactions that committed, transactions that were still active, and which pages may need attention.
2. Redo: replay durable changes that might be missing from data pages. Redo is normally safe to repeat because the database checks page LSNs or equivalent metadata and avoids applying a change that is already present.
3. Undo: reverse changes belonging to transactions that never committed. This removes the effects of work that was interrupted by the crash.

Consider two transactions in the log. Transaction A commits, but its changed page was still only in memory when power failed. Transaction B changed a page that happened to be flushed, but then crashed before committing. Recovery redoes A's logged change and undoes B's change. The resulting pages represent the committed state, regardless of which writes reached disk before the crash.

Some systems use log records designed for both redo and undo; others use variations such as compensation records that document an undo operation itself. The implementation details vary, but the invariant remains: the log is durable before dependent pages, and recovery uses that durable history to make the data files agree with transaction outcomes.

If you have seen settings or messages mentioning fsync, WAL segments, LSNs, checkpoints, “replaying WAL,” or “crash recovery,” they are references to this machinery. The log is not merely an audit trail. It is the ordered, durable work record that lets a database safely separate fast in-memory updates from slower data-file writes while still recovering a consistent state.