PostgreSQL 滑动窗口限流

示例说明

无 Redis 依赖的限流同样可以严谨：滑动窗口按时间点计数，六十秒内的权重总和即当前用量。判定与写入必须原子——CTE 链把清扫旧记录、统计现用量、条件插入本次尝试合并为一条语句，数据库的快照隔离保证并发请求各自看到一致的窗口状态。

技术要点

• CTE 链原子化清扫加判定加记录
• 滑窗按时间点计数无边界突变
• 剩余额度与重置时间随查询产出

代码解读

pruned 删除一分钟前的过期行顺便减表体积；recent 汇总当前主体的用量；attempt 的 INSERT 带 WHERE NOT EXISTS——用量已达上限时插入零行，allowed 零即拒绝。第二个查询输出剩余额度与窗口重置时刻，正是限流响应头的两个标准字段。索引按主体加时间覆盖滑窗扫描。

使用建议

高 QPS 场景数据库限流是兜底方案，主路径用 Redis。表要定期清理防膨胀（清扫已内建）。权重字段支持重接口扣双份的精细策略。

代码示例

-- PostgreSQL 滑动窗口限流：一条语句原子清理旧记录并判额度
CREATE TABLE rate_limit_buckets (
  subject_key text NOT NULL,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  weight int NOT NULL DEFAULT 1
);

CREATE INDEX idx_rate_subject_time
  ON rate_limit_buckets (subject_key, occurred_at);

-- 原子判定 + 清扫 + 记录：单语句完成限流
WITH pruned AS (
  DELETE FROM rate_limit_buckets
  WHERE occurred_at < now() - interval '60 seconds'
  RETURNING 1 AS removed
),
recent AS (
  SELECT subject_key, SUM(weight) AS used
  FROM rate_limit_buckets
  WHERE subject_key = 'api:user-42'
    AND occurred_at >= now() - interval '60 seconds'
  GROUP BY subject_key
),
attempt AS (
  INSERT INTO rate_limit_buckets (subject_key, weight)
  SELECT 'api:user-42', 1
  WHERE NOT EXISTS (
    SELECT 1 FROM recent WHERE used >= 100
  )
  RETURNING occurred_at
)
SELECT
  (SELECT count(*) FROM attempt) AS allowed,
  (SELECT used FROM recent) AS current_usage,
  (SELECT count(*) FROM pruned) AS pruned_rows;

-- 剩余额度与重置时间：响应头的原料
SELECT
  100 - SUM(weight) AS remaining,
  min(occurred_at) + interval '60 seconds' AS window_resets_at
FROM rate_limit_buckets
WHERE subject_key = 'api:user-42'
  AND occurred_at >= now() - interval '60 seconds';