autovacuum_vacuum_insert_threshold

Definition PG 18 manual

Specifies the number of inserted tuples needed to trigger a VACUUM in any one table. The default is 1000 tuples. If -1 is specified, autovacuum will not trigger a VACUUM operation on any tables based on the number of inserts. This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters.

How it works

Minimum number of tuple inserts prior to vacuum. A configuration reload applies a new value; existing work already in flight is not retroactively changed.

For insert-heavy tables the trigger is the insert threshold plus insert scale factor times reltuples times the fraction not all-frozen. Such vacuuming can advance visibility/freeze state even without dead tuples and reduce later aggressive-vacuum work.

Monitor and change autovacuum_vacuum_insert_threshold together with autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor, autovacuum_vacuum_max_threshold. Validate on the relevant server role and real workload, then use its sighup context to choose session change, reload, or restart; a historical boot default is not the current effective value.

Operational considerations

Changing the global value while a table storage parameter overrides it.

Treating reltuples and cumulative change statistics as exact real-time counts.

Blaming a threshold without checking long transactions, replication slots, and worker saturation.

Buying short-term quiet by deferring maintenance until wraparound failsafe activates.

Workload guidance

OLAP: Run explicit ANALYZE/VACUUM after bulk loads instead of waiting only for proportional triggers; plan freezing and visibility-map advancement separately for append-only partitions.

OLTP: Tune autovacuum_vacuum_insert_threshold from table size, change rate, and maintenance SLA, using per-table thresholds for large/hot relations. Observe trigger intervals, dead tuples, and ANALYZE/VACUUM duration.

SMALL: Start with upstream. Fixed thresholds dominate on small tables; after changes, confirm worker and I/O headroom.