题干与适用场景
团队要保存会议室预约。每条记录包含房间、开始时间和结束时间；同一房间不能出现重叠预约，相邻预约允许首尾相接。应用层已经有冲突检查，但高并发下仍偶尔出现重复占用。请给出 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 通常更直接；若规则超出其表达能力，再评估触发器或调度服务。