WAL, Durability, Archiving and PITR

In one line

The write-ahead log is an ordered stream of changes identified by LSNs, stored in 16 MB segment files. It gives crash recovery, streaming replication (replicas replay it) and point-in-time recovery (restore a base backup and replay archived WAL to any moment). synchronous_commit and fsync settings trade latency against the risk of losing commits.

0/5 · 0%

Think of it like this

A ship's logbook. Every event is written in order before it's acted upon. After a storm, you can rebuild the ship's state from the last inspection (checkpoint) by replaying the log; a copy of the logbook sent ashore lets another crew keep an identical ship.

Key ideas

01
LSN (log sequence number) is a byte position in the WAL stream (0/3A2B4C10). Pages store the LSN of their last change, so recovery knows which records to apply.
02
Crash recovery: start at the last checkpoint's redo point, replay every record whose LSN is newer than the page's LSN. Durable because WAL was fsynced before COMMIT returned.
03
WAL archiving: archive_mode = on with an archive_command or archive_library copies each completed segment to durable storage (S3 via pgBackRest or WAL-G). Base backup + continuous archive = PITR to any second in the retention window.
04
Replication: replicas stream WAL from the primary and replay it (physical replication); logical decoding turns WAL into row changes for CDC (Phase 10.4). Replication slots keep WAL until consumers read it, so an abandoned slot fills the disk.
05
Knobs: fsync must stay on (turning it off risks corruption, not just data loss); synchronous_commit = off risks losing the last few hundred ms of commits but not corruption; full_page_writes protects against torn pages; wal_level = replica or logical.

Code & diagrams

wal.sqlsql

SELECT pg_current_wal_lsn();                       -- 0/5A3C1F28
SELECT pg_walfile_name(pg_current_wal_lsn());      -- 000000010000000000000005

-- WAL generated by a statement
SELECT pg_current_wal_lsn() AS before \gset
UPDATE orders SET status = 'shipped' WHERE created_at < now() - interval '30 days';
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'before'));  -- 412 MB

-- replication slots holding WAL
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

pitr-restore.shbash

# pgBackRest: restore to just before a bad DELETE at 14:02:10
pgbackrest --stanza=main --type=time \
  --target="2026-09-14 14:02:00+05:30" --target-action=promote restore
# PostgreSQL starts, restores the base backup, replays archived WAL up to the target,
# then promotes. Verify row counts before redirecting traffic.

Interview problem

The problem

Recover from an accidental DELETE

At 14:02 an engineer ran DELETE FROM orders WHERE merchant_id = 12 (meant for staging), removing 800K rows. The database is 2 TB with nightly base backups and continuous WAL archiving. Recover with minimal disruption.

When it breaks

Inactive logical replication slot

What you see

A CDC consumer is decommissioned but its slot remains; WAL accumulates until the disk fills and the primary stops accepting writes.

Fix & prevent

Monitor retained WAL per slot; set max_slot_wal_keep_size to cap retention; drop unused slots.

WAL archive failing silently

What you see

archive_command errors pile up; WAL can't be recycled and the disk fills, and PITR has a gap exactly when you need it.

Fix & prevent

Alert on pg_stat_archiver.failed_count and last-archived age; verify restores regularly.

Explain it without notes

How does PITR work?

Practice

Measure how much WAL a bulk UPDATE of 1M rows generates, with and without an index on the updated column.

Trade-offs

↔
Synchronous durability costs an fsync per commit (group commit amortises it); archiving costs storage and must be monitored.

Done when you can

I can explain WAL, LSNs, crash recovery, archiving and run a PITR restore.

Command Palette