Postgres外部キー追加でアプリが固まる時の対処法。NOT VALID運用の判断基準

Claude CodeなどAIコーディングエージェントにデータベースのマイグレーションを書かせているチームに向けた内容です。生成されたSQLがそのまま本番障害につながるケースを、具体的な判断基準とともに整理しました。

よくあるのが「注文テーブルに顧客IDの外部キー制約を追加して」と頼んだ結果、デプロイ中に書き込みが一斉に止まる事象です。生成されたSQLは一見教科書通りの ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers (id); でも、本番の大きなテーブルでは致命的な停止を引き起こします。

なぜ1行のSQLが2つのテーブルを止めるのか

PostgreSQLの公式ドキュメント（ALTER TABLEの解説ページ）によると、ADD FOREIGN KEYは対象テーブルと参照先テーブルの両方にSHARE ROW EXCLUSIVEというロックを取得します。

このロックモードは「テーブルへの同時データ変更を防ぐ」ためのもので、INSERT・UPDATE・DELETEが使うROW EXCLUSIVEロックと競合します。つまり制約追加中は、両テーブルへの書き込みが全部キューに積まれて待たされます。

一方でSELECTはACCESS SHAREというロックしか取らず、こちらはACCESS EXCLUSIVEとしか衝突しません。ダッシュボードの読み取りは普通に動くのに、注文確定や顧客情報更新だけが詰まるという、気づきにくい障害パターンになります。

さらに厄介なのが、ロックは制約追加のスキャンが終わった瞬間ではなく、そのトランザクションがコミットされるまで保持される点です。マイグレーションツールが1ファイルを1トランザクションで実行する設計なら、そのファイル内の他の処理が終わるまでロックが残り続けます。

判断軸1: テーブルサイズと書き込み頻度

小さなテーブルや書き込みがほぼないテーブルなら、素朴なADD FOREIGN KEYでも実害は出にくいです。数万行程度で検証スキャンが一瞬で終わるなら、ロック時間も短く済みます。

一方、注文や決済のように常時書き込みが走るテーブルで、行数が数十万〜数百万に達している場合は要注意です。検証スキャンに数秒〜数十秒かかれば、その間のチェックアウト処理が全部キューに積まれます。

判断基準としては「本番相当のデータ量でEXPLAIN ANALYZEやロック時間を事前に計測したか」です。計測していないなら、安全側に倒す方が無難です。

判断軸2: NOT VALIDとVALIDATEの分離可否

PostgreSQLには外部キー制約を2段階で追加する方法があります。まずADD CONSTRAINT ... NOT VALIDで制約だけを宣言し、既存行の検証をスキップします。

この時点で新規のINSERT・UPDATEには制約がすぐ効きますが、既存データのフルスキャンは行われません。そのためSHARE ROW EXCLUSIVEロックを取る時間が極めて短く済みます。

続いてVALIDATE CONSTRAINTを別のトランザクションで実行すると、既存行の検証だけを行います。公式ドキュメントでは、このVALIDATE単独の実行は書き込みをブロックしない、より弱いロックで済むと説明されています。

結果として「制約の即時反映」と「既存データの検証」を時間的に分離でき、検証中も通常の書き込みを許容できます。マイグレーションツール側がトランザクションを2つに分けられる構成かどうかが、この選択肢を使えるかの分かれ目です。

判断軸3: ロック状況を事前に手元で再現できるか

本番で初めて気づくのではなく、ローカルや検証環境で再現してから判断するのが安全です。Docker公式のPostgresイメージがあれば、数分で検証できます。

docker run --name fk-lab -e POSTGRES_PASSWORD=lab -d postgres:18
docker exec -it fk-lab psql -U postgres

顧客10万件・注文100万件程度のダミーデータを作り、別セッションでALTER TABLE ... ADD FOREIGN KEYをBEGINのままコミットせずに止めておきます。その状態で別セッションからINSERTやUPDATEを投げると、ロック待ちで応答が返らなくなるのが確認できます。

誰が何に待たされているかは、次のクエリで確認できます。

SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocked_by, left(query, 45) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

wait_event_typeがLock、wait_eventがrelationになっているセッションが、どのPIDにブロックされているかがblocked_by列でわかります。この手順自体が、AIエージェントが生成したマイグレーションを本番適用前にレビューする際のチェック項目になります。

選択肢の比較

方式	ロック時間	書き込みへの影響	適した場面
単一ALTER TABLE ADD FOREIGN KEY	テーブルサイズに比例（スキャン込み）	検証完了まで両テーブルの書き込みが停止	小規模テーブル・低トラフィック時間帯
ADD CONSTRAINT NOT VALID → VALIDATE CONSTRAINT	NOT VALID適用は一瞬、VALIDATEは別トランザクション	VALIDATE中も通常の書き込みは継続可能	大規模テーブル・常時トラフィックのある本番環境

ケース別の推奨

ステージング環境やバッチ専用テーブルのように、短時間のロックが許容できるなら単一のADD FOREIGN KEYで十分です。わざわざ2段階に分ける複雑さを持ち込む必要はありません。

本番の注文テーブルや顧客テーブルのように、常時トランザクションが走っていて数十万行を超える規模なら、NOT VALIDでまず制約を宣言し、別トランザクションでVALIDATE CONSTRAINTを流す構成を選ぶ方が安全です。AIエージェントに依頼する際も「NOT VALIDとVALIDATEに分けて」と明示するプロンプトにしておくと、生成結果が安定します。

マイグレーションツール（RailsのActive Record、Djangoのmigrations、golang-migrateなど）が1ファイルを1トランザクションに強制する設計の場合は、NOT VALIDとVALIDATEを別ファイルに分割できるかを確認してください。

あえて見送るべき条件

メンテナンスウィンドウを確保できていて、深夜帯など書き込みがほぼ止まる時間に適用できるなら、2段階方式の複雑さを持ち込まなくても実害は出にくいです。

また、対象テーブルが数千行程度で、検証スキャンが体感できないほど速いと計測済みなら、単一のADD FOREIGN KEYのままでも問題は起きにくいです。無理に全ての外部キー追加を2段階化するのは過剰対応になります。

確認すべきこと

AIエージェントが生成したマイグレーションSQLをレビューする際は、次の3点を確認してください。

対象テーブルの行数とトラフィック量（本番相当の環境で計測したか）
ADD FOREIGN KEYが単一トランザクションか、NOT VALIDとVALIDATEに分離できるか
ローカルのDocker環境でロック待ちを再現し、pg_stat_activityで影響範囲を確認したか

PostgreSQL 18の公式ドキュメント（ALTER TABLEおよびExplicit Lockingの章）には、各ロックモードの競合関係が一覧で載っています。エージェントが出したSQLをそのまま適用する前に、ロックモードの欄だけでも目を通しておくと判断材料になります。