質問
本番環境の PostgreSQL テーブルに新しい CHECK または外部キー制約が必要です。既存の行がそれに違反している可能性があり、ビジネス上、長時間の書き込みブロックを許容できません。違反の検出、NOT VALID 制約の追加、過去データの修復、そして VALIDATE CONSTRAINT の実行に至るフローを設計してください。ロック、並行性、モニタリング、および障害処理を網羅してください。
面接官がテストしていること
- 制約の追加時と後の検証スキャンにおけるロックの挙動の違いを区別できているか。
NOT VALIDが古い行のスキャンをスキップしつつ、新規の INSERT や UPDATE に対してはチェックを行うことを知っているか。- 1つのブロッキング DDL を実行するのではなく、修復、検証、およびアプリケーションのロールアウトを協調して進められるか。
- 検証の失敗、長時間トランザクション、ロールバックを通じて、制約の状態が可視化され復旧可能に保たれているか。
模範解答
違反している行の推定、インデックスの可用性、長時間トランザクションを確認するための読み取り専用クエリから開始します。制御されたメンテナンスウィンドウ中に ADD CONSTRAINT ... NOT VALID を実行します。これは既存の行をスキャンしませんが、その後の INSERT や UPDATE は直ちにチェックします。過去の行は依然としてルールに違反している可能性があるため、ステータスは明示的に検証待ち（pending validation）となります。
コミット境界と進捗記録を持つ制限されたバッチ単位で過去の行を修復します。修復ロジックはアプリケーションのルールと一致している必要があるため、必要に応じて互換性のあるコードを先にデプロイします。その後、ロック待ち、スキャン時間、データベース負荷を監視しながら VALIDATE CONSTRAINT を実行します。検証が成功するとカタログ内の制約が有効（valid）とマークされ、移行が完了します。
外部キーの場合は、参照先カラムに適切な一意性制約があることを確認し、並行する書き込みや削除を評価します。検証が失敗した場合は、新しい書き込みを保護するために NOT VALID 制約を維持したまま、残りの行を修復して再試行します。要件自体が真に撤回された場合にのみ制約を削除（DROP）します。
移行フロー
-- 1. Record violations and create repair work
SELECT count(*) FROM orders WHERE total < 0;
-- 2. Add the constraint without scanning historical rows
ALTER TABLE orders
ADD CONSTRAINT orders_total_nonnegative
CHECK (total >= 0) NOT VALID;
-- 3. Repair in batches, then validate
ALTER TABLE orders
VALIDATE CONSTRAINT orders_total_nonnegative;移行ツールには、制約名、バッチカーソル、開始時刻と終了時刻、検証結果、および実行者を記録する必要があります。ロールアウト前に、修復ジョブとビジネスロジックが互いに上書きし合わないよう、アプリケーションのすべての書き込みパスを同じルールに照らして確認します。
NOT VALID の価値は、負荷の高い過去データスキャンを制約追加ステップから分離することにあります。検証では依然としてテーブルをスキャンし、ドキュメントに記載されたロックを取得するため、負荷がゼロになるわけではありません。長時間トランザクションとレプリケーション遅延を確認し、ステートメントタイムアウトとロック待ちアラートを設定した上で、制御されたウィンドウ内で検証を実行します。
検証中も新規トランザクションはチェックされますが、過去データの修復はビジネスによる更新を上書きしないようにする必要があります。バッチ処理には安定したインデックス範囲と短いトランザクションを使用します。FOR UPDATE SKIP LOCKED は適切であればタスクの取得に使用できますが、スキップされた行によって進捗メトリクスが見かけ上完了したように見えてはなりません。
よくある落とし穴
NOT VALIDが制約を無効化し、新規の不正な書き込みを許容すると誤認すること。- 長時間トランザクション、ロック待ち、レプリケーションのキャパシティを確認せずに
VALIDATE CONSTRAINTを実行すること。 - すべての過去行を1つの巨大なトランザクションで修復し、テーブル肥大化（bloat）、長時間のロック、困難なロールバックを引き起こすこと。
- CHECK を修正する一方で、外部キーに必要な参照インデックスと削除パスを無視すること。
- 失敗した制約を安易に DROP してしまい、新規書き込みへの保護と後の修正パスを失ってしまうこと。
障害処理とロールバック
planned、not_valid、backfilling、validating、validated、aborted などの明示的な状態を使用します。移行テーブルと監査ログにすべての状態遷移を永続化します。検証で違反が見つかった場合は、制約名とマスキングされたキーのサンプルを記録し、検証を一時停止して制約を維持します。修復後に、記録された進捗から再開します。
アプリケーションリリースのロールバックが必要になった場合でも、互換コードによって古い行と新しい制約の双方が引き続き処理される必要があります。NOT VALID 制約の削除は最終手段であり、新規の書き込みによって問題が再発しないことを確認する必要があります。いかなる DROP CONSTRAINT も、承認、バックアップ、および再追加計画を伴う必要があります。
可観測性（オブザーバビリティ）
違反件数、修復レート、推定残り時間、検証の進捗、ロック待ち、最も古いトランザクションの経過時間、WAL の増加量、およびレプリケーション遅延を監視します。「新規書き込みが制約に違反した」と「過去データが未検証である」を区別します。これらには異なる対応優先度が求められます。
検証後、pg_constraint.convalidated と制約名をクエリしてカタログの状態を確認します。結果、クエリプラン、負荷ウィンドウを移行レコードに保存します。外部レポートではマスキングされたキーと集計値を使用し、ログにビジネスデータを含めてはなりません。
- PostgreSQL 17
ALTER TABLE：NOT VALIDおよびVALIDATE CONSTRAINTのロックおよび並行性セマンティクス。 - 最新の PostgreSQL 制約ドキュメント：CHECK、外部キー、および過去データの検証ルール。
- PostgreSQL 17
pg_constraint：convalidatedなどのカタログフィールド。
フォローアップの質問
なぜ古い行が無効なままであっても、新しい書き込みはチェックされるのですか？
NOT VALID は、制約が追加された時点で既に存在する行のスキャンをスキップします。定義自体はその後の INSERT および UPDATE 操作に直ちに適用されるため、過去のバックログがさらに拡大するのを防ぎます。
検証中に書き込みを継続できますか？
継続できますが、検証はテーブルを読み取り、ドキュメントに記載されたロックを取得するため、待ち時間と負荷を制御する必要があります。新しい行はチェックされ、修復バッチはアプリケーションの更新を上書きしないようにする必要があります。
検証にかかる時間はどのように見積もりますか？
テーブルサイズ、スキャン計画、キャッシュの挙動、並行ワークロード、およびメンテナンスウィンドウを使用して、実測による見積もりやサンプリングを行います。行数単体で線形にスケールすると仮定してはなりません。本番環境に適用する前に、タイムアウトとキャンセルのポリシーを設定してください。
外部キーの移行において特別な点は何ですか？
参照先カラムの一意性とインデックスを確認し、安定した削除セマンティクスを定義します。検証中は、既存の子行とともに、並行する削除、更新、およびロック競合に注意を払う必要があります。
制約を断念して削除（DROP）すべきなのはどのような場合ですか？
要件が撤回されたか、設計が誤っており、代替の保護手段が存在する場合のみです。検証の失敗単体は DROP する理由にはなりません。NOT VALID を維持することで、新しいデータの保護が継続され、修正パスが維持されます。