PostgreSQL Sliding Window Rate Limit

-- 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';