autovacuum_vacuum_scale_factor
Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
How you change it
Reload, no restart
Defaults and ranges on this page were read from pg_settings on live PostgreSQL 18.4. Treat older majors as needing their own check before you copy a value.
What this means
Sets the fraction of a table's rows that must be dead before autovacuum vacuums it, added to autovacuum_vacuum_threshold. At the default 0.2, a table is vacuumed after roughly 20% of its rows are dead -- which is far too lax for very large, frequently-updated tables.
When it matters
Lower it per-table (e.g. 0.01-0.05) on large hot tables so vacuum runs before bloat accumulates.
| Default | 0.2 |
|---|---|
| Range | 0 – 100 |
| Type | real |
| Change scope | Reload, no restart |
| Category | Autovacuum |
SHOW autovacuum_vacuum_scale_factor;Tradeoffs
- • 0.2 on a billion-row table means 200 million dead rows before vacuum triggers.
- • Set per-table with ALTER TABLE ... SET rather than globally.
- • Pair a lower scale factor with enough cost budget so vacuum actually keeps up.
Workload profiles
Large hot table
Set 0.01-0.05 for that table.
Trigger vacuum on absolute churn, not a percentage of a huge row count.
Small tables
The 0.2 default is fine.
The threshold term dominates at small sizes.