Rate limiting in Postgres is fine (and dropped a whole backing service)

The reflex says rate limiting lives in Redis: INCR, EXPIRE, SETNX, done. My gateway followed the reflex — an Upstash instance held fixed-window rate counters, daily/monthly budget counters, and PoP replay nonces, in a 382-line redis.ts. The problem wasn’t performance. The problem was that Cookey is a self-hosted, one-click-deploy product, and every backing service multiplies the deployment story: another vendor account, two more environment variables (KV_REST_API_URL, KV_REST_API_TOKEN), another thing that can be misconfigured by every single person who deploys their own instance. For a single-tenant gateway doing one owner’s traffic, Redis was buying microsecond counters nobody needed and charging operational complexity everyone paid.

Commit 1a46be6 deleted Redis entirely. Postgres — already there for everything else — became the only backing service. The interesting part is how little changed semantically: the migration spec’s rule was that the Redis behavior is the reference implementation and must be preserved exactly, just re-expressed in SQL primitives.

Know which side of the line you’re on

The design accepts two constraints openly. First, this is fixed-window limiting, not sliding-window or token-bucket — a client can burst up to 2× the limit across a window boundary, exactly as the Redis version could. Second, every limit check costs a database round-trip. For a personal gateway whose realistic ceiling is a few requests per second, both are fine; if you’re fronting thousands of requests per second across many tenants, this is not your architecture and Redis earns its keep. Know which side of that line you’re on before copying anything below.

The translation

Three Redis idioms map to three SQL idioms.

INCR + compare → upsert with atomic increment. The fixed-window counter (apps/proxy/src/server/limits/rate-limit.ts) keys rows by (key, windowStart) and lets the database do the increment:

export async function checkRateLimit(
  key: string,
  maxRequests: number,
  windowSeconds: number,
): Promise<RateLimitResult> {
  const now = Math.floor(Date.now() / 1000);
  const windowStartSecs = Math.floor(now / windowSeconds) * windowSeconds;
  const windowStart = new Date(windowStartSecs * 1000);

  const count = await incrementCounter(key, windowStart);

  return {
    allowed: count <= maxRequests,
    remaining: Math.max(0, maxRequests - count),
    resetAt: windowStartSecs + windowSeconds,
  };
}

async function incrementCounter(
  key: string,
  windowStart: Date,
  retried = false,
): Promise<number> {
  try {
    const row = await prisma.rateCounter.upsert({
      where: { key_windowStart: { key, windowStart } },
      create: { key, windowStart, count: 1 },
      update: { count: { increment: 1 } },
    });
    return row.count;
  } catch (error) {
    if (
      !retried &&
      error instanceof Prisma.PrismaClientKnownRequestError &&
      error.code === "P2002"
    ) {
      return incrementCounter(key, windowStart, true);
    }
    throw error;
  }
}

The P2002 retry deserves a sentence, because it’s the one non-obvious line: Prisma’s upsert is not atomic under concurrent creates of the same missing row — two requests can both find no row and both attempt the insert. One wins; the loser gets a unique violation and retries once, deterministically taking the update path the second time. If you write raw SQL instead, INSERT … ON CONFLICT DO UPDATE SET count = count + 1 RETURNING count collapses this into one genuinely atomic statement and the retry disappears.

SETNX → bare insert against a unique index. Replay nonces don’t need a counter, only existence: an insert that succeeds is a fresh nonce, a unique violation is a replay (apps/proxy/src/server/limits/nonce.ts). No read-then-write, no race.

EXPIRE → a TTL column plus the sweep. Postgres has no key expiry, so every row carries its cutoff (expiresAt on nonces, windowStart on counters) and a daily cron deletes the dead:

export async function cleanupStaleRateCounters(
  olderThanSeconds: number,
): Promise<number> {
  const cutoff = new Date(Date.now() - olderThanSeconds * 1000);
  const result = await prisma.rateCounter.deleteMany({
    where: { windowStart: { lt: cutoff } },
  });
  return result.count;
}

Crucially, correctness never depends on the cleanup running: an expired nonce row past its timestamp window can’t collide with anything a valid request would send, and a stale rate counter belongs to a window no request will ever key into again. The sweep controls table size, not behavior.

File placement if you’re adapting this: the three modules live under a server/limits/ directory, imported by the request pipeline between auth and the upstream call. Budget counters (daily/monthly quotas) follow the same upsert-increment pattern in budget.ts, keyed by (permissionId, periodType, periodStart), with one semantic carried over verbatim from the Redis version: request counts increment at admission (a denied-over-quota request still consumed its slot), while token counts are checked before the upstream call but incremented only after upstream success — you can’t count tokens you haven’t seen. There are integration tests pinning these semantics in apps/proxy/src/server/limits/__tests__/limits.integration.test.ts.

When Postgres is down, everything is down

When Postgres is down, everything is down — the same database serves auth and grant lookups, so there is no partial-failure mode where limits silently stop being enforced while traffic flows. That’s an underrated property: the Redis version had exactly that mode (Redis unreachable, Postgres fine), and the code had to choose between failing open and failing closed on every limit check. Now the choice doesn’t exist.

Under write contention the upsert retry admits one extra attempt before giving up; a second consecutive P2002 propagates as an error and the request fails closed. And if the sweep stalls, the tables grow — bounded in practice by nonce TTL (180 seconds’ worth of authenticated traffic) and window count, and visible in ordinary database monitoring rather than in a separate vendor’s dashboard.

One stateful system

Deleting a backing service is one of the few refactors whose payoff compounds forever: every future deployment, every environment (dev, demo, prod), every incident review now has one stateful system instead of two. Backups cover the limits state for free. Local development needs no Redis container. The .env.example lost two variables. And the counters became queryable — usage dashboards and spend projections read the same PermissionUsage rows the enforcement path writes, with SQL aggregation instead of a parallel stats pipeline. The reflex that put rate limiting in Redis was never wrong about Redis; it was wrong about whether your system’s scale had earned the second service.