PostgreSQL Autovacuum Tuning: scale_factor, cost_limit, and When the Defaults Are Wrong

Autovacuum is not optional maintenance you can defer — it is the process that keeps MVCC from slowly filling your disk with dead rows. The problem is that PostgreSQL ships with defaults tuned to avoid surprising anyone on a tiny database, which means on a busy production table those same defaults are almost always too conservative. This is the guide for making autovacuum actually keep up.

The Default That Trips Everyone Up

autovacuum_vacuum_scale_factor defaults to 0.2 — autovacuum only considers vacuuming a table once 20% of its rows are dead, on top of a small fixed threshold (autovacuum_vacuum_threshold = 50). On a table with 10 million rows, that means autovacuum waits for 2 million dead tuples to accumulate before it even starts. By the time it runs, you already have significant bloat and degraded index performance.

-- The formula PostgreSQL actually uses to decide when to vacuum a table:
-- vacuum threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples
SELECT relname, reltuples,
       (50 + 0.2 * reltuples)::bigint AS default_vacuum_threshold
FROM pg_class
WHERE relkind = 'r' AND reltuples > 100000
ORDER BY reltuples DESC
LIMIT 10;

Per-Table Overrides Are the Correct Fix

Do not lower the global autovacuum_vacuum_scale_factor in postgresql.conf as a first move — that makes every table, including small rarely-updated ones, vacuum more aggressively, which wastes I/O. Override it per table instead, targeting the write-heavy ones:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE orders SET (autovacuum_vacuum_cost_limit = 2000);
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

At 0.05, the same 10-million-row table triggers a vacuum after 500,000 dead tuples instead of 2 million — a 4× tighter loop.

Cost-Based Vacuum Throttling

Autovacuum deliberately throttles itself to avoid saturating I/O: it sleeps briefly after accumulating "cost points" from the pages it reads and dirties. The relevant knobs:

• autovacuum_vacuum_cost_limit (default 200) — total cost budget before autovacuum sleeps.
• autovacuum_vacuum_cost_delay (default 2ms in PG 12+) — sleep duration once the budget is exhausted.

On modern SSD/NVMe storage, the default cost limit is unnecessarily conservative — it was calibrated for spinning disks. Raising autovacuum_vacuum_cost_limit to 1000-2000 (globally or per table) lets vacuum work through a bloated table markedly faster without meaningfully hurting foreground query I/O on fast storage.

How to Tell Autovacuum Is Losing

SELECT schemaname || '.' || relname AS table_name,
       n_dead_tup, n_live_tup,
       ROUND(100.0 * n_dead_tup / GREATEST(n_live_tup + n_dead_tup, 1), 2) AS dead_ratio,
       last_autovacuum, autovacuum_count,
       last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

Two signals mean autovacuum is falling behind: dead_ratio climbing steadily above 15-20%, and a growing gap between now and last_autovacuum on a table that is clearly still receiving writes. A long-running transaction is the most common root cause — it holds back the oldest visible snapshot, and vacuum cannot remove any dead tuple newer than that snapshot no matter how aggressively it is tuned.

Autovacuum Workers

autovacuum_max_workers (default 3) caps how many tables can be vacuumed concurrently. On a schema with dozens of write-heavy tables, three workers is often not enough — they queue up and large tables wait behind small ones. Raising this to 4-6 on a server with headroom (and enough maintenance_work_mem to support them: each worker uses up to autovacuum_work_mem or maintenance_work_mem independently) reduces queueing.

A Practical Tuning Checklist

• Identify your top 10-20 tables by write volume (n_tup_upd + n_tup_del) — these are the ones that need per-table overrides, not the whole database.
• Lower autovacuum_vacuum_scale_factor to 0.02-0.05 on those specific tables.
• Raise autovacuum_vacuum_cost_limit globally if you are on SSD/NVMe storage.
• Check for long-running transactions and inactive replication slots blocking the xmin horizon before assuming it is purely a configuration issue.
• Raise autovacuum_max_workers if large tables are visibly waiting behind small ones.
• Re-check dead_ratio a week after each change — tune iteratively, do not guess once and walk away.

Why This Needs Continuous Monitoring, Not a One-Time Fix

Autovacuum tuning is not "set it once." Write patterns shift, table sizes grow, and a scale_factor that was correct at 1 million rows is wrong at 50 million. PG Monitoring tracks n_dead_tup, dead_ratio, last_autovacuum, and hot-update ratio per table on every collection cycle, and flags tables where the dead ratio is climbing faster than autovacuum is clearing it — before that shows up as a slow sequential scan in production. It is the same underlying data this guide uses, just watched continuously instead of queried by hand once a quarter.