These two are the third matched pair in the autovacuum suite, after the analyze pair and the regular vacuum pair (the latter coming up next). They control when autovacuum runs VACUUM against a table based on the number of inserts since the last vacuum — not updates, not deletes, just inserts. They were added in PostgreSQL 13, and they exist to solve a specific problem.

The append-only table problem

The original autovacuum trigger formula counted only updated and deleted rows. The reasoning was straightforward: vacuum reclaims space from dead tuples, and inserts don’t produce dead tuples, so an insert-only table doesn’t need to be vacuumed. This was wrong, but it took a while for the wrongness to become unignorable.

The reasons inserts need vacuum, briefly:

  • Index-only scans. Vacuum updates the visibility map, which the planner consults to decide whether an index-only scan is safe. Without periodic vacuums, an insert-heavy table accumulates pages marked not-all-visible, and the optimization quietly stops working.
  • Tuple freezing. Inserted tuples carry an xmin that has to eventually be frozen before xid wraparound. On an insert-only table that never triggered the original autovacuum formula, the only thing freezing tuples was the anti-wraparound vacuum — which arrives all at once, often during peak hours, often as a surprise.

PostgreSQL 13 fixed this with a parallel trigger:

1vacuum insert threshold = autovacuum_vacuum_insert_threshold


2                        + autovacuum_vacuum_insert_scale_factor × reltuples

When inserts since the last vacuum exceed this value, autovacuum schedules a vacuum. Defaults: autovacuum_vacuum_insert_threshold = 1000, autovacuum_vacuum_insert_scale_factor = 0.2. Both are sighup and both have per-table storage parameter equivalents.

A PostgreSQL 18 refinement

PostgreSQL 18 changed the formula to multiply the scale factor’s contribution by the fraction of the table that is not frozen, taken from pg_class.relallfrozen / relpages:

1vacuum insert threshold = autovacuum_vacuum_insert_threshold


2                        + autovacuum_vacuum_insert_scale_factor


3                          × reltuples × (1 - relallfrozen / relpages)

This is a meaningful improvement. As more of the table gets frozen, the trigger threshold rises, which means insert-driven vacuums run more often early in a table’s life (when there’s freezing work to do) and less often once the bulk of the table is settled. It dramatically reduces unnecessary vacuum traffic on large mostly-static tables. If you’re on PG 17 or earlier, you don’t get this; if you’re on PG 18, the defaults are more reasonable than they used to be.

Tuning

The defaults are much better calibrated than the regular vacuum defaults — 0.2 × reltuples plus 1000 is more aggressive than the regular vacuum’s 0.2 × reltuples + 50, which is what you want for inserts. But on truly large insert-heavy tables (events, logs, time-series), the defaults still wait for too many inserts:

1ALTER TABLE events SET (


2    autovacuum_vacuum_insert_scale_factor = 0.02,


3    autovacuum_vacuum_insert_threshold = 10000


4);

Setting autovacuum_vacuum_insert_threshold = -1 disables insert-triggered vacuum on a table. There is essentially never a reason to do this; the only reason to know about it is to recognize someone else’s mistake.

Recommendation: Leave the globals alone. On large insert-heavy tables — events, logs, audit trails, time-series — set per-table autovacuum_vacuum_insert_scale_factor to 0.02 or 0.01, with a generous autovacuum_vacuum_insert_threshold to prevent constant re-triggering on briefly-quiet tables. If you are still on PostgreSQL 12, this whole post is a roadmap for what you are missing; upgrade.