PostgreSQL 13 HOT(Heap Only Tuple)

PostgreSQLは追記型のアーキテクチャを採用しており、定期的に「使用済」となった不要な領域を回収するバキューム(VACUUM)と呼ばれる処理を考察しなければなりません。このバキューム(VACUUM)の発生頻度を抑制する仕組みとしてHOT(Heap Only Tuple)があります。

このページではHOT(Heap Only Tuple)について考察します。

• PostgreSQL インストール
• PostgreSQL データベースの作成
• PostgreSQL contribモジュールの使い方
• PostgreSQL パラメタの設定方法
• PostgreSQL ベンチマークツールの紹介
• PostgreSQL WAL(Write Ahead Log)の基本説明
• PostgreSQL バックアップとリストア
• PostgreSQL PITR(Point In Time Recovery)基本概念の説明
• PostgreSQL PITR(Point In Time Recovery)操作方法の説明
• PostgreSQL PITR(Point In Time Recovery)タイムライン操作
• PostgreSQL ストリーミングレプリケーションの最小構成
• PostgreSQL ストリーミングレプリケーションのパラメタ説明
• PostgreSQL ストリーミングレプリケーションの障害復旧
• PostgreSQL ストリーミングレプリケーションの多段構成
• PostgreSQL ストリーミングレプリケーションのDR向け設定
• PostgreSQL ストリーミングレプリケーションのコンフリクト
• PostgreSQL 自動バキューム(AUTO VACUUM)
• PostgreSQL HOT(Heap Only Tuple) (いまここ)
• PostgreSQL インデックスのメンテナンス
• PostgreSQL 統計情報の更新
• PostgreSQL 実行計画
• PostgreSQL スロークエリの調査方法

HOTの挙動

通常のデータ更新

「自動バキューム(AUTO VACUUM)」で紹介した検証シナリオは、idとnumberにindexが作成されています。

createdb test
psql test << EOF
CREATE TABLE staff(
  id      serial      PRIMARY KEY,
  number  integer     default 0,
  f_name  varchar(30) default '',
  l_name  varchar(30) default ''
);
CREATE INDEX idx_number ON staff (number);
EOF

このようなテーブルに対して、indexが付与されているnumberを更新すると以下のような更新が発生します。旧レコード全てがガベージコレクションの対象となってしまいます。

ヒープデータの更新

今回のシナリオはは、idのみにindexを作成します。numberにはindexを付与しません。

createdb test
psql test << EOF
CREATE TABLE staff(
  id      serial      PRIMARY KEY,
  number  integer     default 0,
  f_name  varchar(30) default '',
  l_name  varchar(30) default ''
);
EOF

index以外のデータを更新する場合は以下のような更新処理が発生します。indexを更新する場合に比べると、「使用済」となる領域が少なくガベージコレクションの頻度を抑制する事ができます。

FILLFACTOR

PostgreSQLはFILLFACTORと呼ばれる概念があります。直訳すれば「充填率」でどの程度埋まっているかです。

デフォルトではFILLFACTORは100%ですが、ある程度の空き領域を持たせておくと、HOTが効きやすくなります。前述の図で示したHOTが発生するのは、空き領域がある場合のみですので、FILLFACTORを90%程度にしておけば空き領域に更新データを格納する事ができます。

FILLFACTORを低くすればするほどHOTは発生しやすくなるものの、領域の無駄遣いになってしまうデメリットもあります。FILLFACTORは最低でも70%程度と言われています。

FILLFACTORを設定するには、CREATE TABLE文実行時にWITHオプションで指定します。設定例は以下の通りです。

createdb test
psql test << EOF
CREATE TABLE staff(
  id      serial      PRIMARY KEY,
  number  integer     default 10000,
  f_name  varchar(30) default '',
  l_name  varchar(30) default ''
) WITH (FILLFACTOR=90);
EOF

HOPが発生する条件

HOTが有効となるのはindexではない列を更新する場合、かつ、ページ内に空きがある場合です。

以下のような場合はHOTが働かない事に注意が必要です。

• DELETE+INSERT処理
• indexを更新するUPDATE
• 大量のUPDATE

HOT動作確認

動作確認用テーブルの作成

動作確認のため、以下のようなstaffテーブルを作成します。FILLFACTORは90%とします。

createdb test
psql test << EOF
CREATE TABLE staff(
  id      serial      PRIMARY KEY,
  number  integer     default 0,
  f_name  varchar(30) default '',
  l_name  varchar(30) default ''
) WITH (FILLFACTOR=90);
EOF

10,000レコードをstaffテーブルにINSERTします。

for i in {1..10000}
do
  [ $((${i} % 100)) == 0 ] && echo "INSERT : ${i} records "
  psql test -c "INSERT INTO staff (number) VALUES ('${i}');" > /dev/null
done

動作確認前の統計情報を確認しておきます。「使用済」である事を表すn_dead_tupが0である事を確認します。

[postgres@centos10 ~]$ psql test << EOF
> \x
> SELECT * FROM pg_stat_all_tables where relname ='staff';
> EOF
Expanded display is on.
-[ RECORD 1 ]-------+------------------------------
relid               | 16423
schemaname          | public
relname             | staff
seq_scan            | 1
seq_tup_read        | 0
idx_scan            | 0
idx_tup_fetch       | 0
n_tup_ins           | 10000
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 10000
n_dead_tup          | 0
n_mod_since_analyze | 0
n_ins_since_vacuum  | 0
last_vacuum         | 
last_autovacuum     | 2021-01-09 16:07:39.502135+09
last_analyze        | 
last_autoanalyze    | 2021-01-09 16:07:39.519974+09
vacuum_count        | 0
autovacuum_count    | 3
analyze_count       | 0
autoanalyze_count   | 3

HOTの動作確認

2051件のレコードを更新します。更新対象は非indexの列（ヒープ領域）です。

「自動バキューム(AUTO VACUUM)」で紹介したシナリオならば、2,050レコードを超える更新でで自動バキューム(AUTO VACUUM)が発生します。一方、今回のシナリオはHOTが効くのでAUTO VACUUMが起きない事を確認します。

for i in {1..2051}
do
  [ $((${i} % 100)) == 0 ] && echo "UPDATE : ${i} records "
  num=`expr $RANDOM % 10000`
  psql test -c "UPDATE staff SET number=${num} WHERE id=${i}" > /dev/null
done

autovacuum_countが増えてない事からAUTO VACUUMが発生してない事が分かります。

また後述の検証シナリオのため、n_dead_tupの数もメモに控えておきます。

[postgres@centos10 ~]$ psql test << EOF
> \x
> SELECT * FROM pg_stat_all_tables where relname ='staff';
> EOF
Expanded display is on.
-[ RECORD 1 ]-------+------------------------------
relid               | 16423
schemaname          | public
relname             | staff
seq_scan            | 1
seq_tup_read        | 0
idx_scan            | 2051
idx_tup_fetch       | 2051
n_tup_ins           | 10000
n_tup_upd           | 2051
n_tup_del           | 0
n_tup_hot_upd       | 1535
n_live_tup          | 10000
n_dead_tup          | 517
n_mod_since_analyze | 161
n_ins_since_vacuum  | 0
last_vacuum         | 
last_autovacuum     | 2021-01-09 16:07:39.502135+09
last_analyze        | 
last_autoanalyze    | 2021-01-09 16:10:39.562569+09
vacuum_count        | 0
autovacuum_count    | 3
analyze_count       | 0
autoanalyze_count   | 4

FILLFACTOR

動作確認用テーブルの作成

動作確認のため、以下のようなstaffテーブルを作成します。FILLFACTORはデフォルト設定の100%とします。

FILLFACTORが高い分、前述のシナリオに比べると非効率なデータ更新になっている事を確認します。

dropdb test
createdb test
psql test << EOF
CREATE TABLE staff(
  id      serial      PRIMARY KEY,
  number  integer     default 0,
  f_name  varchar(30) default '',
  l_name  varchar(30) default ''
);
EOF

10,000レコードをstaffテーブルにINSERTします。

for i in {1..10000}
do
  [ $((${i} % 100)) == 0 ] && echo "INSERT : ${i} records "
  psql test -c "INSERT INTO staff (number) VALUES ('${i}');" > /dev/null
done

動作確認前の統計情報を確認しておきます。「使用済」である事を表すn_dead_tupが0である事を確認します。

[postgres@centos10 ~]$ psql test << EOF
> \x
> SELECT * FROM pg_stat_all_tables where relname ='staff';
> EOF
Expanded display is on.
-[ RECORD 1 ]-------+------------------------------
relid               | 16411
schemaname          | public
relname             | staff
seq_scan            | 1
seq_tup_read        | 0
idx_scan            | 0
idx_tup_fetch       | 0
n_tup_ins           | 10000
n_tup_upd           | 0
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 10000
n_dead_tup          | 0
n_mod_since_analyze | 154
n_ins_since_vacuum  | 154
last_vacuum         | 
last_autovacuum     | 2021-01-09 15:21:08.494411+09
last_analyze        | 
last_autoanalyze    | 2021-01-09 15:21:08.507202+09
vacuum_count        | 0
autovacuum_count    | 2
analyze_count       | 0
autoanalyze_count   | 2

HOTの動作確認

2051件のレコードを更新します。

for i in {1..2051}
do
  [ $((${i} % 100)) == 0 ] && echo "UPDATE : ${i} records "
  num=`expr $RANDOM % 10000`
  psql test -c "UPDATE staff SET number=${num} WHERE id=${i}" > /dev/null
done

n_dead_tupに着目します。AUTO VACUUMEは発生していないものの、前述のシナリオではn_dead_tupが517であるのに対し、今回のシナリオはn_dead_tupが872になっています。

今回はFILLFACTORが大きいため、それだけ非効率な更新になっている事が読み取れます。

[postgres@centos10 ~]$ psql test << EOF
> \x
> SELECT * FROM pg_stat_all_tables where relname ='staff';
> EOF
Expanded display is on.
-[ RECORD 1 ]-------+------------------------------
relid               | 16411
schemaname          | public
relname             | staff
seq_scan            | 1
seq_tup_read        | 0
idx_scan            | 2051
idx_tup_fetch       | 2051
n_tup_ins           | 10000
n_tup_upd           | 2051
n_tup_del           | 0
n_tup_hot_upd       | 1180
n_live_tup          | 10000
n_dead_tup          | 872
n_mod_since_analyze | 0
n_ins_since_vacuum  | 154
last_vacuum         | 
last_autovacuum     | 2021-01-09 15:21:08.494411+09
last_analyze        | 
last_autoanalyze    | 2021-01-09 15:34:08.749447+09
vacuum_count        | 0
autovacuum_count    | 2
analyze_count       | 0
autoanalyze_count   | 3