Postgres可重复事件与时间槽冲突检测优化方案问询

1. Postgres 内置能力实现方案 #

Postgres没有专门针对重复事件冲突检测的内置函数，但可以利用递归CTE、时间范围类型（tstzrange）和提前过滤逻辑避免生成全量时间槽，直接快速检测冲突。

核心思路 #

先通过事件的整体时间范围做粗过滤，排除完全不重叠的事件；再对剩余可能冲突的事件，用递归CTE逐个生成时间槽，但一旦找到第一个冲突就停止递归（用LIMIT 1），无需生成所有槽。

示例查询 #

假设待检测事件参数为：new_room_uuid, new_start, new_duration_min, new_repeat_min, new_end，可以用以下SQL快速检测冲突：

-- 检查是否存在冲突，返回1则有冲突，无结果则无冲突
SELECT 1
FROM event e
-- 先做粗过滤：两个事件的整体时间范围有交集
WHERE e.room_uuid = '${new_room_uuid}'
  AND tstzrange(e.start_date, e.end_date + e.duration_min * INTERVAL '1 minute') 
      && tstzrange('${new_start}', '${new_end}' + '${new_duration_min}' * INTERVAL '1 minute')
-- 递归生成已有事件的时间槽，找到第一个重叠就停止
JOIN LATERAL (
    WITH RECURSIVE event_slots AS (
        SELECT 
            e.start_date AS slot_start,
            e.start_date + e.duration_min * INTERVAL '1 minute' AS slot_end
        UNION ALL
        SELECT 
            es.slot_start + e.repeat_every_min * INTERVAL '1 minute',
            es.slot_end + e.repeat_every_min * INTERVAL '1 minute'
        FROM event_slots es
        WHERE es.slot_start + e.repeat_every_min * INTERVAL '1 minute' <= e.end_date
    )
    SELECT 1
    FROM event_slots es
    WHERE tstzrange(es.slot_start, es.slot_end) 
          && tstzrange('${new_start}', '${new_start}' + '${new_duration_min}' * INTERVAL '1 minute')
    LIMIT 1 -- 找到第一个冲突立即终止
) conflict_check ON true
LIMIT 1;

进阶：数学公式直接判断（适合固定间隔重复） #

如果你的重复事件是固定间隔（如每天、每周），可以通过数学计算直接判断两个重复序列是否存在重叠，完全跳过时间槽生成：
对于事件A（起始sA、间隔iA、时长dA、结束eA）和事件B（sB、iB、dB、eB），若满足以下条件则存在冲突：

1. 两个事件的整体时间范围有交集：sA <= eB + dB 且 sB <= eA + dA
2. 存在整数n、m使得：
   sA + n*iA < sB + m*iB + dB
sB + m*iB < sA + n*iA + dA
   且 sA + n*iA + dA <= eA，sB + m*iB + dB <= eB

可以通过求解线性同余方程快速验证，避免递归，但实现复杂度较高，适合规则化的重复场景。

2. 其他技术解决方案 #

优化时间槽表索引 #

在event_time_slot表上创建GIST索引，针对时间范围做快速重叠查询，即使存在大量时间槽，索引也能大幅提升查询效率：

CREATE INDEX idx_event_slot_range ON event_time_slot 
USING GIST (tstzrange(slot_start, slot_end), room_uuid);

后续冲突检测直接查询索引，无需全表扫描。

预计算+延迟生成 #

不要一次性生成所有重复事件的时间槽，仅预计算近期（如未来3个月）的槽，远期槽在需要时动态生成。冲突检测时只需检查近期槽，大幅减少内存占用和数据量。

第三方扩展辅助 #

使用Postgres的pg_rrule扩展，它支持解析iCalendar的RRULE格式（标准重复事件规则），可以直接判断两个重复序列是否重叠，无需手动生成时间槽。

分批次处理 #

如果必须生成时间槽，采用分批次生成+查询的方式：每次生成100-200个槽，查询是否有冲突，若有则立即终止，若无则继续下一批，避免一次性加载大量数据到内存。

内容的提问来源于stack exchange，提问作者46ca1d40-6ee9-4569-b0fc-d53ae0