Scenario · Vacuum & Bloat

Dead tuples growth

A sandboxed PostgreSQL incident — investigate with your own tools, submit a fix, and get deterministic Detect / Fix / Trap scoring.

L2 · 10–15 min · runs locally in Docker

Launch

Start this scenario

Boot it in a real PostgreSQL sandbox and investigate with psql, EXPLAIN and pg_stat_statements.

ride postgres start stage-07/05-dead-tuples-growth

Part of these paths

Vacuum, Bloat & Wraparound DBA Path

Show the postmortem & investigation hints spoilers

Dead tuples growth
Type: incident simulation · Topic: Vacuum & Bloat · Level: L2 · Duration: 10–15 min
Launch: ride postgres start stage-07/05-dead-tuples-growth

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: sustained update/delete churn on billing_db.invoices produced dead
tuples faster than cleanup reclaimed them, so n_dead_tup grew and the table's space
ballooned. app_db was healthy — the growth was isolated to one database/table.

How it was found: pg_stat_user_tables (ordered by n_dead_tup) pinpointed invoices
in billing_db with a high dead-tuple count and cleanup falling behind.

The mitigation: VACUUM ANALYZE invoices to reclaim the dead tuples.

Lesson: dead-tuple growth is an early operational warning — find the churning
table (in the right database) and reclaim it. The deeper fixes vary (autovacuum
thresholds, reducing churn, clearing old snapshots), but the immediate response is
a targeted VACUUM, not an index, not the wrong database, and not VACUUM FULL.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. Disk usage on one database is climbing. Find the table with the most dead tuples: \connect billing_db then SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze, last_autovacuum, autovacuum_count FROM pg_stat_user_tables ORDER BY n_dead_tup DESC; invoices has a huge dead-tuple count and cleanup is behind.
2. Heavy update/delete churn on billing_db.invoices outpaced cleanup, so dead tuples piled up. app_db is healthy — make sure you act on the right database.
3. Reclaim it: \connect billing_db then VACUUM ANALYZE invoices; Don't add an index, don't VACUUM the wrong database, and VACUUM FULL is not an incident reflex.

Start now →← All scenarios