PostgreSQL WAL ordering, crash recovery, and replication conditions

PostgreSQL Write-Ahead Logging (WAL) requires that a WAL record describing a data change reach persistent storage before the corresponding data page is written to the data file. This lets PostgreSQL recover by redoing logged changes after a crash without forcing every modified table page to disk at each commit. The durability observed by an application still depends on when WAL is flushed and on settings such as synchronous_commit and fsync, the storage stack, and transaction type. It is inaccurate to say every commit is unconditionally durable the instant it returns. A Log Sequence Number (LSN) denotes a position in the WAL stream and is used to track recovery and replication progress.

Expert perspective: a PostgreSQL contributor on checkpoints

Bruce Momjian, a PostgreSQL core team member and long-time contributor, presents WAL together with crash recovery, point-in-time recovery, physical and logical replication, and replication slots. PostgreSQL’s documentation says the checkpoint position is saved in pg_control; during recovery, the server reads the checkpoint record and scans forward through WAL to perform REDO. A checkpoint does not mean that all changes are safely stored there and every earlier WAL segment can immediately be deleted. WAL may still be needed for crash recovery, replication, or archiving. Recovery time is not universally seconds or minutes: it depends on the amount of WAL to replay, data volume, I/O, and configuration.

Checkpoint tuning and crash workload

Checkpoints coordinate dirty-buffer writes and record a WAL location to bound the work needed for crash recovery. Very frequent checkpoints can increase write pressure and full-page-image WAL; longer intervals may increase recovery work and WAL retention needs. max_wal_size is not a hard disk-space ceiling. Archiving failures, replication slots, or lagging consumers can retain WAL beyond expectations, so disk alerts matter. Tune checkpoint_completion_target and related controls against the PostgreSQL version and observed workload instead of changing defaults by rule of thumb.

Distinguish replication, backup, and durability objectives

Streaming replication ships and replays WAL on a standby. With asynchronous replication, a primary failure can lose recent commits that have not reached the standby. Synchronous replication can make configured standby acknowledgements part of commit behavior, at a latency and availability cost. Replication slots help retain WAL needed by a consumer, but an inactive slot can cause disk growth. Point-in-time recovery combines a base backup with WAL archiving and depends on a complete archive and a tested restore procedure. Define RPO and RTO, monitor replication and slot retention, alert on archive failures and disk growth, and rehearse recovery on the deployed version.

References