こんにちは、バックエンドエンジニアの中山です。
今回はPostgreSQL 15から利用できる「NULLS NOT DISTINCT」オプションを紹介します。
背景
NULLS NOT DISTINCTについて調べ始めたきっかけは、UNIQUE制約がついているカラムに対し、UNIQUE 制約違反のエラーが発生したことでした。具体的には、「INSERT 前に SELECT で存在を確認し、なければ INSERT する」という実装により、意図しないタイミングで重複エラーが発生していました。
というのも、INSERT がコミットされる前に別のトランザクションが同じデータに対して SELECT を実行すると、両方のトランザクションが「データが存在しない」と判断し、それぞれ INSERT を試みてしまいます。その結果、UNIQUE 制約に違反しエラーとなっていました。
このような状況を防ぐため、今回は仕様上重複が発生した場合に更新するデータはなかったため、一時的に ON CONFLICT DO NOTHING を設定することで、重複時には何も処理を行わないように修正しました。
サンプルは以下です。
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username varchar(30),
email varchar(30) UNIQUE
);
INSERT INTO users (username, email)
VALUES ('alice', 'alice@example.com')
ON CONFLICT (email) DO NOTHING;このエラーへの対応の過程で、ON CONFLICT DO NOTHING を実装しているメソッドに、次のようなコメントが残されていました。
nullableなカラムが含まれているため、ON CONFLICT 句が動かない
PostgreSQL は、SQL 標準に従って NULL 値を扱っています。
このため、UNIQUE 制約を設定したカラムにNULLが含まれる場合、NULL 同士は等しいとはみなされません（NULL = NULLではない扱い）。
そのため、以下のような NULL を含む行が複数挿入されても、重複とは判定されないため、ON CONFLICT句が意図した通りに動作しません。
INSERT INTO users (username, email)
VALUES ('alice', NULL), ('alice', NULL)
ON CONFLICT (email) DO NOTHING;上記について調べていく中で、PostgreSQL 15から導入された「NULLS NOT DISTINCT」オプションの存在を知りました。
NULLS NOT DISTINCTとは？
例
-- 通常のUNIQUE制約
CREATE TABLE samples (id INTEGER UNIQUE);
INSERT INTO samples (id) VALUES (NULL); -- ok
INSERT INTO samples (id) VALUES (NULL); -- ok <- エラーにならないNULLS NOT DISTINCT を付けると、NULL 値も他の値と同様に「重複」とみなされ、NULL 同士も一意制約違反でエラーとなります。
例
-- NULLS NOT DISTINCTを付与
CREATE TABLE samples_nd (id INTEGER UNIQUE NULLS NOT DISTINCT);
INSERT INTO samples_nd (id) VALUES (NULL); -- ok
INSERT INTO samples_nd (id) VALUES (NULL); -- error <- エラーこのように NULLS NOT DISTINCT を付与することで、NULL 同士を比較することができ、重複を弾くことができます。
内部のUNIQUE制約の判定ロジックを見る
前述の「NULLS NOT DISTINCT」の概要を踏まえ、続いてこの仕組みが内部的にどのように実現されているのか、UNIQUE 制約の一意性チェックのロジックを見ていきます。
UNIQUE 制約の実装には B-tree インデックスのみが利用されます。
現在、一意インデックスとして宣言できるのはB-treeインデックスのみです
このB-treeインデックスの挿入処理時に、UNIQUE 制約を満たしているかどうかがチェックされます。
UNIQUE制約の重複チェックの内部ロジック
PostgreSQL では、UNIQUE 制約による一意性チェックは主に以下の2つのフラグで制御されています。
- anynullkeys
- 挿入対象のインデックスキーに1つでも NULL が含まれている場合 true になるフラグ
- checkingunique
- 挿入処理で UNIQUE 制約による一意性チェックを行う場合 true になるフラグ
一意性チェックが実行されるのは、checkingunique が true かつ anynullkeys が false の場合です。
つまり、NULL が含まれていない場合のみ、一意性チェックが行われるという仕様になっています。
条件分岐の実装箇所は以下です。
if (checkingunique)
{
if (!itup_key->anynullkeys)
{
/* No (heapkeyspace) scantid until uniqueness established */
itup_key->scantid = NULL;
}
else
{
/*
* Scan key for new tuple contains NULL key values. Bypass
* checkingunique steps. They are unnecessary because core code
* considers NULL unequal to every value, including NULL.
*
* This optimization avoids O(N^2) behavior within the
* _bt_findinsertloc() heapkeyspace path when a unique index has a
* large number of "duplicates" with NULL key values.
*/
checkingunique = false;
/* Tuple is unique in the sense that core code cares about */
Assert(checkUnique != UNIQUE_CHECK_EXISTING);
is_unique = true;
}
}
...
// 以降に checkingunique フラグで UNIQUE 制約の検証をするかどうか決める分岐処理が存在する
実装のコメントにある通り、データ中にNULLが含まれている場合は、SQL 標準の定義「NULL 同士は等しくない」に従って、重複チェックをする必要がないため、パフォーマンス最適化のために一意性チェックがスキップされる実装となっています
UNIQUE制約による一意性チェックの省略条件
標準のUNIQUE制約では、まず B-tree インデックスにデータを追加する際、各カラムの値について「 NULL を含んでいるか」が判定されます。
この処理は、インデックスへ追加する値スキャンキーの生成時に行われ、
カラム値がすべて NULL 以外であれば、anynullkeys フラグはfalseとなり、B-tree インデックスによる一意性チェックが実行されます。
一方、インデックス追加対象の値の中にNULLが含まれている場合は、anynullkeys フラグが true となります。
この場合、後続のインデックス挿入処理では一意性制約の検証がスキップされます。
そのため、NULL 値同士は重複とみなされず、同じカラムに複数件の NULL を INSERT する挙動になります。
NULL を含んでいるかを判定している箇所は以下です。
/* Record if any key attribute is NULL (or truncated) */
if (null)
key->anynullkeys = true;
...上記の条件を通過後に、checkingunique フラグが true なら UNIQUE 制約の一意性チェックを実行します。
UNIQUE 制約に NULLS NOT DISTINCT を付与した場合
NULLS NOT DISTINCT を指定した場合も、まずは通常の UNIQUE 制約と同様に、挿入される値に NULL が含まれているかどうかをチェックし、NULL が含まれていれば anynullkeys を true に設定します。
その後、NULLS NOT DISTINCTを設定している場合は、anynullkeysをfalseに上書きします。
これにより、NULL 値も他の値と同様に一意性チェックの対象となります。そのため、既に NULL 値が存在する場合、さらに同じカラムに NULL 値を追加しようとすると、一意制約違反となります。
/* Record if any key attribute is NULL (or truncated) */
if (null)
key->anynullkeys = true;
}
/*
* In NULLS NOT DISTINCT mode, we pretend that there are no null keys, so
* that full uniqueness check is done.
*/
if (rel->rd_index->indnullsnotdistinct)
key->anynullkeys = false;
return key;
このように UNIQUE 制約のカラムにNULLが含まれている場合でも、NULLS NOT DISTINCT オプションをつけて UNIQUE 制約を付与することで NULL の重複を弾くことができます。
私のNULLS NOT DISTINCT に移行する時の対応フロー
標準の UNIQUE 制約を NULLS NOT DISTINCT に移行する際の対応は、以下です。
-- UNIQUE 制約を col カラムに付与する
CREATE TABLE test_unique (
id serial PRIMARY KEY,
col INT
);
ALTER TABLE test_unique ADD CONSTRAINT uq_col UNIQUE (col);- アプリケーション側で NULL が重複しないよう実装の調整。また、重複しているデータの削除
- NULLS NOT DISTINCT オプションを持った UNIQUE インデックスの作成
- 同じカラムへの UNIQUE インデックスは違う名前で複数作成することができます
- テーブル全体に対しての ACCESS EXCLUSIVE LOCK を避けるため、CONCURRENTLY オプションを付与して実行します。
CREATE UNIQUE INDEX CONCURRENTLY uniq_col_nulls_not_distinct
ON test_unique (col) NULLS NOT DISTINCT;- 既存の UNIQUE 制約を削除し、新しい NULLS NOT DISTINCT オプション付きの UNIQUE 制約を付与
- ここで問題になる可能性があるのは、既存の UNIQUE 制約がインデックスから作成されていない場合です。制約の付与を先に実行すると制約とインデックスが紐付きます。制約とインデックスが紐づくことで制約を削除する際に、制約の削除とインデックスの削除を一緒に行なってしまいます。
- そのため、インデックスが大きいと削除が完了するまでに時間がかかってしまい、ACCESS EXCLUSIVE LOCK の時間が長くなり障害になる可能性が高くなります。
- 今回のケースではインデックスが大きくなかったので、一瞬で完了しました
ALTER TABLE test_unique DROP CONSTRAINT uq_col;
-- このとき、同名インデックスも一緒に削除されうることに注意インデックスからUNIQUE制約の付与
ALTER TABLE test_unique
ADD CONSTRAINT uq_col_nulls_not_distinct
UNIQUE USING INDEX uniq_col_nulls_not_distinct;まとめ
SQL標準のNULLの扱いによって、UNIQUE 制約にも落とし穴があることを認識し良い学びになりました。
We are hiring!
一緒にPR TIMESの開発を担ってくれるエンジニアはもちろん、各種ポジションで採用を行っています！
