SQL Write-Ahead Logs and Crash Recovery

07 Oct 2026 sqldatabases

A SQL write-ahead log (WAL) is an append-only record of database changes that is written to durable storage before the corresponding table data is written there. After a crash, the database reads this log to reconstruct changes that had been committed but were not yet reflected in the data files.

The key rule is simple:

The log record describing a change must reach durable storage before the changed data page can reach durable storage.

This lets the database treat the log as a trustworthy history of what it intended to do, even if a crash interrupts the slower process of updating table and index files.

The problem WAL solves

A database does not usually update a table file directly for every UPDATE or INSERT. It loads a piece of data into memory, changes that in-memory copy, and eventually writes the modified piece back to disk. This memory area is often called the buffer cache. A modified in-memory page is called a dirty page.

That approach is efficient: many changes can be combined into one disk write, and the database can avoid waiting for storage on every statement. But it creates a failure window. Consider this transaction:

BEGIN;

UPDATE accounts
SET balance = balance - 25
WHERE id = 101;

UPDATE accounts
SET balance = balance + 25
WHERE id = 202;

COMMIT;

The database may have changed both pages in memory, but neither page may have been written to the data files when the process or machine loses power. Without another record of the changes, the committed transfer disappears.

Writing the data pages immediately would reduce that risk, but it would make every transaction depend on slow, unpredictable storage operations. It also would not by itself solve consistency problems: a crash could happen after the first account page was written but before the second one was.

WAL separates recording the change from applying the change to the main data files. The record is small and sequential, which storage can generally write more efficiently than scattered table and index pages.

What the log contains

A WAL contains records describing database actions. Depending on the database, a record might describe a physical change to a page, a logical operation such as inserting a row, or enough information to reproduce and reverse a change. A record normally identifies the transaction, the affected object or page, and the new information needed during recovery.

The log is ordered. Each record receives a position, often called a log sequence number (LSN). An LSN is an identifier for a location in the log, so the recovery process can determine which changes precede others and whether a page already contains a particular change.

A transaction's log records are followed by a commit record when the transaction commits. The database does not normally tell the client that COMMIT succeeded until that commit record has been flushed to durable storage. Flushing means asking the operating system and storage device to persist the data, commonly through an operation such as fsync; keeping bytes in a process or operating-system cache is not enough.

This produces the central ordering:

1. Write the transaction's change records to the WAL.
2. Make the changes in memory, or otherwise prepare the affected data pages.
3. For a commit, write and flush the commit record.
4. Write dirty data pages to their normal table and index files whenever convenient.

The exact internal order can vary, but the durability constraints do not: a data page must not be persisted before its required WAL records, and a successful commit must have a durable commit record.

What happens after a crash

On restart, the database examines the WAL and the data files. This process is called crash recovery. It finds changes that were recorded in the log but may be missing from the data pages, then reapplies the necessary ones. Reapplying a logged change is commonly called redo.

Suppose the account transfer reached durable storage in the following form:

• a WAL record subtracting 25 from account 101;
• a WAL record adding 25 to account 202;
• a durable commit record;
• only the first modified data page written to its table file;
• then a power failure.

After the restart, the database sees the commit record and replays the logged changes. Account 202 receives its missing addition, while the already-written first change is not incorrectly applied twice. Recovery tracks log positions and uses database-specific checks to make redo safe when a change is already present.

The reverse case also matters. A transaction can write WAL records and dirty data pages, then crash before committing. Recovery must not leave those partial effects visible as a successful transaction. Depending on the database design, it removes them, restores the previous values, or uses transaction visibility rules to ensure they are ignored. This part is often called undo or rollback.

So recovery generally distinguishes two groups:

• Committed transactions: redo their logged changes if the data files are missing them.
• Incomplete transactions: undo their effects or prevent them from becoming visible.

The result is the transaction property called durability: once the database has acknowledged a commit, a later crash should not make that committed work vanish. WAL also supports atomicity, because recovery can finish or discard a transaction rather than leaving only part of its intended changes.

Checkpoints and the logs you encounter

A database does not need to replay its entire history after every restart. A checkpoint records a known recovery position and information about pages or transactions that have been processed. Recovery can usually begin near that position, although the database may still need later records to account for dirty pages and in-flight transactions.

Logs can be split into segments, recycled, or archived after the database no longer needs them for crash recovery. Configuration and incident messages may therefore mention WAL size, log retention, replication slots, archive failures, LSNs, or checkpoint delays. These are different operational aspects of the same mechanism: the log is the durable sequence of changes that stands between a committed transaction and an interrupted write to the main data files.

WAL is not a promise that every byte is instantly safe merely because a statement ran. The important boundary is a successful commit whose commit record has been durably flushed. After that boundary, the database can rebuild the committed state from the log even when the ordinary data pages were still waiting in memory when the crash occurred.