質問の歴史
この曖昧さは、PostgreSQL 9.5 の ON CONFLICT 句を介したネイティブ UPSERT 機能の導入に伴い生じました。このリリース以前は、開発者は複雑な PL/pgSQL ループやエラーの起こりやすいアプリケーション側のロジックを使用して冪等性のある挿入を実装していました。 RETURNING 句は、UUID やシリアル ID を取得するために長らく重要でしたが、UPSERT の二重実行モデルとの相互作用、すなわち、ステートメントが INSERT または UPDATE につながる可能性があるため、どの行のバージョンが実際に返されるかについての微妙な意味のギャップを生じさせ、シニアエンジニアでさえ混乱させました。
問題
INSERT ... ON CONFLICT ... DO UPDATE ステートメントが一意制約違反に直面すると、既存の行を更新する方に切り替わります。その後、RETURNING 句はその行の最終的な永続状態を参照します。しかし、アプリケーションロジックが生成された値（例えば created_at タイムスタンプやデフォルト式、アプリケーション計算値など）に依存している場合、ステートメントは代わりに既存行の古いデータを返します。この静かな置換はキャッシュの非同期化、監査トレイルの破損、下流システムが時間的に不整合なメタデータを受け取る微妙なレースコンディションを引き起こします。
解決策
EXCLUDED 擬似テーブルは、衝突を引き起こした提案された挿入値を参照するためのウィンドウを提供します。 RETURNING 句や UPDATE セットリスト内で EXCLUDED.column_name を明示的に参照することにより、どの実行パスが選択された場合でも意図された新しいデータにアクセスできることが保証されます。
INSERT INTO user_sessions (user_id, login_count, last_seen, session_token) VALUES (1001, 1, NOW(), gen_random_uuid()) ON CONFLICT (user_id) DO UPDATE SET login_count = user_sessions.login_count + 1, last_seen = EXCLUDED.last_seen, session_token = EXCLUDED.session_token RETURNING session_id, user_id, CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS operation_type, session_token, EXCLUDED.last_seen AS intended_timestamp;
このパターンにおいて、EXCLUDED.last_seen と EXCLUDED.session_token は、アプリケーションが挿入しようとした新しい値を受け取ることを保証し、たとえデータベースがアップデートを実行した場合でも同様です。
同時ロイヤリティポイント蓄積
高頻度のマイクロトランザクションを処理するフィンテックプラットフォームは、ファントム報酬計算の問題に直面しました。二つの並行するリクエストが同時に同じユーザーアカウントにポイントを付与しようとしたとき、PostgreSQL データベースは正しく原子性を維持しましたが、Redis キャッシュ層は RETURNING 句から古い updated_at タイムスタンプを受け取りました。これによりキャッシュは正当なポイント増加を古いものとして拒否し、収益の漏洩や欠落した報酬に関する顧客の不満を引き起こしました。
解決策 A: Redisによる分散ロック
エンジニアチームは最初に、データベーストランザクションを実行する前に Redis で分散ロックを取得することを提案しました。このアプローチは衝突する操作を直列化し、逐次的な整合性を保証しますが、単一障害点を導入し、リクエストごとに12〜18msのネットワーク待ち時間を追加し、ロックを取得した後にトランザクションが中止された際に複雑なデッドロックシナリオを生み出しました。ロック管理の運用上のオーバーヘッド及び連鎖的な障害の可能性により、このアーキテクチャはスケールで持続不可能になりました。
解決策 B: アプリケーション側の読み取り‐修正‐書き込み
別の提案として、最初にSELECTでレコードの存在を確認し、その後アプリケーションコードで INSERT または UPDATE を選択するというものでした。概念的にはシンプルであるこのパターンは、READ COMMITTED 隔離の下で致命的に失敗します。チェックと書き込みの間で再読み込みを許可するためです。レースコンディションを防ぐために SERIALIZABLE 隔離を実装すると、過剰な直列化失敗および再試行の嵐が発生し、明示的なテーブルロックはスループットを許容できないレベルに制限します。
解決策 C: 適切な EXCLUDED の利用
選択されたアプローチは、RETURNING 句のすべての可変値に対して EXCLUDED を利用するようにクエリを再構成しました。 EXCLUDED.points および EXCLUDED.calculated_at を参照することにより、アプリケーションは操作が新しい行を生成したか、更新を行ったかにかかわらず、一貫して挿入試行からの意図されたメタデータを受け取ることができました。
選択された解決策と結果
チームは報酬マイクロサービス全体に解決策 C を実装しました。これにより、ネットワークホップの追加や隔離レベルの妥協なしにキャッシュ不整合の問題が解消されました。ポイント蓄積の精度は99.99%に改善され、クエリの往復回数が減少したことでデータベースの CPU 使用率は35%減少し、システムは手動介入なしでブラックフライデーのトラフィックピークに対応しました。
ON CONFLICT 句内で複数のインデックスが存在する場合、PostgreSQL はどの一意インデックスを衝突検出に使用するかをどのように決定しますか？
PostgreSQL は ON CONFLICT 句に明示的な仲裁者の指定を必要とします。 ON CONFLICT (column_list) と記述すると、プランナーは提供されたリストに正確に一致するカラムの一意インデックスを選択します。同一カラムに複数のインデックスが存在する場合、最初に作成されたものが選択されます。部分的な一意インデックス（WHERE 句のあるもの）や式インデックスの場合は、 ON CONFLICT ON CONSTRAINT constraint_name 構文を使用する必要があり、それ以外の場合、エンジンは仲裁インデックスを推測できないというエラーをスローします。候補者の中には、データベースが自動的に「最も選択的な」インデックスを選択すると仮定したり、関数型インデックスは明示的に制約名を付ける必要があることを見落とす人もいます。
同一キーの複数のトランザクションが衝突する場合、READ COMMITTED 隔離の下で UPSERT ステートメントが静かに更新を失う理由は何ですか？
これは、UPDATE 句の再評価動作によって発生します。トランザクション A が行を挿入してコミットすると、トランザクション B—行ロックを待っている—は新しく見える行に対して自らの UPDATE 条件を再実行します。もし UPDATE ロジックが絶対的な代入（例：SET balance = 100）を用いる場合、 EXCLUDED を参照する相対的な算術（例：SET balance = account.balance + EXCLUDED.amount）ではなく、トランザクション B はトランザクション A の変更を完全に上書きします。多くの候補者は、UPSERT が自動的なマージや蓄積を意味するという誤った前提を持ち、DO UPDATE 句が冪等的な蓄積の意味を達成するためには EXCLUDED 値を明示的に処理する必要があることに気づいていません。
UPSERT が挿入を実行したかどうかを確認する xmax = 0 と xmax IS NULL の正確な違いは何か、またこの区別が HOT 更新において重要である理由は何ですか？
PostgreSQL では xmax は削除または更新トランザクションのトランザクション ID を格納します。新しく挿入された行に対して xmax は 0 で初期化され、決して NULL にはなりません。候補者の中には、挿入を検出するために xmax IS NULL を誤って確認する人が多く、これは常に偽を返します。 xmax = 0 チェックは挿入と更新を確実に識別します。この区別は、HOT（Heap Only Tuple）更新において重要であり、PostgreSQL がパフォーマンスを最適化するために行を同じページ内でインプレースで更新する際にインデックスを変更しないからです。 xmax が行が変更されたことを適切に示す一方で、0 が「以前の更新者なし」を示すことを理解し、非ゼロはバージョニングを示すため、行生成番号を計算したり、新生物と変異を区別するカスタム変更データキャプチャロジックを実装する際に論理エラーを防ぐことができます。