23
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

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

Posted at

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
23
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?