Write-Ahead Logging (WAL)

WAL is the mechanism databases use to guarantee durability (the D in ACID) and enable crash recovery.

The Core Principle

Write to log BEFORE writing to data files.

If the database crashes mid-write, the log can replay or undo the operation.

WAL Flow

Transaction:
1. Write operation to WAL log (disk)
2. Commit record to WAL
3. Acknowledge to client
4. (Later) Apply to actual data pages

If crash at step 4, recovery replays WAL to restore state.

WAL Structure

LSN (Log Sequence Number): Monotonically increasing
Each record: [LSN | TransactionID | Operation | Before/After data]

Recovery Process

REDO

Reapply committed transactions not yet in data pages.

UNDO

Rollback uncommitted transactions found in log.

Benefits

• Durability: Committed data survives crashes
• Atomic commits: All-or-nothing
• Replication: Postgres streaming replication sends WAL
• Point-in-time recovery: Replay WAL to any timestamp

WAL and Replication

Primary DB
  → Generates WAL
  → Ships WAL to Standby
Standby DB
  → Applies WAL
  → Stays in sync

Log Compaction

WAL files accumulate. Checkpoint process:

1. Flush all dirty pages to disk
2. Record checkpoint LSN
3. WAL before checkpoint can be deleted

Conclusion

WAL is the foundation of ACID durability. Understanding it helps diagnose replication lag, recovery times, and I/O bottlenecks.