問題とコンテキスト
システムが部屋の予約とその詳細を保存します。以前のフローでは挿入前にアプリケーション側で競合をチェックしていましたが、同時リクエストによって依然として重複が発生する可能性がありました。PostgreSQL 18 の時間的制約（temporal constraints）を使用してデータベース内で非重複を強制し、子テーブルが親のカバーする有効期間を参照できるようにします。
面接官が評価するポイント
重要なのは、WITHOUT OVERLAPS が主キーまたは一意性制約の最後の範囲カラムに属し、PERIOD が時間的外部キーに属することです。プレフィックスキーが同一である場合、空でない範囲は重複しないセットを形成します。半開区間の境界値、空の範囲、NULL、更新、ダーティデータのマイグレーション、および同時実行時の競合について説明します。
最初に確認すべき明確化のための質問
時間モデル
スキーマで tstzrange と daterange のどちらを使用するか、どのタイムゾーンが適用されるか、範囲が半開区間であるかを確認します。端点と隣接性のルールが制約の結果を直接左右します。
ビジネスキーと参照
ビジネスプレフィックスとしての部屋 ID、詳細期間が親期間によって完全にカバーされている必要があるかどうか、および1つの予約が複数のバージョンにまたがる可能性があるかどうかを確認します。
マイグレーションと同時実行性
既存の行に重複や空の範囲が含まれているか、マイグレーション期間、およびロールバック戦略を確認します。同時挿入では、アプリケーション側のロックだけでなく、データベースの制約とトランザクションのエラーハンドリングに依存する必要があります。
30秒の回答フレームワーク
「同一の部屋に対する重複を防ぐために、room_id を先頭に、有効期間範囲を末尾に配置し、PRIMARY KEY (room_id, during WITHOUT OVERLAPS) を使用します。詳細テーブルでは FOREIGN KEY (room_id, PERIOD during) を使用して時間的キーを参照します。制約を段階的に追加する前に重複や空の範囲をクリーンアップし、同時実行の競合はトランザクションがリトライまたは報告するデータベースエラーとして処理し、明確な半開区間とタイムゾーンのルールを適用します。」
詳細な解決手順
ステップ 1: 範囲タイプと境界値の選択
ビジネス要件に一致する離散型または連続型の範囲タイプを使用し、半開区間を標準化します。空の範囲を拒否し、隣接性を定義し、夏時間の移行によって偶発的な重複が発生しないようにタイムゾーンを正規化します。
ステップ 2: 時間的主キーの定義
識別子カラムを先頭に、範囲カラムを末尾に配置し、WITHOUT OVERLAPS を使用します。これにより、主キーの識別性と非 NULL セマンティクスを維持しながら、同一エンティティプレフィックス内での非重複を表現します。
CREATE TABLE room_booking (
room_id bigint NOT NULL,
during tstzrange NOT NULL,
guest_id bigint NOT NULL,
PRIMARY KEY (room_id, during WITHOUT OVERLAPS)
);ステップ 3: 期間外部キーの定義
詳細側にも期間がある場合は、PERIOD を使用して時間的主キーまたは一意性制約を参照します。完全なカバレッジが必要であることを確認し、親期間の分割や短縮をテストします。
CREATE TABLE booking_charge (
room_id bigint NOT NULL,
during tstzrange NOT NULL,
amount numeric NOT NULL,
FOREIGN KEY (room_id, PERIOD during)
REFERENCES room_booking (room_id, PERIOD during)
);ステップ 4: 履歴データのクリーンアップ
ロールアウトの前に、部屋ごとに重複、空の範囲、NULL、および無効な端点を検出し、マージ、分割、または無効化のポリシーを選択します。シャドウテーブルで検証し、大規模なテーブルを一度にロックするのを防ぐためにバッチで修正します。
ステップ 5: 同時書き込みの処理
2つのトランザクションが同一の部屋に対して重複する期間を挿入した場合、データベースに判定を委ねます。アプリケーションは制約エラーをキャッチし、空き状況を再読み込みして、リトライするか明確な競合を返します。チェック後に挿入するフローでは不十分であり、無秩序なグローバルロックも代替にはなりません。
ステップ 6: 更新および削除セマンティクスの評価
更新された期間は自身や他の行と競合する可能性があるため、予約の分割は単一のトランザクション内で行います。親期間を削除または短縮する前に、孤立した詳細が発生したりカバレッジが意図せず拡大したりするのを防ぐため、時間的外部キーのアクションを検証します。
ステップ 7: クエリと運用の検証
隣接、包含、同一、空、タイムゾーンまたぎ、および精度境界のケースをテストします。制約エラー率、マイグレーションのロック待機、およびインデックスサイズを監視し、競合が激しい場合の「リトライストーム」を防ぐために書き込みリトライ回数に上限を設定します。
高品質な回答例
半開区間の規約を採用して tstzrange を選択し、主キーとして (room_id, during WITHOUT OVERLAPS) を定義し、期間が親によってカバーされる必要がある詳細外部キーには (room_id, PERIOD during) を使用します。まずレガシーデータの重複や無効な端点をクリーンアップします。同時挿入はデータベース制約に依存し、アプリケーションが競合をキャッチしてリトライするか空き状況を返します。テストでは、隣接性、重複、親の分割、タイムゾーン変換、および高い競合状態を網羅します。
よくある間違い
- 間違い: アプリケーション側で挿入前にチェックするだけにする。 → 理由: 同時トランザクションが同時にチェックを通過してしまう可能性がある。 → 修正方法: 最終的な判定者として時間的制約を使用する。
- 間違い: キーの前に範囲カラムを配置する。 → 理由: 構文およびプレフィックスのセマンティクス上、範囲を末尾にする必要がある。 → 修正方法: ビジネスキーを先にリストし、その後に
WITHOUT OVERLAPSを指定する。 - 間違い: 隣接する範囲は常に競合すると仮定する。 → 理由: 境界値のセマンティクスによって結果が決まる。 → 修正方法: 半開区間を標準化し、端点をテストする。
- 間違い: マイグレーション中に即座に制約を有効化する。 → 理由: 過去データの重複や空の範囲によって失敗や長時間のロックが発生する。 → 修正方法: まず監査と修復を行い、段階的にロールアウトする。
フォローアップの質問と回答
フォローアップ 1: [10:00, 11:00) と [11:00, 12:00) は競合しますか？
一貫した半開区間の規約の下では、11:00 は2番目の範囲にのみ属するため競合しません。閉じた境界や混合した境界の場合は、制約を定義する前に明示的なビジネスクールが必要です。
フォローアップ 2: なぜ範囲カラムを最後にする必要があるのですか？
時間的キーはプレフィックスカラムによって行をグループ化し、その上で最終的な範囲が重複しないことを要求するためです。範囲を先頭に置くと、同一エンティティのグループ化を表現できません。
フォローアップ 3: 期間外部キーは単一の瞬間のみをチェックしますか？
いいえ。PERIOD は、参照される期間が親期間によってカバーされている必要があることを表します。PostgreSQL 18 のセマンティクスとテストに照らし合わせて、正確な組み合わせと境界値を確認してください。これは単一点の外部キーではありません。
フォローアップ 4: 競合下でのリトライストームを回避するにはどうすればよいですか？
リトライに上限を設けてジッター（ランダムな遅延）を追加し、利用可能な期間を再読み込みした上で、制限到達後に競合を返すかリクエストをキューに入れます。制約エラーとロック待機を監視し、必要に応じて部屋ごとに書き込みをシャーディングします。