Why autovacuum ignores your biggest table

This is Issue #4 of Vacuum & Analyze. Issue #1 covered shared_buffers. Issue #2 covered work_mem and why your sort spilled to disk. Issue #3 covered maintenance_work_mem and why vacuum was crawling through multiple passes. All three of those issues assumed autovacuum was firing when it should. Today we check that assumption: the threshold math that decides when autovacuum runs at all — and why it quietly stops working as your tables grow.

The scenario

A team has a 40-million-row table. It gets heavy UPDATE traffic all day. pg_stat_user_tables shows n_dead_tup sitting at 2 million and climbing. Autovacuum hasn't touched the table in nine hours. Nothing is misconfigured — autovacuum_max_workers is fine, maintenance_work_mem is generously sized after Issue #3. Autovacuum simply hasn't decided this table needs it yet.

The setting responsible: autovacuum_vacuum_scale_factor = 0.2, sitting quietly at its default since the day the cluster was created.

Misconception #1: "The threshold is a fixed number of dead rows"

It isn't — and this is the part that makes the default dangerous specifically for large tables. Autovacuum's trigger formula is:

vacuum threshold = autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × n_live_tup)

With defaults, that's 50 + (0.2 × n_live_tup). On a 10,000-row table, that's a trigger at roughly 2,050 dead rows — reasonable, vacuum fires often, bloat stays low. On a 40-million-row table, that same formula triggers at 8,000,050 dead rows.

Autovacuum isn't ignoring your big table out of malice. It's doing exactly the math it was configured to do — the formula just scales the threshold up right alongside the table size, so the percentage stays fixed while the absolute dead-row count required to trigger a vacuum keeps growing.

The consequence: your smallest tables get vacuumed proactively and stay healthy. Your largest, highest-traffic tables — the ones where bloat is most expensive — are the ones the default formula is worst at protecting.

Misconception #2: "If I lower the global scale factor, that fixes it"

This is the fix that creates a new problem. Drop autovacuum_vacuum_scale_factor to 0.05 globally, and yes, your 40-million-row table now triggers at 2,000,050 dead rows instead of 8 million — better. But that same 0.05 also applies to every small, low-traffic table in the database. Now tables that barely change are getting vacuumed constantly, burning I/O and autovacuum worker slots on tables that didn't need the help.

This is the same shape of mistake as raising work_mem globally in Issue #2 — a setting that behaves differently depending on the table it's applied to, changed at the level where it applies to everything at once.

The fix is per-table, not global:

ALTER TABLE large_high_traffic_table SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 5000
);

This overrides the formula for just this table, leaving the global default alone for everything else. A large table's percentage-based threshold effectively collapses toward a much smaller absolute number, so it gets vacuumed proportionally more often — without touching the behavior of every small table in the cluster.

The diagnostic that actually matters

Find out which tables are furthest from their actual trigger point relative to their size — this surfaces tables where the default formula is scaling badly:

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup * 100.0 / nullif(n_live_tup, 0), 2) AS dead_pct,
  (50 + 0.2 * n_live_tup)::bigint AS default_threshold,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 1000000
ORDER BY n_live_tup DESC
LIMIT 20;

Any table here with a default_threshold in the millions and a last_autovacuum from more than a day ago is a strong candidate for a per-table override. Cross-reference against tables you already know are write-heavy — that's where the gap between "technically fine" and "actually needs help sooner" is widest.

The empirically reasonable range

Tables under ~1M rows: leave the global default (0.2 scale factor) alone — it works fine at this size.

Tables 1M–20M rows with moderate write traffic: autovacuum_vacuum_scale_factor = 0.05–0.1 as a per-table override.

Tables over 20M rows, or any table with heavy UPDATE/DELETE traffic regardless of size: autovacuum_vacuum_scale_factor = 0.01–0.02, often paired with a fixed autovacuum_vacuum_threshold in the low thousands so tiny tables in this bucket don't get vacuumed on a single-row change.

Hot tables (queue-style, high-churn): some teams drop the scale factor to near-zero and let a fixed threshold do all the work — worth testing on your specific write pattern rather than assuming.

Same principle from Issue #3 applies here: err generous on tables where bloat is expensive, because the cost of vacuuming slightly too often is far lower than the cost of a table that never gets proactively addressed.

The mental model

Autovacuum isn't broken, and it isn't ignoring your biggest table — it's following a formula that was designed to scale with table size, and the default coefficient just isn't aggressive enough once a table gets large or busy. The global setting is the wrong lever to pull, the same way global work_mem was the wrong lever in Issue #2. The right lever is per-table ALTER TABLE ... SET, aimed specifically at the tables where the gap between "technically compliant with the formula" and "actually bloat-free" is widest.

Wrapping the memory series

This closes out the run on PostgreSQL memory and maintenance settings — shared_buffers, work_mem, maintenance_work_mem, and now the threshold math that decides when vacuum uses any of it at all. If there's a setting from this series you want revisited with more depth, or a different corner of Postgres internals you want covered next, reply and tell me — I read every one.

— Gio