Autovacuum starvation in a tenant database

Autovacuum starvation in a tenant database
Type: incident simulation · Topic: Vacuum & Bloat · Level: L2 · Duration: 10–15 min
Launch: ride postgres start stage-07/01-autovacuum-starvation-in-tenant-db

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: autovacuum was disabled on tenant_b.orders (autovacuum_enabled=false),
so update/delete churn there piled up dead tuples that were never reclaimed. The
default and app databases were healthy, so a default-database health check showed
all-green while one tenant slowly degraded.

How it was found: pg_stat_user_tables in tenant_b showed a high n_dead_tup and no
recent autovacuum; pg_class.reloptions showed autovacuum disabled on the table.

The mitigation: re-enable autovacuum on tenant_b.orders and run VACUUM ANALYZE to
reclaim the dead tuples.

Lesson: in a multi-database cluster, vacuum/bloat problems hide in individual
databases — inspect each tenant, not just the default. Re-enable autovacuum where
it was turned off and vacuum the affected table. Adding an index, or vacuuming the
wrong database, does nothing.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. The default/app database looks healthy, but one tenant is degrading. Check per-tenant: \connect tenant_b then SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC; tenant_b.orders has huge n_dead_tup and no recent autovacuum.
2. Autovacuum is disabled on tenant_b.orders: SELECT relname, reloptions FROM pg_class WHERE relname='orders'; shows autovacuum_enabled=false, so dead tuples never get reclaimed there.
3. Re-enable autovacuum and clean up — in tenant_b: ALTER TABLE orders RESET (autovacuum_enabled); VACUUM ANALYZE orders; Don't vacuum only the default database, and don't add an index.