Postgresql で 巨大テーブルにチェック制約を追加するときの注意

Postgresql (試しているバージョン：9.6) のテーブルにチェック制約をつける場合。単純に

ALTER TABLE some_table ADD CONSTRAINT some_table_check CHECK ( performed_at >='2017-01-01' ) ;

と実行すると、全データ走査して制約の検査を行います。ALTER TABLE は "ACCESS EXCLUSIVE"という非常に強力なロックを取得するため、実行中はSELECT もできなくなります。データ数が多いテーブルに制約を追加する場合、これはつらい。

このような場合は、まず検査なしで制約を作成します。

ALTER TABLE some_table ADD CONSTRAINT some_table_check CHECK ( performed_at >='2017-01-01' )  NOT VALID;

VALIDATE しないので、処理はすぐに終わります。NOT VALID で制約追加した場合、insert や update で制約に違反するデータを登録することはできなくなります。ただ、検査が終了しない限り、例えばパーティションテーブルへのクエリ実行計画にこの制約が考慮されることはありません。なので、NOT VALID 後に、検査をします。

ALTER TABLE some_table VALIDATE CONSTRAINT some_table_check;

CHECK 制約のVALIDATE CONSTRAINT が取得するのは SHARE UPDATE EXCLUSIVE ロックのみなので、検査中もSELECT /INSERT 可能。

参考：
ADD CONSTRAINT : ALTER TABLE
ロックレベル : https://www.postgresql.jp/document/9.6/html/explicit-locking.html