PostgreSQL VACUUM and ANALYZE Guide
Understand PostgreSQL VACUUM, ANALYZE, table bloat, planner statistics and safe DBA checks before changing maintenance settings. This Formalint reference is written for working developers, DBAs and support engineers who need a repeatable debugging path instead of a one-line snippet with no context.
Use the notes below as a practical review order: understand the input, capture evidence, make one small change and verify the result before moving to the next assumption.
When to use this page
Read PostgreSQL maintenance symptoms before tuning blindly. It is most useful when a small validation or debugging mistake can create noisy tickets, misleading logs or hard-to-review production changes.
Practical workflow
| Step | What to confirm |
|---|---|
| Inspect stats first | Look at dead tuples and last maintenance timestamps before making changes. |
| Separate VACUUM from ANALYZE | VACUUM reclaims dead row space for reuse; ANALYZE refreshes planner statistics. |
| Change slowly | Autovacuum tuning belongs in measured maintenance, not panic edits. |
Read-only maintenance checks
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;Review checklist
- Check pg_stat_user_tables before and after maintenance.
- Look for long transactions preventing cleanup.
- Review table size and index size together.
- Avoid VACUUM FULL in production without a lock plan.
- Record exact commands and timestamps.
Common mistake
The loud symptom may be a slow query, but the quiet cause can be stale statistics or a long transaction blocking cleanup.
Formalint is strongest when the page helps the developer decide what the tool cannot prove. Treat every formatter, regex and command as one layer of evidence, not the whole truth.
Frequently asked questions
Does VACUUM shrink the file immediately?
Normal VACUUM makes space reusable. VACUUM FULL rewrites and locks more aggressively.
When does ANALYZE help?
When planner statistics are stale and query plans stop matching real data distribution.
Related Formalint references
Continue with PostgreSQL DBA Checklist, PostgreSQL Lock Debugging, PostgreSQL Index Debugging.