資料工程面試：如何用 PostgreSQL 時間約束防止有效期重疊？

題干與適用場景

價格、租約、排班與權限記錄通常由業務鍵和有效期組成。面試官要求同一商品或租戶的區間不能重疊，並希望資料庫在並發寫入時保證規則。題目考察時間資料建模、約束語義與上線遷移，不是只會寫查詢找衝突。

面試官考察點

是否能區分 WITHOUT OVERLAPS 的時間主鍵或唯一約束與普通 B-tree 唯一鍵。
是否理解範圍端點、空區間、NULL、離散日期和連續時間戳的邊界。
是否知道 PERIOD 外鍵要求被引用資料在時間上完整覆蓋，而不只是匹配業務鍵。
能否規劃歷史資料清理、鎖影響、回滾和並發驗證。
是否把資料庫約束、應用提示和稽核指標分層設計。

約束語義與邊界

PostgreSQL 文件把時間約束定義在範圍欄位上。WITHOUT OVERLAPS 可用於主鍵和唯一約束，要求相同普通鍵部分對應的範圍不重疊；範圍欄位隱含非空，空範圍或多範圍值也不會成為有效時間鍵。這是資料庫級不變量，不能由「先查詢、再插入」的應用邏輯取代。

PERIOD 用於時間外鍵。子表的業務鍵和時間段必須被父表一組或多組記錄覆蓋，不能只證明父表存在同名業務鍵。父表刪除或縮短覆蓋區間時，也要依外鍵動作和交易順序處理引用方。

建模步驟

先確定區間是半開還是閉合，並讓所有寫入路徑使用同一約定。日期有效期常用 [start, end)，連續時間戳則明確時區與精度。普通業務鍵放在範圍欄位之前，範圍型別選 daterange、tsrange 或帶時區的 tstzrange。

再為主記錄建立時間唯一約束，為引用記錄建立 PERIOD 外鍵。應用仍可提供友善提示，但最終提交必須由資料庫約束決定成敗。歷史資料先用臨時查詢或排他鎖找出衝突，再決定拆分、合併或作廢。

SQL 範例

以下範例保證同一 plan_id 的價格區間不重疊，並讓訂單條款被價格計畫完整覆蓋：

sql

CREATE TABLE price_plan (
  plan_id bigint,
  valid_during daterange NOT NULL,
  amount numeric(12, 2) NOT NULL,
  PRIMARY KEY (plan_id, valid_during WITHOUT OVERLAPS)
);

CREATE TABLE plan_rule (
  plan_id bigint,
  valid_during daterange NOT NULL,
  rule_code text NOT NULL,
  CONSTRAINT plan_rule_plan_period_fk
    FOREIGN KEY (plan_id, PERIOD valid_during)
    REFERENCES price_plan (plan_id, PERIOD valid_during)
);

實際遷移前應在影子表驗證語法與約束行為，並確認客戶端驅動能解析資料庫回傳的衝突錯誤。範例只展示核心語義，金額精度、幣別和稽核欄位仍需依業務補齊。

並發寫入與遷移

加入約束前先統計衝突區間，按業務鍵排序處理重疊記錄；不要直接刪除一條「看起來重複」的歷史。大表遷移要評估索引建立、鎖等待和複製延遲，使用分批清理、低峰時段與可觀測的進度標記。

兩次並發插入同一業務鍵的相鄰區間時，資料庫約束應在提交時協調衝突。應用需要把唯一約束衝突轉成可重試或可解釋的業務錯誤，不能先查無衝突就假設插入必定成功。回滾方案保留舊欄位和舊寫入路徑，待影子驗證、雙寫對帳及恢復演練通過後再切換。

常見錯誤

只建立 (planid, startat) 唯一索引，仍允許有效期重疊。
沒有定義端點規則，把 2026-01-01 到 2026-02-01 與下一段起點混用。
把 PERIOD 外鍵當成普通外鍵，只驗證業務鍵存在而不驗證時間覆蓋。
直接在生產大表加入約束，忽略歷史衝突、鎖和複製延遲。
讓應用層重試掩蓋約束衝突，導致重複價格或不可解釋的部分覆蓋。

追問及應對

如何處理既有重疊資料？

先按業務鍵和區間排序產生衝突報告，由業務確認合併、拆分或作廢規則；修復後在影子表重放寫入，再用相同查詢證明沒有重疊，最後才加入約束。

為什麼不用排他約束或觸發器？

排他約束仍可表達區間互斥，但 WITHOUT OVERLAPS 直接表達時間主鍵或唯一語義，便於與 PERIOD 外鍵組合。觸發器容易遺漏並發、遞迴和複製路徑；只有需要額外跨表副作用時才考慮，並把核心不變量留在約束層。

如何驗證上線沒有改變業務時間？

記錄約束前後的區間計數、邊界樣本、衝突拒絕率和查詢計畫；在只讀副本和恢復環境執行覆蓋性驗證。灰度期間保留舊邏輯對帳，發現誤拒絕時可回滾約束切換而不刪除歷史。