そのままCHECK制約を追加する場合の問題
既存のテーブルにCHECK制約を追加するには、ALTER TABLE ... ADD CONSTRAINT ... CHECKを使用する。
ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price > 0);
この方法は、テーブル全体をスキャンして既存の全行が条件を満たすことを検証する。
検証中はテーブルに対してACCESS EXCLUSIVEロックを保持し続けるため、大規模なテーブルでは検証が終わるまでの間、参照・更新を含むすべてのアクセスがブロックされる。
実際にトランザクション内で制約を追加し、コミット前に別セッションからpg_locksを確認すると、AccessExclusiveLockが付与されていることが分かる。
relation | mode | granted
----------+---------------------+---------
products | AccessExclusiveLock | t
(1 row)
このタイミングで別セッションからSELECTを実行すると、コミットされるまでブロックされる。
$ time psql -U postgres -c "SELECT count(*) FROM products;"
...
4.304 total
行数の多いテーブルほど検証に時間がかかり、その間サービスへの参照や更新が止まってしまう。
安全に追加する手順
NOT NULL制約や外部キー制約と同様、CHECK制約もNOT VALID機能を使って段階的に追加すると、ロックによる影響を最小限にできる。
1. NOT VALIDでCHECK制約を追加する
NOT VALIDを指定すると、既存行を検証せずに制約だけを定義できる。
既存行を検証しないため、ACCESS EXCLUSIVEロックは一瞬しか保持されない。
ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price > 0) NOT VALID;
Time: 1.021 ms
制約はこの時点ですでに有効であり、以降に追加・更新される行には即座に適用される。
未検証なのは、NOT VALID追加より前から存在する既存行のみである。
2. VALIDATE CONSTRAINTで検証する
VALIDATE CONSTRAINTで、追加した制約に対して既存行を検証する。
ALTER TABLE products VALIDATE CONSTRAINT price_positive;
この検証に必要なロックはSHARE UPDATE EXCLUSIVEであり、ACCESS EXCLUSIVEより弱い。
検証中に別セッションからpg_locksを確認すると、ロックモードの違いが分かる。
relation | mode | granted
----------+--------------------------+---------
products | ShareUpdateExclusiveLock | t
(1 row)
SHARE UPDATE EXCLUSIVEは参照・更新をブロックしないため、検証中に別セッションからSELECTやINSERTを実行してもブロックされずに完了する。
$ time psql -U postgres -c "SELECT count(*) FROM products;"
0.118 total
$ time psql -U postgres -c "INSERT INTO products (price) VALUES (200);"
INSERT 0 1
0.171 total
検証が完了すると、制約は完全に有効な状態になる。
SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'products'::regclass;
conname | convalidated
----------------+--------------
products_pkey | t
price_positive | t
(2 rows)
まとめ
CHECK制約をそのまま追加すると、既存行の検証中ACCESS EXCLUSIVEロックが保持され続け、大規模テーブルでは長時間SELECTを含むすべてのアクセスがブロックされる。
NOT VALIDで制約を追加してからVALIDATE CONSTRAINTで検証する手順を踏むと、制約自体の追加は一瞬で完了し、既存行の検証も弱いロックで行われるため参照・更新をブロックしない。
本番環境で稼働中の大規模テーブルにCHECK制約を追加する際は、この手順を使うとよい。
