Debug: PostgreSQL table bloat and VACUUM

PostgreSQL table grows much larger than its actual data due to dead tuples from updates and deletes.

Diagnose and fix table bloat:

-- 1. Check dead tuple count
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) as dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- 2. Check table size vs actual data
SELECT pg_size_pretty(pg_table_size('my_table')) as table_size,
       pg_size_pretty(pg_total_relation_size('my_table')) as total_with_indexes;

-- 3. Estimate bloat (using pgstattuple extension)
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('my_table');
-- dead_tuple_percent > 20% = significant bloat

-- 4. Regular VACUUM (reclaims space for reuse, doesn't shrink file)
VACUUM VERBOSE my_table;

-- 5. VACUUM FULL (rewrites table, reclaims disk space)
-- WARNING: Locks table exclusively!
VACUUM FULL my_table;

-- 6. Better alternative: pg_repack (no locks)
-- pg_repack --table my_table --no-superuser-check -d mydb

Tune autovacuum for high-write tables:

ALTER TABLE my_table SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- Default 0.2 (20%)
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_cost_delay = 2        -- More aggressive
);

PostgreSQL MVCC keeps old row versions until VACUUM removes them. High-write tables can bloat to many times their data size if autovacuum can't keep up.

PostgreSQL databases with high write/update workloads