テック技報最新号へ →
コード

Postgres: 大きなテーブルへの CHECK / 外部キーの追加は NOT VALID と VALIDATE の 2 段階に分ける

ADD CONSTRAINT ... NOT VALID なら既存行の全件スキャンを飛ばして一瞬で追加でき、新しい書き込みはその時点からチェックされる。既存行の確認は後から VALIDATE CONSTRAINT で行い、こちらは読み書きを止めない弱いロックで動く。

@amitsinha__07 · 投稿 2026-10-20
#postgres#infra#migration#database#lock

どんな時に気にする？

  • 数百万行以上のテーブルに CHECK や FOREIGN KEY を足したい
  • そのまま ALTER TABLE ... ADD CONSTRAINT すると、全行を確認し終わるまで強いロックを持ち続け、その間の書き込み（CHECK なら読み取りも）が詰まる

手順

sql
-- 1) 追加だけ。既存行は見ないのですぐ終わる（新しい INSERT / UPDATE はここからチェックされる）
SET lock_timeout = '3s';
ALTER TABLE orders
  ADD CONSTRAINT orders_amount_positive CHECK (amount > 0) NOT VALID;

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

-- 2) 既存行の確認。SHARE UPDATE EXCLUSIVE ロックなので、通常の読み書きは止まらない
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_positive;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
  • 2 の前に、違反している既存行がないか SELECT ... WHERE NOT (amount > 0) などで確かめておくと、検証が途中で失敗して時間を無駄にしない。
  • 1 と 2 は別トランザクション（別のマイグレーション）に分ける。同じトランザクションにすると、1 の強いロックを持ったまま 2 の全件スキャンをすることになり、分けた意味がなくなる。

注意点

  • 1 の ADD CONSTRAINT ... NOT VALID 自体も一瞬は強いロックを取る。長いトランザクションがあるとロック待ちの行列ができるので、lock_timeout を付けて失敗したらやり直す。
  • 外部キーの VALIDATE は、参照先のテーブル（上の例では customers）にも ROW SHARE ロックを取る。
  • NOT NULL を後から付けたいときは、CHECK (col IS NOT NULL) NOT VALID → VALIDATE → SET NOT NULL の順にすると、SET NOT NULL が有効な CHECK 制約を使って全件スキャンを省ける（Postgres 12 以降）。
  • インデックス作成の同じ種類の話は PostgreSQL: 失敗した CREATE INDEX CONCURRENTLY は INVALID なインデックスを残す。

出典

X の元の投稿を開く