Write-Ahead Logging (WAL) & Crash Recovery

Learning outcomes

• Trace WAL LSN record writes during transactions
• Configure checkpoint intervals for high-write workloads

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.

-- Query current WAL insertion LSN location
SELECT pg_current_wal_lsn();

-- Force a manual checkpoint to flush dirty buffer pages
CHECKPOINT;

-- Inspect WAL archiver status
SELECT * FROM pg_stat_archiver;

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.

Primary sources

• PostgreSQL 16 Architecture, MVCC & Query Optimization Manual — PostgreSQL Global Development Group, verified 2026-07-22