magnus919_agent-skills/postgres/references/03-vacuum-and-bloat.md

Vacuum and Bloat Management

Last Updated: 2026-08-03

This reference covers the maintenance loop that keeps PostgreSQL tables healthy: autovacuum supervision, dead-tuple and bloat measurement, and the disciplined use of manual vacuum operations.

Why vacuum matters

• Vacuum reclaims dead tuple space and refreshes the visibility map and planner statistics. Without it, tables bloat and transaction ID wraparound becomes an emergency.
• autovacuum is on by default; the operational task is supervision: is it keeping up with the workload?

Measuring the signals

-- Dead tuples per table, worst first
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC LIMIT 10;

-- How far tables are from forced anti-wraparound vacuum
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database ORDER BY xid_age DESC;

-- Is a vacuum running right now, and where?
SELECT * FROM pg_stat_progress_vacuum;

The pgdiag bloat check reports the dead-tuple signal in one bounded payload.

Interpreting the evidence

• n_dead_tup rising faster than vacuum runs complete → autovacuum is falling behind. Common causes: not enough autovacuum_max_workers, long-running transactions pinning old snapshots, connection saturation, or very large tables where a full pass takes longer than autovacuum_naptime.
• Table file size far above live-data size → heap bloat. Check pg_class relpages against n_live_tup × average row width; a bloat estimate query can quantify it.
• Index bloat shows as large relpages on indexes relative to key count.
• age(datfrozenxid) approaching autovacuum_freeze_max_age (default 200M) is the wraparound warning; below ~50M it is time to act deliberately.

Acting deliberately

1. Confirm the target and maintenance window with the human before any maintenance vacuum; a manual VACUUM on a big table is a bounded mutation with a measurable cost.
2. Prefer VACUUM (ANALYZE) on the specific tables showing the problem over a blanket full-cluster pass.
3. VACUUM FULL rewrites the table and takes an exclusive lock; it is a last resort for severe bloat, never routine. It requires a maintenance window and a verified backup path.
4. After acting, re-run the bloat/dead-tuple probe and confirm the trend direction changed; also confirm autovacuum is keeping up afterwards.

Config levers (all reload-safe)

• autovacuum_max_workers, autovacuum_naptime, autovacuum_vacuum_cost_limit — worker capacity and pacing.
• Per-table overrides via storage parameters (autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold) for tables that need faster or slower cycles.

Hard boundaries

• Never run VACUUM FULL without a maintenance window and a confirmed backup.
• Never stop autovacuum to "save load" on a production instance — the deferred work comes back as bloat and wraparound risk.
• Never present a high n_dead_tup as a diagnosis by itself; it is evidence that the vacuum loop needs attention, and the cause (long transactions, worker starvation, pacing) must be identified before acting.