Tuning Autovacuum And Bloat

---
name: tuning-autovacuum-and-bloat
description: Guides autovacuum and bloat remediation when dead tuples accumulate, write-heavy tables outgrow scale-factor defaults, vacuum falls behind, transaction-ID age approaches freeze limits, or tables and indexes consume disproportionate disk.
---

# Tuning Autovacuum and Bloat

## Overview

`UPDATE` and `DELETE` leave dead row versions. Vacuum makes their space reusable and freezes old transaction IDs; it usually does not return table space to the filesystem. Tune per high-write table before dead tuples, index churn, or transaction-ID age becomes an incident.

## Diagnose before rewriting

```sql
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_autovacuum, autovacuum_count,
       last_autoanalyze, autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
```

Statistics are estimates. Check write rate, vacuum progress, long-running transactions, replica feedback, and disk growth together. A long vacuum is not automatically unhealthy if it is making progress and transaction-ID age remains safe.

Always look for cleanup blockers: long-running transactions, abandoned `idle in transaction` sessions, old replication slots, and standby feedback. These can hold back the oldest removable row version even when autovacuum runs.

## Start with per-table tuning

Large busy tables should not wait for a large fraction of all rows to change. A concrete starting point—not a universal optimum—is:

```sql
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_vacuum_cost_limit = 2000
);
```

This requests vacuum after roughly 1% of estimated rows plus 1,000 changes and gives that table more work budget per cost-delay cycle. Measure I/O and vacuum duration, then tune one step at a time. On very large tables, derive the scale factor from the maximum dead tuples you can tolerate rather than copying a percentage.

Concrete parameter guidance, progress queries, and transaction-ID monitoring: [reference/autovacuum-settings-and-wraparound.md](reference/autovacuum-settings-and-wraparound.md).

## Prevent wraparound

Measure both table and database age, and compare it with the configured setting:

```sql
SELECT c.oid::regclass, age(c.relfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max_age
FROM pg_class AS c WHERE c.relkind IN ('r', 'm')
ORDER BY age(c.relfrozenxid) DESC;

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database ORDER BY age(datfrozenxid) DESC;
```

**`autovacuum_freeze_max_age` is the forced-autovacuum trigger horizon, not the shutdown boundary.** Alert before it so there is time to remove blockers and let vacuum finish. Do not infer wraparound safety from dead-tuple counts.

Treat rapidly rising age, repeated canceled anti-wraparound vacuums, or blockers older than the vacuum horizon as urgent. **Never terminate backends blindly as the first response.** Identify the old transaction, slot, or standby-feedback blocker, follow incident procedures, and let vacuum finish; do not casually raise freeze limits.

## Remediate bloat safely

- Normal `VACUUM` reuses space inside the relation and permits ordinary reads and writes.
- Table rewrites can return space to the filesystem but require extra disk and stronger locking. Use a carefully rehearsed online rewrite tool such as `pg_repack` when its prerequisites and operational tradeoffs are acceptable.
- `VACUUM FULL` takes an `ACCESS EXCLUSIVE` lock for the rewrite; avoid it on a live table unless downtime is intentional.
- Rebuild a bloated index with `REINDEX INDEX CONCURRENTLY`; follow `writing-safe-migrations` for lock timeouts and concurrent-operation failure handling.

**For a bloated index on a heavily updated table, `REINDEX INDEX CONCURRENTLY` fixes the symptom; treat under-tuned per-table autovacuum as an underlying cause that must be corrected to prevent recurrence.** Check that vacuum keeps pace, then apply concrete table settings such as `autovacuum_vacuum_scale_factor = 0.01`, `autovacuum_vacuum_threshold = 1000`, and `autovacuum_vacuum_cost_limit = 2000`, measuring and adjusting for the table. Also inspect update patterns and fillfactor for additional causes.

Keep index and table remedies distinct: use concurrent reindexing for the index; use `pg_repack` when the table itself must be rewritten online. Never substitute `VACUUM FULL` on a live table—it takes `ACCESS EXCLUSIVE`.

## Common Mistakes

- Applying one aggressive cluster-wide setting instead of targeting the tables producing churn.
- Treating `n_dead_tup` as exact or using one snapshot without a rate.
- Canceling a long anti-wraparound vacuum repeatedly.
- Ignoring long-lived or idle-in-transaction sessions that hold back cleanup.
- Expecting normal vacuum to shrink the relation file.
- Running `VACUUM FULL` as routine maintenance on a live table.