Postgres VACUUM, Bloat & Autovacuum Tuning Prompt
Diagnose table and index bloat, decide between VACUUM, autovacuum tuning, and a rebuild, and produce per-table autovacuum settings — so dead tuples and wraparound risk stop quietly degrading your database.
- Target user
- DBAs and SREs managing a high-write Postgres database
- Difficulty
- Advanced
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior PostgreSQL DBA who keeps MVCC healthy: you tune autovacuum so dead tuples are reclaimed before bloat hurts, and you treat transaction-ID wraparound as a hard deadline, not a suggestion. I will paste: - Per-table stats from pg_stat_user_tables (n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze, autovacuum_count): [PG_STAT_USER_TABLES] - Bloat estimates or table/index sizes (pg_relation_size, your bloat query): [BLOAT / SIZES] - Current autovacuum settings (autovacuum_vacuum_scale_factor, _threshold, _cost_limit, naptime, max_workers) and any per-table overrides: [SETTINGS] - age(relfrozenxid) for the biggest tables and the workload write profile: [XID AGE / WORKLOAD] Work through: 1. **Rank bloat and risk** — identify tables with a high dead-tuple ratio (n_dead_tup relative to n_live_tup), tables autovacuum is falling behind on (stale last_autovacuum with growing dead tuples), and any table whose relfrozenxid age approaches autovacuum_freeze_max_age (wraparound risk — highest priority). 2. **Choose the remedy per table** — plain VACUUM (reclaim for reuse), VACUUM ANALYZE (also refresh stats), tuned autovacuum (the durable fix for hot tables), or an offline rebuild (VACUUM FULL / pg_repack) only when bloat is severe and reuse won't recover it. Explain the lock and downtime implications of each. 3. **Recommend per-table autovacuum overrides** — for hot tables, propose lower autovacuum_vacuum_scale_factor / analyze_scale_factor and an appropriate cost_limit so vacuum keeps up without starving the workload. Give the exact ALTER TABLE ... SET (autovacuum_*) statements. 4. **Address wraparound** — if any table's XID age is high, give the manual VACUUM (FREEZE or aggressive vacuum) to run and explain monitoring going forward. Output format: (a) a priority table [table | symptom | remedy | lock impact], (b) the exact ALTER TABLE / VACUUM commands, (c) a short monitoring query to re-check after. Guardrails: VACUUM FULL and pg_repack take heavy locks — schedule them in a maintenance window and rehearse on a replica/snapshot first. Never run VACUUM FULL on a hot prod table during business hours. Wrap ALTER TABLE settings changes in a transaction and confirm the effect with pg_stat_user_tables before and after.
Why this prompt works
Bloat and autovacuum problems are invisible until they aren’t: queries slow, disk grows, and eventually wraparound threatens to halt writes entirely. This prompt makes the database’s own catalog views do the diagnosis — n_dead_tup ratios, stale last_autovacuum timestamps, and relfrozenxid age — so the remedy is grounded in what the tables actually look like rather than a generic “run VACUUM” reflex.
It also separates the four remedies that engineers routinely conflate. Plain VACUUM reclaims space for reuse, tuned autovacuum is the durable fix for hot tables, and VACUUM FULL or pg_repack is a heavy last resort with real lock cost. Forcing the model to name the lock and downtime implications of each prevents an offhand VACUUM FULL from taking a production table offline.
By elevating transaction-ID wraparound to top priority and emitting concrete per-table ALTER statements plus a re-check query, the output is a safe, ordered runbook. The guardrails keep the destructive operations behind maintenance windows and replica rehearsals, leaving the human firmly in control.
Related prompts
-
Postgres postgresql.conf Workload Tuning Prompt
Get a reviewed postgresql.conf tuning plan for your specific hardware and workload — memory, WAL/checkpoint, planner, and autovacuum settings explained one by one, with how to verify each took effect.
-
Postgres Index Advisor from pg_stat_statements Prompt
Mine pg_stat_statements for your most expensive queries and get a prioritized list of missing indexes to add and redundant indexes to drop — with the write cost of each spelled out.