What Is Write-Ahead Logging?
Write-Ahead Logging (WAL) is a durability technique where a database records changes in an append-oriented log before the corresponding modified data pages must be written to their final storage locations.
This ordering lets a database acknowledge committed transactions without immediately flushing every changed table page to disk. After a crash, the database can use the WAL to reconstruct changes that were committed but had not yet reached the main data files.
Table of Contents
- Why Write-Ahead Logging Exists
- How Write-Ahead Logging Works
- The Write-Ahead Rule
- What Happens During COMMIT?
- WAL vs Data Pages
- Crash Recovery
- Checkpoints
- WAL Buffering and Flushing
- Group Commit
- WAL and Replication
- WAL and Point-in-Time Recovery
- WAL Performance Cost
- WAL Growth and Retention
- Production Design Example
- Monitoring WAL
- Failure Scenarios
- Common WAL Mistakes
- Frequently Asked Questions
- Conclusion
Why Write-Ahead Logging Exists
Consider a transaction that updates several database pages:
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 10;
UPDATE accounts
SET balance = balance + 100
WHERE id = 20;
COMMIT;The database may need to modify multiple physical pages containing rows and indexes.
A simple durability strategy would be to write every changed page to durable storage before returning success:
UPDATE
↓
Write table page
↓
Write index page
↓
Write another table page
↓
Flush everything
↓
COMMIT succeedsThis is expensive because database pages can be scattered across different files and storage locations.
It also creates a consistency problem if a crash occurs after only some pages have been written.
Write-ahead logging introduces a sequential durability record:
Transaction changes
↓
Write WAL records
↓
Flush required WAL
↓
COMMIT acknowledged
↓
Data pages written laterThe database no longer needs every modified table page to reach persistent storage before acknowledging the transaction.
How Write-Ahead Logging Works
Suppose a row changes from:
balance = 1000to:
balance = 900The database modifies the relevant page in memory and creates information in its transaction log describing the change.
Application
↓
UPDATE balance
↓
Database memory
├── Modified data page
└── WAL record
↓
WAL storageThe exact WAL record format is database-specific. It is not necessarily a copy of the SQL statement.
The important property is that enough recovery information is persisted so the database can restore the required state after a crash.
The Write-Ahead Rule
The defining WAL rule is:
Log information required to recover a change must reach durable storage before the corresponding changed data page is allowed to reach durable storage.
Conceptually:
Correct:
WAL record durable
↓
Data page may be flushed
Incorrect:
Data page flushed
↓
Required WAL missingThis ordering matters because the database must always have enough log information to understand and recover persisted changes.
For committed transactions, the database also needs the required WAL to be durable before reporting a durable commit under normal synchronous durability settings.
What Happens During COMMIT?
A simplified transaction lifecycle looks like:
BEGIN
↓
Modify pages in memory
↓
Generate WAL records
↓
COMMIT requested
↓
Flush required WAL
↓
Commit becomes durable
↓
Return successNotice what is missing:
Flush every modified table pageThat is not normally required at commit time.
The table and index pages can remain dirty in the database buffer cache and be written later.
This separates:
- transaction durability — the log contains enough information to recover the committed change;
- data-page persistence — the final table and index pages have been written to storage.
This distinction is central to understanding modern transactional databases.
WAL vs Data Pages
WAL and data pages serve different purposes.
| WAL | Data Pages |
|---|---|
| Records database changes for durability and recovery | Store the database's primary table and index structures |
| Primarily append-oriented | Updated throughout database files |
| Must be persisted according to write-ahead ordering | Can often be flushed later |
| Used during crash recovery | Represent the normal persistent database state |
| Can support replication and recovery workflows | Serve normal table and index access |
Suppose a transaction modifies pages 120, 9,521, and 81,204.
Writing those pages immediately means storage operations against several different locations:
Page 120
Page 9,521
Page 81,204WAL allows the database to first append recovery information to the log:
WAL
────────────────────────────→
record A | record B | record CThe dirty pages can then be written according to the database's buffer-management and checkpoint strategy.
Crash Recovery
WAL becomes especially important when the database process, operating system, or server crashes.
Suppose a transaction commits:
1. Data changed in memory
2. WAL flushed
3. COMMIT acknowledged
4. Server crashes
5. Data page was never flushedWithout WAL, the acknowledged change could disappear.
With WAL, the database can recover it.
Database starts
↓
Find recovery starting point
↓
Read WAL
↓
Replay required changes
↓
Restore consistent state
↓
Accept trafficThis process is commonly called redo or WAL replay.
The database uses the log to reconstruct changes that should exist even though the corresponding data pages had not reached their final persistent state before the crash.
This is one of the mechanisms supporting the durability property of database transactions. The broader transaction model is covered in Database Transactions.
Checkpoints
If a database had to replay its entire WAL history after every restart, recovery time would continuously increase.
Checkpoints establish recovery boundaries that limit how far back normal crash recovery needs to begin.
A simplified timeline looks like:
Checkpoint
│
├── WAL A
├── WAL B
├── WAL C
├── WAL D
│
CrashRecovery can begin from the relevant checkpoint rather than reconstructing the database from its original creation.
Checkpoint processing coordinates the persistence of dirty data pages and recovery metadata.
Checkpoint frequency creates a trade-off.
Very frequent checkpoints can increase write pressure:
Frequent checkpoints
↓
More aggressive dirty-page flushing
↓
Higher storage I/O pressureVery infrequent checkpoints can increase:
- the amount of WAL retained or generated between checkpoints;
- recovery work after a crash;
- the number of dirty pages that accumulate.
The correct configuration depends on workload, storage performance, recovery objectives, and database engine behavior.
WAL Buffering and Flushing
Writing every individual WAL record directly to durable storage would create excessive synchronization overhead.
Databases therefore buffer WAL records before flushing them.
Transaction A ─┐
Transaction B ─┼──→ WAL Buffer ──→ Durable WAL
Transaction C ─┘A commit may need to wait until the WAL containing its required commit information reaches durable storage.
This makes WAL flush latency an important component of transaction latency.
Application latency
↓
Database execution
+
Lock waits
+
WAL flush latency
+
Other workStorage with high synchronous-write latency can therefore directly affect transaction commit performance.
Group Commit
High-throughput databases may have many transactions committing at nearly the same time.
Instead of performing one independent durable flush for every transaction:
T1 → flush
T2 → flush
T3 → flush
T4 → flushthe database can combine durability work:
T1 ─┐
T2 ─┤
T3 ─┼──→ one WAL flush
T4 ─┘This is commonly known as group commit.
Several transactions can become durable through the same underlying flush operation, improving throughput while maintaining durability guarantees.
This is one reason a database can sustain far more commits per second than a naive "one transaction equals one completely independent disk write" model would suggest.
WAL and Replication
Transaction logs are also a natural source for database replication.
A primary database already produces an ordered stream describing changes:
Primary
↓
WAL
↓
Replica
↓
Replay changesA replica can receive WAL information and apply changes to maintain another copy of the database.
This is conceptually attractive because the primary does not need to independently rediscover which rows changed after every transaction. The change stream already exists for durability.
The replication architecture introduces additional questions:
- When is WAL sent to replicas?
- When is it acknowledged?
- How far behind is replay?
- Does commit wait for a replica?
- How much WAL must the primary retain?
These decisions affect durability, latency, failover behavior, and replication lag. What Is Database Replication? covers those trade-offs in more detail.
WAL and Point-in-Time Recovery
WAL can also be combined with backups to restore a database to a specific point in time.
Suppose a full backup is taken at midnight:
00:00
Base Backup
↓
WAL records
↓
01:00
↓
02:00
↓
03:00
↓
04:00At 04:10, an operator accidentally deletes important data.
If the required WAL history has been archived, recovery can conceptually work like:
Restore base backup
↓
Replay archived WAL
↓
Stop before destructive transaction
↓
Recovered databaseThis is known as Point-in-Time Recovery (PITR).
A base backup without the required subsequent WAL cannot reconstruct arbitrary states after the backup. WAL without an appropriate base state is also not a complete backup strategy by itself.
Backup, snapshot, and replication responsibilities should be designed separately rather than treated as interchangeable. Replication, Snapshots, and Backup Strategies explains those distinctions.
WAL Performance Cost
WAL improves durability and often makes the write path more efficient, but it still creates additional I/O.
A logical database update can result in multiple physical writes:
Application UPDATE
↓
WAL write
↓
Later data-page write
↓
Potential index-page writesThis contributes to write amplification.
Indexes can amplify the effect further.
Suppose one row update changes an indexed column:
UPDATE row
│
├── table changes
├── index changes
└── WAL describing required changesA database receiving 10 MB/s of logical application writes can therefore generate substantially more than 10 MB/s of physical storage traffic.
The exact ratio depends on:
- database engine;
- page layout;
- index count;
- checkpoint behavior;
- full-page logging behavior;
- transaction patterns;
- compression;
- replication configuration.
This is why storage throughput and latency need to be sized from measured database I/O rather than application payload size alone.
WAL Growth and Retention
WAL is continuously generated by write activity and cannot be retained forever without consuming unbounded storage.
Normally, old WAL becomes removable or recyclable after it is no longer required for configured recovery and replication purposes.
Problems occur when something prevents old WAL from being released.
For example:
Primary generates WAL
↓
Replica stops consuming
↓
Primary must retain required WAL
↓
WAL storage grows
↓
Disk fillsOther retention causes can include backup/archive requirements, replication slots, failed archiving, or database-specific recovery configuration.
This creates an important production principle: WAL retention must be monitored as a bounded resource.
A system can have plenty of free table space while the filesystem fills because transaction logs are accumulating unexpectedly.
Production Design Example
Consider a PostgreSQL-backed payment platform processing:
Payments: 3,000/sec
Database writes: 12,000/sec
Peak commits: 6,000/secA payment transaction updates several tables:
BEGIN;
INSERT INTO payments (
id,
account_id,
amount_cents,
status
)
VALUES ($1, $2, $3, 'completed');
UPDATE accounts
SET balance_cents = balance_cents - $3
WHERE id = $2;
INSERT INTO ledger_entries (
payment_id,
account_id,
amount_cents
)
VALUES ($1, $2, -$3);
COMMIT;During execution, PostgreSQL changes pages in memory and generates WAL.
Payment Transaction
↓
Modify buffers
↓
Generate WAL
↓
COMMIT
↓
Required WAL flushed
↓
Success returnedThe table pages do not all need to be flushed before the API receives success.
During peak traffic, monitoring shows:
Query execution: 4 ms
Lock waits: 1 ms
WAL flush: 2 ms
Other DB work: 1 ms
---------------------------
Transaction latency: 8 msLater, storage latency increases:
Query execution: 4 ms
Lock waits: 1 ms
WAL flush: 35 ms
Other DB work: 1 ms
---------------------------
Transaction latency: 41 msApplication code did not change, but commit latency increased dramatically because synchronous WAL persistence became slower.
The database also has two read replicas:
Primary
│
WAL
┌──┴──┐
↓ ↓
Replica A Replica BReplica B develops a network problem and stops consuming WAL efficiently.
Monitoring now shows:
Replica A lag: 2 seconds
Replica B lag: 47 minutes
WAL retained: rapidly growing
Disk free space: rapidly fallingReplica lag has now become a primary-storage risk.
The operations team removes the unhealthy replica from read traffic, investigates its replication state, and protects the primary from unbounded WAL retention according to the recovery requirements.
The system also archives WAL for point-in-time recovery:
Base backup
+
Archived WAL
↓
Recovery to selected timeProduction monitoring includes:
- WAL bytes generated per second;
- WAL flush latency;
- WAL write latency;
- checkpoint frequency;
- checkpoint duration;
- dirty-page write activity;
- replica receive lag;
- replica replay lag;
- retained WAL volume;
- archive failures;
- disk utilization;
- commit latency.
The important design lesson is that WAL connects several systems that may initially look unrelated: transaction latency, crash recovery, storage I/O, replication, backup recovery, and disk-capacity management.
Monitoring WAL
WAL should be treated as a first-class production subsystem.
Useful metrics include:
- WAL generation rate;
- WAL write rate;
- WAL flush latency;
- WAL storage usage;
- retained WAL volume;
- checkpoint count;
- checkpoint duration;
- checkpoint write volume;
- replication lag;
- archive success and failure counts;
- database commit latency;
- storage latency and IOPS.
A sudden increase in WAL generation can indicate:
- a large bulk update;
- increased write traffic;
- index creation;
- data migration;
- schema maintenance;
- unexpected application behavior.
A useful operational correlation is:
Commit latency rising
+
WAL flush latency rising
+
Storage latency rising
↓
Investigate durable write pathAnother is:
WAL disk usage rising
+
Replica lag rising
↓
Investigate WAL retentionFailure Scenarios
The server crashes after COMMIT but before dirty data pages are flushed. Durable WAL allows crash recovery to replay the committed changes.
The server crashes before the transaction's required WAL becomes durable. The transaction should not be treated as durably committed under normal synchronous durability semantics.
WAL storage becomes slow. Commit latency can rise even when SQL execution itself remains fast.
A replica stops consuming WAL. Required log retention may grow until storage capacity becomes a risk.
WAL archiving fails. The expected point-in-time recovery window may become incomplete even though the primary database continues serving traffic.
A checkpoint creates heavy I/O pressure. Query latency can increase while dirty pages are being written aggressively.
The WAL filesystem fills. The database may no longer be able to safely process new writes.
A large migration generates unexpected WAL volume. Replicas, archive storage, network bandwidth, and local disk can all experience additional pressure.
Recovery requires WAL that was deleted too early. The desired recovery or replica synchronization path may no longer be possible.
Common WAL Mistakes
- Assuming COMMIT means every changed table page has been flushed. Durability can come from WAL while dirty pages are written later.
- Thinking WAL stores SQL statements. Physical or logical logging formats are database-specific and often operate below the SQL level.
- Treating WAL as a complete backup. Recovery normally requires an appropriate base database state plus the required WAL history.
- Ignoring WAL flush latency. It can directly affect transaction commit latency.
- Ignoring WAL disk usage. Replication or archive problems can cause unexpected retention.
- Assuming replicas do not affect the primary. WAL retention requirements can turn replica failures into primary-storage problems.
- Creating excessive indexes without considering WAL. Additional index maintenance can increase write and log volume.
- Running massive updates without estimating WAL generation. Bulk operations can create large storage, network, and replication spikes.
- Configuring checkpoints only for recovery speed. Checkpoints also affect foreground and background I/O.
- Testing backups without testing WAL replay. A recovery strategy is useful only when the complete restore process works.
- Confusing replication with backup. Replication can quickly copy accidental or destructive changes to replicas.
Frequently Asked Questions
Write-ahead logging is often described simply as "write the log first," but its consequences extend into transaction durability, storage performance, replication, and recovery.
Is WAL a Backup?
Not by itself.
WAL records changes, while a backup provides a base database state. Point-in-time recovery commonly combines a base backup with the required sequence of archived WAL records.
Does Every Database Write Go to WAL?
The exact rules depend on the database engine, object type, configuration, and operation.
For ordinary durable PostgreSQL data, changes needed for crash recovery are normally represented through WAL. Some databases and configurations also provide special non-durable or unlogged mechanisms with different guarantees.
Why Not Write Data Pages Directly?
Data pages can be scattered throughout large table and index files, while WAL provides an append-oriented durability path.
Separating durable logging from eventual data-page flushing also lets the database recover partially persisted state after a crash.
Can WAL Improve Performance?
Yes. WAL allows transaction durability without forcing every modified data page to be synchronously written during each commit.
However, WAL itself still requires durable writes, and high WAL volume or slow WAL storage can become a performance bottleneck.
What Happens If WAL Is Lost?
The consequences depend on which WAL was lost and why it was needed.
Missing required WAL can prevent crash recovery, break a point-in-time recovery sequence, or prevent a replica from catching up from its existing state. This is why WAL storage, archival, and retention need explicit reliability controls.
Conclusion
Write-Ahead Logging makes database changes recoverable by persisting the required log information before modified data pages need to reach their final storage locations. This allows transactions to commit without synchronously flushing every changed table and index page.
The same mechanism becomes foundational for crash recovery, replication, point-in-time recovery, and high-throughput transaction processing. In production, WAL generation, flush latency, checkpoints, replication consumption, archival, and retention all need monitoring.
The core principle is: make the recovery record durable first, then allow the database to persist the final data pages asynchronously and recover them from the log when necessary.
Comments (0)