VACUUM & ANALYZE

Postgres · Reference cheat sheet

📋 Overview

MVCC leaves dead row versions. VACUUM reclaims space for reuse; ANALYZE updates planner statistics. Autovacuum handles most cases — watch bloat and freeze age.

🔧 Core concepts

💡 Examples

Targeted maintenance:

VACUUM (ANALYZE) orders;

Check bloat signals:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Aggressive rewrite (maintenance window):

VACUUM FULL orders; -- locks heavily; prefer wisely

⚠️ Pitfalls

• VACUUM FULL is not routine — it locks and needs disk headroom.
• Disabling autovacuum “for performance” causes wraparound emergencies.
• Long transactions delay vacuum — find idle-in-transaction sessions.

🔗 Related

• explain_analyze.md
• indexes.md
• connection_pooling.md
• getting_started.md