以前に、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);