PostgreSQL の INSERT ON CONFLICT と MERGE の簡易性能比較

以前に、PostgreSQLの3つのUPSERT方式の簡易性能検証を行いましたが、今回はその検証で最も高性能だったINSERT ON CONFLICTとPostgreSQL15新機能のMERGEを簡易性能検証します。

簡易性能検証では、以下の3パターンで各コマンドが1000万件のUPSERTにかかる時間を計測します。計測は3回ずつ行い、中央値を計測結果として採用します。計測には、PostgreSQL15 Beta2を使用します。

• ① 1000万件のUPSERTのうち、1000万件がINSERT、0件がUPDATEになるパターン
• ② 1000万件のUPSERTのうち、約500万件がINSERT、約500件がUPDATEになるパターン
• ③ 1000万件のUPSERTのうち、0万件がINSERT、1000件がUPDATEになるパターン

簡易性能検証の結果は以下のとおりで、今回の検証内容ではMERGEの方がINSERT ON CONFLICTよりも高性能でした。

検証パターン	INSERT ON CONFLICT	MERGE
① 1000万件をINSERT	23.1 s	9.3 s
② 約500万件をINSERT、約500万件をUPDATE	25.8 s	17.4 s
③ 1000万件をUPDATE	41.5 s	38.1 s

検証方法

-- psqlの時間計測機能を有効化
\timing on

-- UPSERT元と先のテーブルとしてsrcとdstを作成する。
-- できるだけUPSERT処理そのものの時間計測となるように、
-- テーブルはUNLOGGEDで作成して、autovacuumを無効化する。
DROP TABLE IF EXISTS src, dst;
CREATE UNLOGGED TABLE src
  (key bigint PRIMARY KEY, val bigint)
  WITH (autovacuum_enabled=false);
CREATE UNLOGGED TABLE dst
  (key bigint PRIMARY KEY, val bigint)
  WITH (autovacuum_enabled=false);

-- UPSERT元テーブルsrcに1000万件を挿入する。
INSERT INTO src VALUES (generate_series(1, 1000000), 1);
VACUUM ANALYZE;

-- 計測中にCHECKPOINTが走らないように、事前に実行する。
CHECKPOINT;
TRUNCATE dst;

-- 空のテーブルdstに対してINSERT ON CONFLICTを実行して、
-- 「① 1000万件をINSERT」の実行時間を計測する。
VACUUM ANALYZE;
INSERT INTO dst AS d SELECT key, val FROM src
  ON CONFLICT (key) DO UPDATE SET val = d.val + EXCLUDED.val;

-- 1000万件格納済のテーブルdstに対してINSERT ON CONFLICTを実行して、
-- 「③ 1000万件をUPDATE」の実行時間を計測する。
VACUUM ANALYZE;
INSERT INTO dst AS d SELECT key, val FROM src
  ON CONFLICT (key) DO UPDATE SET val = d.val + EXCLUDED.val;

-- テーブルdstから50%の約500万件をランダムに削除する。
DELETE FROM dst WHERE key IN
  (SELECT key FROM dst TABLESAMPLE BERNOULLI(50));

-- 約500万件格納済のテーブルdstに対してINSERT ON CONFLICTを実行して、
-- 「② 約500万件をINSERT、約500万件をUPDATE」の実行時間を計測する。
VACUUM ANALYZE;
INSERT INTO dst AS d SELECT key, val FROM src
  ON CONFLICT (key) DO UPDATE SET val = d.val + EXCLUDED.val;

-- 計測中にCHECKPOINTが走らないように、事前に実行する。
-- テーブルdstを空にする。
CHECKPOINT;
TRUNCATE dst;

-- 空のテーブルdstに対してMERGEを実行して、
-- 「① 1000万件をINSERT」の実行時間を計測する。
VACUUM ANALYZE;
MERGE INTO dst AS d USING src AS s ON d.key = s.key
  WHEN MATCHED THEN UPDATE SET val = d.val + s.val
  WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val);

-- 1000万件格納済のテーブルdstに対してMERGEを実行して、
-- 「③ 1000万件をUPDATE」の実行時間を計測する。
VACUUM ANALYZE;
MERGE INTO dst AS d USING src AS s ON d.key = s.key
  WHEN MATCHED THEN UPDATE SET val = d.val + s.val
  WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val);

-- テーブルdstから50%の約500万件をランダムに削除する。
DELETE FROM dst WHERE key IN
  (SELECT key FROM dst TABLESAMPLE BERNOULLI(50));

-- 約500万件格納済のテーブルdstに対してMERGEを実行して、
-- 「② 約500万件をINSERT、約500万件をUPDATE」の実行時間を計測する。
VACUUM ANALYZE;
MERGE INTO dst AS d USING src AS s ON d.key = s.key
  WHEN MATCHED THEN UPDATE SET val = d.val + s.val
  WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val);