Gotcha: PostgreSQL VACUUM and table bloat

PostgreSQL tables grow larger over time even with deletions. Dead rows accumulate, queries slow down, disk usage increases.

Understanding and managing PostgreSQL bloat:

1. Why it happens:

-- PostgreSQL MVCC keeps old row versions for concurrent transactions
-- Deleted/updated rows are 'dead' but still on disk
-- VACUUM marks dead space as reusable (but doesn't shrink file)

1. Check table bloat:

SELECT
relname,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
n_dead_tup,
n_live_tup,
last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

1. Autovacuum should handle most cases:

-- Check if autovacuum is running:
SELECT * FROM pg_stat_activity WHERE query LIKE '%autovacuum%';
-- Tune if needed:
ALTER TABLE big_table SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.01);

1. Manual vacuum:

VACUUM table_name; -- Mark dead rows reusable
VACUUM ANALYZE table_name; -- Also update statistics
VACUUM FULL table_name; -- Reclaim disk (LOCKS TABLE!)

1. For extreme bloat:

-- pg_repack: online VACUUM FULL (no lock)
-- CREATE TABLE new AS SELECT * FROM old; DROP old; RENAME...

1. Prevention:

-- Don't disable autovacuum
-- Don't run long transactions (blocks vacuum)
-- Monitor dead tuple ratio