postgresql · advanced
WAL and Crash Recovery
Quick answer
A write-ahead log (WAL) records changes before they hit main data files. After a crash, the database replays WAL to reach a consistent state. Understanding WAL clarifies fsync, checkpoints, replication, and "is my commit safe?"
Why this matters
- Durability depends on WAL flush policies.
- Replication and PITR are WAL consumers.
- Misconfigured fsync risks silent data loss.
Learning objectives
- Explain why WAL exists. 2. Relate commits to log flush. 3. Describe crash recovery. 4. Connect WAL to replicas. 5. Spot dangerous settings.
Explain like I am 5
Write the diary entry before rearranging the room—so if you trip, you remember what you meant to do.
Mental model
flowchart LR
Txn --> WAL
WAL --> Flush[fsync WAL]
Flush --> CommitOK
WAL --> DataFiles
Crash --> Replay[Replay WAL]
Core concepts
Log first
Data page writes can lag; WAL is the durability source of truth.Checkpoints
Periodically sync data files so old WAL can be recycled.fsync
Skipping fsync boosts benches and risks loss on power failure.Replication
Streaming replicas follow WAL; lag is replay delay.PITR
Keep WAL archives to restore to a timestamp.Worked example
Power loss after commit returns success: with proper WAL flush, recovery replays the commit; with fsync off, last commits may vanish—unacceptable for payments.
Trade-offs
| Sync every commit | Async commit |
|---|---|
| Safer | Faster, riskier |
Failure modes
| Mode | Mitigation |
|---|---|
| Disk full on WAL | Monitor + alert |
| Unarchived WAL | Backup/PITR design |
| Untested restore | Regular restore drills |
Interview mode
Skeleton: "WAL logs first for durability; recovery replays; replicas and PITR ride the same log stream."
Knowledge check
So crashes can recover committed changes by replaying the log
To avoid using indexes
To make SQL optional
Only for UI themes
By Shubham Jain