Write-Ahead Log
Log the change before the page. Redo/undo, crash recovery, and replication's raw feed.
Level: advanced
What is it?
A write-ahead log (WAL) records changes in a sequential log before the corresponding modified data pages are written to their main data files. After a crash, the DBMS uses the log and checkpoint state to recover a consistent database according to its recovery design.
Why does it matter?
WAL separates the durability of a committed logical change from the timing of random data-page writes. It supports crash recovery and can also provide a stream of changes for replication or point-in-time recovery.
Mental model
Before flushing a dirty data page, the log record describing the change must be safely persisted to the required point. The log is a durable history that lets the engine redo or otherwise reconcile changes after a crash.
How it works
- The WAL rule requires relevant log records to reach durable storage before the data page they describe is flushed.
- Commit protocols ensure the required log records are durable before acknowledging a transaction commit under the configured contract.
- Checkpoints limit the amount of log that must be processed during recovery.
- Recovery may redo changes that are logged but not present in data pages; exact undo/redo behavior depends on the storage/recovery design.
- Log retention interacts with replication slots, backups, archive policies, and disk capacity.
Steps
- A transaction updates a database row and creates WAL records.
- The commit path flushes the log through the commit record when required.
- The DBMS can acknowledge commit even if the changed data page has not yet been written, because recovery can replay the durable log.
- After a crash, recovery reconciles data pages with durable log records and checkpoints.
Real-world usage
- Explaining durability and crash recovery.
- Diagnosing WAL disk growth and replication lag.
- Planning backup retention and point-in-time recovery.
Common mistakes
- Assuming a successful write to an in-memory buffer guarantees durable storage.
- Deleting required WAL/archive files before backups or replicas no longer need them.
- Treating WAL as a complete backup; it usually depends on base data and correct retention.
Interview questions
Why must the log be durable before its data page is written?
The log contains the recovery information needed to redo or reconcile the change after a crash; writing the page first could leave an unrecoverable state.
Why can WAL grow unexpectedly?
A slow replica, retained replication slot, long-running transaction, archive failure, or backup policy may prevent required log segments from being recycled.