PostgreSQL 9.5 UPSERTを試してみた

しばらくPostgreSQL 9.5調査をサボっていたのですが、久々に再開。
9.5の目玉機能になりそうなものとして、所謂UPSERTがある。

UPSERTの概要

UPSERTとは、非常に簡単に言えば、
「INSERTできるならINSERTし、INSERTできない場合は代わりにUPDATEを行う」
という機能。
本家のアナウンスーンのページでは
INSERT ON CONFLICT UPDATE, otherwise known as "UPSERT"
と書かれている。
この記述からもわかるように、UPSERT専用の構文が追加されたというわけではなく、INSERT文へ構文を追加してUPSERT相当を実現するというもの。

PostgreSQL文書のINSERTのページを見てのとおり、9.5からは
ON CONFLICT [ conflict_target ] conflict_action
という節が追加されている。
結構複雑そうな構文なんだが、UPSERTの一番シンプルな書き方は、

INSERT INTO ...
ON CONFLICT ON CONSTRAINT 制約名
DO UPDATE SET ( カラム名 = 値 )

になるのかな。
「制約名」を構文に書かなければいけないところが重要。
つまり、UPSERTはどんな表に対しても使えるわけではなく、制約を定義した表に対してのみ使用可能重点。
この制約は、制約ならなんでもOKというわけではなく、UNIQUE制約(PRIMARY KEYも含まれる)に限定されるっぽい(少なくとも文書上からはそう読めた)。

つかってみた

ON CONFLICT句を使ってUPSERT相当を実装するシンプルなパターンを試してみた。

まず、primary keyをもつ簡単なテーブルを作成する。

CREATE TABLE test (id int primary key, data text);
CREATE TABLE
     Table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 data   | text    | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

"test_pkey"はid列に設定した、primary key制約の名前になる。

とりあえず3件のレコードを挿入しておく。

INSERT INTO test VALUES (1, 'aaa'),(2, 'bbb'),(3, 'ccc');
INSERT 0 3
TABLE test;
 id | data 
----+------
  1 | aaa
  2 | bbb
  3 | ccc
(3 rows)

この状態で、id列が2のレコードを挿入しようとすると

INSERT INTO test VALUES (2,'ddd');
psql:upsert-01.sql:6: ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(2) already exists.

当然ながらprimary key制約エラーとなる。

そこでCONFLICT ON 〜 DO UPDATE 〜ですよ。

INSERT INTO test VALUES (2, 'ddd')
  ON CONFLICT ON CONSTRAINT test_pkey
  DO UPDATE SET data = 'ddd';
INSERT 0 1
TABLE test;
 id | data 
----+------
  1 | aaa
  3 | ccc
  2 | ddd
(3 rows)

test_pkey制約が制約違反となった場合、DO UPDATE SET句で指定した更新を、制約違反で衝突した行(この例だと id = 2 の例)に対して実施する。

もちろん、tset_pkeyが制約違反でない場合、DO IPDATE SET句の指定は無視され、普通にVALUES句に指定した値が挿入される。

INSERT INTO test VALUES (4, 'ddd_i')
  ON CONFLICT ON CONSTRAINT test_pkey
  DO UPDATE SET data = 'ddd_i';
INSERT 0 1
TABLE test;
 id | data  
----+-------
  1 | aaa
  3 | ccc
  2 | xxx_u
  4 | ddd_i
(4 rows)

過去の代替機能との比較

さて、このUPSERT機能は昔からアプリケーション開発者からは欲しいと言われていた機能らしい。
で、PostgreSQLでは今まで直接該当する機能がなかったので、UPSERTっぽいことを一文で実現するために

CTE(Common Table Expression:共通テーブル式)を使って代替
トリガを使って代替

とかをしていたらしい。

CTEを使って代替

PostgreSQL 9.1から導入されたCTE(WITH構文)を使ってもUPSERTっぽのができる。
例えばさっきのUPSERT実験で使ったテーブルを例にすると、それぞれの代替方法はこんな感じになる。

まず、さっきと同じようなPrimary keyを持つテーブルを用意して、数件データを放り込んでおく。

CREATE TABLE test (id int primary key, data text);
CREATE TABLE
\d test
     Table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 data   | text    | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

INSERT INTO test VALUES (1, 'aaa'),(2, 'bbb'),(3, 'ccc');
INSERT 0 3
TABLE test;
 id | data 
----+------
  1 | aaa
  2 | bbb
  3 | ccc
(3 rows)

ここで普通にidが2のレコードを挿入すると制約違反エラーになるのは同じ。
で、以下の様なCTEを用いる。

WITH val AS (SELECT ((2, 'xxx_i')::test).*),
     upd AS (UPDATE test SET data = 'xxx_u' FROM val
               WHERE test.id = val.id RETURNING test.id)
  INSERT INTO test SELECT * FROM val WHERE id NOT IN (SELECT id FROM upd);

既に同じidのものが存在する場合にはUPDATEが、存在しなければvalに指定したレコードを検索した結果をtestに挿入する・・・という動きだと思うｗ

例示するとこんな感じになる。

WITH val AS (SELECT ((2, 'xxx_i')::test).*),
     upd AS (UPDATE test SET data = 'xxx_u' FROM val
               WHERE test.id = val.id RETURNING test.id)
  INSERT INTO test SELECT * FROM val WHERE id NOT IN (SELECT id FROM upd);
INSERT 0 0

WITH val AS (SELECT ((4, 'ddd_i')::test).*),
     upd AS (UPDATE test SET data = 'ddd_u' FROM val
               WHERE test.id = val.id RETURNING test.id)
  INSERT INTO test SELECT * FROM val WHERE id NOT IN (SELECT id FROM upd);
INSERT 0 1

TABLE test;
 id | data  
----+-------
  1 | aaa
  3 | ccc
  2 | xxx_u
  4 | ddd_i
(4 rows)

CTEを使う方法は事前の準備は不要とはいえ、構文自体が結構理解しづらい感じがある。
個人的には、CTEは黒魔術めいた構文のように思っているのであんまり使いたくない・・・
(書くには書くけど、3日くらい経つと自分で書いた文ながら、これなんだっけ？と悩むことになる)

ON CONFLICTの構文も多少複雑だが、だいぶわかりやすくなった印象はあるなあ。

トリガを使って代替

CTEが導入される前は、トリガを使って代替をしていたんじゃないかなあ。例えばこんな感じ。

まずテーブルの定義。

CREATE TABLE test (id int , data text);

トリガを使う場合にはidにはPrimary key制約を指定しない。
なぜなら、トリガが実行される前に制約チェックが実行されてしまうから。

で、トリガ関数とトリガ定義。
今回は面倒なのでトリガ関数はplpgsqlで実装。高速化するならここをC言語関数で組む必要がある。

CREATE OR REPLACE FUNCTION test_upsert_trig_func() RETURNS trigger AS $$
DECLARE
  i integer;
BEGIN
  SELECT id INTO i FROM test WHERE id = NEW.id;
  IF NOT FOUND THEN
    -- DO NOTHING
    RETURN NEW;
  ELSE
    UPDATE test SET data = NEW.data WHERE id = NEW.id;
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_insert_trig BEFORE INSERT ON test
    FOR EACH ROW EXECUTE PROCEDURE test_upsert_trig_func();

ここまで設定すればINSERT文自体は非常にシンプルに。

INSERT INTO test VALUES (2,'xxx_i');

INSERT INTO test VALUES (4, 'ddd_i');

id=2の場合はトリガ関数内のUPDATE文が、id=4の場合はトリガ関数では何もせず、その後にフツーにINSERTが実行される。

トリガ方式はINSERT文はシンプルになるけど、事前設定が結構面倒ですなあ。
(開発者は助かるがDBAがツラい)
あと、少々汎用性に欠ける気がする。

各方式のINSERT/UPSERT性能

せっかくなので、トリガ方式、CTE方式、ON CONFLICT方式の性能ってどうなんだろうというのも比較してみた。

手元の環境(VM上CentOS+PostgreSQL 9.5alpha1)上で、さっきのテーブルを例にして

空テーブルから100万件挿入
上記と全く同じように100万件を挿入しようとする(で、更新される)
100万件投入後の状態からさらに100万件挿入
上記と全く同じように100万件を挿入しようとする(で、更新される)

というパターンを試してみた。
違いが見えやすくなるかな、と思いテーブル定義は

UNLOGGED (WAL書き出し時間をOFFる)
fillfacotr=90 (更新がHOTになることを狙う)

としてみた。

結果はこんな感じ。

まあトリガ方式が遅いのは想定どおり。トリガ関数をC言語関数化すれば高速化はできると思うけど。
格納済み件数が少ない場合、かつ挿入になる場合は、UPSERTよりもCTEのほうが有利なケースもありそう。
UPDATEになる場合は、UPSERT方式のほうがCTEよりは有利？
UPSERT方式は件数にあまり左右されず性能が安定する？

おわりに

今回は一番シンプルなUPSERTのやりかたを書きましたが、構文を見ても分かるように、結構複雑なこともできそう。
column_name_index の使い方とか、まだ理解しきれていないところもあるので、そのへんもおいおい調べてみようかと。