Write-ahead log

Also known as: WAL

On this page 7

A write-ahead log (WAL) is an append-only file in which a database records a description of every change before applying it to the data files, so that committed work survives crashes and incomplete work can be undone. In the study of transactions in Databases, the WAL is the mechanism that makes the durability and atomicity guarantees of ACID practical on real hardware.

The governing rule is simple: no modified data page may reach disk before the log records describing that change have been written durably. Because log writes are sequential and small, a database can acknowledge a commit as soon as its log records are flushed, while the far more expensive random writes to tables and indexes happen later in the background.

How the write-ahead log protects committed data

The write-ahead log protects data by separating the moment of commit from the moment of page writing. When a transaction modifies a row, the engine changes the page in its memory buffer and appends a log record describing the change. Each record receives a monotonically increasing log sequence number (LSN), and every data page stores the LSN of the last record that modified it.

At commit, the engine writes a commit record and forces the log to stable storage with a system call such as fsync. Only after that flush returns does the client receive success. If the server crashes a second later, the dirty pages in memory are lost, yet the log contains enough information to rebuild them. The buffer manager enforces the rule by refusing to write a page whose LSN is newer than the last flushed log position.

The table below lists the main elements that cooperate in a WAL-based design.

ComponentRoleWhy it matters
Log recordDescribes one change or eventUnit of redo and undo
Log sequence numberOrders records and tags pagesShows which changes a page contains
Log bufferCollects records in memoryBatches writes before flushing
Commit recordMarks a transaction as completeDefines the durability point
CheckpointFlushes dirty pages, notes positionLimits recovery work
Core components of a write-ahead logging system

Crash recovery with redo and undo

After a crash, the database replays the write-ahead log to return to a consistent state that includes every committed transaction and no uncommitted ones. The best-known formulation is the ARIES algorithm, which divides recovery into three passes and underlies the designs of many commercial and open-source engines.

Recovery begins at the most recent checkpoint, because all changes before that point are already on disk. The engine then reapplies logged changes whose LSN is newer than the page LSN, a process called repeating history. Finally, it reverses the effects of transactions that never committed, writing compensation log records so that a second crash during recovery does not repeat the same undo work.

The phases of ARIES-style recovery are summarized here.

PhaseDirection through the logPurpose
AnalysisForward from checkpointFind dirty pages and active transactions
RedoForwardReapply all logged changes not on disk
UndoBackwardRoll back uncommitted transactions
Phases of crash recovery

Not every engine needs all three. PostgreSQL, for example, uses redo-only recovery because its MVCC design keeps old row versions in place, so aborted changes are simply invisible and need no physical undo. Engines that update rows in place, such as InnoDB, keep separate undo information to support both rollback and consistent reads.

Logging styles and durability settings

Write-ahead logs differ in what they record and how strictly they flush. Physical logging stores byte-level page changes, logical logging stores high-level operations such as inserted row values, and most production systems use a hybrid called physiological logging, which identifies a page physically but describes the change within it logically.

Flushing policy is the main lever for trading safety against speed. Group commit combines the commit records of many concurrent transactions into one flush, which greatly raises throughput on busy systems. Some engines also offer asynchronous commit, where success is returned before the flush, accepting a small window of possible data loss in exchange for lower latency.

Commit modeWhen the log is flushedRisk on crashTypical use
Synchronous commitBefore acknowledging the clientNo loss of acknowledged commitsFinancial and order data
Group commitShared flush for a batchNo loss of acknowledged commitsHigh-concurrency workloads
Asynchronous commitShortly after acknowledgmentLast fraction of a second lostLogging, analytics, caches
Synchronous replicationOn local and standby storageSurvives loss of one serverHigh availability clusters
Durability options and their trade-offs

Uses of the WAL beyond crash recovery

Because the write-ahead log is a complete, ordered history of changes, databases reuse it for several features beyond restart recovery. Streaming the log to another server produces a physical replica that stays seconds or less behind the primary. Archiving log segments next to a base backup enables point-in-time recovery, in which an administrator restores the database to the state it had at any chosen moment.

Decoded log streams also feed change data capture, where tools read the WAL and publish row-level events to message queues or data warehouses without querying the tables. The WAL relates closely to other transaction concepts: it records the outcome of each commit and rollback, supports savepoints through partial undo, and in a two-phase commit it durably records the prepared state so that a participant can honor the coordinator decision after a restart.

Operational pitfalls and tuning

A write-ahead log is reliable only when the storage stack honors flush requests. Disks or controllers with volatile write caches that report completion early can silently break durability, which is why battery-backed or power-loss-protected caches matter for database servers.

Common problems and their remedies are listed below.

SymptomLikely causeRemedy
Log volume fills the diskStalled archiving or idle replication slotMonitor retention, remove unused slots
Commit latency spikesSlow fsync on shared storagePlace the log on dedicated low-latency devices
Very long restart after crashCheckpoints too far apartShorten checkpoint interval
I/O bursts at checkpointsToo many dirty pages flushed at onceSpread checkpoint writes over time
Data loss despite commitsDisabled fsync or unsafe write cacheRestore safe flushing settings
Frequent WAL problems and fixes

Tuning is always a balance between recovery time and steady-state performance. Frequent checkpoints shorten recovery but increase write amplification, and settings such as full-page images after each checkpoint protect against torn pages at the cost of extra log volume.

The idea in brief

Think of a careful accountant who writes every transaction in a bound ledger before touching the cash drawer. If the power fails while money is being counted, the ledger still shows exactly what should have happened, and the drawer can be corrected from it.

A write-ahead log is that ledger for a database. It writes down the change first and does the heavy work later. The key takeaway is that once the note is safely written, the change cannot be lost, even if the computer stops suddenly.

A practical scenario

An online ticketing platform runs PostgreSQL and sells up to 2,000 seats per second when popular concerts go on sale. Each purchase updates a seat row, inserts an order and adjusts a payment record in a single transaction, and every confirmed sale must survive any failure.

With synchronous commit and group commit enabled, the database flushes the write-ahead log about every few milliseconds for whole batches of purchases, keeping commit latency low. During one sale, the primary server lost power. On restart, recovery replayed the log from the last checkpoint in under a minute, and a streaming replica fed by the same WAL was ready to take over within seconds. Every ticket confirmed to a buyer was present after recovery, and no half-finished order appeared in the system.