關于autovacuum_vacuum_scale_factor的了解

autovacuum_vacuum_scale_factor參數聯合autovacuum_vacuum_threshold參數共同定義何時對表進行vacuum操作。autovacuum_vacuum_scale_factor是一個比例因子，預設為0.2，表示當變化的行資料為表上記錄數20%的時候就可能觸發vacuum，之是以是可能觸發那是因為還需要考慮第二個參數autovacuum_vacuum_threshold

當dead_tuple超過autovacuum_vacuum_scale_factor×表上記錄數+autovacuum_vacuum_threshold的時候就會觸發對表的vacuum

注意的幾點：

• 注意小數點，如果你的需求是變化行數量達到表數量90%的時候，應該設定為0.9而不是90
• 可以單獨對表進行設定
• 對于大表，可以單獨調低autovacuum_vacuum_scale_factor

測試

a) 建立測試資料,在這裡建立了一個190W的表

create table tbs_big(id integer,name varchar(20),course int,testtime date,note text);
insert into tbs_big 
select generate_series(1,1900000) as id,
f_random_str(3+(random()*5)::integer) as name,
3+(random()*100)::integer as grade,
now() - ((random()*1000)::integer||' day')::interval as testtime,
repeat(f_random_str(2),3) as note;       

b)配置表的autovacuum_vacuum_scale_factor為0.9

alter table tbs_big set (autovacuum_vacuum_scale_factor=0.9);
bench_db=# \d+ tbs_big
                                                 Table "public.tbs_big"
  Column  |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Descr
iption 
----------+-----------------------+-----------+----------+---------+----------+-------------+--------------+------
-------
 id       | integer               |           |          |         | plain    |             |              | 
 name     | character varying(20) |           |          |         | extended |             |              | 
 course   | integer               |           |          |         | plain    |             |              | 
 testtime | date                  |           |          |         | plain    |             |              | 
 note     | text                  |           |          |         | extended |             |              | 
Access method: heap
Options: autovacuum_vacuum_scale_factor=0.9      

表的資料190W，根據之前的計算公式當變化的記錄數要達到190W*0.9+50的時候才會觸發vacuum

c)對表進行變更

update tbs_big set note='abc' where id<1700000; 
select * from pg_stat_user_tables where relname='tbs_big';
-[ RECORD 1 ]-------+------------------------------
relid               | 87739
schemaname          | public
relname             | tbs_big
seq_scan            | 24
seq_tup_read        | 22800000
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 4125365
n_tup_upd           | 7489991
n_tup_del           | 0
n_tup_hot_upd       | 6188
n_live_tup          | 1900000
n_dead_tup          | 1699999
n_mod_since_analyze | 1699999
n_ins_since_vacuum  | 0
last_vacuum         | 
last_autovacuum     | 2022-11-02 22:39:12.744596+08
last_analyze        | 
last_autoanalyze    | 2022-11-02 22:39:14.062275+08
vacuum_count        | 0
autovacuum_count    | 5
analyze_count       | 0
autoanalyze_count   | 6      

從n_dead_tup中可以發現目前還沒有達到觸發條件

alter table tbs_big set (autovacuum_vacuum_scale_factor=0.1);
bench_db=# select * from pg_stat_user_tables where relname='tbs_big';
-[ RECORD 1 ]-------+------------------------------
relid               | 87739
schemaname          | public
relname             | tbs_big
seq_scan            | 25
seq_tup_read        | 24700000
idx_scan            | 
idx_tup_fetch       | 
n_tup_ins           | 4125365
n_tup_upd           | 7490160
n_tup_del           | 0
n_tup_hot_upd       | 6261
n_live_tup          | 2109875
n_dead_tup          | 169
n_mod_since_analyze | 169
n_ins_since_vacuum  | 0
last_vacuum         | 
last_autovacuum     | 2022-11-02 22:41:08.819437+08
last_analyze        | 
last_autoanalyze    | 2022-11-02 22:40:07.257426+08
vacuum_count        | 0
autovacuum_count    | 6
analyze_count       | 0
autoanalyze_count   | 7