Topic 15.3
Database Failure Engineering and the Failure Lab
In one line
For every architecture, ask what happens when each part fails: primary, replica, replication, disk, connections, slow queries, lock contention, a shard, the cache, the network, data corruption, and a bad migration. For each, know the detection signal, the impact, the immediate mitigation, the recovery, and the prevention, and practise them deliberately in a failure lab.
Think of it like this
A fire drill that also tests the sprinklers, alarms and exits one by one. Knowing each failure's alarm, blast radius and fix makes a real fire survivable.
Key ideas
- 01
Primary dies: automated failover (Patroni, managed Multi-AZ) promotes a replica in ~30 s–2 min; applications must reconnect (short DNS TTLs, retry on connection errors); async replication means possible loss of the last commits.
- 02
Connections exhausted: new requests fail with
too many clientsor pool timeouts. Mitigate by killing idle-in-transaction sessions and shedding load; prevent with PgBouncer, small pools, timeouts. - 03
Disk full: PostgreSQL stops accepting writes (it can't write WAL). Causes: WAL retained by slots or failed archiving, bloat, temp files, logs. Mitigate by freeing space (drop inactive slot, fix archiving, remove old logs; never delete WAL files by hand); prevent with alerts and autoscaling storage.
- 04
Slow queries and lock contention: p99 rises, pools fill, and cascading timeouts follow. Mitigate by cancelling the offender (
pg_cancel_backend), statement timeouts, disabling the feature. Bad migration: locks or corrupt data; mitigate by cancelling it and restoring from PITR if data changed. - 05
Cache failure: stampede to the database (Topic 12.1). Shard unavailable: only its tenants fail (good design), or everything fails if requests fan out (bad design). Network partition: split brain risk; rely on fencing. Corruption: checksums and
amcheckdetect it; restore affected data from backup. - 06
Failure lab: deliberately cause each failure in staging (kill the primary, fill the disk, exhaust connections, create deadlocks, lag a replica, stop Kafka, run a locking migration), and document detection, impact, root cause, mitigation, recovery and prevention.
Code & diagrams
# 1. connection exhaustion
pgbench -c 400 -T 60 shop # watch: FATAL: sorry, too many clients already
# 2. lock contention from a "quick" migration
psql -c "BEGIN; LOCK TABLE orders IN ACCESS SHARE MODE; SELECT pg_sleep(120);" &
psql -c "ALTER TABLE orders ADD COLUMN note text" # waits and blocks everyone behind it
# 3. replica lag
pgbench -c 32 -T 300 shop & # heavy writes; watch replay_lag in pg_stat_replication
# 4. primary failure with Patroni
patronictl -c /etc/patroni.yml failover --force
patronictl -c /etc/patroni.yml list # new leader, old primary rejoins as replica
# emergency: cancel / terminate offenders
psql -c "SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE now() - query_start > interval '5 min' AND state = 'active'"Interview problem
The problem
Write the failure section of a design review
For an order service on PostgreSQL (primary + 2 replicas), Redis cache and Kafka outbox, write the failure table: primary loss, replica loss, Redis loss, Kafka loss, disk full, connection exhaustion, bad migration.
When it breaks
Deleting files from pg_wal to free disk space
What you see
The database can't recover or start; replicas and PITR break; data loss is likely.
Fix & prevent
Never touch pg_wal manually. Free space by fixing archiving, dropping unused slots, removing logs or temp files, or growing the volume.
Explain it without notes
What happens when PostgreSQL's disk fills up, and what should you do?
Practice
Run the connection-exhaustion lab and document detection, impact, mitigation and prevention.
Trade-offs
- ↔
Failure testing costs time and some risk in staging; skipping it moves the learning to production incidents.
Done when you can
I can list failure modes for a database architecture with detection, mitigation, recovery and prevention, and I've practised them.