PostgreSQL按间隔原子插入：多并发进程仅单条插入成功实现方案

针对你的需求——多个并发进程每隔N秒尝试插入scheduled_event_log表，但每个N秒窗口内仅允许一次插入成功——我整理了三种实用的PostgreSQL实现方案，你可以根据业务场景选择：

方案一：基于时间窗口的唯一约束（最直观） #

核心思路是通过时间截断+唯一约束，让同一N秒窗口内的插入触发冲突，从而只保留第一次成功的插入。

步骤1：修改表结构，添加时间窗口生成列 #

我们新增一个存储时间窗口起始时间的生成列，自动根据timestamp_utc计算所属的N秒窗口：

-- 假设N=10秒，可根据实际需求修改%后面的数值
ALTER TABLE scheduled_event_log 
ADD COLUMN window_start timestamp without time zone 
GENERATED ALWAYS AS (
  date_trunc('second', timestamp_utc) - (extract(second from timestamp_utc)::int % 10) * interval '1 second'
) STORED;

-- 给窗口起始时间添加唯一约束
ALTER TABLE scheduled_event_log ADD CONSTRAINT unique_window_start UNIQUE (window_start);

步骤2：进程插入逻辑 #

进程只需执行普通插入，利用数据库默认的UTC时间即可，同一窗口内的重复插入会触发唯一约束错误：

INSERT INTO scheduled_event_log ("key") VALUES ('你的业务key');

应用层处理 #

进程需要捕获IntegrityError（唯一约束冲突），忽略失败即可，等待下一个N秒周期再尝试。

方案二：使用PostgreSQL咨询锁（无需修改表结构） #

咨询锁是轻量级的应用级锁，我们可以基于当前N秒窗口的标识获取锁，只有拿到锁的进程才能执行插入。

插入逻辑示例 #

你可以在事务中结合pg_try_advisory_xact_lock（事务级锁，事务结束自动释放）实现：

BEGIN;
-- 计算当前N秒窗口的哈希值作为锁键（N=10秒）
SELECT pg_try_advisory_xact_lock(hashtext(
  date_trunc('second', now() at time zone 'utc') - (extract(second from now() at time zone 'utc')::int % 10) * interval '1 second'::text
));
-- 成功获取锁则执行插入
IF FOUND THEN
  INSERT INTO scheduled_event_log ("key") VALUES ('你的业务key');
END IF;
COMMIT;

应用层处理 #

进程只需执行上述事务，若pg_try_advisory_xact_lock返回true则插入成功，否则直接跳过本次周期。

方案三：行级锁+存在性检查（复用现有key字段） #

如果你的key字段可以用来存储时间窗口标识，可利用行级锁实现互斥：

INSERT INTO scheduled_event_log ("key", timestamp_utc)
SELECT window_start, now() at time zone 'utc'
FROM (
    -- 计算当前N秒窗口起始时间（N=10秒）
    SELECT date_trunc('second', now() at time zone 'utc') - (extract(second from now() at time zone 'utc')::int % 10) * interval '1 second' AS window_start
) AS t
WHERE NOT EXISTS (
    SELECT 1 FROM scheduled_event_log WHERE "key" = t.window_start::text
)
FOR UPDATE;

FOR UPDATE会锁定查询范围，同一窗口内的其他进程会因为已存在对应行（或锁被占用）而无法插入。

方案对比 #

注意事项 #

1. 时间一致性：所有进程务必使用数据库的UTC时间（即表默认的now() at time zone 'utc'），避免客户端时间不同步导致窗口计算偏差。
2. 错误处理：无论哪种方案，进程都需要处理插入失败的情况（如约束冲突、锁获取失败），确保不中断后续尝试。

内容的提问来源于stack exchange，提问作者Myles McDonnell