🐘 PostgreSQL Intermediate

What is the difference between VACUUM and ANALYZE in PostgreSQL?

Answer

VACUUM reclaims disk space occupied by dead tuples (rows deleted or updated under MVCC). VACUUM tablename; marks space as reusable (but doesn't return to OS). VACUUM FULL tablename; rewrites the entire table to reclaim disk space — requires an exclusive lock. VACUUM FREEZE prevents transaction ID wraparound — critical for long-lived databases. ANALYZE collects statistics about table data distribution and stores them in pg_statistic — the query planner uses these to choose optimal plans. ANALYZE tablename; updates statistics. VACUUM ANALYZE does both in one pass. autovacuum is the background daemon that runs VACUUM and ANALYZE automatically — never disable it in production.