Autovacuum Thresholds and Dead Tuple Drift #

Autovacuum’s defaults were chosen when a large table meant a few million rows. Applied unchanged to a table with hundreds of millions, they produce a daemon that waits patiently while tens of millions of dead tuples accumulate — inflating relation sizes, degrading index-only scans, and letting statistics drift far from reality before a single pass runs.

This page shows how to compute what your current settings actually mean for each table, and how to set thresholds that behave sensibly at scale. The plan-level consequences are covered in autovacuum and plan stability.

The Formula, Applied to Real Tables #

Both vacuum and analyze use the same shape:

vacuum threshold  = autovacuum_vacuum_threshold  + autovacuum_vacuum_scale_factor  × reltuples
analyze threshold = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × reltuples

Defaults are 50 / 0.2 for vacuum and 50 / 0.1 for analyze. Because the scale factor multiplies the row count, the absolute threshold grows without limit:

rows vacuum triggers after analyze triggers after
10,000 2,050 dead tuples 1,050 modifications
1,000,000 200,050 100,050
100,000,000 20,000,050 10,000,050
500,000,000 100,000,050 50,000,050

The last row is the problem. Half a billion rows means the planner tolerates 50 million modifications before refreshing its picture of the table — by which time the most-common-value list may describe a distribution that no longer exists.

Absolute threshold versus table size Two lines against table row count. With the default scale factor of zero point two, the dead tuple threshold rises steeply. With a tuned scale factor of zero point zero two, it rises far more gently, keeping the absolute threshold manageable at large table sizes. table rows → dead tuples tolerated scale_factor = 0.2 (default) scale_factor = 0.02 10M 100M 300M 500M 60M dead tuples 6M dead tuples

Reading the Drift #

pg_stat_user_tables holds everything needed to judge the current state:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       n_mod_since_analyze,
       last_autovacuum,
       last_autoanalyze
  FROM pg_stat_user_tables
 WHERE n_dead_tup > 100000
 ORDER BY n_dead_tup DESC;

Three patterns matter:

The same view tells you whether cleanup is blocked rather than slow:

SELECT pid, state, xact_start, now() - xact_start AS age, left(query, 60) AS query
  FROM pg_stat_activity
 WHERE xact_start IS NOT NULL
 ORDER BY xact_start
 LIMIT 5;

A transaction open for hours pins the oldest snapshot, and no amount of autovacuum tuning will reclaim tuples newer than it.

Step-by-Step Tuning #

  1. Rank tables by absolute dead tuples, not percentage — the biggest tables cause the biggest plan damage.

  2. Compute the effective threshold for each candidate so the numbers are concrete:

    SELECT relname,
       n_live_tup,
       50 + 0.2 * n_live_tup AS vacuum_threshold_now,
       50 + 0.02 * n_live_tup AS vacuum_threshold_tuned
  FROM pg_stat_user_tables WHERE relname = 'events';

  3. Set per-table storage parameters on the tables that need them:

    ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor  = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit    = 2000
);

  4. Raise the global cost limit if workers are the bottleneck. The default autovacuum_vacuum_cost_limit of 200 throttles a worker to a fraction of what modern storage can do:

    ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;
SELECT pg_reload_conf();

  5. Add workers only alongside the cost limit. The budget is shared, so more workers with the same limit means each runs slower.

  6. Watch a cycle complete rather than assuming it did:

    SELECT p.relid::regclass, p.phase, p.heap_blks_scanned, p.heap_blks_total
  FROM pg_stat_progress_vacuum p;

  7. Guard against snapshot holders. Set idle_in_transaction_session_timeout and monitor replication slot lag, both of which silently prevent reclamation.

Which setting to change A two by two matrix. Recent autovacuum with high dead percentage means raise the cost limit. Old autovacuum with high dead percentage means lower the scale factor. Low dead percentage with high modifications means lower the analyze scale factor. Low on both means leave it alone. dead_pct time since last_autovacuum → raise the cost limit running, but not keeping up lower the vacuum scale factor threshold never reached check n_mod_since_analyze insert-heavy: lower analyze factor leave it alone healthy table

Before and After #

-- BEFORE: defaults on a 480M-row table
n_dead_tup      : 61,204,882      dead_pct: 11.3
last_autovacuum : 3 days ago
index scan for 412 rows: Buffers shared hit=31 read=27      Execution Time: 11.9 ms

-- AFTER: scale_factor 0.02, cost_limit 2000
n_dead_tup      : 3,918,204       dead_pct: 0.8
last_autovacuum : 41 minutes ago
index scan for 412 rows: Buffers shared hit=9  read=2       Execution Time:  1.6 ms

The queries were untouched. Cutting the dead-tuple population by 94% removed the pages that had been padding every scan.

Common Pitfalls #

Lowering the scale factor globally. Every small table then gets vacuumed constantly, consuming worker slots the big tables need. Diagnostic signal: workers permanently busy on trivial tables. Fix: set the parameter per table.

Adding workers without raising the cost limit. The budget is shared, so each worker simply gets a smaller slice. Diagnostic signal: more workers, same total throughput. Fix: raise autovacuum_vacuum_cost_limit in step.

Tuning vacuum when the problem is analyze. An insert-only table accumulates no dead tuples but drifts statistically. Diagnostic signal: n_mod_since_analyze high, n_dead_tup near zero. Fix: lower autovacuum_analyze_scale_factor.

Ignoring snapshot holders. Long transactions, abandoned idle in transaction sessions, unused replication slots, and prepared transactions all block reclamation regardless of settings. Diagnostic signal: dead tuples rising despite a recent successful vacuum. Fix: find and clear the holder, then set timeouts.

Trusting n_dead_tup as an exact figure. It is an estimate maintained by the statistics collector and updated as backends report activity, so it lags reality and resets when statistics are reset. Diagnostic signal: a dead-tuple count that disagrees with pgstattuple on the same table. Fix: use the counter for ranking and alerting, and confirm with a direct measurement before a costly intervention.

Setting autovacuum_vacuum_threshold instead of the scale factor. The fixed component is dwarfed by the scaled one on any large table, so raising it from 50 to 5,000 changes nothing meaningful. Diagnostic signal: a tuning change with no observable effect on cycle frequency. Fix: adjust the scale factor, which is the term that actually scales.

Disabling autovacuum on a busy table. Turning it off to avoid interference guarantees transaction-ID wraparound protection eventually kicks in with an aggressive, unavoidable freeze pass at the worst possible moment. Diagnostic signal: autovacuum_enabled = false in a table’s reloptions. Fix: tune the cost limit so the daemon is unobtrusive rather than switching it off, and never rely on a nightly cron job as the only cleanup.

Ignoring insert-only tables before PostgreSQL 13. Older versions triggered vacuum only on dead tuples, so an append-only table was never vacuumed and its visibility map never advanced. Diagnostic signal: index-only scans with rising Heap Fetches on a table that is never updated. Fix: schedule periodic vacuums explicitly, or rely on the insert-based threshold on modern versions.

The threshold formula, term by term The fixed threshold of fifty is negligible beside the scaled term, which multiplies the scale factor by the row count, giving one hundred million dead tuples on a five hundred million row table at the default. threshold 50 + scale_factor × reltuples 0.2 × 500,000,000 = dead tuples tolerated 100,000,050 the fixed term is irrelevant at scale — only the scale factor is worth tuning

Frequently Asked Questions #

What scale factor should a large table use? Roughly 0.01–0.02, set per table. Think in absolute terms: on a 100-million-row table a scale factor of 0.01 still allows a million dead tuples before a cycle begins, which is usually the most you want to tolerate.

Why does n_dead_tup keep rising even though autovacuum ran? Because something is holding an old snapshot. Vacuum cannot remove tuples that a transaction older than their deletion might still need, so a long-running query, an idle-in-transaction session, a stale replication slot, or a prepared transaction freezes reclamation.

Is raising autovacuum_max_workers enough? Usually not. The I/O cost budget is shared across workers, so more workers each move more slowly. Raise autovacuum_vacuum_cost_limit at the same time, or set a per-table cost limit on the tables that must finish promptly.