How Write-Ahead Logging Works: Durability Before Data Pages Hit Disk

A committed row that vanishes after a power cut is a broken contract. The database promised durability. RAM is fast and volatile. Disk pages are durable and expensive to rewrite in place. Write-ahead logging (WAL) is the mechanism that lets a system accept a commit without first flushing every dirty data page: it appends a description of the change to a sequential log on stable storage, and only then is it allowed to treat the transaction as durable.

This article is about that rule and the machinery around it: log sequence numbers, steal and no-force buffer policies, group commit, checkpoints, torn pages, and what recovery actually replays. It is not a second tour of SQL versus NoSQL, B-tree lookup, or the whole storage engine. Those topics live in other posts on this site. The search intent here is a single question: how does write-ahead logging make a commit survive a crash?

The two rules

WAL is a policy with two obligations.

• Before a dirty data page may be written to disk, every log record that describes a change on that page must already be on stable storage.
• Before a transaction may be reported committed, every log record of that transaction, including its COMMIT record, must already be on stable storage.

The first rule protects undo. If an uncommitted change reaches a data file and the process dies, the log still contains enough information to roll the page back. The second rule protects redo. If a committed change never reached the data file, the log still contains enough information to roll the page forward.

Together they support a buffer manager that is allowed to steal frames (evict a page that still belongs to an in-flight transaction) and that is not required to force all of a committing transaction's pages to disk at commit time. Steal plus no-force is how a long-running update can proceed without pinning every touched page until the end, and how a commit can finish after a sequential log flush instead of a scatter of random page writes.

What a log record actually holds

Each record is identified by a log sequence number (LSN): a monotonically increasing value, often implemented as a file offset or as a pair of (segment, offset). The LSN is the clock the rest of the engine uses.

A typical update record carries:

• its own LSN
• the previous LSN of the same transaction (a backward chain for undo)
• transaction id
• page id
• enough before-image or logical undo information to reverse the change
• enough after-image or logical redo information to repeat the change
• the type of the record (update, commit, abort, compensation, checkpoint, …)

Each data page stores a pageLSN: the LSN of the latest update applied to that in-memory copy. The log manager tracks flushedLSN: the highest LSN known to be on stable storage. The WAL rule on eviction is then a comparison. A page with pageLSN greater than flushedLSN cannot go to disk until the log catches up.

Logging can be physical (byte ranges on a page), logical ("insert key K into index I"), or physiological (describe a change to a specific page in a way that does not require the rest of the page to look identical at redo time). Production engines mix these. Physiological logging is common because redo can apply to a page that later compactors or other transactions have rearranged, as long as the page identity is stable.

A commit is a log flush, not a page flush

Walk through a tiny transaction that updates one row.

1. The executor finds the heap page in the buffer pool and latches it.
2. It generates an update log record, appends that record to the in-memory log tail, and applies the change to the page. The page's pageLSN becomes the new LSN. The page is now dirty.
3. The transaction writes a COMMIT record to the log tail.
4. The log manager forces the tail through COMMIT onto stable storage. Only after that fsync (or equivalent) returns does the engine acknowledge commit to the client.
5. The dirty heap page may still sit in RAM. A background writer or checkpoint will write it later.

If the machine dies between steps 4 and 5, recovery will see the COMMIT record and redo the update onto the stale on-disk page. If the machine dies between steps 2 and 4, there is no COMMIT on disk. Recovery will undo the in-memory change if the page had already been stolen to disk, or simply discard the buffer-only version if it had not.

Group commit

A naive implementation would fsync the log once per COMMIT. On a hard disk that costs a seek and a rotation. Even on NVMe, a barrier per transaction becomes the limiter once the CPU is no longer the bottleneck.

Group commit batches. Several transactions append COMMIT records to the same log buffer. One flush makes all of them durable. Latency for an individual commit rises by whatever wait was needed to form the group; throughput rises because the device sees sequential, larger writes. Most engines cap the wait with both a size threshold and a time threshold so a lonely transaction is not stuck behind an empty queue.

This is why "synchronous_commit" settings exist in systems such as PostgreSQL: they trade the second WAL rule's strictness against latency. Turning the flush off does not stop logging. It stops waiting for stable storage before answering the client. After a crash those transactions are not durable. That is a configuration choice, not a different algorithm.

Checkpoints shrink recovery

Without a checkpoint, restart would scan the log from the beginning of time. A checkpoint writes enough metadata that analysis can start later.

A fuzzy checkpoint, in the style used by ARIES-like recoverers, does not freeze the database. It writes a BEGIN_CHECKPOINT record, copies the transaction table and dirty-page table as they stand, writes an END_CHECKPOINT record that contains those copies, and then persists a master pointer to the BEGIN_CHECKPOINT LSN. Work continues during the copy, so the tables are slightly stale by the time END_CHECKPOINT hits disk. Analysis starts from the checkpoint and then applies the log after it, which brings the tables forward to the crash point.

The dirty-page table's recLSN for each page is the LSN of the first log record that dirtied that page since it was last flushed. Redo can therefore start at the smallest recLSN rather than at the checkpoint itself. Pages whose on-disk pageLSN is already past a given log record are skipped. That test is how redo stays idempotent when a crash hits in the middle of recovery itself.

Undo, compensation records, and repeating history

ARIES-style recovery is three forward-or-backward passes, not a single rewind.

• Analysis rebuilds the transaction table and dirty-page table from the last checkpoint plus the tail of the log.
• Redo repeats history from the oldest recLSN: every logged update is applied unless the page already reflects it. Winner and loser transactions are both redone. The goal is to recreate the exact dirty state at crash time.
• Undo walks the loser transactions backward along prevLSN chains. For each undone update the engine writes a compensation log record (CLR) that describes the undo and points to the next record still to undo. If the system crashes during undo, the next restart will redo the CLRs and then continue undo from undoNextLSN instead of reversing the same update twice.

That last detail is easy to miss in short explanations. Logging the undo is what makes recovery itself crash-safe.

Torn pages and full-page images

A 4 KiB or 8 KiB database page is larger than the unit a drive is guaranteed to write atomically. A crash can leave the first half of a page new and the second half old. Redoing a physiological change onto a torn page corrupts it.

One common countermeasure is to log a full-page image the first time a page is modified after a checkpoint (PostgreSQL's full_page_writes). Redo then has a known-good copy of the page and can apply later incremental records on top. Doublewrite buffers, as used in InnoDB, write pages to a sequential scratch area before the home location so a torn home write can be repaired from the scratch copy. Both exist because WAL's logical description of a change is not enough if the page it targets is physically incoherent.

WAL outside the classic RDBMS

SQLite's rollback journal is the older sibling: it copies original pages aside, then writes the database file, then deletes the journal. WAL mode in SQLite reverses the direction. Writers append frames to a -wal file; readers use a WAL index to see a consistent snapshot; a checkpoint later copies frames back into the main database file. The rule is the same. The durable record of a change lands in the log before the main file is required to reflect it.

File systems use journals for metadata. Copy-on-write file systems such as ZFS and btrfs make a different durability bet (never overwrite live blocks; atomically swing a root pointer), but they still sequentialize intent. Consensus logs in Raft, covered separately on this site, are a distributed form of the same idea: a committed prefix of a replicated log is the source of truth, and the state machine is allowed to lag.

An application-level "append events to a file, then fsync, then update the cache" design is WAL even if nobody named it that. The mistake is acknowledging success before the append is stable, or updating the durable structure before the log describes the update.

Misconceptions

The log is a backup. A backup is a point-in-time copy you can restore elsewhere. A WAL is an ordered intent stream the same process uses to reconstruct its own files after a crash. You can ship WAL for replication, but that is a second use of the same records.

fsync on the data file is enough. Without a log, a crash in the middle of updating two indexes and one heap page leaves no description of which of the three writes completed. Durability of individual pages is not atomicity of a transaction.

Redo is only for committed transactions. In a repeating-history design, redo reapplies loser updates too, so undo starts from a known state. Skipping loser redo looks like an optimization and becomes a source of subtle bugs.

Checkpoint means "the database is consistent on disk." A fuzzy checkpoint means "recovery may start here," not "every dirty page is clean."

WAL replaces indexes. Indexes still exist. They are data pages. They get logged when they change, for the same steal and no-force reasons the heap does.

Takeaways

Write-ahead logging makes durability a property of a sequential file. A change is described in the log, the log is forced at commit, and data pages catch up later under buffer and checkpoint policy. LSNs tie pages to records. Steal and no-force stay safe only while the two WAL rules hold. Recovery analyzes the tail, repeats history, then undoes losers, writing compensation records so a crash during recovery does not loop. Torn writes are a separate physical problem that full-page images or doublewrite buffers have to close.

Related reading on this site: How Databases Work Internally, Database Indexing Explained, and How Raft Works.