autovacuum_vacuum_insert_threshold
A configuration parameter specifying the number of inserted tuples needed to trigger an autovacuum
autovacuum_vacuum_insert_threshold
is a configuration parameter specifying the number of inserted tuples needed to trigger an autovacuum.
autovacuum_vacuum_insert_threshold
was added in PostgreSQL 13.
Default value
The default value for autovacuum_vacuum_insert_threshold
is: 1000
.
Change history
- PostgreSQL 13
- added (commit b07642db)
Examples
Demonstration of autovacuum_vacuum_insert_threshold
's effect - here with autovacuum_vacuum_insert_threshold
set to the default 1000
:
postgres=# CREATE TABLE foo (id INT); CREATE TABLE postgres=# INSERT INTO foo VALUES (generate_series(1,10)); INSERT 0 10 postgres=# SELECT n_tup_ins, last_autovacuum FROM pg_stat_user_tables WHERE relname='foo'; n_tup_ins | last_autovacuum -----------+----------------- 10 | (1 row)
If autovacuum_vacuum_insert_threshold
is changed to 5 (here via a relopt on the table), it will be vacuumed at the next autovacuum pass:
postgres=# ALTER TABLE foo SET (autovacuum_vacuum_insert_threshold = 5); ALTER TABLE postgres=# INSERT INTO foo VALUES (generate_series(1,10)); INSERT 0 10 postgres=# SELECT n_tup_ins, last_autovacuum FROM pg_stat_user_tables WHERE relname='foo'; n_tup_ins | last_autovacuum -----------+------------------------------- 20 | 2021-12-14 12:10:13.156198+01 (1 row)
References
- PostgreSQL documentation: autovacuum_vacuum_insert_threshold
Useful links
- PostgreSQL v13 new feature: tuning autovacuum on insert-only tables - April 2020 blog article by Laurenze Albe / CyberTec