Vacuum, Autovacuum, and Bloat

PostgreSQL updates and deletes leave old tuple versions until vacuum can reclaim their space for reuse. Autovacuum also updates visibility information and prevents transaction-ID wraparound. Bloat occurs when allocated table or index space is poorly utilized; its cause matters more than its label. Long transactions, write patterns, thresholds, and maintenance capacity determine health.

What will you be able to do?

• Explain dead tuples, vacuum, analyze, freezing, and bloat.
• Inspect table maintenance statistics and long transactions.
• Tune per-table autovacuum from workload evidence.
• Distinguish reusable space from returned filesystem space.

Prerequisites and mental model

MVCC keeps old row versions for snapshots. Vacuum identifies versions no longer visible to any relevant transaction and marks space reusable. Normal vacuum usually does not shrink the table file; rewrite-style operations can return space but require disruptive planning.

What can you inspect?

Statistics are estimates and can reset. Combine them with table/index size, workload, logs, progress views, and supported extensions/tools.

High-churn tables may need lower per-table scale factors/thresholds or more maintenance capacity. Do not disable autovacuum; wraparound protection is mandatory.

Why does this matter in AI-native systems?

Re-embedding millions of chunks creates update/delete churn, new indexes, and WAL. Prefer versioned batch replacement with observable cleanup. Long model/evaluation jobs must not wrap all work in one database transaction, or they can block cleanup for hours.

Prove it worked

In a disposable table, insert and update many rows, inspect statistics, run VACUUM (ANALYZE) outside a transaction block, and compare. Show that logical cleanup and file shrink are different claims.

Production judgment

• Emergency wraparound vacuum can consume resources and block some operations; prevent it through monitoring.
• VACUUM FULL rewrites and takes strong locks; it is not routine medicine.
• Index bloat has separate causes and remediation.
• Replication slots and long replicas can retain WAL even when vacuum is healthy.

Common mistakes

• Autovacuum disabled to “save CPU”: maintenance debt grows → tune capacity/thresholds instead.
• Table file did not shrink: normal vacuum reuses internally → measure whether shrink is actually needed.
• Dead tuples persist: old snapshot/slot/workload pressure → identify blocker and cause.

AI Pair-Programmer Prompt

Hands-on exercise

Design maintenance for a chunk table replaced in batches during re-embedding.

Acceptance criterion: batches are short, old versions have lifecycle state, cleanup is monitored, and no job holds a transaction for the full rebuild.

Knowledge check

1. Why do old tuple versions exist?
2. Does normal vacuum normally shrink files?
3. Why is wraparound prevention special?

---

Answers

1. MVCC snapshots may still need them until they become globally removable.
2. No; it generally marks space reusable internally.
3. Transaction ID reuse could make visibility unsafe, so freezing is mandatory.

Completion checklist

• Autovacuum remains enabled.
• Long transactions are monitored.
• Bloat cause is identified before remediation.
• AI backfills use short batches and cleanup evidence.

Primary references

• Routine vacuuming
• Autovacuum configuration
• VACUUM