Write-Ahead Logging (WAL)

Definition: A durability technique where every data modification is first recorded in an append-only log on disk, and only afterward applied to the actual data files, so a crash can never lose a committed change.

How It Works

1. A client issues a write (INSERT/UPDATE/DELETE).
2. The engine appends a record describing the change to the WAL, a sequential, append-only file, and fsyncs it to disk. This is a fast sequential write, cheap compared to the random writes updating scattered data pages would require.
3. The engine updates the corresponding data pages in memory (the buffer pool/page cache). These dirty pages are not forced to disk immediately.
4. Once the WAL record is durably on disk, the engine acknowledges the transaction as committed to the client.
5. Dirty pages are flushed to disk later, in the background, at checkpoints, batched and reordered for efficient I/O rather than one random write per transaction.
6. Crash recovery: on restart, the engine replays WAL records written since the last checkpoint, reapplying any committed change that never made it to a data page, and undoing any change from a transaction that never committed (ARIES-style redo-then-undo recovery).

Under the Hood

A write hitting the WAL before the data page, followed by a crash and the recovery replay that restores the change:

Worked example 1: crash before the data page is flushed

• Given: account 7 has $500. A client updates it to$400 and receives a COMMIT acknowledgment. The power fails one second later, before the buffer pool’s dirty page for that row is flushed to disk.
• Step: on restart, the engine scans the WAL from the last checkpoint forward and finds the committed record for this change.
• Step: it replays the change onto the data page, exactly as if the write had happened again.
• Answer: account 7 correctly shows $400 after recovery, even though the actual data file on disk still physically held$500 at the moment of the crash. The WAL, not the data file, was the source of truth for what “committed” means.

Worked example 2: undoing an uncommitted transaction

• Given: a transaction updates account 7 to $300 but the client disconnects before calling COMMIT, and the server crashes shortly after with the change still only in memory and partially logged.
• Step: recovery’s redo phase would normally reapply this record if it were part of a committed transaction, but the WAL records for this transaction were never followed by a commit record.
• Step: the undo phase identifies this transaction as never having committed and reverses any of its effects that made it into data pages.
• Answer: account 7 ends recovery at its last actually-committed value, $400 from the prior example, not the half-applied$300. This is exactly what Atomicity requires, see ACID (Atomicity, Consistency, Isolation, Durability).

Worked example 3: checkpoint math

• Given: a database takes a checkpoint every 5 minutes and processes roughly 2,000 write transactions per second.
• Step: without checkpoints, recovery after a crash would have to replay the entire WAL history since the database was created, potentially days or years of records.
• Step: with a checkpoint every 5 minutes, recovery only ever needs to replay at most 5 minutes’ worth of WAL, roughly 600,000 records in the worst case here.
• Answer: checkpoint frequency is a direct trade-off between two costs: too frequent, and checkpointing itself (flushing many dirty pages) burns I/O bandwidth needed for live traffic; too infrequent, and crash recovery time grows unbounded.

ARIES Recovery in Detail

Most production databases implement some variant of the ARIES algorithm (Algorithm for Recovery and Isolation Exploiting Semantics), which splits recovery into three phases rather than the simplified two above:

• Analysis: scan forward from the last checkpoint to determine which transactions were active at crash time and which data pages were dirty, building the exact work list for the next two phases instead of guessing.
• Redo: replay every logged change since the earliest point any dirty page might not have made it to disk, including changes from transactions that ultimately didn’t commit. This intentionally overshoots, it’s cheap and simpler to redo everything and let undo clean up, than to figure out precisely which changes are already safely on disk.
• Undo: roll back the effects of any transaction that was still active (never committed) at crash time, using the log’s undo information, restoring the database to a state as if those transactions never ran.

This redo-everything-then-undo-the-losers approach is what makes ARIES-style recovery correct even when checkpoints are “fuzzy,” taken while transactions are still in flight, rather than requiring the database to pause all writes to take a clean checkpoint.

Why It Matters

WAL is what makes Durability affordable. Without it, guaranteeing a committed transaction survives a crash would require flushing every changed data page to disk, at its scattered, random location, before acknowledging the client, which is slow. WAL turns that into one fast sequential append per transaction, deferring the expensive random-write work to the background, without weakening the durability guarantee at all.

• It decouples correctness from performance: the durability guarantee doesn’t depend on how fast random I/O happens to be on a given disk, only on how fast a sequential append can be fsync’d.
• It gives every other durability-adjacent feature, replication, point-in-time recovery, change data capture, a single, well-defined stream of truth to build on, instead of each needing its own separate mechanism.
• It makes the cost of durability visible and tunable, synchronous_commit, checkpoint intervals, and WAL segment size are all knobs that trade explicit amounts of risk for explicit amounts of throughput, rather than durability being an all-or-nothing switch.

Common Pitfalls

• Running out of disk space for WAL files, which halts all writes immediately, an easy oversight on a database whose WAL directory shares a disk with logs or backups that can grow unpredictably.
• Setting synchronous_commit = off (or equivalent) for a throughput boost without understanding the trade: recent commits become vulnerable to loss on crash, since the engine no longer waits for the WAL fsync before acknowledging the client.
• Assuming WAL alone protects against disk failure. WAL durability only holds if the disk it’s stored on survives, WAL doesn’t replace backups or replication for protecting against media failure.
• Letting WAL files accumulate unbounded because a replication consumer (or archival process) has stalled and the primary can’t recycle old WAL segments it might still need to send, filling the disk even on an otherwise healthy primary.
• Underestimating checkpoint tuning: default settings that are fine for a small database can produce painfully long crash-recovery windows once a database grows into the terabytes.
• Forgetting the “before” part of write-ahead: some engines log both the old and new value (undo/redo logging) specifically so a transaction can be rolled back cleanly even after its changes reached a data page, confusing this with redo-only logging leads to incorrect assumptions about what rollback can and can’t do.
• Assuming a WAL entry means the change is visible to other transactions. Logging to WAL and making a version visible under MVCC are related but distinct steps, a transaction’s changes are durable once logged and committed, but isolation rules still govern who can see them.

WAL and Write Amplification

Every logical write actually touches disk at least twice: once in the WAL (immediately, sequentially) and once in the data file (eventually, at checkpoint time, often as part of a batched page flush). On top of that, many engines also log at the page level for the checkpoint flush itself, and some filesystems or storage layers add their own journaling on top of that. This layered logging is a deliberate trade: each layer buys a specific guarantee, transaction durability, page-level consistency, filesystem consistency, at the cost of writing more bytes than the logical change alone would require. Database tuning discussions about full_page_writes (PostgreSQL) or the InnoDB redo log size are really about deciding how much of this layered cost is worth paying for how much protection.

Comparison

Technique	Write pattern	Recovery cost	Used by
Write-Ahead Logging	Sequential log append, async page flush	Replay log since last checkpoint	PostgreSQL, MySQL InnoDB, SQLite (WAL mode), SQL Server
Shadow Paging	Copy-on-write whole pages, atomic pointer swap	None needed, old version simply discarded	Some embedded/older databases, early BTRFS-like designs
No logging (in-place, fsync per write)	Random write, fsync before ack	None, but extremely slow under load	Rarely used for serious workloads

Shadow paging avoids needing a replay step at all, since a transaction either fully switches to a new page tree or doesn’t, but it fragments storage more and complicates concurrent access, which is why WAL dominates in mainstream relational engines despite the extra recovery step.

No-logging designs are not a real contender for anything beyond a toy or throwaway system, waiting for a random-I/O fsync on every write before acknowledging a client caps throughput at whatever the underlying disk’s random-write IOPS happen to be, orders of magnitude below what a WAL-based engine sustains on the same hardware.

Example

PostgreSQL’s WAL is also the backbone of two other major features beyond crash recovery: streaming replication, where a replica continuously receives and replays the same WAL stream the primary generates, and Point-In-Time Recovery (PITR), where a base backup plus archived WAL segments can restore the database to any specific moment in time, not just the latest state. SQLite’s optional WAL mode (versus its default rollback-journal mode) uses the same core idea to let readers and a single writer proceed concurrently without blocking each other, notable because SQLite is normally thought of as a single-file, single-writer embedded database.

MySQL InnoDB splits this responsibility across two logs rather than one: a redo log (the WAL-equivalent, for crash recovery and durability) and a separate binary log (binlog, used for replication and point-in-time recovery), which means a MySQL crash-recovery discussion and a MySQL replication discussion are actually talking about two related but distinct log files, unlike PostgreSQL where WAL serves both purposes at once.

WAL as a Building Block

Beyond crash recovery, the “append durably first, apply later” pattern generalizes far beyond relational databases:

• Replication: shipping the WAL stream to another node and replaying it there is a simpler and more robust way to keep replicas in sync than trying to replicate individual SQL statements, which can behave non-deterministically (NOW(), RAND()) across machines.
• Change Data Capture: tools like Debezium tail a database’s WAL/binlog directly to produce a stream of row-level change events for downstream systems, without adding any load to the application’s own write path.
• Distributed consensus logs: systems like Kafka, and consensus protocols like Raft, use the same append-only-log-then-apply structure at a larger scale, WAL is really a specific, well-understood instance of a much broader “log as source of truth” pattern in distributed systems.

Related Terms

• ACID (Atomicity, Consistency, Isolation, Durability) — WAL is the primary mechanism delivering the Durability and Atomicity properties
• MVCC — new row versions are themselves written through the WAL before becoming visible
• Transaction Isolation Levels — recovery must respect which transactions had actually committed, not just which writes were logged
• Database Replication — WAL shipping is the most common mechanism behind physical replication
• Database Indexing Internals — index page changes are logged through the same WAL as table data changes