New in PostgreSQL 18. This parameter is the project’s first-class fix for a problem the entire community has been working around for roughly fifteen years: on very large tables, the default vacuum trigger formula waits absurdly long before doing anything.

The original formula:

1vacuum threshold = autovacuum_vacuum_threshold


2                 + autovacuum_vacuum_scale_factor × reltuples

With the defaults — autovacuum_vacuum_threshold = 50 and autovacuum_vacuum_scale_factor = 0.2 — a 100M-row table waits for 20M dead tuples before vacuum runs. A billion-row table waits for 200M. By the time vacuum actually starts, you have a vast amount of bloat, the vacuum itself takes hours, and the next one starts from a similarly bad place.

The classic workaround was per-table tuning of autovacuum_vacuum_scale_factor to something like 0.02 or 0.005, which we will discuss when we hit those parameters in the next post. It works, but it requires identifying every large table in advance and giving it bespoke storage parameters. Easy to forget, easy to miss when a small table grows into a large one.

PostgreSQL 18’s solution is structural. The new formula is:

1vacuum threshold = MIN(autovacuum_vacuum_max_threshold,


2                       autovacuum_vacuum_threshold


3                     + autovacuum_vacuum_scale_factor × reltuples)

autovacuum_vacuum_max_threshold is a hard cap on the trigger value. Default is 100,000,000 tuples. Context is sighup, with per-table override via storage parameters. Setting it to -1 disables the cap and reverts to the old behavior.

In practical terms: a small table still waits for ~20% changes before vacuum runs (the scale factor wins), and a billion-row table now triggers at 100M dead tuples instead of 200M (the cap wins). The transition between regimes happens automatically at table size 500M rows — below that, the scale factor still governs; above that, the cap does. No per-table configuration required to get most of the benefit.

For very large tables you can still go further with a per-table cap:

1ALTER TABLE big_table SET (


2    autovacuum_vacuum_max_threshold = 10000000   -- 10M cap


3);

A few operational notes:

  • The default of 100M is generous. On systems where 100M dead tuples is already too much bloat — heavily-updated OLTP workloads on tables with many indexes — lower the global value to 10M or 25M.
  • This does not replace per-table autovacuum_vacuum_scale_factor tuning for tables where you want vacuum to run more aggressively than the cap. The cap only ratchets the trigger downward; it never raises it.
  • Pre-PG 18 systems do not have this. The per-table scale-factor tuning approach remains the only option there.

Recommendation: On PG 18, leave the default at 100M unless you have a specific reason to lower it. For very large heavily-updated tables, set a tighter per-table cap. The combination of this parameter and the PG 18 changes to insert-vacuum freezing arithmetic means the autovacuum defaults are, finally, defensible on large tables. After fifteen years.