PostgreSQL upsert の定石 — ON CONFLICT の使い方と罠

インフラ PostgreSQL データベース
読了 4分

「あれば更新、なければ挿入」は、アプリケーションで SELECT して確認してから分岐するのではなく、INSERT ... ON CONFLICT の 1 文で処理するのが定石です。 確認と挿入の間の隙間に別のトランザクションが割り込む競合状態を、DB が原子的に塞いでくれるからです。基礎第 6 回で立てた「読んで-判断して-書くは原子的な 1 文で」の代表例が、まさに upsert です。

SELECT してから INSERT が壊れる理由 #

アンチパターン: SELECT してから INSERT 
-- アンチパターン: 確認と挿入の間に隙間があります
SELECT id FROM users WHERE email = 'tanaka@example.com';
-- (「ない」という返事を受け取った間に、別のリクエストが同じメールを INSERT)
INSERT INTO users (email, name) VALUES ('tanaka@example.com', '田中太郎');
-- ERROR: duplicate key value violates unique constraint

トラフィックが少ないうちは破裂せず、同じユーザーの重複リクエスト(ダブルクリック、リトライ)が重なった瞬間、ユニーク制約違反で破裂します。このエラーを try-catch で拾ってリトライするコードが増え始めたら、そこが ON CONFLICT の席です。

DO NOTHING と DO UPDATE #

DO NOTHING と DO UPDATE 
-- すでにあれば黙って通過: 冪等な挿入
INSERT INTO subscriptions (user_id, plan)
VALUES (42, 'basic')
ON CONFLICT (user_id) DO NOTHING;

-- すでにあれば更新: 本当の upsert
INSERT INTO users (email, name, last_seen_at)
VALUES ('tanaka@example.com', '田中太郎', now())
ON CONFLICT (email) DO UPDATE
SET name         = EXCLUDED.name,
    last_seen_at = EXCLUDED.last_seen_at;

文法の部品は 3 つです。衝突判定の対象 (email) はユニーク制約(またはユニークインデックス)が張られたカラムでなければならず、upsert の前提自体が「ユニーク制約がある」ことです(基礎第 2 回の制約設計がここで活きてきます)。EXCLUDED は「挿入しようとして衝突したその新しい行」を指す仮想テーブルで、新しい値で更新するときに使います。そして DO UPDATE には WHERE を付けて条件付き更新もできます。

条件付き更新 
-- 新しい値のほうが新しいときだけ更新 (イベントの順序逆転への防御)
ON CONFLICT (device_id) DO UPDATE
SET reading = EXCLUDED.reading, reported_at = EXCLUDED.reported_at
WHERE EXCLUDED.reported_at > sensor_readings.reported_at;

知っておく罠 3 つ #

  • 衝突対象は明示するのが定石です。 ON CONFLICT DO NOTHING のような対象の省略も文法上可能ですが、ユニーク制約が複数あるテーブルでは「どの衝突を吸収する意図だったのか」がコードから消えます。意図した制約を (カラム) で明示すれば、他の制約の違反はきちんとエラーになり、バグを隠しません。
  • DO UPDATE は値が同じでも行を書きます。 衝突した行はロックされ、新しいバージョンが作られます(MVCC の原理どおり dead tuple も生まれます)。「実質何も変わっていない」大量 upsert を繰り返すバッチなら、WHERE (既存の値) IS DISTINCT FROM (新しい値) の条件で無意味な更新を濾すのが VACUUM の負担を減らします。
  • シーケンスは衝突しても消費されます。 IDENTITY の採番は衝突判定の前に起きるので、DO NOTHING で吸収された挿入も番号を 1 つ使います。id が飛び飛びになるのは正常で、欠番のない連番が必要な要件(請求書など)はそもそもシーケンスではなく別の採番設計が正解です。

MERGE との関係 #

PostgreSQL 15 からは標準 SQL の MERGE もあります。分かれ道はシンプルです。同時実行に安全な upsert が目的なら ON CONFLICT(ユニーク制約ベースの原子的処理)、「挿入・更新・削除を条件別に振り分けるバッチ同期」が目的なら MERGE(WHEN MATCHED / NOT MATCHED の分岐。PostgreSQL 17 からは RETURNING も対応)です。Web アプリケーションの日常の upsert で MERGE を使う理由はほとんどありません。MERGE は ON CONFLICT と違って同時挿入の競争でユニーク違反エラーが出ることがあり、リトライのロジックなしでは upsert の代わりになりません。

適用の順序 #

  1. upsert 対象のカラムにユニーク制約(またはユニークインデックス)があるか確認します。なければ作るのが先です。
  2. 冪等な挿入(重複無視)なら DO NOTHING、更新まで必要なら DO UPDATE + EXCLUDED で書きます。衝突対象は明示します。
  3. 大量の反復バッチなら IS DISTINCT FROM の条件で無変更の更新を濾して dead tuple を減らします。
  4. 条件別の挿入・更新・削除が混ざった同期作業だけ MERGE を検討します。

まとめ #

  • upsert は SELECT 後の分岐ではなく INSERT … ON CONFLICT の 1 文です。競合状態を DB が原子的に塞ぎます。
  • 重複無視は DO NOTHING、更新は DO UPDATE + EXCLUDED です。DO UPDATE の WHERE で条件付き更新までできます。
  • 衝突対象の明示が定石です。他のユニーク制約の違反まで黙って飲み込まなくなります。
  • DO UPDATE は同じ値でも新バージョンを書き、シーケンスは衝突しても消費されます。どちらも正常な動作です。
  • 同時実行に安全な upsert は ON CONFLICT、条件分岐型のバッチ同期は MERGE です。日常の upsert に MERGE は答えではありません。
X