在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)

ITPub博客

PostgreSQL DBA(174) - PG 13（Allow inserts to trigger autovacuum）

最新文章