前言
在表级别设置autovacuum有关参数清理表的死亡元祖可以有效规避autovacuum触发全局默认阈值的高峰。例如，如果想让表的autovacuum任务更频繁，可以在表级设置更小的触发autovacuum有关参数autovacuum_vacuum_scale_factor。
如果需要在很多表上单独设置autovacuum有关参数，别忘了增加autovacuum_max_workers进程，默认是3，意味着同一时间autovacuum进程只能清理3张触发autovacuum阈值的表。
如果数据库中有多个大量数据的表，需要设置足够大的maintenance_work_mem内存空间，避免出现多次索引scan。本例中将测试如何在表上设置autovacuum有关参数，以及何时触发。
设置表级autovacuum阈值的步骤:
1.开启自动清理守护进程 alter table t2 set(autovacuum_enabled);
2.设置触发VACUUM/ANALYZE的被更新或被删除元组的最小数量
alter table t2 set(autovacuum_vacuum_threshold=1);
3.或者设置表尺寸 alter table t2 set(autovacuum_vacuum_scale_factor =0.1/autovacuum_analyze_scale_factor =0.01);
autovacuum_vacuum_scale_factor 默认值为0.2. autovacuum_analyze_scale_factor 默认值为0.1
取消表级参数:
alter table t2 reset(autovacuum_enabled);
alter table t2 reset(autovacuum_vacuum_threshold);
测试
1.测试t表，仅设置autovacuum_vacuum_threshold参数
alter table t2 set(autovacuum_enabled);
alter table t2 set(autovacuum_vacuum_threshold=1);
TEST=# \d+ t2
Table "public.t2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+--------------------------------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
start_time | timestamp(3) without time zone | | | | plain | |
Access method: heap
Options: autovacuum_enabled=true, autovacuum_vacuum_threshold=1
update t2 set id=11 where id=1;
update t2 set id=12 where id=2;
t2表总共10行数据
autovacuum阈值=autovacuum_vacuum_threshold+autovacuum_vacuum_scale_factor*总行数=1+0.2*10=3条
已经更新两条数据后没有触发autovacuum，可以看到autovacuum_count字段为0
TEST=# select * from sys_stat_user_tables where relname='t2';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_va
cuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+---------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-----------------
--------------+-----------------+--------------+------------------+--------------+------------------+---------------+-------------------
16425 | public | t2 | 14 | 159 | | | 2 | 3 | 5 | 3 | 10 | 3 | 10 | 2023-04-26 15:10
:32.712381+08 | | | | 1 | 0 | 0 | 0
(1 row)
update t2 set id=13 where id=3;
此时触发autovacuum，就是说仅仅在表上设置了autovacuum_vacuum_threshold=1并不是更新一条记录后触发autovacuum，而是更新3条记录，因为此时触发了autovacuum阈值。
我们没有在表级设置autovacuum_vacuum_scale_factor，但是使用了默认的全局参数autovacuum_vacuum_scale_factor，值为0.2。
继续更新数据
update t2 set id=14 where id=4;
等待3min，超过参数autovacuum_naptime=1min，仍然没有触发autovacuum。
update t2 set id=15 where id=5;
update t2 set id=16 where id=6;
update t2 set id=17 where id=7;
这里看到更新到第4条后才触发的autovacuum
TEST=# select * from sys_stat_user_tables where relname='t2';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum
| last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+---------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+---------------------
----------+-------------------------------+--------------+------------------+--------------+------------------+---------------+-------------------
16428 | public | t2 | 15 | 164 | | | 20 | 12 | 0 | 12 | 10 | 4 | 32 | 2023-05-18 16:03:25.
276072+08 | 2023-05-18 16:16:08.123656+08 | | | 1 | 1 | 0 | 0
(1 row)
TEST=# select * from sys_stat_user_tables where relname='t2';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum
| last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+---------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+---------------------
----------+-------------------------------+--------------+------------------+--------------+------------------+---------------+-------------------
16428 | public | t2 | 15 | 164 | | | 20 | 12 | 0 | 12 | 10 | 0 | 32 | 2023-05-18 16:03:25.
276072+08 | 2023-05-18 16:27:08.561672+08 | | | 1 | 2 | 0 | 0
(1 row)
2.在表级设置只受一个参数autovacuum_vacuum_threshold影响，需要这样设置
添加条件
alter table t2 set(autovacuum_vacuum_scale_factor=0);
表级设置参数结果是这样的
TEST=# \d+ t2
Table "public.t2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+--------------------------------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
start_time | timestamp(3) without time zone | | | | plain | |
Access method: heap
Options: autovacuum_enabled=true, autovacuum_vacuum_threshold=1, autovacuum_vacuum_scale_factor=0
这时只更新一条记录
不触发autovacuum。
TEST=# update t2 set id=18 where id=8;
UPDATE 1
TEST=#
TEST=# select * from sys_stat_user_tables where relname='t2';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum
| last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+---------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+---------------------
----------+-------------------------------+--------------+------------------+--------------+------------------+---------------+-------------------
16428 | public | t2 | 17 | 184 | | | 20 | 13 | 0 | 13 | 10 | 1 | 33 | 2023-05-18 16:03:25.
276072+08 | 2023-05-18 16:27:08.561672+08 | | | 1 | 2 | 0 | 0
(1 row)
又更新一条记录，n_dead_tup=2>autovacuum_vacuum_threshold ,这时候触发了autovacuum。
update t2 set id=19 where id=9;
TEST=# select * from sys_stat_user_tables where relname='t2';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum
| last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+---------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+---------------------
----------+-------------------------------+--------------+------------------+--------------+------------------+---------------+-------------------
16428 | public | t2 | 19 | 204 | | | 20 | 14 | 0 | 14 | 10 | 0 | 34 | 2023-05-18 16:03:25.
276072+08 | 2023-05-18 16:34:08.762470+08 | | | 1 | 3 | 0 | 0
(1 row)
3.另一个和analyze有关的两个参数autovacuum_analyze_threshold，autovacuum_analyze_scale_factor设置同理
需要表级设置两个参数，如果仅仅设置其中一个，另一个参数会使用默认的全局参数设置值
alter table t set(autovacuum_analyze_threshold=1);
alter table t set(autovacuum_analyze_scale_factor=0);
TEST=# \d+ t
Table "public.t"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+--------------------------------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
start_time | timestamp(3) without time zone | | | | plain | |
Access method: heap
Options: autovacuum_vacuum_threshold=1, autovacuum_enabled=true, autovacuum_vacuum_scale_factor=0, autovacuum_analyze_threshold=1, autovacuum_analyze_scale_factor=0
只更新一条记录
update t set id=21 where id=11;
查看结果last_autoanalyze是上次发生autoanalyze时间，autoanalyze_count=1，说明触发了autoanalyze。
TEST=# select * from sys_stat_user_tables where relname='t';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_v
acuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+---------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+----------------
---------------+-------------------------------+--------------+-------------------------------+--------------+------------------+---------------+-------------------
16425 | public | t | 30 | 319 | | | 2 | 11 | 5 | 11 | 10 | 1 | 1 | 2023-04-26 15:1
0:32.712381+08 | 2023-04-26 16:05:50.740441+08 | | 2023-04-26 16:16:51.070583+08 | 1 | 3 | 0 | 1
(1 row)
4.怎样在表级别设置参数而不发生autoanalyze功能
只需要设置autovacuum_analyze_threshold>表的总行数(这里需要评估随着时间的推移，表数据增长的情况)。
alter table t set(autovacuum_analyze_threshold=25);
TEST=# \d+ t
Table "public.t"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------+--------------------------------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
start_time | timestamp(3) without time zone | | | | plain | |
Access method: heap
Options: autovacuum_vacuum_threshold=1, autovacuum_enabled=true, autovacuum_vacuum_scale_factor=0, autovacuum_analyze_scale_factor=0, autovacuum_analyze_threshold=25
update t set id =30 where id<=21;
没有触发 autoanalyze
update t set id =40 where id=30;
总共更新了20行记录，仍然没更新autoanalyze
再次更新10行记录，总共更新30行记录，大于设置的阈值25，触发了autoanalyze。
update t set id =50 where id=40;
TEST=# select * from sys_stat_user_tables where relname='t';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_v
acuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+---------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+----------------
---------------+-------------------------------+--------------+-------------------------------+--------------+------------------+---------------+-------------------
16425 | public | t | 34 | 359 | | | 2 | 41 | 5 | 41 | 10 | 0 | 0 | 2023-04-26 15:1
0:32.712381+08 | 2023-04-26 16:32:51.480662+08 | | 2023-04-26 16:32:51.484595+08 | 1 | 6 | 0 | 2
(1 row)
因此不触发autoanalyze只需要设置autovacuum_analyze_threshold成一个天文数字（以后插入，更新或者删除的元组永远不会达到这个数），同时设置 autovacuum_analyze_scale_factor=1。
总结
1.设置了表级的autovacuum_vacuum_threshold=1，还需要设置autovacuum_vacuum_scale_factor=0，这样大于一条死亡元祖触发autovacuum。
如果表级只设置autovacuum_vacuum_threshold=1，没有设置autovacuum_vacuum_scale_factor，则按照默认的全局参数autovacuum_vacuum_scale_factor=0.2，也就是死亡元祖大于0.2*总记录+autovacuum_vacuum_threshold触发autovacuum。
2.如果一个服务器有60个数据库并且autovacuum_naptime设置为60秒，那么自动清理工作器每秒启动一次[自动清理_naptime/DB数]。这里需要考虑数据库数量。
大量数据库场景
如果实例中有更多数据库，最好增大autovacuum_naptime。同时，可以通过增大autovacuum_cost_limit和减少autovacuum_cost_delay参数以及将autovacuum_max_workers的默认值从3增加到4或5来使自动清理过程更加激进。