Production Failure Scenarios

Every earlier lesson explains a mechanism. This one is about what happens when those mechanisms are neglected — the handful of failure modes that account for most Postgres production incidents.

Table bloat from a stalled autovacuum

MVCC means every UPDATE/DELETE leaves the old row version in place until VACUUM reclaims it. If autovacuum can't keep up — a long-running transaction holding back the cleanup horizon, or autovacuum simply undertuned for the write rate — dead tuples accumulate. Tables and indexes bloat, sequential scans get slower (more dead rows to skip over per live row), and eventually disk fills.

SELECT relname, n_live_tup, n_dead_tup,
     round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Transaction ID wraparound

Postgres transaction IDs are 32-bit and MVCC visibility is defined in terms of comparing them, which only works if "old" and "new" stay well-ordered — so IDs are compared cyclically, and rows need to be periodically frozen (marked as "always visible") so old IDs can safely be reused. If autovacuum can't freeze fast enough — same root cause as bloat, usually a stuck transaction or disabled autovacuum — Postgres eventually forces the issue: past a warning threshold it logs aggressively, and past the hard limit it shuts down and refuses new transactions rather than risk data corruption from ID wraparound. This is one of the few Postgres failure modes that is a full outage, not just degraded performance.

Lock storms

A single query holding a lock longer than expected — an ALTER TABLE that takes an ACCESS EXCLUSIVE lock, or an idle in transaction connection sitting on a row lock — can cascade: every subsequent query touching that table queues up behind it. pg_stat_activity fills with backends in state = active but making no progress, all waiting on the same lock. From the outside this looks identical to the database being "down," even though nothing has crashed.

-- who's blocking whom, right now
SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocking.query
FROM pg_locks blocked
JOIN pg_locks blocking
ON blocking.locktype = blocked.locktype
AND blocking.database IS NOT DISTINCT FROM blocked.database
AND blocking.relation IS NOT DISTINCT FROM blocked.relation
AND blocking.pid != blocked.pid
AND blocking.granted
JOIN pg_stat_activity blocked_a ON blocked_a.pid = blocked.pid
WHERE NOT blocked.granted;

Replication lag growing unbounded

A replica that can't keep up — undersized hardware, a network blip, or a heavy query holding a snapshot open on the replica — falls further behind with every write on the primary. Left unaddressed, reads against that replica become increasingly stale, and if the primary's WAL retention runs out before the replica catches up, the replica can no longer resume and needs to be rebuilt from a fresh base backup.

Connection exhaustion

Every backend is a process holding real memory. Without pooling, each new app instance opening its own connection pool adds up fast, and once max_connections is hit, new clients get FATAL: too many connections — including the operational tooling you'd want to use to diagnose the problem.

OOM from a runaway query

As covered in Performance Tuning, work_mem is per-operation, not per-query. A handful of concurrent queries each doing several large sorts or hash joins can multiply past available RAM, and the Linux OOM killer doesn't negotiate — it picks a process (often the postmaster or a busy backend) and kills it, which can crash the whole instance and trigger crash recovery on restart.

The pattern underneath all of these

Every scenario above is a mechanism from an earlier lesson, left unmonitored past the point where it self-corrects: vacuum falling behind, a lock held too long, a replica falling behind, connections piling up, memory multiplying. None of them are exotic — they're the ordinary mechanisms this module covers, given enough time and enough neglect. The Monitoring lesson's catalog views are exactly what catches each of these while they're still a warning in a dashboard, not yet a page at 3am. That's the real point of this whole module: understand what Postgres is doing internally, and production failures stop being mysterious — they're just the mechanism you already know, showing up somewhere you weren't looking.

That closes out the module. Back to the PostgreSQL overview for the full lesson list, from why Postgres exists through everything covered here.