PostgreSQLの制約検査を遅延させるとリストア時間が早くなるか検証してみた

#PostgreSQL

岩田智哉

2022.05.02

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

MAD事業部@大阪の岩田です。

PostgreSQLでは制約を利用する際の動作として

ステートメント実行毎に制約を検査するIMMEDIATE
トランザクションコミット時に制約を検査するDEFERRED

の2パターンが存在します。

https://www.postgresql.jp/document/13/html/sql-set-constraints.html

また、ドキュメントでは大量データ投入時の外部キー制約に関して以下のように記述されています。

インデックスの場合と同様、外部キー制約は一行一行検査するよりも効率的に、「まとめて」検査することができます。従って、外部キー制約を削除し、データをロード、そして、制約を再作成する方法は有用となることがあります。繰り返しますが、データロードの速度と、制約が存在しない間のエラー検査がないという点とのトレードオフがあります。

https://www.postgresql.jp/document/13/html/populate.html

上記の記述はデータ投入前に外部キー制約を削除しておき、データ投入後に外部キー制約を再作成する手法を紹介していますが、制約検査のタイミングをDEFERREDに設定することで高速化は期待できないのでしょうか？DEFERREDな制約検査は

一行一行検査するよりも効率的に、「まとめて」検査

に該当しないのでしょうか？実際に試してみました。

環境

今回検証に利用した環境です

OS: Amazon Linux2 (5.10.109-104.500.amzn2.x86_64)
インスタンスタイプ: m5.large
PostgreSQL: 13.3

PostgreSQLはamazon-linux-extras使ってインストール後

log_min_duration_statement = 1000
log_statement = 'all'

のみ設定をデフォルトから変更しています

やってみる

テーブルとテストデータの準備

まずはテスト用のテーブルとテストデータを準備します。外部キーで参照する親テーブルです。

CREATE TABLE parent (
  id serial PRIMARY KEY,
  val text
);
INSERT INTO parent(
  val
)values(
  'hogehoge'
);

続いてIMMEDIATE制約を持つテーブルを作成

CREATE TABLE not_deferrable  (
  id serial, 
  parent_id int,
  val text,
  created_at timestamp,
  FOREIGN KEY ( parent_id )
    REFERENCES parent (id)
);

続いてデフォルトでDEFERREDモードを利用するテーブルを作成

CREATE TABLE initially_deferred  (
  id serial, 
  parent_id int,
  val text,
  created_at timestamp,
  FOREIGN KEY ( parent_id )
    REFERENCES parent (id) DEFERRABLE INITIALLY DEFERRED
);

外部キーの無いテーブルも作成しておきましょう

CREATE TABLE no_fkey  (
  id serial, 
  parent_id int,
  val text,
  created_at timestamp
);

作成した各テーブルにテストデータを100万件づつ投入します

INSERT INTO not_deferrable(
    parent_id,
    val,
    created_at
)
SELECT 
    1,
    repeat('hoge', 100),
    now()
FROM
    generate_series(
        1,
        1000000
    ) AS s;

INSERT INTO initially_deferred(
    parent_id,
    val,
    created_at
)
SELECT 
    1,
    repeat('hoge', 100),
    now()
FROM
    generate_series(
        1,
        1000000
    ) AS s;

INSERT INTO no_fkey(
    parent_id,
    val,
    created_at
)
SELECT 
    1,
    repeat('hoge', 100),
    now()
FROM
    generate_series(
        1,
        1000000
    ) AS s;

データの準備ができたので、pg_dumpでダンプを吐き出します。フォーマットはカスタムフォーマットとしました

pg_dump -d blog -Fc -f blog.dmp

計測してみる

準備ができたので以下のシェルスクリプトを実行して各テーブルにデータを投入した際の所要時間を計測します

#!/bin/bash

LOOP_CNT=10
SLEEP_SEC=30

echo "start restore to not_deferred"
for i in `seq 1 $LOOP_CNT` ; do
  psql -d blog -U postgres -c "TRUNCATE not_deferrable;" > /dev/null
  /usr/bin/time -f %e pg_restore -U postgres --data-only -d blog  --single-transaction --table=not_deferrable  blog.dmp
  sleep $SLEEP_SEC
done

echo "start restore to initially_deferred"
for i in `seq 1 $LOOP_CNT` ; do
  psql -d blog -U postgres -c "TRUNCATE initially_deferred;" > /dev/null
  /usr/bin/time -f %e pg_restore -U postgres --data-only -d blog  --single-transaction --table=initially_deferred  blog.dmp
  sleep $SLEEP_SEC
done

echo "start restore to no_fkey"
for i in `seq 1 $LOOP_CNT` ; do
  psql -d blog -U postgres -c "TRUNCATE no_fkey;" > /dev/null
  /usr/bin/time -f %e pg_restore -U postgres --data-only -d blog  --single-transaction --table=no_fkey  blog.dmp
  sleep $SLEEP_SEC
done

やっていることはシンプルで、ループ処理の中で各テーブルに

TRUNCATEを実行
行データをリストア
チェックポイントやVACUUMが完了するまで十分な時間sleep ※よく考えたらTRUNCATEするのでループ内でsleepしなくても良かったかも...

を実行しつつ、リストアの所要時間を出力しているだけです。

結果は以下の通りでした

start restore to not_deferred
10.68
11.61
11.33
10.81
12.49
10.48
10.74
10.86
10.63
11.95
start restore to initially_deferred
10.49
10.90
12.40
10.49
10.91
10.56
10.66
11.74
10.49
10.57
start restore to no_fkey
8.19
5.26
5.89
5.28
5.29
6.54
5.29
5.63
7.41
5.31

結果

まとめるとリストアの所要時間は以下の通りでした

制約検査	平均	最小値	最大値	中央値
なし	6.009	5.26	8.19	5.47
IMMEDIATE	11.158	10.48	12.49	10.835
DEFERRED	10.921	10.49	12.4	10.615

10セットだけの計測ですが、パっと見る限りは制約検査のタイミングによって高速化しているようには見えないですね。

リストア時のログも確認しておきましょう。制約検査がIMMEDIATEなテーブルにリストアした際のログは以下のようなものでした。

2022-05-01 23:34:23.755 UTC [1264] LOG:  statement: COPY public.not_deferrable (id, parent_id, val, created_at) FROM stdin;

2022-05-01 23:34:34.611 UTC [1264] LOG:  duration: 10856.688 ms
2022-05-01 23:34:34.612 UTC [1264] LOG:  statement: COMMIT
2022-05-01 23:34:34.612 UTC [1264] LOG:  statement: --
        -- PostgreSQL database dump complete
        --

対して制約検査がDEFERREDなテーブルに対してリストアした際のログです

2022-05-01 23:42:39.304 UTC [1457] LOG:  statement: COPY public.initially_deferred (id, parent_id, val, created_at) FROM stdin;

2022-05-01 23:42:44.651 UTC [1457] LOG:  duration: 5346.409 ms
2022-05-01 23:42:44.651 UTC [1457] LOG:  statement: COMMIT
2022-05-01 23:42:49.870 UTC [1457] LOG:  duration: 5218.817 ms
2022-05-01 23:42:49.870 UTC [1457] LOG:  statement: --
        -- PostgreSQL database dump complete
        --

COPY自体は5秒弱で完了していますが、代わりにコミットにも5秒弱かかっています。IMMEDIATEの場合はCOPYで1レコード投入毎に制約検査を実施しているためCOMMITのオーバーヘッドは小さいものの、DEFERREDの場合はCOMMIT時に全レコードに対して制約検索が必要になるため、このような結果になったと考えられます。

まとめ

今回の計測結果から考えると、DEFERREDな制約検査はドキュメントに記載されている

一行一行検査するよりも効率的に、「まとめて」検査

には該当しないように見えます。

今回の計測に利用したpg_restoreのオプションは

DEFERREDな制約検査の効果を計測したいという意図があったので--single-transactionを設定
複数のテーブルへのデータ投入をサクっと比較したかったので、テーブルは再作成せずに--data-onlyで行データのみ投入

というものでしたが、実際にリストアを実行する場合はデータベース丸ごとリストアするケースが多いと思います。その場合は

--single-transactionを外して、代わりに-jで処理を並列化する
--tableの指定を外してデータベース全体をリストアする

といった指定に変えることで、データ投入完了後に制約を作成するような動作となります。

pg_restoreのもっとも時間がかかる部分、つまり、データのロード、インデックスの作成、制約の作成部分を最大number-of-jobsの並行セッションを使用して実行します。このオプションは、複数プロセッサマシンで稼働するサーバに大規模なデータベースをリストアする時間を劇的に減らすことができます。データベースサーバに直接接続するのではなくスクリプトを生成する場合には、このオプションは無視されます。

https://www.postgresql.jp/document/13/html/app-pgrestore.html

これで