TopolearnDurability settings, backups, failover and replica lag all follow from how the WAL works.
PostgreSQL never writes a changed data page to disk before the change is recorded in the write-ahead log (WAL), an append-only sequence of change records. On COMMIT, only the WAL needs to be flushed with fsync; the modified data pages stay in memory and are written later by the background writer and at checkpoints. Sequential WAL writes are much cheaper than scattered page writes, and that design gives you:
- Crash recovery: after a crash, PostgreSQL replays WAL from the last checkpoint. Committed transactions reappear; uncommitted ones never become visible.
- Tunable durability:
synchronous_commit = offreturns fromCOMMITbefore the WAL flush. A crash can lose the last fraction of a second of commits, but never corrupts data. Useful for low-value, high-volume writes. - Checkpoint tuning:
max_wal_sizeandcheckpoint_timeouttrade write bursts against recovery time.
Because WAL describes every change, it can be shipped elsewhere.
- Point-in-time recovery: a base backup (
pg_basebackup) plus archived WAL can be restored to any moment, such as one second before someone ran a badDELETE. A dump frompg_dumpis a logical snapshot, useful but not point-in-time. - Streaming (physical) replication: a standby receives WAL continuously and replays it, giving a byte-identical copy that can serve read-only queries (hot standby) and be promoted on failover. It is asynchronous by default, so replicas lag and a failover can lose recent commits.
synchronous_standby_namesmakes commits wait for a standby, trading latency for zero loss. - Logical replication: publications and subscriptions stream row changes for chosen tables to another server, even on another major version. Useful for upgrades and feeding other systems.
Replication slots guarantee the primary keeps WAL a consumer has not yet received. An abandoned slot keeps WAL forever and will fill the disk; monitor it.
Example
-- Where is the WAL now?
SELECT pg_current_wal_lsn(); -- e.g. 0/3A01F2C8
-- On the primary: connected standbys and their lag
SELECT client_addr, state, sync_state, replay_lag
FROM pg_stat_replication;
-- Slots that are holding WAL back
SELECT slot_name, active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;
-- Relax durability for one bulk, low-value transaction only
BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO page_views (path, viewed_at) VALUES ('/', now());
COMMIT;
-- Logical replication of selected tables
CREATE PUBLICATION shop_pub FOR TABLE customers, orders;
-- on the subscriber:
-- CREATE SUBSCRIPTION shop_sub
-- CONNECTION 'host=primary dbname=shop' PUBLICATION shop_pub;Remember
- Changes reach the WAL before data pages; COMMIT waits only for the WAL flush
- Crash recovery replays WAL from the last checkpoint
- Base backup + archived WAL = point-in-time recovery
- Physical replicas lag by default; abandoned replication slots fill the disk
Watch out for
- Treating an asynchronous replica as a backup; a bad DELETE replicates to it within milliseconds.