Autovacuum's scale factor fails silently on large tables

Autovacuum decides whether to vacuum a table with this:

threshold = autovacuum_vacuum_threshold
          + autovacuum_vacuum_scale_factor * reltuples

Defaults are 50 and 0.2. The second term is the problem. It is a proportion of the table, so the number of dead tuples you must accumulate before anything happens scales with how big the table already is.

On a table of ten thousand rows that comes to 2,050 dead tuples, which is fine. At a million rows it is 200,050, which is still fine. Take a table of 400 million rows, which is not unusual for an events or analytics table nobody has pruned:

50 + 0.2 * 400,000,000 = 80,000,050

Eighty million dead tuples before autovacuum considers the table worth visiting. A fifth of the table has to be garbage first.

autovacuum_analyze_scale_factor defaults to 0.1, so statistics stop refreshing at 40,000,050. The planner is working from a snapshot taken when the table was meaningfully different.

Nothing warns you when you cross from the first case into the third. The setting has not changed. The table got bigger, and the configuration that was aggressive at launch is now close to inert.

Why the failure is quiet

A vacuum that never runs does not produce an error. It produces absence.

The visible symptoms arrive weeks later and look like other problems. Sequential scans get slower because the heap has dead tuples interleaved with live ones and the pages are mostly air. Index scans get slower for the same reason. Plans go strange for the separate reason above, that statistics have gone stale at the same time.

By the time someone opens a ticket, the story is "the database got slow", and vacuum is not usually the first place people look.

Finding it takes one query

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 20;

What you are looking for is a large n_dead_tup next to a last_autovacuum that is old, or null. A null on a busy table means autovacuum has never once run on it, which on a table taking constant updates means the threshold has never been reached.

Work out the actual threshold for a table and compare:

SELECT c.relname,
       s.n_dead_tup,
       (current_setting('autovacuum_vacuum_threshold')::int
         + current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples)::bigint
         AS trigger_at
FROM pg_class c
JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind = 'r'
ORDER BY trigger_at DESC
LIMIT 20;

If trigger_at runs to the millions on your busiest table, you have found it.

For the 400 million row table above, trigger_at is 80,000,050. If that is the shape of what comes back on your busiest table, the setting has stopped meaning anything.

Confirming it in the pages themselves

pg_stat_user_tables gives you counters. If you want to see the dead tuples, pageinspect shows you the line pointers on a single page:

CREATE EXTENSION IF NOT EXISTS pageinspect;
 
SELECT lp,
       lp_flags,
       t_xmin,
       t_xmax
FROM heap_page_items(get_raw_page('your_table', 0));

Here is a table of 500 rows with autovacuum disabled, 50 of them updated once. The updates leave the superseded versions behind:

 lp | lp_flags | t_xmin | t_xmax
----+----------+--------+--------
  1 |        1 |    728 |    729
  2 |        1 |    728 |    729
  3 |        1 |    728 |    729
  4 |        1 |    728 |    729
  5 |        1 |    728 |    729

I expected lp_flags = 3, which is LP_DEAD. That is not what dead tuples look like when autovacuum has not been there. They are lp_flags = 1, ordinary line pointers, with t_xmax set to the transaction that superseded them. LP_DEAD is set later, by index scans or opportunistic pruning, and if nothing triggers that it never appears at all.

So the marker to look for is t_xmax, not lp_flags:

SELECT lp_flags,
       count(*),
       count(*) FILTER (WHERE t_xmax <> 0) AS superseded
FROM heap_page_items(get_raw_page('llm_usage_analytics', 0))
GROUP BY lp_flags;

 lp_flags | count | superseded
----------+-------+------------
        1 |    52 |         50

Fifty of the fifty-two tuples on that page are versions nobody can see, still occupying space. Run VACUUM and the same page reads:

 lp_flags | count | superseded
----------+-------+------------
        0 |    50 |          0
        1 |     2 |          0

lp_flags = 0 is LP_UNUSED, a slot free for reuse. The fifty dead versions are gone and two live rows remain.

Counters tell you a number. This tells you what is on the page, which is what you want when someone asks whether you are sure.

The fix is per-table, not global

The instinct is to lower autovacuum_vacuum_scale_factor in postgresql.conf. Resist it. A global 0.01 makes autovacuum aggressive on every table in the cluster, including the thousands of small ones where 0.2 was correct, and you trade a vacuum problem for a contention problem.

Set it on the tables that need it:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.0,
  autovacuum_vacuum_threshold    = 50000,
  autovacuum_analyze_scale_factor = 0.0,
  autovacuum_analyze_threshold    = 25000
);

Zeroing the scale factor converts the trigger from a proportion into a constant, so the table can grow without the threshold growing with it. Fifty thousand dead tuples means fifty thousand at any size.

Fifty thousand against a 400 million row table is 1,600 times more often than the default would have managed. Pick your own number from your write rate rather than from a blog post: if the table takes a hundred thousand updates an hour and you want vacuum roughly hourly, that is the figure.

Since Postgres 13 there is also autovacuum_vacuum_insert_threshold for append-only tables, which never accumulate dead tuples and so never trigger the dead-tuple path at all, while still needing vacuum for the visibility map and freezing.

Check the ones you inherited

The tables most likely to be affected are the ones nobody has looked at in a year. They were small when the defaults were chosen and nobody revisited the settings, because there was no reason to. Nothing broke and nothing logged. The table grew past the point where its configuration meant anything, and no step in that process announced itself.

Run the second query above against production. It takes a second and it tells you whether this applies to you.