概要
原著者の許諾を得て翻訳・公開いたします。
- 英語記事: Adding a NOT NULL CONSTRAINT on PG Faster with Minimal Locking
- 原文公開日: 2017/12/05
- 著者: Christophe Escobar
画像は元記事からの引用です。
PostgreSQLのNOT NULL制約のロックを最小化して高速化する（翻訳）
Doctolibは医師や患者向けに年中無休で運営されるWebサービスです。あらゆる医師のアジェンダを取り扱うという任務を完了したので、現在の重要な課題はダウンタイムゼロです。医師が自分たちのアジェンダやイベント通知にいついかなるときでもアクセスできるようにしなければなりません。サービスとして、新機能リリースのためにときおりデータベーススキーマのマイグレーションを行っています。このマイグレーションはリスクを伴うことがあり、サービス中断を何としても避けるために注意深く行わなければなりません。
マイグレーションは、データベーステーブルの変更を大規模に実施する場合は特別に慎重になります。Doctolibでは数日おきにデータのマイグレーションを行っていますが、残念なことにテーブルのデータ総量が著しく大きい（3000万行以上）ため、標準的なマイグレーションのベストプラクティスではサービスのダウンタイムを十分防止できないことがあります。私たちは現在危険な操作を防止するツールを使っていますが、マイグレーションによっては追加の安全策が必要になることもあります。
データベース制約の利用はデータの破損防止のために重要ですが、データベース制約の追加操作を誤るとテーブルをロックする危険があります。本記事でこの後説明するように、私たちはこの問題を別の角度から検討することにしました。PostgreSQLのおかげで、新しいカラムにNOT NULL制約を追加して操作中の読み書きを一切ロックせずに巨大なテーブルをマイグレーションすることができます。
厄介なマイグレーションをPostgreSQLで行う
より安全性の高いマイグレーションの必要性に気づかせてくれた、この事例についてもう少し詳しく説明します。この事例では、行数が3000万あるテーブルで、あるカラムにNOT NULL制約を追加します。データ自体はさほど巨大ではありませんが、うかつなマイグレーションを行うと一部のサービスでダウンタイムが生じる可能性があります。
カラムにNOT NULL制約を追加しようとする場合は、PostgreSQLで次のようにアトミックな操作として実行します。
ALTER TABLE テーブル名 ALTER COLUMN カラム名 SET NOT NULL;
これによってPostgreSQLでは次が行われます。
- テーブルをフルスキャンし、すべての行で制約が有効かどうかをチェックする
ACCESS EXCLUSIVE LOCKを取得（書き込みをテーブル全体でロックする）（https://www.postgresql.org/docs/current/static/explicit-locking.html)など）
この操作は、次の場合にリスクを伴います。
- そのテーブルを意図的に変更する場合
- フルスキャンに時間がかかる場合（特に巨大なテーブルを扱う場合）
これによって、アプリのワーカーが待ち状態になってアプリがフリーズし、問題がたちまち広がって操作中にサービス全体が停止する可能性があります。
staging環境で試してみたところ、3000万行で操作に1.7秒を要しました。
もしこのテーブルに1秒間に100回書き込みを行っていたら、操作中に100を超えるデータベース接続がロックされたでしょう。
標準的な解決方法: とにかくやる
こういう状況についてよくある意見は、カラムにNOT NULL制約を追加し、慎重に作業を進めるためにサービスの利用頻度が最も低い適切な時間を選んでマイグレーション計画を立て、コストの大きいこのマイグレーション中はメンテナンスモードに切り替えればよいのでは、というものです。
私たちがこのアドバイスを採用したくない理由がおわかりでしょうか。
マイグレーション対象であるこのテーブルは、アプリの中でも重要な部分であり、医師や患者からのトラフィックが少ない夜中を選んでマイグレーションを決行する以外の選択肢がなくなってしまいます。残念ながら、夜勤中の医師にとってはこれでも問題が生じることが示されるでしょう。Doctolibユーザーに不便を強いるあらゆるリスクも回避できる、もっと賢い方法でなければなりません。
PostgreSQLのCHECK CONSTRAINTで切り抜ける
PostgreSQLのドキュメントを見てみましょう。
NOT-NULL制約は、常にカラムの制約として記述される。NOT-NULL制約は、チェック制約
CHECK (カラム名 IS NOT NULL)を作成することと機能上同等である。
要するにCHECK CONSTRAINTはある意味でカラム制約と似ているのですが、これはそのテーブルに属しています。たとえば、priceカラムの値は常に100を超えていなければならないというCHECK CONSTRAINTは次のようになります。
CREATE TABLE products (
product_no integer,
name text,
price numeric,
CONSTRAINT check_price_value CHECK (price > 100)
);
ここで注目したいのは、CHECK CONSTRAINTは値の非NULLを強制できる点です。
そしてここでのトリックは、チェック制約を追加するときにNOT VALIDオプションを発行できることです。このオプションを指定すると、「この制約は既存データについては有効ではない可能性があるので（既存データの）チェックは必ずしも必要なわけではない」とあなたが認識していることがPostgreSQLに伝わります。ただし、以後のINSERTやUPDATEでは制約が強制されます。
本質的にこのオプションは、テーブルで行われる可能性がある仰々しい初期チェックを行わなくなります。この操作を行っても従来どおりEXCLUSIVE LOCKが取得されてテーブルへの書き込みは差し止められますが、すべての行に対するバリデーションを行わなくなるので非常に高速です（私たちのデモ環境では6ms）。
これこそ探していたものです！
この機能はどのように使えばよいのでしょうか？
1. CHECK CONSTRAINTを追加する。NOT VALIDの定義を忘れないこと！
ALTER TABLE テーブル名 ADD CONSTRAINT 制約名 CHECK (カラム名 IS NOT NULL) NOT VALID;
2. 制約のバリデーションは別のステートメントでPostgreSQLに指示するだけでよい
ALTER TABLE テーブル名 VALIDATE CONSTRAINT 制約名;
このVALIDATEコマンドは以下を実行します。
- テーブルのフルスキャン
SHARE UPDATE EXCLUSIVEロック（他のALTER TABLEコマンドなどと同様、スキーマ変更だけをロック）を取得。このテーブルへの読み書きは引き続き可能。- PostgreSQLは、新しいデータについては（制約が）既に強制されていることを前提とするので、テーブル上の既存データをチェックして制約が有効であることを確認する。したがって、書き込みロックは完全に不要になる。
カラムのNOT NULL制約とCHECK CONSTRAINTのnot nullの違い
最終的な結果は同じになることもありますが、それでもいくつかの違いがあります。
- チェック制約は名前が必要であり、テーブルに属する必要があります。
NOT NULLカラムは後者のオプションのひとつに過ぎません。 - パフォーマンスの観点から、PostgreSQLドキュメントには次のように書かれています。
PostgreSQLにおいては、明示的にnot-null制約を作成する方が効率が高い。
Stackexchangeのベンチマークによると、書き込み時のパフォーマンスで0.5%以上のペナルティが生じます。私たちのテストでは1%程度だったので私たちの事例では無視できますが、状況によってはパラメータで懸念が生じることがあるかもしれません。
- 関連付けられたカラムを削除する前にはチェック制約を削除しなければならない
NOT NULLはpsqlで\d your_tableを発行するときにカラム名に続けて書くが、チェック制約は特定のセッション下で記述する
いずれの場合であっても、デフォルト値を用いてテーブルにすべてのデータを埋め戻すことをお忘れなく ;-)
新しい標準？
この種のマイグレーションは、サービス全体で用いられるコアのテーブルを変更する場合や、大量のデータが頻繁に変更される場合に非常にトリッキーになることがあります。幸いなことに、コアテーブルの変更の必要性は時間とともに減少します。
この特定のマイグレーションは、NOT NULL CONSTRAINTSをコアテーブルに追加するような状況で使ったことがありません。このマイグレーションは1回成功していますが、まだDoctolibの標準として定めていません。何より、このソリューションは私たちの「驚き最小の法則」の哲学に沿っていません。私たちの事例では容認できますが、今後もそうとは限りません。再びこの状況に直面するときがきたら、私たちのニーズに合ったソリューションを再度検討しなければならないでしょう。
追伸: パリで私たちのチームに参加いただけるPostgreSQLラブな開発者を募集しています。www.doctolib.fr/jobs/engineeringまでどうぞ。