Finding tables with high dead tuple counts

Tables are growing on disk faster than their live-row count suggests — dead tuples from UPDATEs and DELETEs are accumulating because autovacuum is not keeping pace.

Diagnose it

-- pg_stat_user_tables is pg_stat_all_tables filtered to user schemas:
SELECT schemaname,
       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,
       last_autovacuum,
       last_vacuum,
       autovacuum_count,
       n_mod_since_analyze
FROM pg_stat_all_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
  AND n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

Tables with high dead_pct AND a stale last_autovacuum are the
priority targets.

Why it happens

Every UPDATE in PostgreSQL writes a new row version; every DELETE marks the old version as
dead. Dead versions cannot be removed until no running transaction can still see them
(the VACUUM visibility horizon). When autovacuum runs too infrequently, dead tuples accumulate,
increasing table size and slowing sequential scans (more pages to read for fewer live rows).