そのままNOT NULL制約を追加する場合の問題
既存のカラムにNOT NULL制約を追加するには、ALTER TABLE ... ALTER COLUMN ... SET NOT NULLを使用する。
ALTER TABLE big_users ALTER COLUMN email SET NOT NULL;
この方法は、テーブル全体をスキャンして既存の全行がNULLを含まないことを検証する。
検証中はテーブルに対してACCESS EXCLUSIVEロックを保持し続けるため、大規模なテーブルでは検証が終わるまでの間、参照・更新を含むすべてのアクセスがブロックされる。
実際にトランザクション内でSET NOT NULLを実行し、コミット前に別セッションからpg_locksを確認すると、AccessExclusiveLockが付与されていることが分かる。
locktype | relation | mode | granted
----------+-----------+----------------------+---------
relation | big_users | AccessExclusiveLock | t
(1 row)
このタイミングで別セッションからSELECTを実行すると、コミットされるまでブロックされる。
$ time psql -U postgres -c "SELECT count(*) FROM big_users;"
...
4.044 total
行数の多いテーブルほど検証に時間がかかり、その間サービスへの参照や更新が止まってしまう。
安全に追加する手順
NOT NULL制約と同等の検証を、CHECK制約のNOT VALID機能を使って段階的に行うと、ロックによる影響を最小限にできる。
1. NOT VALIDでCHECK制約を追加する
NOT VALIDを指定してCHECK制約を追加すると、既存行を検証せずに制約だけを定義できる。
既存行を検証しないため、ACCESS EXCLUSIVEロックは一瞬しか保持されない。
ALTER TABLE big_users ADD CONSTRAINT email_not_null CHECK (email IS NOT NULL) NOT VALID;
2. VALIDATE CONSTRAINTで検証する
VALIDATE CONSTRAINTで、追加した制約に対して既存行を検証する。
ALTER TABLE big_users VALIDATE CONSTRAINT email_not_null;
この検証中に必要なロックはSHARE UPDATE EXCLUSIVEであり、ACCESS EXCLUSIVEより弱い。
検証中に別セッションからpg_locksを確認すると、ロックモードの違いが分かる。
locktype | relation | mode | granted
----------+-----------+--------------------------+---------
relation | big_users | ShareUpdateExclusiveLock | t
(1 row)
SHARE UPDATE EXCLUSIVEは参照・更新をブロックしないため、検証中に別セッションからUPDATEを実行してもブロックされずに完了する。
$ time psql -U postgres -c "UPDATE big_users SET email = email WHERE id = 1;"
UPDATE 1
0.059 total
3. NOT NULL制約を追加する
CHECK制約の検証が完了したら、SET NOT NULLでNOT NULL制約を追加する。
ALTER TABLE big_users ALTER COLUMN email SET NOT NULL;
PostgreSQL 12以降では、対象カラムにcol IS NOT NULLを検証済みのCHECK制約が存在する場合、SET NOT NULLは改めて全行をスキャンせずにすむ。
そのためACCESS EXCLUSIVEロックはごく短時間しか保持されず、実行はほぼ一瞬で完了する。
Time: 1.039 ms
4. 不要になったCHECK制約を削除する
NOT NULL制約を追加した後は、同じ内容を検証するCHECK制約は不要になるため削除する。
ALTER TABLE big_users DROP CONSTRAINT email_not_null;
まとめ
SET NOT NULLをそのまま実行すると、既存行の検証中ACCESS EXCLUSIVEロックが保持され続け、大規模テーブルでは長時間サービスへの影響が出る。
NOT VALIDでCHECK制約を追加してからVALIDATE CONSTRAINTで検証し、最後にSET NOT NULLを実行する手順を踏むと、既存行の検証は弱いロックで行われ、NOT NULL制約自体の追加は一瞬で完了する。
本番環境で稼働中の大規模テーブルにNOT NULL制約を追加する際は、この手順を使うとよい。
同様のNOT VALIDを使った手順は、外部キー制約を追加する際にも使える。詳しくは【PostgreSQL】外部キー制約を後から安全に追加する方法
を参照。
