WAL, checkpoints, and database recovery

WAL rule: changes are written (and fsynced) to an append-only log before the data pages they modify. A commit is a WAL record, not a data-page flush. Consequences:

• Durability: after COMMIT returns, the change survives crash — recovery replays WAL onto the data files (redo).
• Performance: random page writes become sequential log appends; dirty pages flush lazily at checkpoints (every checkpoint_timeout or WAL volume).
• Replication/PITR: streaming replicas and point-in-time recovery are both "replay WAL" — shipped WAL is the change history.

Settings that matter (know their defaults): synchronous_commit=on (fsync per commit; off = batched, tiny window of lost commits on crash, big latency win), wal_level=replica (minimal → logical), full_page_writes (first-touch page image after checkpoint — torn-page protection), archive_mode + archive_command (ship WAL to storage → PITR).

Crash recovery sequence: start → find last checkpoint → replay WAL from there → undo nothing (MVCC handles aborts via its own records) → open. That's the same mechanism that makes kill -9-ing Postgres safe — your dev loops did this constantly.

Traverse's PITR — the real implementation

docker-compose.yml configures Postgres with wal_level=replica, archive_mode=on, archive_timeout=300 (forces a WAL switch at least every 5 min — bounds your recovery point objective!). WAL segments ship to the S3 bucket traverse-db-backups-697114252450 (us-east-1) — deliberately a different bucket and region than CAREERS_S3_BUCKET (ap-south-1): backup data and user data in separate blast radii, separate credentials. scripts/pg-backup-sync.sh (host cron, every 3 days) additionally syncs a base backup to Aiven — an offsite managed copy. The service layer (src/services/pitr.service.ts) exposes admin endpoints: /pitr/status, /pitr/backups, /pitr/snapshot, /pitr/restore, /pitr/rollback-safety, /pitr/history.

The recovery math to recite: RPO = up to 5 minutes (archive_timeout) — that's the worst-case data loss window; RTO = restore base backup + replay WAL to timestamp = minutes-scale for your data size. You can say "my RPO is 5 minutes, my RTO is minutes, and the backup credentials can't touch user-file credentials" — that's a complete DR answer before the interviewer finishes asking.

Zoth's honest gap: Postgres in-cluster (dev compose; Aiven for prod per DEVOPS docs) — no archive pipeline, no base backups. The recovery story is "redeploy + seed." For a demo: fine. For an interview: state it as the known gap with the exact fix (add archive_command to an Azure storage container; zoth already has Azure plumbing). Volunteering the gap converts it from a "gotcha" into a roadmap item.

The extensions that build on WAL

• Logical replication (wal_level=logical): row-level change streams — zero-downtime migration tool (Day 85's read-drain pattern), CDC-to-queue (an alternative to your polling outbox — Debezium-style). Your outbox chose polling; know why: no Kafka/connector infra, transactions make the outbox atomic, 5 s latency acceptable. The CDC alternative scales better but adds a pipeline.
• Replicas: streaming = physical WAL replay (exact copy, read-only); promotion = replica becomes primary. HA tooling (Patroni/stolon) automates failover — you have none, single-node by choice.