Parameter · Vacuuming / Automatic Vacuuming
autovacuum_vacuum_insert_threshold
Minimum number of tuple inserts prior to vacuum. -1 disables insert vacuums.
- Default:
1000 - Type: integer
- Range: -1 … 2147483647
- Applies: Reload
In plain words
Tables that only receive INSERTs never get dead rows, so before PostgreSQL 13 autovacuum never vacuumed them until a wraparound emergency. Insert-triggered vacuum fixes that: after enough inserts, vacuum runs to set visibility-map bits (faster index-only scans) and freeze rows a little at a time.
-1 turns insert-triggered vacuum off.
For DBAs and operators
PostgreSQL 18 rule:
ins_thresh = autovacuum_vacuum_insert_threshold
+ autovacuum_vacuum_insert_scale_factor * reltuples
* (1 - relallfrozen / relpages) -- new in 18: only unfrozen part
vacuum if n_ins_since_vacuum > ins_threshThe relallfrozen term (new column in pg_class in 18) means a huge, mostly frozen archive table is re-vacuumed after a reasonable number of new rows instead of after 20% of its total size.
Why it matters: index-only scans need all-visible pages; and freezing in small steps avoids a giant anti-wraparound vacuum later. Watch n_ins_since_vacuum in pg_stat_user_tables. Disable per table only for pure scratch tables that are truncated anyway: ALTER TABLE t SET (autovacuum_vacuum_insert_threshold = -1).
How to change it
Change in postgresql.conf or ALTER SYSTEM, then run SELECT pg_reload_conf().
See it in context
Autovacuum: when tables get vacuumed Free space map and visibility map
Related parameters
autovacuum_vacuum_insert_scale_factor autovacuum_vacuum_threshold vacuum_freeze_min_age