プロンプトとコンテキスト
注文テーブルでは、テナントごとに1つのオプショナルな外部参照を許可しています。つまり、テナントごとに最大1行のみNULLを持つことができます。履歴データの重複、並行書き込み、およびロールバックに対処しながら、PostgreSQL 18でこれをどのように強制しますか？
PostgreSQLのデフォルトの一意性インデックスはNULL値を別個（distinct）のものとして扱うため、複数のNULLがあっても衝突しません。PostgreSQL 18ではNULLS NOT DISTINCTが追加され、NULL値が一意性に関与するようになります。この質問では、レースコンディションが発生しやすいビジネスチェックをアプリケーションコードに移すのではなく、制約の設計、マイグレーションの安全性、および並行性セマンティクスをテストします。
面接官がテストしていること
- デフォルトのNULL一意性と
NULLS NOT DISTINCTの違いを正確に説明できるか。 - このオプションが通常の比較ではなく、一意なB-treeインデックスまたは一意性制約に適用されることを知っているか。
- 過去の重複するNULLおよび非NULLの組み合わせを最初に見つけて解決できるか。
- オンラインマイグレーション、ロック計画、並行書き込みの動作、およびロールバックを設計できるか。
- ORM、レプリケーション、パーティショニング、および下流の規約との互換性が維持されるか。
最初に明確にすべき質問
- スコープはテーブル全体か、それともテナント、リージョン、アクティブ状態ごとのルールか？
- NULLは未割り当て、不明、それとも意図的に共有された参照を意味するのか？
- 履歴行には複数のNULL、空文字、大文字・小文字の違い、または論理削除されたキーが含まれているか？
- 書き込みレート、許容ロック時間（ロックバジェット）、およびマイグレーションウィンドウはどの程度か？
- アプリケーション、ORM、CDC、およびレポートは、NULLが重複する可能性があることを前提としているか？
30秒での回答
「NULLの意味とスコープを明確にし、履歴データの重複を監査します。テナントスコープのルールの場合、テナントと参照をNULLS NOT DISTINCTを指定した複合ユニークキーに含めます。このオプションにより、NULLはそのキーの一意性に関与するようになりますが、SQLの3値論理の比較は変更されず、カラムがNOT NULLになるわけでもありません。過去の競合をクリーンアップまたは決定し、インデックス作成前に競合処理をデプロイし、ロックとレイテンシの計画を立ててインデックスを構築し、ORMとCDCの動作をテストします。データベースが並行処理における最終的な権威となります。ビジネスセマンティクスが誤っている場合は、競合の起こりやすい事前チェックに頼るのではなく、制約とアプリケーションポリシーをロールバックします。」
ステップごとの詳細解説
ステップ 1: NULLの意味とスコープを確認する
未割り当ての値と不明な値を区別します。NULLが未割り当てを意味する場合、1つのNULLが意図されたルールである可能性があります。不明を意味し重複が有効である場合、一意性の適用は誤りです。制約がグローバルなのか、それともテナント、リージョン、アクティブ状態ごとにグループ化されるのかを決定し、複合キーの順序と部分インデックスが必要かどうかを選択します。
ステップ 2: データベースの表現を選択する
PostgreSQL 18は、一意性インデックスでNULLS NOT DISTINCTをサポートしています。デフォルトではNULLを等しくないと扱い、複数のNULLを許可します。一意性制約を使用するとモデルがより明確になる場合があり、一意なB-treeインデックスはオンラインマイグレーションに適している場合があります。このオプションは一意性の比較のみを変更します。WHERE value = NULLは引き続き3値論理に従い、カラムはnull許容のままにできます。
ステップ 3: 履歴データを監査およびクリーンアップする
提案されたキーでグループ化し、複数のNULL、空文字、大文字・小文字の違い、および依然としてキーを占有している論理削除された行をカウントします。注文をマージするか、参照を埋めるか、1行を保持して他を移行するか、例外を文書化するかを競合ごとに決定します。クリーンアップを再実行可能かつ監査可能にし、インデックス作成によって未解決の競合が露呈する前にシャドウ環境で検証します。
ステップ 4: オンラインマイグレーションを設計する
まず一意性競合に対する互換性のあるアプリケーション処理をリリースし、次に制御されたメンテナンスウィンドウ中にインデックスまたは制約を作成します。大規模なテーブルの場合は、並行作成（CONCURRENTLY）、ロックレベル、ディスク容量、および書き込みレイテンシを評価し、全体を通じて競合と長時間トランザクションを監視します。テナントを段階的に移行する必要がある場合は、バッチでルールを作成し、完了ウォーターマークを記録します。データベースの制約とエラーマッピングの準備が整うまで、古い事前チェックを維持します。
ステップ 5: 並行性と下流の規約を処理する
一意性インデックスは、並行するINSERTおよびUPDATEに対する最終的な決定者です。アプリケーションの「チェックしてから挿入」はメッセージを改善できますが、制約の代わりにはなりません。一意性違反を、無限リトライに陥らないリトライ可能またはユーザーに表示可能なビジネスエラーにマッピングします。CDC、レプリケーション、ORMスキーマ、レポート、およびキャッシュにおいてNULLが重複可能であるという前提がないか確認し、規約とアラートを更新します。
ステップ 6: 検証、監視、およびロールバック
ステージング環境およびカナリアテナントで、1つのNULL、2つ目のNULL、等しい非NULL値、異なる非NULL値、更新、削除して再作成、および並行書き込みをテストします。インデックス構築、ロック待ち、競合率、アプリケーションエラー、および下流のレイテンシを監視します。セマンティクスまたは競合率が許容できない場合は、新しい書き込みパスを停止し、制約を削除し、互換性のあるエラー処理を復元して、分析のために監査証跡を保持します。
質の高い回答例
まずNULLの意味とスコープを確認します。各テナントが1つのオプショナルな参照を持てる場合、テナントと参照を複合ユニークキーに含め、PostgreSQL 18でNULLS NOT DISTINCTを使用します。これにより、通常のSQLの3値論理とnull許容カラムはそのまま維持されつつ、NULLがそのキーの一意性に関与するようになります。
ロールアウトの前に、複数のNULL、空文字、大文字・小文字の違い、論理削除された行を監査し、各競合をどのようにマージまたは入力するかを決定し、その決定を記録します。インデックスを構築する前に一意性競合の処理をデプロイし、ロック、容量、長時間トランザクション、および競合を監視します。テストでは、1つおよび2つのNULL、同一および異なる非NULL、更新、削除して再作成、および並行性をカバーします。データベースが最終的な権威であり、ORM、CDC、レポート、キャッシュも同じ規約を採用する必要があります。ビジネス上の意味が誤っていることが判明した場合は、制約を削除してアプリケーションポリシーをロールバックします。
よくある間違い
- UNIQUEはデフォルトで1つのNULLしか許可しないと思い込む → PostgreSQLはNULLを別個のものとして扱います →
NULLS NOT DISTINCTを明示的に使用します。 - これをNOT NULLとして扱う → このオプションは依然として1つのNULLを許可します → 欠損値の意味と一意性を切り離して考えます。
- アプリケーションの事前チェックのみを使用する → 並行リクエスト間でレースコンディションが発生します → データベースの一意性インデックスに判断させます。
- 空文字や大文字・小文字の違いを無視する → ビジネス上の重複はNULLの競合ではない可能性があります → まず正規化とクリーンアップを定義します。
- 履歴データのクリーンアップなしでオンライン構築を行う → 既存の重複によりマイグレーションが失敗またはブロックされる可能性があります → 監査、決定、および長時間トランザクションの監視を行います。
- データベースのみを変更する → ORM、CDC、レポートが引き続き重複可能なNULLを想定している可能性があります → データ規約とエラーマッピングを更新します。
フォローアップの質問
NULLS NOT DISTINCTは通常のNULL比較を変更しますか？
いいえ。一意性インデックスにおいてNULL同士が衝突するかどうかを変更するだけです。WHERE value = NULLは引き続きSQLの3値論理に従うため、IS NULLを使用する必要があります。クエリセマンティクス、インデックスセマンティクス、およびカラムのnull許容性は個別に説明する必要があります。
すでに2つのNULLが存在する場合、ダウンタイムなしでどのようにマイグレーションできますか？
テナントおよびビジネス状態ごとに保持する行を選択し、他方をマージまたは入力して、監査テーブルに決定を記録します。競合処理をデプロイし、ロックと長時間トランザクションを監視しながらバッチでインデックスを作成し、ウィンドウ内で競合を解決できないテナントは後回しにします。
マルチテナントルールには複合キーと部分インデックスのどちらを使用すべきですか？
すべての状態で一意である必要がある場合は、複合ユニークキーでテナントと参照を使用します。アクティブな行のみが制約される場合は、アクティブ状態に限定された部分インデックスが適している場合があります。選択は削除、復元、および状態遷移の規約に依存し、並行性テストによって検証される必要があります。