Tuning autovacuum and bloat
Skill pumarogie/claude-postgres-skills/skills/tuning-autovacuum-and-bloat
Production Postgres survival skills for Claude Code — schema design, safe migrations, query performance, connection pooling, autovacuum/bloat, and queue/partitioning patterns.
npx -y skills add pumarogie/claude-postgres-skills --skill tuning-autovacuum-and-bloatAssembled from the repository path, not quoted from the project. Check it against their README if it does not work.
2 things to look at
- 15 days oldThe repository was created 15 days ago. New is not bad, but a brand new repository carrying a familiar-sounding name is the shape a typosquat arrives in, and there has been no time for anyone else to find a problem with it.
- 1 stars1 stars. Stars are a popularity signal and not a quality one, but at this level it is likely that nobody has read this closely except its author, and you would be relying on your own review.
What its author says it does
Copied from the file, not written here
Use when writes outpace vacuum, autovacuum runs for a long time, transaction-ID wraparound is a risk, or tables and indexes are bloating on disk.
SKILL.md
2.6 KB, 644 tokens by cl100k_base, as published. Nobody here has run it
Tuning Autovacuum and Bloat
Overview
Every UPDATE/DELETE leaves the old row version on disk as a dead tuple. Autovacuum reclaims
dead tuples and manages transaction IDs. When writes outpace autovacuum you get bloat and, in the
worst case, transaction-ID wraparound — a forced-downtime event. Tune proactively, not after it hurts.
When to Use
- Fast write workload where dead tuples may pile up.
- Autovacuum queries running a long time (> ~1 hour is a warning sign).
- Table or index disk usage growing faster than the data.
- Preventing transaction-ID wraparound.
Quick Reference
| Problem | Cause | Fix |
|---|---|---|
| Dead tuples pile up | Writes outpace autovacuum | Tune autovacuum to run more aggressively |
| Autovacuum runs > ~1h | Under-tuned settings | Retune (Cybertec: tuning-autovacuum-postgresql) |
| Txn ID wraparound | IDs exhausted before reclaim | Catastrophic downtime — monitor & prevent |
| Table bloat | Partly-filled 8KB pages | pg_repack; avoid VACUUM FULL (long lock) |
| Index bloat | Same, on the index | REINDEX INDEX CONCURRENTLY |
Monitoring
Watch for long-running autovacuum before it becomes a problem:
-- autovacuum workers running right now, oldest first
SELECT pid, now() - xact_start AS running_for, query
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%'
ORDER BY xact_start;
An autovacuum running longer than ~1 hour means your settings need tuning.
Fixing Bloat
- Table bloat comes from 8KB pages that can't fit new rows, so Postgres allocates new pages;
reclaimed dead tuples leave pages partly filled, inflating disk usage. Use the
pg_repackextension to rewrite tables online. AvoidVACUUM FULL— it takes a long exclusive lock. (Postgres 19 addsREPACK … CONCURRENTLYnatively.) - Index bloat is a special case — rebuild online with
REINDEX INDEX CONCURRENTLY. - The real cure for both is good autovacuum settings applied before bloat accumulates.
Common Mistakes
- Running
VACUUM FULLto fix bloat — it locks the table for the whole rewrite. Usepg_repack. - Ignoring long autovacuum runs until wraparound forces downtime.
- Long-running transactions — they hold back the "oldest transaction" horizon so autovacuum can't
reclaim dead tuples (see
writing-safe-migrations).
What ships with it: 1 file
3.3 KB alongside SKILL.md