Write-Ahead Logging (WAL) & Crash Recovery

Mental model

Write-Ahead Logging (WAL) guarantees durability (ACID D) by requiring changes to be logged sequentially to non-volatile disk before physical data pages are updated in buffer memory.

Theory

Every database modification generates a WAL record identified by a 64-bit Log Sequence Number (LSN). During COMMIT, PostgreSQL flushes the WAL buffer to disk via fsync(). During crash recovery, the database engine locates the last valid checkpoint LSN and replays subsequent WAL records (REDO phase) to restore state consistency.

Alternatives and trade-offs

• Synchronous WAL Flush (synchronous_commit = on): Guarantees zero data loss on power failure; limits commit rate to disk fsync latency.
• Asynchronous WAL (synchronous_commit = off): Achieves 10x higher write throughput; risks losing up to wal_writer_delay milliseconds of recent transactions on OS crash.

Failure modes and misconceptions

1. Unconstrained WAL Accumulation: Disabling max_wal_size limits or accumulating unconsumed replication slots causes WAL files to fill disk partitions, shutting down PostgreSQL.
2. FSYNC Bypassing: Setting fsync = off for performance corrupts database page structures during abrupt power loss.

Decision scenario

Use synchronous_commit = off for non-critical metric ingestion pipelines to maximize transaction throughput, but maintain strict synchronous_commit = on for financial transactions.

Learning outcomes

• Trace WAL record creation and LSN ordering across transaction commits.
• Configure checkpoint parameters (max_wal_size, checkpoint_completion_target).
• Diagnose disk exhaustion caused by stale replication slots.

Trade-offs

WAL guarantees ACID crash durability and point-in-time recovery, but introduces sequential disk write overhead during high-frequency transaction commits.