PostgreSQLのUPSERT時にデッドタプルはどう増える?

本エントリーはPostgreSQL Advent Calendar 2022の7日目の記事です。

UPSERT時のデッドタプルについて簡単に動作確認してみたので、共有します。

前日の記事は @toshikawa さんによる次の記事でした。

UPSERT とは

データベースにレコードを反映する際、新規の場合は INSERT 、更新の場合は UPDATE というようにデータの状態によって UPDATE と INSERT を使い分けることを、UPDATE と INSERT をくっつけて UPSERT と呼びます。

PostgreSQL でUPSERT処理を記述する場合、INSERT ON CONFLICT構文が古くから利用されており、15からはMERGE文も利用できるようになりました。

PostgreSQL UPSERTの詳細については、明日9日目のアドカレ @meijik さんの『UPSERT大全』をお楽しみください！

以下では、UPSERT実行時にデッドタプルがどのように生成されるか確認します。

テスト用テーブル

テスト用のシンプルなテーブルを用意します。

CREATE TABLE blog (
  id int PRIMARY KEY, 
  name varchar(20)
);

シンプルな INSERT の場合

まっさらなテーブルに、新規レコードを追加します。

postgres=> INSERT INTO blog values(1, 'a');
INSERT 0 1

postgres=> SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'blog';

 relname | n_dead_tup | n_live_tup
---------+------------+------------
 blog    |          0 |          1
(1 row)

INSERTに対応するliveタプルが一つだけ存在します。

同じプライマリーキーで INSERT すると、duplicate key エラーが発生します。

INSERT INTO blog(id, name)
VALUES(1, 'a');

ERROR:  duplicate key value violates unique constraint "blog_pkey"
DETAIL:  Key (id)=(1) already exists.

INSERT に失敗したタプルがdeadとマークされるため、n_dead_tup が1つ増えます。

INSERT ON CONFLICT UPSERTの場合

次に INSERT ON CONFLICT の UPSERT で UPDATE します。

INSERT INTO blog(id, name)
VALUES(1, 'b')
ON CONFLICT(id)
DO UPDATE SET name = excluded.name;

INSERT 0 1

UPDATEにより、初回INSERT時のタプルがデッドとマークされるため、n_dead_tup が1つ増えました。

次に、少し変則的に INSERT コンフリクト時に UPDATE の代わりに何もしないことにします(DO NOTHING)。

INSERT INTO blog(id, name)
VALUES(1, 'c')
ON CONFLICT(id)
DO NOTHING;

INSERT 0 0

この場合、デッドタプルは増えていません。

INSERT..ON CONFLICT 文には投機的挿入(speculative insertion)という仕組みがあり、INSERT前に制約違反がないか事前チェックし、違反があると判断した場合は INSERT の代わりに ON CONFLICT 文を実行するからです。

先のUPSERT において、デッドタプルが増えたのはUPDATE に対応する 1タプル分だけなのも、納得です。 コンフリクトで失敗したINSERTと成功したUPDATEの2タプル分が増えるわけではありません。

MERGE UPSERTの場合

次に、PostgreSQL 15から対応した MERGEの動作を確認します。

MERGEでUPDATEを走らせます。

MERGE INTO blog AS t
USING (VALUES (1, 'd')) AS i(id, name)
ON t.id = i.id
WHEN MATCHED THEN
    UPDATE SET name = i.name
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (i.id, i.name);

MERGE 1

UPDATEにより最新のタプルが置き換わったため、デッドタプルが1つ増えます。

先程の ON CONFLICT DO NOTHING と同様に、マッチ時には何もしないようにします。

MERGE INTO blog AS t
USING (VALUES (1, 'e')) AS i(id, name)
ON t.id = i.id
WHEN MATCHED THEN
    DO NOTHING
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (i.id, i.name);

MERGE 0

この場合、デッドタプルは増えていません。

MERGEではソース・ターゲットテーブル間でJOINしてマッチ判定をしています。MATCHEDとNOT MATCHED(live)の二律背反です。

First, the MERGE command performs a join from data_source to target_table_name producing zero or more candidate change rows. For each candidate change row, the status of MATCHED or NOT MATCHED is set just once, after which WHEN clauses are evaluated in the order specified. For each candidate change row, the first clause to evaluate as true is executed. No more than one WHEN clause is executed for any candidate change row.

https://www.postgresql.org/docs/15/sql-merge.html

並列INSERTでコンフリクトした場合

最後に、INSERT..ON CONFLICT/MERGE を使って並列で同じキーの新規INSERTが走り、あと負けした場合を考えます。

MERGE の場合の具体的な実行例は以下の通りです。

※ INSERT..ON CONFLICTの場合は、メインのSQLを読み替えてください。

結果として、投機的挿入を使う INSERT..ON CONFLICT はUPDATEとして成功し、MERGEは上記のように制約違反により INSERTが失敗します。

When MERGE is run concurrently with other commands that modify the target table, the usual transaction isolation rules apply; see Section 13.2 for an explanation on the behavior at each isolation level. You may also wish to consider using INSERT ... ON CONFLICT as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs. There are a variety of differences and restrictions between the two statement types and they are not interchangeable.

https://www.postgresql.org/docs/15/sql-merge.html

タプルを確認すると、いずれのケースも liveとdeadのタプルが1つずつ増えます。

INSERT..ON CONFLICT の場合、UPDATE に伴ってデッドタプルが1つ増え、MERGE の場合、INSERT できなかったことによるデッドタプルが1つ増えています。

最後に

PostgreSQLのUPSERT時のデッドタプルの増え方を確認しました。 デッドタプルの増え方は、想像どおりだったでしょうか?

なお、本記事は、今年参加した PGConf EU 2022のLTでも発表のあった、次のブログにインスパイアされたものです。

Hidden dangers of duplicate key violations in PostgreSQL and how to avoid them | AWS Database Blog

元ブログでは、内部構造、トランザクションID、デッドタプルによるテーブルの肥大化などにも触れられているため、ぜひご覧ください。

明日はKosuke_Kidaさんの記事です。楽しみください！