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.
| Component | Role | Why it matters |
|---|---|---|
| Log record | Describes one change or event | Unit of redo and undo |
| Log sequence number | Orders records and tags pages | Shows which changes a page contains |
| Log buffer | Collects records in memory | Batches writes before flushing |
| Commit record | Marks a transaction as complete | Defines the durability point |
| Checkpoint | Flushes dirty pages, notes position | Limits recovery work |
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.
| Phase | Direction through the log | Purpose |
|---|---|---|
| Analysis | Forward from checkpoint | Find dirty pages and active transactions |
| Redo | Forward | Reapply all logged changes not on disk |
| Undo | Backward | Roll back uncommitted transactions |
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 mode | When the log is flushed | Risk on crash | Typical use |
|---|---|---|---|
| Synchronous commit | Before acknowledging the client | No loss of acknowledged commits | Financial and order data |
| Group commit | Shared flush for a batch | No loss of acknowledged commits | High-concurrency workloads |
| Asynchronous commit | Shortly after acknowledgment | Last fraction of a second lost | Logging, analytics, caches |
| Synchronous replication | On local and standby storage | Survives loss of one server | High availability clusters |
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.
| Symptom | Likely cause | Remedy |
|---|---|---|
| Log volume fills the disk | Stalled archiving or idle replication slot | Monitor retention, remove unused slots |
| Commit latency spikes | Slow fsync on shared storage | Place the log on dedicated low-latency devices |
| Very long restart after crash | Checkpoints too far apart | Shorten checkpoint interval |
| I/O bursts at checkpoints | Too many dirty pages flushed at once | Spread checkpoint writes over time |
| Data loss despite commits | Disabled fsync or unsafe write cache | Restore safe flushing settings |
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.