Rate Limiting API Routes Without Redis (Using Postgres)

Rate limiting without Redis is practical for most small apps: record each hit in a Postgres table, count the caller's hits in a sliding window inside one database function, reject at the limit, and serialize concurrent checks for the same user with an advisory lock so two requests can't both slip through. Clean old rows with a scheduled job. One small query per request, no extra service. Here's LetRelay's limiter, and where it stops being the right tool.

Why limit at all

Rate limits protect three things:

• Money and quota. AI endpoints spend tokens. On a free tier, one user sending a message every second can exhaust a model's daily limit — Groq's free plan documents 200,000 tokens per day for openai/gpt-oss-20b — and break the feature for everyone.
• Accounts. Login, sign-up and password-reset forms attract credential stuffing and email bombing.
• Everyone else. One noisy client shouldn't slow the database for all tenants.

The standard response is HTTP 429 Too Many Requests, defined in RFC 6585, optionally with a Retry-After header telling the client when to try again.

Why not just use Redis?

Redis (or a managed equivalent) is the classic answer: atomic increments with expiry, very fast. But it's one more service with its own credentials, limits and failure modes, and free tiers of hosted Redis come with their own caps. If you already have Postgres, a limiter that needs a few hundred checks per minute — not tens of thousands per second — fits comfortably inside it.

The algorithm: sliding-window log

Common algorithms:

For low volume, the sliding-window log is the easiest to get exactly right, and it gives you something the others don't: an audit trail of hits.

The table

create table public.rate_hits (
  user_id uuid not null,
  bucket  text not null,
  at      timestamptz not null default now()
);
create index on public.rate_hits (user_id, bucket, at);
alter table public.rate_hits enable row level security;  -- no policies: nobody reads it directly

Row-level security is on with no policies, so no API role can read or write it; only the limiter function can. If users could delete their own hits, the limiter would be decorative.

The function

This is the shape of LetRelay's take_rate():

create or replace function public.take_rate(p_bucket text)
returns boolean language plpgsql security definer set search_path = public
as $$
declare
  v_me uuid := auth.uid();
  v_burst int; v_burst_sec int; v_day int;
  n_burst int; n_day int;
begin
  if v_me is null then return false; end if;
  case p_bucket
    when 'assistant'     then v_burst := 12; v_burst_sec := 60;  v_day := 300;
    when 'assistant_act' then v_burst := 15; v_burst_sec := 60;  v_day := 200;
    else raise exception 'unknown rate bucket %', p_bucket;
  end case;
 
  -- one check at a time per user+bucket, so concurrent requests can't both pass
  perform pg_advisory_xact_lock(hashtextextended(v_me::text || ':' || p_bucket, 0));
 
  select count(*) filter (where at > now() - make_interval(secs => v_burst_sec)), count(*)
    into n_burst, n_day
    from public.rate_hits
   where user_id = v_me and bucket = p_bucket and at > now() - interval '1 day';
 
  if n_burst >= v_burst or n_day >= v_day then return false; end if;
  insert into public.rate_hits (user_id, bucket) values (v_me, p_bucket);
  return true;
end $$;

Four details carry the weight:

1. Identity comes from auth.uid(), not an argument. A caller can't spend someone else's budget or dodge their own.
2. Limits live in the function, not in the caller. The app can't pass limit = 1000000.
3. Two windows: a burst limit (12 per minute for the assistant) and a daily cap (300). The burst limit stops floods; the daily cap stops slow, steady abuse.
4. The advisory lock. Without it, two simultaneous requests can both count 11 hits, both pass, and both insert — a classic check-then-act race. pg_advisory_xact_lock on a hash of user and bucket serializes checks for that key only, releasing automatically at the end of the transaction. Other users aren't blocked.

Test it the way it will be attacked — concurrently. LetRelay's direct-message limiter is checked with a flood of 60 simultaneous sends against a limit of 40: exactly 40 go through and 20 are refused. A sequential test would never find the race.

Calling it from a Route Handler

const { data: allowed } = await supabase.rpc("take_rate", { p_bucket: "assistant" });
if (!allowed) {
  return NextResponse.json(
    { ok: false, error: "You're sending messages quickly — try again in a minute." },
    { status: 429, headers: { "Retry-After": "60" } },
  );
}

Call it before the expensive work (the AI call), and with the user's own client so auth.uid() is set. Tell the user plainly what happened; a silent failure looks like a bug. Rate limits are one layer of protecting a shared AI quota; the others — failover between providers and cooldowns on 429s — are in a free AI gateway with Gemini and Groq failover.

Limits are part of the product, not only security

A limit users hit during normal work is a bug. Set burst limits above what a fast human does (a dozen chat messages a minute is plenty for a person, and nothing for a script), and daily caps above a heavy day's use. Then watch how often real users hit them: if anyone legitimate is refused weekly, the limit is wrong.

The same machinery enforces plan limits — "50 AI assists a month on the Free plan" is a rate limit with a monthly window and an upgrade button instead of a retry. That side is covered in enforcing a freemium plan gate in the database.

Cleanup

Rows older than the longest window are useless. A daily pg_cron job deletes them:

select cron.schedule('purge-rate-hits', '17 3 * * *',
  $$delete from public.rate_hits where at < now() - interval '1 day'$$);

At a few hundred hits per user per day, the table stays small.

Per-IP limits for auth forms

Login, sign-up and password reset happen before there's a user, so the key is the client IP. LetRelay keeps a separate auth_attempts table (service-role only) and checks it in the server actions for those forms. Two decisions worth copying:

• Key on IP only, not IP + email. Keying on email lets an attacker lock a victim out by failing their logins on purpose.
• Fail open on limiter errors. If the limiter itself can't reach the database, let the attempt proceed (Supabase Auth has its own limits behind it). A limiter outage shouldn't become a login outage.

Behind a proxy or CDN, read the client IP from the header your platform guarantees, and don't trust arbitrary X-Forwarded-For values a client can set.

When you do need Redis (or something else)

• High volume. Thousands of checks per second will load your database. An in-memory store is built for it.
• Edge limits before your app. Blocking abusive traffic at the CDN or firewall is cheaper than rejecting it in your app.
• Global limits across regions with very low latency.

For an internal tool or early SaaS, none of these usually apply yet. Start in Postgres, measure, and move when the numbers say so.

FAQ

Can Postgres handle rate limiting?

For modest traffic, yes. A sliding-window check is one indexed count and one insert. At very high request rates, a dedicated in-memory store is more appropriate.

How do I prevent race conditions in a database rate limiter?

Serialize checks for the same key — for example with pg_advisory_xact_lock on a hash of the user and bucket — or use an atomic upsert-and-compare. Otherwise concurrent requests can all pass the same check.

What status code should a rate limit return?

429 Too Many Requests, ideally with a Retry-After header and a clear human-readable message.

Should I rate limit by user or by IP?

By user for signed-in actions, by IP for unauthenticated forms like login and sign-up. Avoid keying on email for auth limits, which lets attackers lock out victims.

Sources

• RFC 6585 — Additional HTTP Status Codes (429)
• MDN — 429 Too Many Requests
• MDN — Retry-After
• Supabase — pg_cron
• Wikipedia — Token bucket
• Groq — Rate limits