More than 1 year has passed since last update.

PostgreSQLAdvent Calendar 2022

MERGEの挿入/更新性能の比較結果

Last updated at 2022-12-18Posted at 2022-12-18

PostgreSQLのアドベントカレンダー2022の19日目の記事です。
昨日はkingtomo1122さんの仮想空間で学ぶPostgreSQLという記事でした。
ちゃぶ台を作って、CREATE TABLEして、ご飯と味噌汁を並べてデータを登録し、最後にはちゃぶ台をひっくり返してTRUNCATEするという。。。DBも仮想空間で遊ぶ時代になったんですねｗ

はじめに

今回の記事ですが、タイトルのとおりMERGE文を使用したときの挿入処理の性能について調べたので、その内容をについて書きます。なるべく単純な性能比較ができるようにINSERT/UPDATEを含む以下の3つのSQLを2つのケースで比較してみたのでその結果を共有しようと思います。

比較対象のSQL
- 挿入処理
  - INSERT
  - INSERT ON CONFLICT
  - MERGE
- 更新処理
  - UPDATE
  - INSERT ON CONFLICT
  - MERGE
ユースケース（※比較のため、ON CONFILICT、MERGE文では挿入のみ、更新のみが発生するように調整して実施）
- 1件ずつ処理するパターン
- バルクで処理するパターン（複数テーブルをマージするケースを想定）

INSERT ON CONFLICTもMERGEも便利ではありますが、単純な処理に比べるとどれくらい違いがあるのか気になったので調べてみました。

ちなみに。。。

PostgreSQL 15の目玉機能でもあり、既に多くの方が説明や検証をされているので機能の説明は本記事では省いています。
以下の記事はどれも大変参考になるので、リンクのみ紹介させていただきます。興味のある方は勉強になるのでご確認いただければと思います。

MERGE文の機能の紹介
- PostgreSQL 15にMERGE文UPSERTがやってくる
性能の紹介
- fujii_masaoさんの PostgreSQL の INSERT ON CONFLICT と MERGE の簡易性能比較という記事
- PGConf.Japan 2022の澤田さんのセッション

測定結果

挿入処理：

更新処理：

上記の結果を見ると、処理内容（挿入/更新）、ユースケースによって、性能の傾向が異なっているので、使い方がある程度見えている場合はON CONFLICTかMERGE文か意識的に使い分けたほうがよさそうです。

1件ずつ処理する場合
- 挿入が多く発生する場合は「ON CONFILICT」が優位。
- 更新が多く発生する場合も「ON CONFILICT」が優位。
バルクで処理する場合
- 挿入が多く発生する場合は「MERGE」が優位。
- 更新が多く発生する場合は「ON CONFILICT」が優位。

と、これだけ見ると「ON CONFILICT」のほうが良さそうに見えますが、MERGE文の場合、パターンにマッチした場合にINSERT, UPDATEに加えて、DELETEも可能という使い勝手の部分で優位な面があることも忘れてはいけません。性能の違いに加えて、これらの使い勝手の部分も考慮した上で要件に合う選択をしてもらうのがよいかと思います。

以降は実施した検証内容について紹介します。気になる方はご確認いただければと思います。

性能比較（挿入）の試験内容

各SQL（INSERT、INSERT ON CONFILICT、MERGE）と2つのユースケースを想定した内容で性能を比較します。

測定結果

測定結果は以下のとおり。（各3回測定した結果の平均値を表示しています。）
左：ユースケース1（1件ずつ処理するパターン）
右：ユースケース2（バルクで処理するパターン）

試験概要

環境

OS：Rocky 8.5
DB：PostgreSQL 15.1
ディスク：SSD

測定方法

各SQLを用いた場合の1万レコードの挿入に掛かる時間、レイテンシを比較する
pgbenchのカスタムクエリとして実行し、レポート(-r オプション)の結果を用いて評価を行う
各測定は3回ずつ実施する
処理の対象のテーブルはunlogged tableとする

ユースケース1：1件ずつ処理するパターン

UPSERTとして利用する場合を想定して、1件ずつレコードを処理する形で実施した場合の結果がこちらです。

想定通りINSERTが最も早く、あとはON CONFLICTとMERGEと続くという結果になりました。

比較に使用したSQL

1件ずつ挿入し、1万件のレコードを挿入するまでのレイテンシの平均値を集計しました。

-- INSERT
INSERT INTO pgbench_accounts (aid, bid, filler) VALUES (:aid, :bid, random()::text);

-- CONFILICT
INSERT INTO pgbench_accounts (aid, bid, filler) VALUES (:aid, :bid, random()::text)
    ON CONFLICT (aid)
    DO UPDATE SET bid = :bid, filler = random()::text;

-- MERGE
MERGE INTO pgbench_accounts pa USING (VALUES (:aid, :bid, random()::text)) AS i(aid, bid, filler) ON pa.aid = i.aid
  WHEN MATCHED THEN UPDATE SET filler = 'updated'
  WHEN NOT MATCHED THEN INSERT (aid, bid, filler) VALUES (i.aid, i.bid, i.filler);

手順

今回の測定では、pgbench_accountsをunlogged tableで作成し、TRUNCATEしてから利用しています。

$ pgbench -i -s 1 postgres --unlogged-tables
$ psql postgres -c "TRUNCATE pgbench_accounts" -c "VACUUM ANALYZE"
$ pgbench -r -t 1 -f <各SQLファイル>

ユースケース2：バルクで処理するパターン

複数のテーブルのマージ等、バルクで処理する場合を想定して、1万レコードをまとめて処理する場合の比較結果がこちらです。

1件ずつ処理した場合とは異なり、INSERTとMERGEがほぼ同等で、ON CONFLICTが最も遅いという結果になりました。

比較に使用したSQL

今回の検証では、実際のテーブルのマージではなく、generate_seriesを持ちいて1万件のレコードをまとめて挿入した際の処理が完了するまでの時間を計測しました。

-- INSERT
INSERT INTO pgbench_accounts (aid, bid, filler) SELECT num, 2, random()::text
FROM generate_series(1, 10000) AS num;

-- INSERT ON CONFLICT
INSERT INTO pgbench_accounts (aid, bid, filler) SELECT num, 2, random()::text
FROM generate_series(1, 10000) AS num
    ON CONFLICT (aid)
    DO UPDATE SET bid = 2, filler = random()::text;

-- MERGE
MERGE INTO pgbench_accounts pa
USING (SELECT num, 2,  random()::text FROM generate_series(1, 10000) AS num) AS i(aid, bid, filler)
ON pa.aid = i.aid
WHEN MATCHED THEN
    UPDATE SET filler = 'updated'
WHEN NOT MATCHED THEN
    INSERT (aid, bid, filler) VALUES (i.aid, i.bid, i.filler);

手順

ユースケース１と同様で、pgbench_accountsをunlogged tableで作成し、TRUNCATEしてから利用しています。

$ pgbench -i -s 1 postgres --unlogged-tables
$ psql postgres -c "TRUNCATE pgbench_accounts" -c "VACUUM ANALYZE"
$ pgbench -r -t 1 -f <各SQLファイル>

性能比較（更新）の試験内容

挿入処理の結果を見たら、100%更新の場合がどうなるのかも気になったのでついでに確認しました。（元々は挿入処理のみ確認する予定でしたが、やってみると意外と性能に違いが見えたので更新処理も気になったという感じです。）

実施な内容は挿入の比較とほぼ同じものとなっています。

比較対象のSQL
- UPDATE
- INSERT ON CONFLICT
- MERGE
ユースケース（※比較のため、ON CONFILICT、MERGE文では更新のみが発生するように調整して実施）
- 1件ずつ処理するパターン
- バルクで処理するパターン（複数テーブルをマージするケースを想定）
  - ただし、UPDATEについては id カラムが10000以下のレコードを全て更新するという形にしているので、比較としてはちょっと微妙な気がしています。

比較結果

測定結果は以下のとおり。（挿入処理と同様に各3回測定した結果の平均値を表示しています。）
左：ユースケース1（1件ずつ処理するパターン）
右：ユースケース2（バルクで処理するパターン）

1件ずつ処理する場合に関しては、MERGE文が一番遅いという結果は挿入処理と同じで変わりませんでした。ただ、1万レコードをバルクで処理した場合の結果については挿入処理とは異なり、こちらもMERGEが一番遅いという結果になりました。

ちなみに、3回実施した限り、こちらも挿入処理のときと同様で結果のブレはほぼありませんでした。

さいごに

ON CONFLICTもMERGE文も、単純な挿入処理や更新処理に比べると複雑な処理になるので、多少の性能差はあると思っていましたが、想定どおり違いがあるものあれば、意外と単純な処理と性能に違いがないパターンもあることがわかりました。また、ON CONFLICTとMERGE文の性能比較において、処理内容によっては結果に違いがあることも見れたので、確認した意味はあったかと思います。

PostgreSQL 15でMERGE文が実装されたことによって、システムの運用に合わせて選択可能な手段が増えて、益々PostgreSQLがDBMSとして洗練されているのを感じます。MERGE文の実装によって、Oracleとの互換性も上がり、移行の面でも良い改善になったかと思います。

さて、アドベントカレンダーもあとわずか。明日は@tom-satoの記事です。どんな記事が書かれるのか楽しみですね。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up