数据工程面试：如何用 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 外键组合。触发器容易遗漏并发、递归和复制路径；只有需要额外跨表副作用时才考虑，并把核心不变量留在约束层。

如何验证上线没有改变业务时间？

记录约束前后的区间计数、边界样本、冲突拒绝率和查询计划；在只读副本和恢复环境执行覆盖性校验。灰度期间保留旧逻辑对账，发现误拒绝时可回滚约束切换而不删除历史。