Write-ahead logging and recovery

The essential rule is that the recovery information describing a page change must reach durable storage before the changed page can safely reach durable storage. A synchronous durable commit also requires the relevant commit log records to be flushed according to the configured policy before acknowledgement. The database can later write dirty pages in a different order because recovery can replay the ordered log. Group commit amortizes one flush across multiple transactions without changing their durability contract.

Durability is a promise involving software, the operating system, the device, and its power-loss behavior. A completed write into a volatile device cache is not necessarily a stable write. Configuration that delays commit flushing may deliberately permit loss of recently acknowledged transactions after a crash. Distinguish that tradeoff from disabling protective synchronization altogether. PostgreSQL documents these settings separately; inspect the exact deployed policy rather than assuming all successful SQL commits offer identical failure guarantees.

A checkpoint establishes a recovery starting relationship between persisted database state and the log. It reduces the log work needed after a crash, but checkpoint completion does not mean that no later log is required. Recovery identifies the appropriate starting point and replays necessary records, using page and transaction metadata to avoid treating an uncommitted change as visible. Exact algorithms differ by engine; PostgreSQL's WAL should not be described as implementing every detail of another database's recovery algorithm.

Crash recovery on surviving storage differs from point-in-time recovery after lost storage or operator error. The latter needs a suitable base backup and an uninterrupted retained sequence of required WAL, plus the target recovery policy. Replication supplies another copy of changes, which may also include the accidental deletion you wish to undo. Test restoration into an isolated environment and verify application-level invariants, rather than declaring a backup useful merely because an object exists.

A recovering replica must consume old log while the primary continues creating new log. If replay capacity does not exceed generation rate, lag cannot shrink under the constant-rate model. Measure both rates in the same physical units: logical changed-row bytes are not automatically WAL bytes. Full-page images, index changes, and transaction metadata affect generated volume. Some records replay more slowly than others, so bytes per second is a workload-dependent planning measurement.

Retain enough history for the actual outage and recovery interval, and monitor retention pressure separately from replay lag. A stalled replication slot can preserve WAL until disk fills; an aggressive retention cap can instead make a consumer require reinitialization. Checkpoint frequency trades background write pressure and WAL behavior against restart work. Use measured restoration and replay drills to choose it, including the time to provision storage, fetch backup objects, and validate the recovered service.