こんにちは、バックエンドエンジニアの永井です。今回は本番運用されているPostgreSQLのテーブルのカラムに対して、安全にNOT NULL制約を追加する流れを書いていきます。
背景
以前、バックエンドの実装していたときに「このカラムはNOT NULL」と思い込んでコードを書いてしまい、インシデントを起こしてしまったことがありました。また、そのカラム以外にも意味的には必須なのに、NULL許容のままになっているカラムがいくつか見つかりました。テーブルのスキーマ定義をちゃんと確認することも大切ですが、NOT NULL 制約をきちんと設定することで、「NOT NULLだと思って書いたら実はNULL許容だった」というような事故を少しは減らせると思っています。
そこで、NOT NULLにできるカラムがあればNOT NULLに変更する対応を進めています。今回はその中で学べたことを書いていこうと思います。
前提
今回書いている内容はPostgreSQLが前提となっています。
また、分かりやすいように例としてtodoテーブルで考えていきます。
- id
- name
- completed_at
- このカラムがNULL許容であり、今回NOT NULL制約を追加していくカラムとします
- created_at
NOT VALIDやVALIDATEを使用せず、NOT NULL制約を追加する場合
とりあえず、何も考えずにSQLを書くと以下のようになると思います。
ALTER TABLE todo ALTER COLUMN completed_at SET NOT NULLしかし、これではtodoテーブルにACCESS EXCLUSIVEロックを取ります。そして、NOT NULL制約を追加する時にテーブル全体に走査が起きるため、テーブルが大きければ大きいほど実行時間が長くなります。そうなると、その間はUPDATE, INSERTはもちろん、SELECTまでもACCESS EXCLUSIVEロックと競合して実行できないことになります。もし、todoテーブルがアクセス頻度が高かった場合、ロック待ちが大量になった結果として高負荷になり、ユーザー影響が出てインシデントに繋がる可能性があります。
そこで、NOT VALIDとVALIDATEオプションを使うと本番運用されているテーブルでも安全にNOT NULL制約を追加することができます。
NOT VALIDとVALIDATEを使う
先ほど書いたように、NOT NULL制約を付与する時にALTER TABLEを実行するとACCESS EXCLUSIVEロックが取得されます。つまり、先ほど書いた通り大規模テーブルでアクセス頻度がそれなりにあるものだと、ユーザーの操作が完了できずインシデントになる可能性があります。そこで、NOT VALIDとVALIDATEを使うと、ALTER TABLEでテーブル全体に走査が実行される時にSHARE UPDATE EXCLUSIVEロックまでロックレベルを下げてくれます。
SHARE UPDATE EXCLUSIVEロック
このロックはUPDATE/DELETE、INSERT、SELECTも競合しないロックレベルです。基本的にはユーザー操作に影響が出ないということです。ロックについては以下の表で見ると分かりやすくて、競合するロックが減っていることが分かります。
（以下はテーブル単位のロックの競合に関する表です）
なぜSHARE UPDATE EXCLUSIVEロックで済むのか
NOT VALIDオプションを付けてCHECK制約を追加すると、それ以降に新しく追加・更新されるときだけCHECK制約が実行されます。また、ALTER TABLE ~ NOT VALIDはACCESS EXCLUSIVEロックを取りますが、既存のレコードに対して検証を実行しません。なので、テーブル走査が起きずにすぐ実行完了になるため、基本的に問題にならないです。
これにより、ALTER TABLE ~ VALIDATEを実行する時に並行して実行されるクエリは必ずNOT NULLだと保証されます。よって、既存のレコードに対してCHECK制約を実行（走査）するだけで良くなり、並行して実行されるUPDATE/DELETEやINSERTと競合させる必要がなくなるため、SHARE UPDATE EXCLUSIVEロックで済みます。
先ほどのロックの競合の表からも分かりますが、UPDATE/DELETEのような更新で取得されるROW EXCLUSIVEロックはSHARE UPDATE EXCLUSIVEロックと競合しません。なので、ユーザー操作に影響が出ることは基本的になくなります。
NOT NULL制約を追加するまでの流れ
ALTER TABLE ~ NOT VALIDの実行
NOT VALIDオプションを使い、既存データを検証せずCHECK制約を追加します。
ALTER TABLE todo ADD CONSTRAINT completed_at_not_null CHECK (completed_at IS NOT NULL) NOT VALID;
ALTER TABLE ~ VALIDATEの実行
次に、VALIDATEオプションを使って既存データの検証を行います。ここで、大規模テーブルだとテーブル走査があるため時間がかかります。しかし、ロックレベルはSHARE UPDATE EXCLUSIVEロックなのでINSERT、UPDATE/DELETEなどユーザー操作に影響が出ることは基本的にありません。
ALTER TABLE todo VALIDATE CONSTRAINT completed_at_not_null;ちなみに、実際に取得されるロックがSHARE UPDATE EXCLUSIVEロックかどうか確認するには、上記のALTER TABLE ~ VALIDATEを実行している間に以下のSQLを実行すると確認できます。
SELECT l.locktype, l.mode, l.granted, l.pid, c.relname
FROM pg_locks l JOIN pg_class c ON l.relation = c.oid
WHERE c.relname='todo';NOT NULL制約を追加
以上で、全ての行（これから追加される行も）がNOT NULLということが保証されています。よって、NOT NULL制約を追加するときにテーブル走査が実行されずユーザーに影響が出ずに済みます。
ALTER TABLE todo ALTER COLUMN completed_at SET NOT NULL;CHECK制約を削除
そして最後に、不要になったCHECK制約を削除します。
ALTER TABLE todo DROP CONSTRAINT completed_at_not_null;なぜCHECK制約のままではなく、NOT NULL制約にするのか？
フレームワークやDBスキーマを見るツールを導入するときに、NOT NULLという情報は型として付与していないと、CHECK制約だとNOT NULLとして判定されない可能性が出てきます。そのような場合、毎回CHECK制約を考慮するのは大変です。また、単純にNOT NULLかどうか確認する際にみんな基本的にNOT NULL制約があるか確認すると思うので、合わせておいた方が無難です。
以上の理由から、最終的にはNOT NULL制約を追加してCHECK制約は削除しています。
おまけ
自分はまだ試せていないですが、NOT VALIDとVALIDATEオプションは外部参照制約の追加にも使えます。外部参照制約の場合も同じ仕組みで、ALTER TABLE ~ NOT VALIDを実行すると、既存のデータは一旦無視して新しく追加と更新されたレコードに対して外部参照制約を実行します。そして、既存のデータでも外部参照を保証するためにALTER TABLE ~ VALIDATEを実行して、既存のデータに対して、外部参照制約を実行します。この時、新しく追加・更新されるレコードは外部参照の制約が実行されることが保証されている（ALTER TABLE ~ NOT VALIDによって）ので、SHARE UPDATE EXCLUSIVEロックが取得されます。
一応注意ですが、外部参照制約を追加する時は被参照テーブルにもロックが取得されます。ロックを取得と言っても、SHARE ROW EXCLUSIVEロックなので他のALTER TABLEなどのテーブル構造を変更するクエリでなければ、基本的にユーザー操作と競合することはないと思います。
最後に
今回NOT NULL制約を追加するタスクをやっていて、ちゃんと知識をつければ本番運用されているスキーマ変更も不必要に怖がらずに変更していけると分かりました。これからも色々学び、理解して、経験していこうと思います。
ここまで読んでいただきありがとうございました。少しでも参考になることがあれば幸いです。
We are hiring!
一緒にPR TIMESの開発を担ってくれるエンジニアはもちろん、各種ポジションで採用を行っています！
