autovacuum_vacuum_insert_scale_factor

Definition PG 18 manual

Specifies a fraction of the unfrozen pages in the table to add to autovacuum_vacuum_insert_threshold when deciding whether to trigger a VACUUM. The default is 0.2 (20% of unfrozen pages in table). 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

Number of tuple inserts prior to vacuum as a fraction of reltuples. 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_scale_factor 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_scale_factor 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.