autovacuum_vacuum_scale_factor
Fraction of table dead tuples before VACUUM fires. Default 0.2 = BIG TABLE TRAP: 1B-row table waits 200M dead tuples. Reduce to 0.01-0.05 for large tables.
Tier 1 — Lab-Proven — Measured behavior and safety boundaries are available below.
What We Observed
box
(PG 17.10), avsf_test 1M rows
scale factor 002
- heap before
42MB
- heap after
44MB (clean)
- dead tuples
0
- vacuum trigger
20,000 dead tuples
scale factor 040
- heap before
42MB
- heap after
61MB (45% bloat)
- dead tuples
150000
- vacuum trigger
400,000 dead tuples
big table trap
- description
Default scale_factor=0.2 on a 10M row table: trigger at 2M dead tuples. On 1B rows: 200M dead tuples before VACUUM fires.
- evidence
10M row lab: 1.5M dead tuples sat uncleaned at scale_factor=0.2
- source file
avsf danger raw
=== DANGER A: steady-state BLOAT under churn (600k row-updates), lax vs aggressive scale_factor === scale_factor | trigger_thr | heap_before | heap_after | final_n_dead_tup
0.02 | 20050 | 42 MB | 44 MB | 0
0.40 | 400050 | 42 MB | 61 MB | 150000(aggressive 0.02 fires continuously -> heap stays tight; lax 0.40 rarely fires -> heap and dead tuples bloat.)
=== DANGER B: BIG-TABLE TRAP -- absolute dead-tuple budget = scale_factor * reltuples === predicted trigger at the DEFAULT scale_factor=0.2 (threshold=50):
reltuples=1000000 -> autovacuum waits for ~200050 dead tuples reltuples=10000000 -> autovacuum waits for ~2000050 dead tuples reltuples=100000000 -> autovacuum waits for ~20000050 dead tuples reltuples=1000000000 -> autovacuum waits for ~200000050 dead tuples
physical proof on a 10,000,000-row table at sf=0.2 (threshold ~2,000,050):
reltuples=9999860 threshold=2000022 dead_made=1500000 fired=NO n_dead_tup=1506520 (same 0.2: a 10M table lets 1.5M dead tuples sit uncleaned -> 10x the 1M table. On 1B rows it waits for 200M.)
=== restore lab autovacuum settings ===
Reproduced Danger
Sizing / Safe Range
Workload Guidance
Unlock Incident Pro
The free sections tell you what to do. Pro is the measured proof — the exact failure signature, safe-ceiling formula, and per-workload defaults, each captured on real PG 14–18 runs.
- Reproduced Danger
- Sizing / Safe Range
- Workload Guidance
autovacuum_vacuum_scale_factor decides HOW MUCH dead garbage a table is allowed to accumulate before autovacuum cleans it. Every UPDATE and DELETE leaves a dead tuple (the old row version) behind; autovacuum's job is to reclaim that space and keep the visibility map current. It does NOT vacuum on a fixed schedule — it vacuums a table when the number of dead tuples crosses a threshold computed from the table's size: vacuum_threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples. The scale_factor is the PROPORTIONAL part: at the default 0.2, autovacuum waits until 20% of the table's estimated live rows are dead before it acts (plus the small fixed autovacuum_vacuum_threshold, default 50). Lower it and autovacuum fires sooner and more often (tighter heap, less bloat, more vacuum work); raise it and autovacuum tolerates more dead tuples (less vacuum work, more bloat). It is the single biggest lever on how aggressively your tables are kept clean — and its proportional design is exactly what makes it a trap on very large tables.
- name
autovacuum_vacuum_scale_factor
- setting default
0.2
- unit
(none — a fraction of reltuples)
- vartype
real
- category
Autovacuum
- context
sighup
- min val
0
- max val
100
- enumvals
(n/a — real)
- boot val
0.2
- reset val
0.2
- pending restart
false
- short desc
Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
- ground truth note
Captured literally from pg_settings on PG 14-18 (-18) on 2026-06-19. setting=0.2, boot_val=0.2, reset_val=0.2 on EVERY version (default_changed=false, boot_fallback_changed=false). context=sighup (reloadable cluster-wide, no restart; also overridable per-table as a storage reloption). vartype=real, min_val=0, max_val=100, unit empty. category='Autovacuum', short_desc='Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.' Default and bounds are identical across 14, 15, 16, 17, 18. (pg_settings reports the category as 'Autovacuum'; the docs group it under Automatic Vacuuming.)
Rock-stable across versions: default=0.2, min=0, max=100, context=sighup, vartype=real on PG 14, 15, 16, 17 and 18 — no version cliff (default_changed=false, boot_fallback_changed=false). The trigger arithmetic is also unchanged: autovacuum vacuums a table when n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples. The version-adjacent nuance is NOT in this GUC's value but in surrounding autovacuum machinery: PG13 added autovacuum_vacuum_insert_scale_factor (a SEPARATE insert-driven trigger, also default 0.2), and PG18 makes a new related control available — but autovacuum_vacuum_scale_factor itself and its 0.2 default have not moved. Per-version detail in _guc_matrix_machine.
context=sighup: change it in postgresql.conf (or ALTER SYSTEM) and reload — NO restart. It applies cluster-wide as the default for every table. Crucially it is ALSO a per-table storage reloption: ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.05) overrides the global value for that one table and is the recommended way to tune a specific hot/large table without touching everyone else. The lab set the global value via ALTER SYSTEM + pg_reload_conf and swept per-table values with the reloption. The trigger is re-evaluated continuously by the autovacuum launcher (every autovacuum_naptime, default 60s); the new factor takes effect on the next evaluation cycle. There is no need to ANALYZE for the factor to take effect, but the threshold uses reltuples (refreshed by ANALYZE/VACUUM), so a stale reltuples slightly shifts the trigger point. BOUNDARY PROOF (captured literal): an out-of-range / invalid value is rejected at set time -- ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 1e13; -> ERROR: 1e+13 is outside the valid range for parameter "autovacuum_vacuum_scale_factor" (0 .. 100). It is context=sighup, so a session-level set is refused -- SET autovacuum_vacuum_scale_factor = ... -> ERROR: parameter "autovacuum_vacuum_scale_factor" cannot be changed now; change it in postgresql.conf / ALTER SYSTEM and reload (pg_reload_conf), no restart.
Managed providers (Azure Database for PostgreSQL Flexible Server, Amazon RDS / Aurora, Cloud SQL, Crunchy, Supabase, Neon, Heroku) all expose autovacuum_vacuum_scale_factor as a server parameter (sighup) and honor per-table reloptions, with the default 0.2 everywhere. The tuning advice is identical to self-hosted: leave the global default for small/medium tables and lower the per-table scale_factor (or use a fixed autovacuum_vacuum_threshold with scale_factor=0) on your large, high-churn tables. Several managed services surface autovacuum/bloat metrics (n_dead_tup, last_autovacuum) in their dashboards — use those to find the tables that need a per-table override rather than changing the cluster-wide default.
Common Pitfalls
- Leaving the default 0.2 on very large, high-churn tables — the proportional trigger lets a 100M-row table accumulate ~20M dead tuples (and a 1B-row table ~200M) before autovacuum acts; big tables need a LOWER per-table factor or a fixed threshold.
- Lowering the GLOBAL default to fix one big table — it makes autovacuum fire constantly on every small table too; use a per-table reloption instead.
- Shrinking scale_factor without giving autovacuum the capacity to keep up — if cost_limit/workers are too low, you queue more vacuums than autovacuum can finish and bloat still wins.
- Forgetting reltuples drives the threshold — a table with badly stale statistics (no recent ANALYZE) triggers at the wrong point; on a freshly truncated/reloaded table reltuples can be 0 until analyzed.
- Assuming a change needs a restart — it is sighup (reload) for the global and a plain ALTER TABLE for a per-table override; both take effect on the next autovacuum cycle.
- Setting it very high to 'reduce vacuum load' and unknowingly raising transaction-ID-wraparound and disk-bloat risk — the work is deferred, not removed, and comes back as a bigger, more disruptive vacuum (or an anti-wraparound autovacuum) later.
Evidence and References
- PostgreSQL docs: Runtime Config — Automatic Vacuuming (autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold; trigger = threshold + scale_factor × reltuples).
- PostgreSQL docs: Routine Vacuuming — Autovacuum daemon (when autovacuum decides to process a table).
- PostgreSQL docs: CREATE TABLE / ALTER TABLE Storage Parameters (per-table autovacuum_vacuum_scale_factor reloption).
- PostgreSQL docs: pg_stat_user_tables (n_dead_tup, autovacuum_count, last_autovacuum).
- PostgreSQL docs: Preventing Transaction ID Wraparound Failures (why deferring vacuum too long is dangerous; autovacuum_freeze_max_age backstop).
- Lab capture 2026-06-19: , 1M-row av table (fire/no-fire + steady-state bloat) and 10M-row av10 table (big-table trap); pg_settings ground truth PG 14-18 (-18).
Technical Reference
- Category
- Autovacuum
- Type
- real ((none; a fraction of reltuples — the proportion of a table's estimated live rows that must become dead before autovacuum vacuums it; combined with autovacuum_vacuum_threshold into a dead-tuple trigger))
- Blast radius
- database
- Severity
- High
- Context
- sighup
- Versions
- 14, 15, 16, 17, 18
- Reviewed
- 2026-06-19
Severity scale: 1 Minimal · 2 Low · 3 Moderate · 4 High · 5 Critical.