PostgreSQLのUPSERT方式の簡易性能比較

結果

PostgreSQLの3つのUPSERT方式の簡易性能検証の結果は以下のとおり。

方式	100万件のUPSERT時間	説明
INSERT失敗時UPDATE	43.6 秒	INSERTを試み、ユニーク制約違反で失敗時にはUPDATEを実行する関数を使ったUPSERT
UPDATE失敗時INSERT	26.7 秒	UPDATEを試み、更新件数が0件のときにはINSERTを実行する関数を使ったUPSERT
INSERT ON CONFLICT	8.1 秒	9.5 からサポートされるINSERT ON CONFLICT構文によるUPSERT

検証方法

• 端末上で以下を実行

# UPSERT対象テーブルのベースになる100万件のpgbench_accountsテーブルを作成
$ pgbench -i -s 10 --unlogged-table

• psqlで以下を実行

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

-- 100万件のpgbench_acountsテーブルから50%のデータをランダムに削除
DELETE FROM pgbench_accounts WHERE aid IN
  (SELECT aid FROM pgbench_accounts TABLESAMPLE BERNOULLI(50));
VACUUM ANALYZE pgbench_accounts;

-- INSERT失敗時にUPDATEを実行するUPSERT関数を定義
CREATE OR REPLACE FUNCTION upd_if_ins_fails
  (myaid INT, mybid INT, myabalance INT, myfiller CHAR(84))
  RETURNS void AS $$
BEGIN
  INSERT INTO hoge
    VALUES (myaid, mybid, myabalance, myfiller);
EXCEPTION WHEN unique_violation THEN
  UPDATE hoge
    SET bid = mybid, abalance = myabalance, filler = myfiller
    WHERE aid = myaid;
END;
$$ LANGUAGE plpgsql;

-- 0件UPDATE時にINSERTを実行するUPSERT関数を定義
CREATE OR REPLACE FUNCTION ins_if_no_upd
  (myaid INT, mybid INT, myabalance INT, myfiller CHAR(84))
  RETURNS void AS $$
BEGIN
  UPDATE hoge
    SET bid = mybid, abalance = myabalance, filler = myfiller
    WHERE aid = myaid;
  IF FOUND THEN
    RETURN;
  END IF;
  INSERT INTO hoge
    VALUES (myaid, mybid, myabalance, myfiller);
END;
$$ LANGUAGE plpgsql;

-- UPSERT対象のテーブルhogeをpgbench_accountsからコピー作成
DROP TABLE hoge;
CREATE UNLOGGED TABLE hoge AS SELECT * FROM pgbench_accounts;
ALTER TABLE hoge ADD PRIMARY KEY (aid);

-- UPSERT検証をautovacuumやCHECKPOINTが邪魔しないようにする
VACUUM ANALYZE hoge;
CHECKPOINT;

-- 100万件UPSERTの時間計測その1
SELECT upd_if_ins_fails (num, 9, 99, 'UPSERT') FROM generate_series(1, 1000000) num;

-- 100万件UPSERTされたことを確認
SELECT count(*) FROM hoge WHERE bid = 9;

-- UPSERT対象のテーブルhogeをpgbench_accountsからコピー作成
DROP TABLE hoge;
CREATE UNLOGGED TABLE hoge AS SELECT * FROM pgbench_accounts;
ALTER TABLE hoge ADD PRIMARY KEY (aid);

-- UPSERT検証をautovacuumやCHECKPOINTが邪魔しないようにする
VACUUM ANALYZE hoge;
CHECKPOINT;

-- 100万件UPSERTの時間計測その2
SELECT ins_if_no_upd (num, 9, 99, 'UPSERT') FROM generate_series(1, 1000000) num;

-- 100万件UPSERTされたことを確認
SELECT count(*) FROM hoge WHERE bid = 9;

-- UPSERT対象のテーブルhogeをpgbench_accountsからコピー作成
DROP TABLE hoge;
CREATE UNLOGGED TABLE hoge AS SELECT * FROM pgbench_accounts;
ALTER TABLE hoge ADD PRIMARY KEY (aid);

-- UPSERT検証をautovacuumやCHECKPOINTが邪魔しないようにする
VACUUM ANALYZE hoge;
CHECKPOINT;

-- 100万件UPSERTの時間計測その3
INSERT INTO hoge SELECT num, 9, 99, 'UPSERT'
  FROM generate_series(1, 1000000) num
  ON CONFLICT (aid) DO UPDATE
  SET bid = EXCLUDED.bid,
          abalance = EXCLUDED.abalance,
          filler = EXCLUDED.filler;

-- 100万件UPSERTされたことを確認
SELECT count(*) FROM hoge WHERE bid = 9;