PostgreSQL VACUUM & ANALYZE
debt(d5/e5/b7/t7)
Closest to 'specialist tool catches' (d5). The term's detection_hints.tools list cites pganalyze, pg-stat-statements, and datadog — all specialist monitoring tools that can detect bloated tables, stale statistics, and autovacuum lag. Default linters won't catch this; it requires dedicated database monitoring infrastructure.
Closest to 'touches multiple files / significant refactor' (e5). The quick_fix suggests scheduling weekly ANALYZE and tuning autovacuum thresholds. While a single ANALYZE command is trivial, properly addressing VACUUM/ANALYZE issues requires understanding table write patterns, tuning autovacuum parameters per-table, setting up monitoring, and potentially restructuring maintenance windows — a significant operational refactor.
Closest to 'strong gravitational pull' (b7). This applies broadly (web, cli contexts) and affects all PostgreSQL-backed PHP applications. Once you have high-write tables, VACUUM maintenance becomes a persistent operational concern that shapes how you design updates, partition tables, and schedule maintenance. Every schema change and write-heavy feature must consider autovacuum impact. It's a load-bearing operational decision.
Closest to 'serious trap' (t7). The misconception field explicitly states developers believe 'VACUUM is for disk space' when the real purpose is preventing transaction ID wraparound (database emergency) and table bloat affecting query performance. This contradicts intuition from other databases where cleanup is simpler. The common_mistakes reinforce this: VACUUM FULL seems like the 'thorough' option but locks the table, and disabling autovacuum seems safe but causes rapid bloat.
Also Known As
TL;DR
Explanation
PostgreSQL's MVCC never overwrites rows in-place — updates write a new row version and mark the old one dead. Dead tuples accumulate, bloating tables and indexes. VACUUM reclaims this space (VACUUM FULL rewrites the table but takes an exclusive lock). autovacuum handles this automatically but may need tuning for high-write tables. ANALYZE samples table data to update statistics used by the query planner — stale statistics cause bad query plans. VACUUM ANALYZE runs both. Transaction ID wraparound (XID wraparound) is a critical failure mode prevented only by regular vacuuming.
Diagram
flowchart TD
subgraph MVCC Updates
UPD[UPDATE row] --> OLD[Old version<br/>marked dead]
UPD --> NEW[New version<br/>written]
OLD --> BLOAT[Dead tuple<br/>accumulates]
end
subgraph VACUUM
BLOAT --> VAC[VACUUM reclaims space]
VAC --> CLEAN[Reusable pages]
end
subgraph ANALYZE
STATS[Table statistics] --> ANA[ANALYZE updates stats]
ANA --> PLAN[Query planner<br/>makes better choices]
end
AUTO[autovacuum<br/>runs automatically] -.->|triggers| VAC & ANA
style CLEAN fill:#238636,color:#fff
style AUTO fill:#1f6feb,color:#fff
style PLAN fill:#238636,color:#fff
Common Misconception
Why It Matters
Common Mistakes
- VACUUM FULL on production tables — takes an exclusive lock, blocking all reads and writes.
- Disabled autovacuum on high-write tables — tables bloat rapidly without it.
- Not monitoring autovacuum metrics — autovacuum silently failing means no maintenance happening.
- Manual VACUUM replacing autovacuum tuning — tune autovacuum thresholds for busy tables.
Code Examples
-- autovacuum disabled on busy table -- DO NOT DO:
ALTER TABLE events SET (autovacuum_enabled = false);
-- 100M writes/day later:
-- Table size: 200GB (actual data: 20GB)
-- 94% dead tuples
-- Query planner using 3-month-old statistics
-- All queries doing sequential scans-- Tune autovacuum for high-write tables:
ALTER TABLE events SET (
autovacuum_vacuum_scale_factor = 0.01, -- Vacuum at 1% dead tuples (not 20%)
autovacuum_analyze_scale_factor = 0.005 -- Analyze at 0.5% changes
);
-- Monitor bloat:
SELECT relname, n_dead_tup, n_live_tup,
round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables ORDER BY dead_pct DESC;
-- Emergency: non-blocking vacuum during business hours:
VACUUM (VERBOSE, ANALYZE) events; -- No FULL -- no lock