PostgreSQL 18からNOT NULL制約をNOT VALIDで追加できるようになりました

こんにちは！ デジスマチームの山田です。これはデジスマチームのブログリレー3日目の投稿です。

本番稼動中のデータベースの運用において、NOT NULL制約を持たせたいカラムを既存のテーブルに追加する作業は頭を悩ませるものです。PostgreSQL 11（以下、PG11）以降、DEFAULTを固定の値で指定した場合のカラム追加は高速化されました。しかしDEFAULTとして固定値を用意するのではなく、各行ごとに異なる値の非NULLなカラムを追加したいというケースもあります。このような場合「一度NULLを許可してカラムを追加し、アプリケーションの改修やUPDATEによるバックフィルを完了させた後にNOT NULL制約を追加する」という手順を踏むことがあります。この際テーブルが長時間ロックされることを避けるためにCHECK制約を介した少し複雑な手順が必要でした。
そんな中PostgreSQL 18のリリースノートを読んでいると、次の一文が目に留まりました。

Allow ALTER TABLE to set the NOT VALID attribute of NOT NULL constraints

PostgreSQL: Release Notes

ついにNOT NULL制約を追加する際のNOT VALIDがサポートされたようです。この記述から、これまでのCHECK制約を経由する手順が不要になり、運用がよりスマートになるのではないかと期待をしました。

本稿ではこの改善が既存の運用手順をどう簡素化するのか、実際に検証した内容をお届けします。

• サマリ
• 検証した環境
• 検証：PG17
  • 1. NULLを許可してカラムを追加
  • 2. 追加したカラムのバックフィル
  • 3. 非NULLを検証するCHECK制約をNOT VALIDで追加
  • 4. VALIDATE CONSTRAINT
  • 5. SET NOT NULL
  • 6. CHECK制約の削除
• 検証：PG18
  • 1. NULLを許可してカラムを追加
  • 2. 追加したカラムのバックフィル
  • 3. NOT NULL制約をNOT VALIDで追加
  • 4. VALIDATE CONSTRAINT
• 手順の比較
• この機能が実現するまで
• 終わりに
• We are Hiring!
  • エンジニア採用ページはこちら
  • カジュアル面談もお気軽にどうぞ
  • エンジニア新卒採用サイト！

サマリ

• PG18でNOT NULL制約を NOT VALIDで追加できることを確認しました
• PG17までのCHECK制約を利用した方法と比較して、手順を減らせることがわかりました
• 内部的にはpg_constraint カタログでNOT NULL制約が管理されるようになり、制約に名前がつけられるなどの運用の柔軟性が向上しています

検証した環境

検証は筆者のローカル開発環境（Docker）にて、PostgreSQL 17.8 と 18.3 を比較して行いました。各SQLには実行時間の記載もありますが、あくまで参考値としてご覧ください。また事前にusersという名前のテーブルを作成し、1,000万のデータをINSERTした状態で行っています。

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    email TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX users_email_idx ON users(email);

INSERT INTO users (name, email, created_at, updated_at)
SELECT
    'user_' || i AS name,
    'user_' || i || '@example.com' AS email,
    CURRENT_TIMESTAMP - (random() * INTERVAL '365 days') AS created_at,
    CURRENT_TIMESTAMP
FROM generate_series(1, 10000000) AS i;

今回の検証においては、上述のusersテーブルにawesome_idというUUID型のカラムを追加するシナリオを想定しています。

ALTER TABLE users ADD COLUMN awesome_id uuid;

UUID型のカラムを追加するシナリオとした理由は、VOLATILE関数であるgen_random_uuidを利用し、ALTER TABLE ... ADD COLUMN ... DEFAULT ... のようなDEFAULTを伴うカラム追加の実行時に長時間ロックを発生させたいという意図があったためです*1*2。

検証：PG17

まずPG17にて次のDDLを実行します。

ALTER TABLE users ADD COLUMN awesome_id uuid NOT NULL DEFAULT gen_random_uuid();

実行したところ約20秒を要しました。これはDEFAULT gen_random_uuid()としたことでテーブルの書き換えがおきていることが大きな要因です。

testdb=*# ALTER TABLE users ADD COLUMN awesome_id uuid NOT NULL DEFAULT gen_random_uuid();
ALTER TABLE
Time: 20773.405 ms (00:20.773)

実行中のロックを確認すると、ACCESS EXCLUSIVE ロックを保持していたことがわかります*3。

testdb=# SELECT l.pid, l.mode, l."granted", l.locktype, l.relation::regclass, sa.query FROM pg_catalog.pg_locks l JOIN pg_catalog.pg_stat_activity sa ON l.pid = sa.pid WHERE l.relation = 'users'::regclass;
-[ RECORD 1 ]------------------------------------------------------------------------------
pid      | 377980
mode     | AccessExclusiveLock
granted  | t
locktype | relation
relation | users
query    | ALTER TABLE users ADD COLUMN awesome_id uuid NOT NULL DEFAULT gen_random_uuid();

ACCESS EXCLUSIVE ロックはSELECTやINSERTコマンドとも競合するため、usersテーブルが頻繁にアクセスされるテーブルである場合このDDLは危険なものになります*4。

このリスクを軽減するため、以下の手順を踏みます。

1. NULLを許可してカラムを追加
2. 追加したカラムのバックフィル
3. 非NULLを検証するCHECK制約をNOT VALIDで追加
4. VALIDATE CONSTRAINTの実行
5. SET NOT NULLの実行
6. CHECK制約の削除

1. NULLを許可してカラムを追加

testdb=*# ALTER TABLE users ADD COLUMN awesome_id uuid;
ALTER TABLE
Time: 3.518 ms

このDDLもACCESS EXCLUSIVEロックを取得することは変わりませんが、先程と異なりテーブルデータの書き換えを伴わないため実行自体は高速に完了します。

2. 追加したカラムのバックフィル

実運用ではアプリケーションの改修と組み合わせたりしますが、今回はあくまでNOT NULL制約の検証なので、ここは一括でUPDATEすることで代替します。

testdb=*# UPDATE users SET awesome_id = gen_random_uuid();
UPDATE 10000000
Time: 153698.557 ms (02:33.699)

3. 非NULLを検証するCHECK制約をNOT VALIDで追加

1の手順と同様にACCESS EXCLUSIVE ロックを取得しますが、NOT VALIDをつけることでテーブルのスキャンを省略できます。実行後pg_constraintカタログにconvalidatedがfalseで追加されていることが確認できます。

testdb=*# ALTER TABLE users ADD CONSTRAINT awesome_id_not_null CHECK (awesome_id is not null) NOT VALID;
ALTER TABLE
Time: 4.945 ms

testdb=# SELECT con.conname, con.contype, con.convalidated FROM pg_catalog.pg_constraint con WHERE con.conrelid = 'users'::regclass;
       conname       | contype | convalidated
---------------------+---------+--------------
 users_pkey          | p       | t
 awesome_id_not_null | c       | f
(2 rows)

4. VALIDATE CONSTRAINT

NOT VALIDで作成した制約を検証します。NOT VALIDによって追加された場合でもその後のINSERTやUPDATEには制約が適用されます。そのためこの操作ではSELECTやINSERT, UPDATEをブロックしないSHARE UPDATE EXCLUSIVE ロックが取得され、稼働中のサービスへの影響を小さく実行することができます。

testdb=*# ALTER TABLE users VALIDATE CONSTRAINT awesome_id_not_null;
ALTER TABLE
Time: 616.848 ms

testdb=# SELECT l.pid, l.mode, l."granted", l.locktype, l.relation::regclass, sa.query FROM pg_catalog.pg_locks l JOIN pg_catalog.pg_stat_activity sa ON l.pid = sa.pid WHERE l.relation = 'users'::regclass;
-[ RECORD 1 ]--------------------------------------------------------
pid      | 377980
mode     | ShareUpdateExclusiveLock
granted  | t
locktype | relation
relation | users
query    | ALTER TABLE users VALIDATE CONSTRAINT awesome_id_not_null;

Time: 0.825 ms

convalidatedがtrueになりました。

testdb=# SELECT con.conname, con.contype, con.convalidated FROM pg_catalog.pg_constraint con WHERE con.conrelid = 'users'::regclass;
       conname       | contype | convalidated
---------------------+---------+--------------
 users_pkey          | p       | t
 awesome_id_not_null | c       | t
(2 rows)

5. SET NOT NULL

ここでSET NOT NULLを実行します。再びACCESS EXCLUSIVE ロックを取得しますが、PG12以降は有効なCHECK制約によってNULLが存在しないことが検証済みであればテーブルスキャンが省略されるため、高速に完了します。

testdb=*# ALTER TABLE users ALTER COLUMN awesome_id SET NOT NULL;
ALTER TABLE
Time: 3.980 ms

期待通り数msで終わり、テーブル定義からもnot nullになっていることを確認できました。

testdb=# \d users;
                              Table "public.users"
   Column   |           Type           | Collation | Nullable |      Default
------------+--------------------------+-----------+----------+-------------------
 id         | uuid                     |           | not null | gen_random_uuid()
 name       | text                     |           | not null |
 email      | text                     |           |          |
 created_at | timestamp with time zone |           |          | CURRENT_TIMESTAMP
 updated_at | timestamp with time zone |           |          | CURRENT_TIMESTAMP
 awesome_id | uuid                     |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "awesome_id_not_null" CHECK (awesome_id IS NOT NULL)

6. CHECK制約の削除

最後に不要になったCHECK制約を削除します。

ALTER TABLE users DROP CONSTRAINT IF EXISTS awesome_id_not_null;

検証：PG18

続いてPG18での検証です。

1. NULLを許可してカラムを追加

testdb=*# ALTER TABLE users ADD COLUMN awesome_id uuid;
ALTER TABLE
Time: 3.518 ms

2. 追加したカラムのバックフィル

testdb=*# UPDATE users SET awesome_id = gen_random_uuid();
UPDATE 10000000
Time: 156414.148 ms (02:36.414)

ここまではPG17と同じです。

3. NOT NULL制約をNOT VALIDで追加

ここでNOT NULL NOT VALIDを実行します。

testdb=*# ALTER TABLE users ADD CONSTRAINT awesome_id_not_null NOT NULL awesome_id NOT VALID;
ALTER TABLE
Time: 3.473 ms

ACCESS EXCLUSIVE ロックを取得しますが、NOT VALIDのおかげで高速に完了しました。pg_constraint を確認するとcontypeがn、convalidatedがfalseの行が追加されていることがわかります。他にもPG17の検証時にはなかったusers_id_not_null, users_name_not_nullがcontype nで存在することがわかります。

testdb=# SELECT con.conname, con.contype, con.convalidated FROM pg_catalog.pg_constraint con WHERE con.conrelid = 'users'::regclass;
       conname       | contype | convalidated
---------------------+---------+--------------
 users_id_not_null   | n       | t
 users_name_not_null | n       | t
 users_pkey          | p       | t
 awesome_id_not_null | n       | f
(4 rows)

4. VALIDATE CONSTRAINT

最後にNOT VALIDで作成した制約を検証します。PG17で検証したときと同様にこのDDLはSHARE UPDATE EXCLUSIVEロックを取得して実行されます。

testdb=*# ALTER TABLE users VALIDATE CONSTRAINT awesome_id_not_null;
ALTER TABLE
Time: 1396.483 ms (00:01.396)

testdb=# SELECT l.pid, l.mode, l."granted", l.locktype, l.relation::regclass, sa.query FROM pg_catalog.pg_locks l JOIN pg_catalog.pg_stat_activity sa on l.pid = sa.pid WHERE l.relation = 'users'::regclass;
-[ RECORD 1 ]--------------------------------------------------------
pid      | 351568
mode     | ShareUpdateExclusiveLock
granted  | t
locktype | relation
relation | users
query    | ALTER TABLE users VALIDATE CONSTRAINT awesome_id_not_null;

Time: 4.741 ms

PG18の手順はこれで終わりです。記載は省略しますがconvalidatedがtrueになり、テーブル定義上でnot nullとなっていることが確認できました。

手順の比較

PG17と18の手順をあらためて比較します。PG18ではCHECK制約を利用する必要がなくなったため、手順が素直になった印象があります。

ステップ	PG17	PG18
1	カラム追加（NULL許可）	カラム追加（NULL許可）
2	バックフィル	バックフィル
3	CHECK制約の追加（NOT VALID）	NOT NULL制約の追加（NOT VALID）
4	CHECK制約の検証（VALIDATE）	NOT NULL制約の検証（VALIDATE）
5	SET NOT NULLの実行	-
6	CHECK制約の削除	-

この機能が実現するまで

今回、なぜNOT NULL制約をNOT VALIDで追加できるようになったのでしょうか？ ここからは本機能に携わったコントリビューターの方のブログと、PostgreSQLのメーリングリストを遡って調べた内容を整理してみます。

Postgres 18 Changes: Rework of NOT NULL Constraints

従来、PostgreSQLのNOT NULL制約はpg_attribute カタログのattnotnullフラグで管理されてきました。そのため制約の名前や検証状態の管理といったフラグ以上の情報を持てなかったという事情があります。こうした背景からNOT NULL制約をpg_constraintカタログで管理しようとする試みは古くから繰り返されてきました。
2009年には継承における子テーブルのNOT NULL制約を削除できてしまう問題への対応を発端に、pg_constraintでの管理に向けた実装が行われていました*5。しかし影響範囲が多岐にわたることや仕様に対する議論も必要だったことから、実現には至りませんでした。2012年にはNOT NULL制約を内部的にCHECK (column_name IS NOT NULL)として変換する（逆にCHECK (column_name IS NOT NULL)を定義した場合にpg_attribute.attnotnull フラグもセットする）アプローチも検討されましたが、これもまた課題があり見送られることとなりました。その後も粘り強く実現が試みられ、2024年についにpg_constraintでの管理が実現し*6、さらにその後本稿でも紹介したNOT VALIDのサポートが実現されました*7。
pg_constraintで管理できるようになったおかげで、PG18での検証結果にもあったようにNOT NULL制約に名前がつけられるようになっています。なおattnotnullフラグはサードパーティ製アプリケーションとの互換性を考慮して維持されるとのことです。

これまでもPostgreSQLはpg_attribute.attmissingvalを利用したDEFAULT設定の高速化（PG11）や、CHECK制約が検証済みの場合のSET NOT NULLの高速化（PG12）などの改善を積み重ねてきましたが、その裏にはNOT NULL制約のカタログ管理の難しさがあり、この壁が15年という長い年月を経て乗り越えられたことを知り感慨深い気持ちになりました。またコントリビューターの方がブログ上でチームワークに言及している点も個人的には印象的でした。

終わりに

本稿ではPG18で実現されたNOT NULL制約を追加する際のNOT VALIDの検証を行い、その実現に至るまでの道のりを調べてみました。PG18はこれ以外にもパフォーマンスの改善を含めた多くの改善がなされています。気になった方はリリースノートや解説記事をご覧ください。

We are Hiring!

エムスリーではエンジニアを絶賛募集しています！ 少しでもご興味をお持ちの方は、ぜひカジュアル面談等にご応募ください！

エンジニア採用ページはこちら

jobs.m3.com

カジュアル面談もお気軽にどうぞ

jobs.m3.com

エンジニア新卒採用サイト！

fresh.m3recruit.com