What is a Write-Ahead Log (WAL) and Why Do Databases Use One?
Learn what a write-ahead log is, why databases log before writing data pages, and how WAL enables fast, durable commits.
Expected Interview Answer
A write-ahead log (WAL) is a durability mechanism where the database records every change as a sequential log entry on stable storage before it modifies the actual data pages, guaranteeing that committed changes can always be reconstructed even if a crash happens mid-write.
Instead of updating data pages on disk immediately (which is slow and risky if interrupted), the engine first appends a compact description of the change to an append-only log file, then flushes just that log record with a fsync. Because appending sequentially is fast and the log record is small, this makes commits durable without paying the cost of flushing large, randomly-located data pages on every transaction. The actual data pages are updated later, asynchronously, and if the system crashes before that happens, the log still holds everything needed to redo the change on restart.
- Durable commits without slow random-page flushes on every write
- Sequential log writes are far faster than scattered page writes
- Provides the foundation for crash recovery via redo
- Decouples fast commit latency from slower background page flushing
AI Mentor Explanation
Think of a scorer who jots every ball's outcome in a running notebook the instant it happens, before ever updating the big physical scoreboard on the ground. If the scoreboard operator steps away or the display glitches, the notebook still has the complete, ordered record of every run and wicket. A write-ahead log works the same way: the sequential entry is captured first and cheaply, and the slower, official data structure catches up from it afterward.
Step-by-Step Explanation
Step 1
Transaction requests a change
A statement modifies a row, and the engine builds a log record describing the change.
Step 2
Append the log record
The log record is appended sequentially to the WAL file and fsynced to stable storage.
Step 3
Acknowledge the commit
Once the log record is durably on disk, the transaction can be reported as committed to the client.
Step 4
Flush data pages later
The corresponding in-memory data pages are written to their actual disk locations asynchronously, independent of commit latency.
What Interviewer Expects
- Understanding that the log is written before the data pages
- Explanation of why sequential appends beat random page writes
- Connection between WAL and durability (the D in ACID)
- Awareness that WAL underpins crash recovery, not just performance
Common Mistakes
- Saying the data pages are updated before the log, reversing the rule
- Confusing WAL with a general application log or audit trail
- Not explaining why sequential I/O is faster than random I/O
- Forgetting that a WAL enables recovery, not just faster commits
Best Answer (HR Friendly)
“A write-ahead log means the database writes down what it is about to change in a fast, sequential log file before it actually updates the real data on disk. That way, even if the system crashes right after committing, the log has everything needed to redo the change, so committed work is never lost.”
Code Example
-- PostgreSQL writes every change to WAL before touching heap pages.
-- You can inspect and tune the durability trade-off directly:
SHOW wal_level;
-- fsync=on ensures WAL records are flushed to durable storage
-- before a transaction is reported as committed.
SHOW fsync;
-- A simple transaction: PostgreSQL appends WAL records for both
-- statements, fsyncs at COMMIT, then updates the heap pages later.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;Follow-up Questions
- How does a write-ahead log relate to crash recovery specifically?
- What is the difference between synchronous and asynchronous WAL flushing?
- How does checkpointing interact with the write-ahead log?
- Why is sequential I/O faster than random I/O on disk-based storage?
MCQ Practice
1. In a write-ahead logging scheme, what must happen before a transaction is reported as committed?
WAL requires the log record describing the change to be durably persisted before the commit is acknowledged, not the data pages themselves.
2. Why do databases append to a sequential WAL file instead of writing directly to data pages on every transaction?
Sequential appends avoid the seek overhead of random writes across scattered data pages, making commits far faster.
3. What best describes the relationship between the WAL and the actual data pages?
The WAL records intent first; data pages are updated afterward, and the log is what lets the engine redo those updates after a crash.
Flash Cards
What is a write-ahead log? — A sequential log where changes are recorded before the corresponding data pages are modified on disk.
Why write the log before the data pages? — Sequential log appends are fast and durable, while random data-page writes are slower and can be deferred safely.
What guarantee does WAL provide? — Durability — committed changes survive a crash because they can be redone from the log.
What happens to data pages relative to WAL? — They are updated later, asynchronously, using the WAL as the source of truth if a crash occurs first.