PostgreSQL 之 autovacuum的触发条件

作者：瀚高PG实验室 （Highgo PG Lab）- 波罗

autovacuum 是 postgresql 里非常重要的一个服务端进程，能够自动地执行，在一定条件下自动地对 dead tuples 进行清理并对表进行分析

autovacuum参数控制 autovacuum 进程是否打开,默认为 "on"

根据postgresql.conf相关配置，理解autovacuum会在两种情况下会被触发：

1.表上(update,delte 记录) >= autovacuum_vacuum_scale_factor* reltuples(表上记录数) + autovacuum_vacuum_threshold

说明：

清理基本阈值是autovacuum_vacuum_threshold

清理的缩放系数是autovacuum_vacuum_scale_factor

元组的数目是 reltuples 可以从统计收集器里面获取，参考sql如下：

SELECT reltuples from pg_class WHERE relkind = 'r' AND relname = 'test';

2.指定表上事务的最大年龄配置参数autovacuum_freeze_max_age，默认为2亿，达到这个阀值将触发 autovacuum进程，从而避免 wraparound。

表上的事务年龄可以通过 pg_class.relfrozenxid查询。
--例如,查询表 test_1 的事务年龄
select relname,age(relfrozenxid) from pg_class where relname='test_1';
 relname |   age    
---------+----------
 test_1  | 14208876
(1 row)

 

附：相关参数说明

autovacuum：是否启动系统自动清理功能，默认值为on。

autovacuum_max_workers：设置系统自动清理工作进程的最大数量。

autovacuum_naptime：设置两次系统自动清理操作之间的间隔时间。

autovacuum_vacuum_threshold和autovacuum_analyze_threshold：设置当表上被更新的元组数的阈值超过这些阈值时分别需要执行vacuum和analyze。

autovacuum_vacuum_scale_factor设置表大小的缩放系数。

autovacuum_freeze_max_age：设置需要强制对数据库进行清理的XID上限值。

 

更多详细内容参见如下：

PostgreSQL: Documentation: 9.6: Automatic Vacuuming

 

autovacuum_vacuum_threshold (integer)

Specifies the minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conffile or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters.

autovacuum_analyze_threshold (integer)

Specifies the minimum number of inserted, updated or deleted tuples needed to trigger an ANALYZE in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters.

autovacuum_vacuum_scale_factor (floating point)

Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM. The default is 0.2 (20% of table size). This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters.

autovacuum_freeze_max_age (integer)

Specifies the maximum age (in transactions) that a table's pg_class.relfrozenxid field can attain before a VACUUM operation is forced to prevent transaction ID wraparound within the table. Note that the system will launch autovacuum processes to prevent wraparound even when autovacuum is otherwise disabled.

Vacuum also allows removal of old files from the pg_clog subdirectory, which is why the default is a relatively low 200 million transactions. This parameter can only be set at server start, but the setting can be reduced for individual tables by changing table storage parameters. For more information see Section 24.1.5.