設問とコンテキスト
あるチームが会議室の予約を保存しています。各レコードには会議室、開始時刻、終了時刻が含まれます。同一の会議室に対する時間枠は重複してはなりませんが、隣接する予約のエンドポイント同士が接することは許容されます。アプリケーション側で競合チェックをすでに行っていますが、並行処理の下では依然として二重予約が発生しています。PostgreSQLによる設計を提示し、半開区間、NULL、タイムゾーン、並行書き込み、エラーハンドリング、および既存データのマイグレーションについて説明してください。
この質問は、データエンジニアリング、バックエンド、およびデータベース関連の職種に適しています。重要なのは、各呼び出し元が同じクエリを実行することに依存するのではなく、複数行にまたがるビジネスルールをデータベースの不変条件として表現することです。優れた回答では、UNIQUE、CHECK、トリガー、および排他制約の境界を明確に区別し、制約違反とプロダクトのワークフローを結びつけて説明します。
面接官がテストしていること
優れた回答では、予約時間をtstzrangeまたは別の適切な範囲型としてモデル化し、隣接する時間枠が競合しないように明示的に[start, end)を使用します。また、会議室の一致と時間の重複をGiST排他制約で組み合わせます。btree_gistが必要になるタイミング、アプリケーションの事前チェックでは競合状態を排除できない理由、制約例外のマッピング方法、無限の境界や空の範囲の処理方法、およびマイグレーション前に既存の競合を見つける方法を説明します。
最初に明確にすべき質問
- 開始時刻と終了時刻のタイムゾーンポリシーはどうなっているか、また予約が夏時間の移行期間をまたぐことはあるか？
- 終了時刻は開始時刻より後でなければならないか、また長さゼロの予約に業務上の意味はあるか？
- 競合のスコープは単一の会議室のみか、それともフロア、デバイス、テナントも含まれるか？
- 隣接する区間が接することは可能か、またキャンセル済みまたは論理削除された行もリソースを消費し続けるか？
- 既存データにすでに重複が含まれているか、またマイグレーション中に書き込みを短時間停止できるか？
30秒の回答フレームワーク
「開始時刻と終了時刻をタイムゾーン対応の半開区間範囲であるtstzrange(start_at, end_at, '[)')に正規化し、データベースにEXCLUDE USING gist (room_id WITH =, during WITH &&)を追加します。同一会議室に対する重複する時間枠は拒絶されますが、隣接する時間枠は共存できます。btree_gistにより、整数またはUUIDの会議室キーがGiST比較に参加できるようになります。事前チェック後に挿入するのではなく、直接書き込みを試行し、制約競合をリトライ可能なビジネスレスポンスにマッピングします。ロールアウト前に、過去の競合をスキャンして修復し、段階的に制約を有効化して、失敗を監視します。」
ステップバイステップの解決策
ステップ1：時間セマンティクスを選択する
ローカル時刻の文字列をデータベースに渡すのではなく、絶対的な瞬間を表すtstzrangeを使用します。[start, end)は開始を含み終了を除外するため、[10:00, 11:00)と[11:00, 12:00)は重複しません。PostgreSQLは&&を重複演算子として文書化しており、この種の不変条件に範囲制約を使用します。
書き込み時にstart_at < end_atを検証し、空の範囲に意味があるかどうかを決定します。一貫したタイムゾーン表現を保存し、閲覧者のタイムゾーンに合わせてフォーマットします。夏時間の移行日におけるローカル時計の算術演算から期間を推測しないでください。
ステップ2：ルールを排他制約として表現する
範囲は生成列にすることも、制約式の中で構築することもできます。明示的な範囲列を用意すると、クエリや監査に便利です：
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE room_reservations (
reservation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_id bigint NOT NULL,
during tstzrange NOT NULL,
CHECK (NOT isempty(during)),
EXCLUDE USING gist (
room_id WITH =,
during WITH &&
)
);この制約は、すべての行のペア間における少なくとも1つの比較がfalseまたはnullであることを要求します。room_id =とduring &&の両方がtrueである場合、2行目は拒絶されます。PostgreSQLは、排他制約に対して選択された型のインデックスを自動的に作成します。
ステップ3：btree_gistとインデックスコストを理解する
範囲型にはGiST演算子クラスがあります。整数、テキスト値、UUIDなどのスカラーには通常、等価比較用のデフォルトのGiSTクラスがないため、btree_gistを使用することで同じGiST制約に参加できるB-tree風の演算子クラスを提供できます。この拡張機能はデプロイ時の依存関係として扱い、マイグレーション環境で検証してください。
GiST制約インデックスは書き込みおよび更新コストを増加させます。読み取りでは範囲演算子と選択的な述語を使用する必要があります。インデックスが見えているからといって重複する範囲GiSTインデックスを作成しないでください。実行計画を確認し、制約インデックスがすでに読み取りワークロードをまかなえているか確認してください。
ステップ4：並行性とトランザクションを処理する
競合をチェックするためにSELECTを実行してからINSERTを実行するような処理は行わないでください。2つのトランザクションが両方とも空き枠を観測してしまう可能性があります。データベースの制約に調停させ、名前付き制約違反をキャッチして「その時間枠はすでに予約されています」と返し、ユーザーが画面を更新するか別の枠を選択できるようにします。
予約によって決済、通知、クォータの消費が発生する場合もあります。短いトランザクションで予約をコミットし、外部の副作用についてはアウトボックスや信頼性の高いイベントを発行します。リトライしても安全なシリアライゼーションエラーや一時的なエラーのみをリトライしてください。制約の競合はビジネス上の確定事実であるため、やみくもにリトライしても成功しません。
ステップ5：キャンセル、テナンシー、削除を定義する
論理削除された行が依然として会議室を占有するかどうかは、制約モデルの一部でなければなりません。キャンセルによって時間枠が解放される場合は、アクティブな予約を履歴から分離するか、強制可能な状態遷移を設計します。アプリケーションのクエリにおけるWHERE status = 'active'フィルターは、通常の排他制約が他の行を無視するようには機能しません。
マルチテナンシーの場合、リソースの名前空間がテナントローカルであるならテナントキーを含め（例：(tenant_id WITH =, room_id WITH =, during WITH &&)）、あるテナントが他のテナントの会議室に書き込めないように認可を強制します。制約は競合を防ぐものであり、行レベルの権限やビジネスステートマシンを代替するものではありません。
ステップ6：既存データをマイグレーションする
自己結合またはウィンドウクエリを使用して会議室ごとに重複するペアを見つけ、件数と所有者を記録します。マージ、分割、キャンセル、またはビジネス上の決定を得ることによって各競合を解決します。データを勝手に切り捨ててはいけません。データがクリーンになった後、低リスクの時間帯に制約を作成します。大規模なテーブルの場合は、ロック、インデックス構築時間、ロールバック、およびバックアップ・リストアのリハーサルを評価します。
ステップ7：エラーと可観測性を設計する
ドライバーの制約名がユーザー向けの安定したレスポンスにマッピングされるよう、制約に名前（例：room_reservations_no_overlap）を付けます。不要な個人情報を含めずに、会議室、リクエスト識別子、および時間枠のサマリーをログに記録します。通常の競合とリトライの嵐を区別しながら、競合率、マイグレーションの残り、トランザクションのレイテンシ、インデックスの増加を監視します。
ステップ8：並行シナリオをテストする
同一会議室での重複（失敗）、同一会議室での隣接する時間枠（成功）、異なる会議室での重複（成功）、タイムゾーンをまたぐ等価な瞬間、競合を引き起こす更新、キャンセル、空の範囲、およびNULLの処理をテストします。順次実行スクリプトだけでなく、2つの並行トランザクションを使用し、リカバリ、バックアップ・リストア、および制約再構築の挙動を検証します。
トレードオフと境界
排他制約は、どの行のペアも同時に特定の比較条件セットを満たしてはならないという継続的に維持されるルールに適しています。これはアプリケーションのミューテックスよりもデータソースに近く、競合が発生しやすい個別のトリガープロトコルを回避できます。コストとしては、GiSTの書き込み増幅、拡張機能への依存、およびアプリケーションが制約エラーを理解する必要がある点が挙げられます。
ルールが複数テーブルにまたがる場合、動的なキャパシティを持つ場合、または制限付きの重複を許可する場合は、単一の排他制約では不十分な場合があります。表現可能な不変条件についてはデータベースの制約を維持しつつ、ロック可能なスロット、トランザクションレベルのロック、またはスケジューリングサービスの導入を検討してください。CHECK制約は、この複数行にまたがるルールを維持するために他の行を確実に参照することはできません。
ロールアウト計画と根拠
本番データをシャドウテーブルにロードし、重複スキャンを実行して、会議室およびテナントごとの修復リストを作成します。次に拡張機能と制約をインストールし、並行書き込みを再生して、エラーマッピング、インデックスコスト、バックアップ・リストア、およびアラートを検証します。トラフィックのごく一部に対して有効化し、制約競合を手動で観測された競合と比較し、結果が安定した後にメインテーブルを切り替えます。
PostgreSQLの範囲型に関するドキュメントでは&&などの演算子が定義されており、重複する予約を防ぐGiST排他制約が示されています。制約に関するドキュメントではペアごとの排他セマンティクスが定義されており、制約を追加すると指定されたインデックスが作成されることが記されています。これらの一次資料はデータ型、演算子、およびインデックスに関する主張を裏付けています。デプロイの詳細については、実際のPostgreSQLバージョンに対するテストが依然として必要です。
公開されている予約システムの面接資料でも、並行二重予約の防止とPostgreSQLの排他制約が面接の論点として挙げられています。この記事ではその認知度の高いシナリオを維持しつつ、予約システム全体の設計を繰り返すのではなく、データの不変条件、マイグレーション、および障害検証に回答を絞り込んでいます。
よくある間違いとフォローアップ
「チェックしてから挿入」しか行わない
並行するトランザクションは両方ともチェックを通過してしまう可能性があります。有用であればユーザー体験のためのヒントとしてクエリを残しても構いませんが、最終的な結果の判定はデータベース制約に任せてください。
ローカル時刻をtimestampに保存する
同じ文字列であっても、地域や夏時間の変更によって異なる瞬間を表すことがあります。タイムゾーンポリシーを定義し、絶対時刻を保存して、表示時にのみ変換してください。
重複防止にUNIQUE(room_id, start_at)を使用する
一意性制約は同一の開始値をブロックしますが、複数の短い区間をカバーする長い区間をブロックすることはできません。範囲演算子は重複を直接表現します。
通常の制約で論理削除された行を無視させようとする
通常の排他制約はすべての行を比較します。アクティブなレコードと履歴レコードを分離するか、状態モデルを再設計してください。アプリケーションのクエリ内だけでフィルタリングするのでは不十分です。
トリガーだけを使用しない理由は？
トリガーは独自の並行性、ロック、およびエラーのセマンティクスを実装する必要があり、バックアップやリストア時に厄介なエッジケースを生み出す可能性があります。ルールが範囲と演算子で表現可能であれば、ネイティブの排他制約の方が通常はより明確です。ルールがそのモデルを超える場合にトリガーやスケジューラーを使用してください。