令和最新版: PostgreSQLの安全なSET NOT NULL

データベースのスキーマを変更するときは、スキーマの変更作業によってテーブルが長期間ロックされてしまわないように注意が必要です。

2019年にリリースされたPostgreSQL 12.0以降では、NOT NULLを安全に追加するためによりよいベストプラクティスができています。まだ知らない人もいるかもしれないので、ここで紹介します。

何が問題なのか?

次のようなDDLコマンドを考えます。

-- posts.moderatedをNULL禁止にする
ALTER TABLE posts
  ALTER COLUMN moderated SET NOT NULL;

これはテーブルをACCESS EXCLUSIVEでロックしたままフルテーブルスキャンを行います。その間は他のトランザクションはこのテーブルに関する処理を進行できません。

テーブルが小さければこれで特に問題ありません。しかし、postsがそれなりに大きい場合は、これによりフルテーブルスキャンにかかる時間に応じたサービス一時停止が発生する可能性があります。これはテーブルサイズや求められているサービス品質次第では許容できない可能性があります。

どうすればいいのか?

NOT VALIDなCHECK制約を経由してNOT NULL制約を足します。つまり、以下の4ステップを用います。

column IS NOT NULL を検査するCHECK制約を、NOT VALIDオプションつきで足す。
上で足したCHECK制約をVALIDATEする。
NOT NULL制約を足す。
CHECK制約は不要になるので、削除する。

-- posts.moderatedをNULL禁止にする

-- (1) moderatedがNOT NULLであるという式をCHECK制約として入れる
-- この時点では既存の行の正当性はチェックされない
ALTER TABLE posts
  ADD CONSTRAINT posts_moderated_not_null
    CHECK (moderated IS NOT NULL) NOT VALID;

-- (2) CHECK制約を既存の行にも適用する
ALTER TABLE posts
  VALIDATE CONSTRAINT posts_moderated_not_null;

-- (3) NOT NULL制約を足す (意味的には先ほど足したCHECK制約と同じ)
ALTER TABLE posts
  ALTER COLUMN moderated SET NOT NULL;

-- (4) CHECK制約は不要になるので消す
ALTER TABLE posts
  DROP CONSTRAINT posts_moderated_not_null;

なぜこれで上手くいくのか

DDLのテーブルロック

PostgreSQLは並行性制御のためにトランザクションの隔離 (楽観的制御)とロック (悲観的制御)を併用します。さらに、ロックとして主にテーブルロックと行ロックが使われます。

DDLの分離には主にテーブルロックが使われています。原則として、DDLはACCESS EXCLUSIVEロックを取得します (source)。

An `ACCESS EXCLUSIVE` lock is acquired unless explicitly noted.

ACCESS EXCLUSIVEは最も強いロックで、テーブルは他の処理をほぼ全て受け付けなくなります。

ただし、CREATE TABLEなど多くの処理はテーブルメタデータの操作だけを行うため、操作が一瞬で完了します。そのため、強いロックを取ってもサービスの動作を止めることはあまりありません。

※ 長いトランザクションが頻繁に要求されるようなワークロードでは、このようなケースでもサービスの一時停止につながる可能性があります。

NOT NULL検査

既存のテーブルに制約をつける場合、基本的には既存のデータが制約を満たしているかを調べる必要があり、フルテーブルスキャンが必要になります。これはNOT NULLにも当てはまります。 (source)

Ordinarily this is checked during the `ALTER TABLE` by scanning the entire table;

この時間もACCESS EXCLUSIVEロックは保持されたままになるため、大きなテーブルでは他のトランザクションへの影響が無視できなくなります。

しかし、PostgreSQL 12.0以降では、この条件に例外が規定されています。 (source)

Ordinarily this is checked during the `ALTER TABLE` by scanning the entire table; however, if a valid `CHECK` constraint is found which proves no `NULL` can exist, then the table scan is skipped.

既存の(validate済みの)CHECK制約から column is NOT NULL が導ける場合は、スキャンを行いません。この例外条件は、低コストでCHECK制約をNOT NULLに転換できるということを意味しています。

CHECK制約の検査

しかしCHECK制約自体も、通常であればフルテーブルスキャンが必要になります。

しかし、PostgreSQLでは一部のテーブル制約 (CHECK制約と外部キー制約) に対してNOT VALIDというオプションを付与できます。

This form adds a new constraint to a table using the same constraint syntax as `CREATE TABLE`, plus the option `NOT VALID`, which is currently only allowed for foreign key and `CHECK` constraints.

NOT VALIDがついた制約は以下のように扱われます。

新しくINSERT/UPDATEされる行では制約がチェックされる。
制約の導入時に既に存在している行は、その場ではチェックされない (source)。そのため、この時点では読み取った行が制約を満たしているという保証はない。

これにより、CHECK制約を追加する ADD CONSTRAINT コマンドは短い時間で終わらせることができます。

追加した制約は不完全な状態のため、続くトランザクションでVALIDATE CONSTRAINT コマンドを発行する必要があります。

## VALIDATE CONSTRAINT

This form validates a foreign key or check constraint that was previously created as `NOT VALID`, by scanning the table to ensure there are no rows for which the constraint is not satisfied. Nothing happens if the constraint is already marked valid. (See Notes below for an explanation of the usefulness of this command.)

This command acquires a `SHARE UPDATE EXCLUSIVE` lock.

VALIDATE CONSTRAINTは後回しにした制約チェックを実行する処理のため、ここでは結局フルテーブルスキャンが必要になります。ポイントは、この処理がより弱いテーブルロックのもとで実行できるということです (source)。

テーブルロックの排他表にもとづくと、SHARE UPDATE EXCLUSIVEロックは以下のロックと共存できます。なお、名前にROWとついているため紛らわしいですが、これらもテーブル単位のロックです。

ACCESS SHARE
ROW SHARE
ROW EXCLUSIVE

基本的なDML (SELECT, SELECT FOR UPDATE, SELECT FOR SHARE, INSERT, UPDATE, DELETE) が取るロックはこれに含まれるため、基本的にSHARE UPDATE EXCLUSIVEはサービスの一時停止をもたらさないと考えられます。

このようなルールで問題ないのは、このCHECK制約が他の進行中のトランザクションの書き込みに対して適用されることがこの時点ですでに保証されているためです。そのため、他のトランザクションとの前後関係とは関係なく、CHECK制約追加前の行が全て検査されることさえ保証できればよいという寸法です。

それ以外のよくあるケース

型の変更

内部表現を変えずに行えるいくつかのケースでは、型の変更はノーコストです。たとえば VARCHAR(n) から TEXT へはノーコストで変更できたはずですが、実際に実行するときはよく調査してから実行してください。

As an exception, when changing the type of an existing column, if the `USING` clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed;

binary coercibleかどうかはPostgreSQLのマスターテーブル内を見ることで確認できます。また、この次に書いてあるように、インデックスなどが張られている場合は注意が必要です。

デフォルト値

デフォルト値だけを変更するときは時間はかかりません。

デフォルト値のあるカラムを追加する場合は、既存の行に対する値をそのデフォルト値で埋める必要があります。例外として、PostgreSQL 11.0以降でデフォルト値が定数値の場合は、「既存の行に対するデフォルト値」をあらわすためのメタデータを用意することで書き込み処理をスキップできます。

CHECK制約 / 外部キー制約

NOT VALIDを経由することで安全に追加できます。

インデックスとユニーク制約

CREATE INDEXのCONCURRENTLYオプションを使うことで、SHAREロックのかわりにより弱いSHARE UPDATE EXCLUSIVEロックを用いたインデクシングを行うことができます。

プライマリキー

プライマリキーを足す必要がある (つまり、プライマリキーがなぜか存在しない) 不幸な事情がたまにはあるかもしれません。

この場合もADD CONSTRAINT PRIMARY KEY USING INDEXで既存のインデックスを指定できるため、CREATE INDEX CONCURRENTLYと組み合わせて安全にプライマリキーを設定できます。

安全なマイグレーションを強制する

そもそも、安全でないマイグレーションを機械的に検出し、抑制するのが望ましいと考えられます。

アプリケーション側で対策する方法として、たとえばActiveRecordの場合はstrong_migrationsなどのライブラリがあります。

今回紹介したPostgreSQL 12の変更点も、strong_migrationsのchangelogを読んでいたときに発見しました。

まとめ

DDLはしばしばテーブルを長い期間ロックしてしまい、サービスの一時停止に繋がってしまうため注意が必要である。
特に、SET NOT NULLがテーブルをロックする問題についてはこれまで解決策が存在しなかったが、PostgreSQL 12.0以降ではCHECK制約を経由することで安全なSET NOT NULLが可能になった。