在PG 13中,在插入数据时可自动触发autovacumm,从而可以生成表的vm文件而无需手工触发vacumm才能生成. vm文件可以加快VACUUM清理的速度. **新增的配置选项** *autovacuum_vacuum_insert_threshold* 控制插入数据触发autovacuum的的阈值,默认值为1000 ``` [local:/data/run/pg13]:5130 pg13@masterdb=# show autovacuum_vacuum_insert_threshold; autovacuum_vacuum_insert_threshold ------------------------------------ 1000 (1 row) ``` *autovacuum_vacuum_insert_scale_factor* 控制插入数据时超过什么比例会触发autovacuum,默认值为20% ``` [local:/data/run/pg13]:5130 pg13@masterdb=# show autovacuum_vacuum_insert_scale_factor; autovacuum_vacuum_insert_scale_factor --------------------------------------- 0.2 (1 row) ``` **试用体验** 创建数据表 ``` [local:/data/run/pg13]:5130 pg13@masterdb=# drop table tbl1; DROP TABLE [local:/data/run/pg13]:5130 pg13@masterdb=# create table tbl1(id int,c1 char(20)); CREATE TABLE [local:/data/run/pg13]:5130 pg13@masterdb=# insert into tbl1(id,c1) select x,'c1'||x from generate_series(1,1000000) as x; INSERT 0 1000000 [local:/data/run/pg13]:5130 pg13@masterdb=# select pg_relation_filepath('tbl1'); pg_relation_filepath ---------------------- base/16384/16395 (1 row) [local:/data/run/pg13]:5130 pg13@masterdb=# ``` 日志输出,PG自动对tbl1进行了分析 ``` 2020-05-22 14:52:56.790 CST,,,62686,,5ec776c8.f4de,1,,2020-05-22 14:52:56 CST,4/197,0,LOG,00000,"automatic vacuum of table ""masterdb.public.tbl1"": index scans: 0 pages: 0 removed, 7353 remain, 0 skipped due to pins, 0 skipped frozen tuples: 0 removed, 1000000 remain, 0 are dead but not yet removable, oldest xmin: 508 buffer usage: 14734 hits, 2 misses, 4 dirtied avg read rate: 0.031 MB/s, avg write rate: 0.063 MB/s system usage: CPU: user: 0.27 s, system: 0.02 s, elapsed: 0.49 s WAL usage: 7354 records, 1 full page images, 442212 bytes",,,,,,,,,"","autovacuum worker" 2020-05-22 14:52:57.065 CST,,,62686,,5ec776c8.f4de,2,,2020-05-22 14:52:56 CST,4/198,508,LOG,00000,"automatic analyze of table ""masterdb.public.tbl1"" system usage: CPU: user: 0.16 s, system: 0.01 s, elapsed: 0.27 s",,,,,,,,,"","autovacuum worker" [pg13@localhost 16384]$ ``` 生成了vm文件 ``` [pg13@localhost 16384]$ pwd /data/pg13/masterdb/base/16384 [pg13@localhost 16384]$ ls -l 16395* -rw------- 1 pg13 pg13 60235776 May 22 14:52 16395 -rw------- 1 pg13 pg13 32768 May 22 14:52 16395_fsm -rw------- 1 pg13 pg13 8192 May 22 14:52 16395_vm [pg13@localhost 16384]$ ``` **参考资料** [Improved (auto)vacuum in PostgreSQL 13](http://amitkapila16.blogspot.com/2020/05/improved-autovacuum-in-postgresql-13.html)