【PostgreSQL】データUPDATE, DELETE時のAUTO VACUUMの動作確認
動作確認はPostgreSQL 13.0で行っています。
不要タプル発生によるAUTO VACUUMの実行条件
PostgreSQLのドキュメント24.1. 定常的なバキューム作業には不要タプルが発生した際のAUTO VACUUM実行に関する閾値の計算方法が記述されています。
テーブルの
relfrozenxid値がautovacuum_freeze_max_ageトランザクション年齢よりも古い場合、そのテーブルは常にバキュームされます （これはfreeze max ageがストレージパラメータにより変更されたテーブルに対しても適用されます。以下を参照）。 さもなければ、直前のVACUUMの後に不要となったタプル数が「バキューム閾値」を超えると、テーブルはバキュームされます。 このバキューム閾値は以下のように定義されます。バキューム閾値 = バキューム基礎閾値 + バキューム規模係数 * タプル数
ここで、バキューム基礎閾値はautovacuum_vacuum_threshold、バキューム規模係数はautovacuum_vacuum_scale_factor、タプル数はpg_class.reltuplesです。
PostgreSQL 13.1文書 24.1. 定常的なバキューム作業より引用
ローカルのPostgreSQLテーブルを作成した後、データ更新・削除を行いAUTO VACUUMが実行されるかを確認しました。パラメータは全てデフォルトです。
検証用テーブルの作成
以下のSQLを使用し、検証用のテーブルとデータを作成します。
-- テーブル作成
create table book (
id int,
book_title text not null,
category_id int not null,
updated_at timestamp with time zone not null,
constraint pk_book primary key(id)
);
-- データ投入
insert into book (
id,
book_title,
category_id,
updated_at
)
select
i,
md5(i::text),
1 + round(random() * 9999),
timestamp '1970-01-01 00:00:00' + random() * (timestamp '2000-12-31 23:59:59' - timestamp '1970-01-01 00:00:00')
from
generate_series(1, 100, 1) as i;テーブル作成後にpg_stat_all_tablesを確認します。テーブル作成後にupdate, deleteを行なっていないため、n_tup_upd, n_tup_del, n_dead_tupは0です。
-- テーブル情報確認
select * from pg_stat_all_tables where schemaname = 'public' and relname ='book';
-[ RECORD 1 ]-------+------------------------------
relid | 73734
schemaname | public
relname | book
seq_scan | 1
seq_tup_read | 0
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 100
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 100
n_dead_tup | 0
n_mod_since_analyze | 0
n_ins_since_vacuum | 100
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze | 2022-02-25 14:37:07.769042+00
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 1動作確認
データUPDATE
データを70件更新します。n_dead_tupが70となります。
-- データ更新
update book set book_title = 'updated' where id <= 70;
-- テーブル情報確認
select * from pg_stat_all_tables where schemaname = 'public' and relname ='book';
-[ RECORD 1 ]-------+------------------------------
relid | 73734
schemaname | public
relname | book
seq_scan | 2
seq_tup_read | 100
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 100
n_tup_upd | 70
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 100
n_dead_tup | 70
n_mod_since_analyze | 0
n_ins_since_vacuum | 100
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze | 2022-02-25 14:40:04.537608+00
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 2AUTO VACUUM実行の閾値である70を超えていないため、AUTO VACCUMは実行されません。
さらに1件データを更新します。n_dead_tupが71となります。
-- データ更新
update book set book_title = 'updated' where id = 71;
-- テーブル情報確認
select * from pg_stat_all_tables where schemaname = 'public' and relname ='book';
-[ RECORD 1 ]-------+------------------------------
relid | 73734
schemaname | public
relname | book
seq_scan | 3
seq_tup_read | 200
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 100
n_tup_upd | 71
n_tup_del | 0
n_tup_hot_upd | 1
n_live_tup | 100
n_dead_tup | 71
n_mod_since_analyze | 1
n_ins_since_vacuum | 100
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze | 2022-02-25 14:40:04.537608+00
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 2AUTO VACUUM実行の閾値である70を超えたため、AUTO VACCUMは実行されてvacuum_countが増加しました。
-- テーブル情報確認
select * from pg_stat_all_tables where schemaname = 'public' and relname ='book';
-[ RECORD 1 ]-------+------------------------------
relid | 73734
schemaname | public
relname | book
seq_scan | 3
seq_tup_read | 200
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 100
n_tup_upd | 71
n_tup_del | 0
n_tup_hot_upd | 1
n_live_tup | 100
n_dead_tup | 0
n_mod_since_analyze | 1
n_ins_since_vacuum | 0
last_vacuum |
last_autovacuum | 2022-02-25 14:42:05.963093+00
last_analyze |
last_autoanalyze | 2022-02-25 14:40:04.537608+00
vacuum_count | 0
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 2データDELETE
データを初期化するためにbookテーブルをdropして再度作成します。
まずはデータを70件削除します。n_tup_del, n_dead_tupが70となります。
-- データ削除
delete from book where id <= 70;
-- テーブル情報確認
select * from pg_stat_all_tables where schemaname = 'public' and relname ='book';
-[ RECORD 1 ]-------+------------------------------
relid | 73752
schemaname | public
relname | book
seq_scan | 2
seq_tup_read | 100
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 100
n_tup_upd | 0
n_tup_del | 70
n_tup_hot_upd | 0
n_live_tup | 30
n_dead_tup | 70
n_mod_since_analyze | 70
n_ins_since_vacuum | 100
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze | 2022-02-26 02:02:32.418608+00
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 1データ更新時と同じく、AUTO VACUUM実行の閾値である70を超えていないためAUTO VACCUMは実行されません。
さらに1件データを削除します。n_dead_tupが71となります。
-- データ削除
delete from book where id = 71;
-- テーブル情報確認
select * from pg_stat_all_tables where schemaname = 'public' and relname ='book';
-[ RECORD 1 ]-------+------------------------------
relid | 73752
schemaname | public
relname | book
seq_scan | 3
seq_tup_read | 130
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 100
n_tup_upd | 0
n_tup_del | 71
n_tup_hot_upd | 0
n_live_tup | 29
n_dead_tup | 71
n_mod_since_analyze | 1
n_ins_since_vacuum | 100
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze | 2022-02-26 02:03:31.423163+00
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 2AUTO VACUUMが実行され、vacuum_countが増加したことが確認できました。
-- テーブル情報確認
select * from pg_stat_all_tables where schemaname = 'public' and relname ='book';
-[ RECORD 1 ]-------+------------------------------
relid | 73743
schemaname | public
relname | book
seq_scan | 3
seq_tup_read | 200
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 100
n_tup_upd | 70
n_tup_del | 1
n_tup_hot_upd | 0
n_live_tup | 99
n_dead_tup | 0
n_mod_since_analyze | 1
n_ins_since_vacuum | 0
last_vacuum |
last_autovacuum | 2022-02-26 01:41:32.141176+00
last_analyze |
last_autoanalyze | 2022-02-26 01:37:31.581595+00
vacuum_count | 0
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 2