Write-Ahead Logging
Write-ahead logging persists changes to a sequential log before applying them to data files, enabling atomic commit and crash recovery.
Definition
Write-ahead logging (WAL) records every change in a durable, append-only log before the corresponding data pages are written to their final location. A transaction is committed when its log record is flushed, not when the data file is updated. On restart, the database replays the log to recover committed changes and undo uncommitted ones.
Context and scope
WAL is the foundation of durability and atomicity in PostgreSQL, SQLite, SQL Server, and many storage engines. It also enables replication and change data capture by exposing an ordered stream of changes.
Common misconceptions
- A WAL is a backup — it is a recovery log, and a usable backup still requires a base backup plus the relevant log segments
- Committing a transaction guarantees durability regardless of configuration — synchronous_commit and storage flushing determine what survives a crash
- WAL files can be deleted freely — removing segments needed for recovery or replication breaks the database
Anti-patterns
- Disabling fsync or synchronous commit in production for speed without accepting data loss
- Running out of disk because WAL retention or replication slots are unbounded
- Copying only the data directory without a consistent base backup and WAL
Related concepts
Related examples
Authoritative source
Log first, data later
Writing a small sequential log record is cheaper than updating random data pages. More importantly, it gives the database an ordered history. A transaction writes its changes to WAL, flushes the log on commit, and reports success. Data pages are written later by a background process. If the process crashes, recovery replays the log from the last checkpoint and brings the data files to a consistent state.
SHOW wal_level;
SELECT pg_current_wal_lsn();
SELECT * FROM pg_stat_wal;wal_level, synchronous_commit, and fsync are policy settings, not tuning trivia. synchronous_commit=off can lose recent transactions after a crash while keeping the database consistent. That may be acceptable for analytics, but not for payments. Measure the durability requirement before changing these settings.
Commit is a flush boundary
A transaction’s WAL records must reach durable storage before the client receives a commit acknowledgement, unless synchronous commit is relaxed. The operating system page cache is not durable storage; fsync is what forces the write. A database that acknowledges before the flush can lose acknowledged transactions in a power failure.
Group commit amortizes the cost: several transactions share one flush. That is why high-throughput databases can commit many transactions per second without one disk flush per commit. The guarantee remains: a committed transaction is in the log before the acknowledgement.
Retention and replication
WAL segments are reused after checkpoints. A replica, an archive, or a replication slot can require older segments to be retained. An unbounded slot or a broken archive command fills the disk and stops the primary. Monitor WAL generation, replication slot lag, and archive failures. A full disk on the primary is an outage, not a warning.
When to use it
WAL is a storage-engine implementation detail, not something most applications implement themselves. Understand it when designing backups, replication, crash recovery, or change data capture. The advantage is durable atomic commit with sequential I/O; the drawback is write amplification and operational complexity around retention.