PostgreSQL房间预订：可用/已预订时段存储方案抉择及查询优化

场景说明 #

我正在基于PostgreSQL开发房间预订系统，目前在两种时段存储方案之间犹豫，希望得到选型建议，同时想了解针对第二种方案的查询优化方法。

方案1：存储可用时段 #

表结构 #

CREATE TABLE "Calendar"
(
    "id"                       bigserial PRIMARY KEY,
    "calendar_day"             date    NOT NULL,
    "room_id"                  integer NOT NULL,
    "available_interval_start" time  NOT NULL,
    "available_interval_end"   time  NOT NULL,
    "booked"                   boolean NOT NULL DEFAULT false
);

核心特点 #

• 优势：查询可用时段直接高效，多房间多日期的预订校验可通过事务快速完成，无需复杂计算。
• 劣势：取消预订时必须处理时段碎片合并，簿记逻辑繁琐。例如原可用时段[a,e]被预订[b,c]后，会拆分为[a,b]、[c,e]两个可用时段，[b,c]标记为已预订；若后续[b,c]取消，需要将相邻的可用碎片合并并更新数据，涉及多笔数据库操作。

方案2：存储已预订时段 #

表结构 #

CREATE TABLE "Calendar"
(
    "id"                       bigserial PRIMARY KEY,
    "calendar_day"             date    NOT NULL,
    "room_id"                  integer NOT NULL,
    "booked_interval_start"    time  NOT NULL,
    "booked_interval_end"      time  NOT NULL,
    "booked"                   boolean NOT NULL DEFAULT true
);

核心特点 #

• 优势：取消预订操作简单，只需删除对应记录或标记为无效，完全不需要处理时段碎片整理。
• 劣势：下单校验时需要检查请求时段与该房间当日所有已预订时段是否冲突，随着已预订记录增多，查询耗时会明显增加。

问题 #

1. 哪种方案更适合房间预订系统的长期发展？
2. 针对第二种方案，有没有有效的方法减少查询量、提升下单校验的效率？

内容的提问来源于stack exchange，提问作者Kostas Oreopoulos