autovacuum_vacuum_insert_threshold

• 19
• 18
• 17
• 16
• 15
• 14
• 13

演示 autovacuum_vacuum_insert_threshold 的效果 - 这里设置为默认值 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)

如果将 autovacuum_vacuum_insert_threshold 改为 5 (此处通过表的 relopt 设置)，它将在下一次自动清理时进行清理。

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)

• PostgreSQL 文档： autovacuum_vacuum_insert_threshold

• PostgreSQL v13 新特性：调整仅插入表 autovacuum 行为 - 2020 年 4 月 Laurenze Albe / CyberTec 的博客文章