題幹與適用場景
團隊要儲存會議室預約。每筆記錄包含房間、開始時間與結束時間；同一房間不能出現重疊預約，相鄰預約允許首尾相接。應用層已經做衝突檢查，但高並發下仍偶爾出現重複占用。請給出 PostgreSQL 設計，並解釋半開區間、空值、時區、並發寫入、錯誤處理與既有資料遷移。
這道題適合資料工程、後端與資料庫職位。重點是把跨列的業務規則表達成資料庫不變量，而不是只寫一個查詢再寄望呼叫端遵守。回答應能區分 UNIQUE、CHECK、觸發器與 exclusion constraint 的邊界，並說明約束失敗如何回饋產品流程。
面試官考察點
強回答會把預約時間建模為 tstzrange 或合適的 range 類型，明確使用 [start, end) 讓相鄰區間不衝突；再用 GiST exclusion constraint 組合房間相等與時段重疊。它會說明何時需要 btree_gist、為什麼應用層預檢不能消除競態、如何捕捉約束例外、如何處理無窮邊界與空區間，以及如何在遷移前找出既有衝突。
回答前需要釐清的問題
- 開始與結束時間的時區規則是什麼，是否允許跨夏令時間切換？
- 結束時間是否必須晚於開始時間，零長度預約是否有業務意義？
- 衝突範圍是同一房間，還要按樓層、設備或租戶隔離嗎？
- 相鄰區間是否允許首尾相接，取消與軟刪除的記錄是否仍占用資源？
- 現有資料是否已存在重疊，遷移期間能否短暫停寫？
30 秒回答框架
「我會把開始與結束時間規範化為帶時區的半開區間 tstzrange(startat, endat, '[)')，並在資料庫上加 EXCLUDE USING gist (roomid WITH =, during WITH &&)。這樣同一房間的重疊區間會被拒絕，相鄰區間允許共存；btreegist 用於讓整數或 UUID 房間鍵參與 GiST 比較。寫入時直接嘗試新增並把約束衝突轉成可重試的業務錯誤，不能依賴先查再寫。上線前先掃描舊資料、修復衝突，再逐步啟用約束並監控失敗率。」
分步驟深入解答
第一步：選擇時間語義
用 tstzrange 表達絕對時間，避免把本地時間字串交給資料庫解釋。[start, end) 表示包含開始、不包含結束，因此 [10:00, 11:00) 與 [11:00, 12:00) 不重疊。資料庫文件把範圍運算 && 定義為重疊判斷，並展示範圍約束的典型用途。
寫入前驗證 startat < endat，並決定是否允許空範圍。統一儲存時區後，展示層再按使用者時區格式化；不要透過夏令時間當天的本地小時差推導時長。
第二步：把規則寫成 exclusion constraint
可以把起止欄位生成一個範圍欄位，也可以在約束表達式中直接構造範圍。以下使用顯式範圍欄位，方便查詢與稽核：
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 &&
)
);約束要求任意兩列的比較至少有一個運算結果為 false 或 null；room_id = 且 during && 同時成立時，第二列會被拒絕。PostgreSQL 會為 exclusion constraint 自動建立指定類型的索引。
第三步：理解 btree_gist 與索引代價
範圍本身適合 GiST；普通整數、文字或 UUID 的相等比較通常沒有預設 GiST operator class，因此可以安裝 btree_gist 擴充，讓這些純量參與同一個 GiST 約束。擴充屬於資料庫部署依賴，遷移腳本要明確宣告並在受控環境驗證版本。
GiST 約束索引會增加寫入與更新成本，查詢也應使用範圍運算與合適條件。不要為了「有索引」再建立重複的範圍 GiST 索引；先檢查執行計畫與約束索引是否已滿足讀取需求。
第四步：處理並發與交易
不要先執行 SELECT 檢查衝突，再執行 INSERT；兩個交易可能同時看到空閒並都插入。讓資料庫約束成為最終裁決，應用捕捉唯一的約束名稱，把衝突轉換成「時段已被占用」，必要時讓使用者重新整理或選擇其他時間。
預約還可能涉及付款、通知或配額。先在短交易中寫入預約並提交，再用可靠事件或 outbox 觸發外部副作用。重試只應針對可安全重試的序列化或暫時性錯誤；約束衝突代表業務事實，盲目重試不會成功。
第五步：明確取消、租戶與刪除策略
軟刪除記錄是否繼續占用房間必須成為查詢與約束模型的一部分。若取消記錄要釋放時段，可以把活動預約放在獨立表，或設計可驗證的狀態遷移；僅在查詢中加入 WHERE status = 'active' 無法直接讓 exclusion constraint 忽略其他列。
多租戶場景應把 tenantid 納入約束鍵，例如 (tenantid WITH =, room_id WITH =, during WITH &&)，並在授權層保證租戶不能寫入別人的房間。約束保證衝突關係，不取代列級權限與業務狀態機。
第六步：遷移既有資料
先用自連接或視窗查詢找出同一房間的重疊對，記錄衝突數量與負責人。修復方案可以合併、拆分、取消或人工確認，不能直接截斷資料。驗證無衝突後在低風險時段建立約束；大表遷移要評估鎖、索引建立時間與回滾路徑，並在發布前做備份還原演練。
第七步：設計錯誤與可觀測性
給約束命名，例如 roomreservationsno_overlap，讓驅動回傳的約束名稱可穩定映射到使用者提示。日誌記錄房間、請求識別碼與時段摘要，避免寫入不必要的個人資訊。監控約束衝突率、遷移殘留衝突、交易耗時與索引膨脹，區分正常競爭與異常客戶端重試風暴。
第八步：用並發場景驗證
至少驗證同房間重疊失敗、同房間相鄰成功、不同房間重疊成功、跨時區等價時間、更新導致衝突、取消釋放、空範圍與缺失值。用兩個並發交易實際壓測，而不是只執行順序腳本；同時驗證恢復、備份與約束重建後的結果。
設計取捨與邊界
Exclusion constraint 適合「任意兩列不得同時滿足某組比較」的持續不變量。它比應用層互斥鎖更接近資料來源，也比觸發器少一套競態維護邏輯。代價是 GiST 寫放大、擴充依賴與約束錯誤需要應用理解。
如果規則涉及跨表容量、動態優先級或需要允許有限重疊，單一 exclusion constraint 可能不夠；可以把資源分配拆成可鎖定的槽位、使用交易級鎖，或引入專門的調度服務，但仍應讓資料庫約束保護能表達的核心不變量。CHECK 不能可靠地引用其他列來維持這種跨列規則。
落地計畫與證據
先在影子表匯入生產資料，執行衝突掃描並按房間與租戶輸出修復清單。隨後建立擴充與約束，回放真實並發寫入，確認錯誤映射、索引成本、備份還原與監控告警。小流量啟用後比較約束衝突與人工衝突率，穩定後再切換正式表。
PostgreSQL 文件說明範圍類型支援 && 等運算，並給出使用 GiST exclusion constraint 防止重疊的預約示例；約束章節進一步定義 exclusion 的成對比較語義，並說明建立約束會自動建立指定索引。這些一手資料足以支援資料型別、運算子與索引結論，部署細節仍需按實際版本驗證。
公開的 booking-system 面試資料也把「並發防止重複預約」與 PostgreSQL exclusion constraint 列為回答要點；本題據此保留預約場景，同時把重點收窄到資料不變量、遷移與失敗驗證，而不是重述整套預約系統設計。
常見誤區與追問
只做「先查再新增」
並發交易會同時通過檢查。把查詢作為使用者體驗提示可以保留，但最終一致性必須由資料庫約束裁決。
用 timestamp 儲存本地時間
跨地區與夏令時間會讓相同字串代表不同瞬間。先確定時區策略，優先儲存絕對時間，再按展示時區轉換。
用 UNIQUE(roomid, startat) 防重疊
唯一約束只能阻止相同起點，不能阻止一個長區間覆蓋多個短區間。範圍運算才能表達重疊關係。
讓約束忽略軟刪除列
普通 exclusion constraint 會比較所有列。把活動記錄與歷史記錄分離，或重新設計可約束的狀態模型；不要只在應用查詢裡過濾。
為什麼不只用觸發器？
觸發器需要自行處理並發、鎖與錯誤語義，容易與備份還原產生複雜邊界。若規則能表達為範圍與比較運算，原生 exclusion constraint 通常更直接；若規則超出其表達能力，再評估觸發器或調度服務。