WAL — Write-Ahead Logging

Before any change is written to the actual database files, it is first written to a log. "Write Ahead" = write to log FIRST, update actual data AFTER. The log is the source of truth.

How WAL Works Step by Step

Transaction: UPDATE balance = 800 WHERE id = 1

Step 1: Write to WAL log first
  [LSN=001, TX=101, UPDATE, id=1, old=500, new=800, status=IN_PROGRESS]

Step 2: WAL flushed to disk (fsync) -- log is now durable

Step 3: Update in-memory buffer (fast)
  buffer pool: id=1 → 800

Step 4: Return SUCCESS to user

Step 5: Later — dirty pages written to actual data files (async)
  [background checkpoint process]

WAL Structure

LSN	TxID	Operation	PageID	Before	After
001	TX1	UPDATE	P001	500	800
002	TX1	UPDATE	P002	300	100
003	TX1	COMMIT	—	—	—
004	TX2	INSERT	P003	—	newrow
005	TX2	ROLLBACK	—	—	—

Sequential writes → much faster than random disk I/O

Crash Recovery Using WAL

• ANALYSIS phase: scan WAL from last checkpoint, identify which TXs were in progress
• REDO phase: replay ALL committed transactions (even if already written to disk)
• UNDO phase: rollback all UNCOMMITTED transactions using "before" values in WAL

REDO vs UNDO Logs

Log Type	Used For	When Used
REDO log (After image)	REAPPLY committed changes	Committed TX not yet in data file
UNDO log (Before image)	REVERSE uncommitted changes	TX was in progress during crash

Checkpoints

WAL grows forever if never cleaned up. Checkpoint = periodic sync of dirty pages to disk. Recovery after crash only needs to replay WAL from last checkpoint — not from the beginning of time.

• PostgreSQL: checkpoint every 5 minutes or 1GB WAL
• MySQL: similar via innodb_log_file_size

WAL and Replication

Master writes changes to WAL → WAL shipped to replicas (WAL streaming). Replica receives WAL records and replays them. This IS master-slave replication.

WAL Write Modes — Durability Tradeoff

Mode	Behavior	Safety
fsync = ON (default)	WAL flushed to disk before confirming write. Slower.	Zero data loss
fsync = OFF	WAL stays in OS buffer cache. Much faster.	Dangerous. Data corruption on OS crash
Group commit	Batch multiple TXs, flush WAL for all at once.	Good balance

Point in Time Recovery (PITR)

Base backup taken at Sunday midnight
WAL archived continuously since then
Database corrupted on Wednesday 2pm

PITR:
→ restore base backup (Sunday)
→ replay WAL from Sunday → Wednesday 1:59pm
→ stop just before corruption point
→ database restored to exact moment

WAL Enables

ACID Property/Feature	How WAL Enables It
Atomicity	Undo uncommitted TXs on crash
Durability	Redo committed TXs after crash
Replication	Ship WAL to replicas
MVCC	Undo log serves old versions to readers
Point in Time Recovery	Replay WAL to any point in time