Rate limiting in Postgres, no Redis

The symptom

A handful of endpoints in my Supabase app needed a cap: joins, uploads, an expensive edge function. The standard advice is “add Redis” or reach for Upstash, a whole extra service with its own credentials and its own failure mode, to count to five. For an indie app already on Supabase that felt absurd. I wanted rate limiting with what I already had: Postgres.

What was actually happening

The naive version has a race. “Count this user’s recent calls, and if it is under the limit, insert one” is two statements, and under concurrent requests two of them can both read four, both decide they are fine, and both write, so the user gets six. And doing the check in the client is no check at all: anyone can call the API directly and skip it.

The fix

One table keyed by (user, action, time window), and a single atomic upsert that increments and reports the new count in the same statement, so there is no gap to race:

create table rate_limit_counters (
  user_id uuid, action text, window_start timestamptz,
  count int not null default 0,
  primary key (user_id, action, window_start)
);

create function check_rate_limit(p_action text, p_max int, p_window_secs int default 60)
returns boolean language plpgsql security definer set search_path = public as $$
declare
  v_window timestamptz := to_timestamp(floor(extract(epoch from now()) / p_window_secs) * p_window_secs);
  v_count  int;
begin
  insert into rate_limit_counters (user_id, action, window_start, count)
  values (auth.uid(), p_action, v_window, 1)
  on conflict (user_id, action, window_start)
  do update set count = rate_limit_counters.count + 1
  returning count into v_count;

  return v_count <= p_max;
end; $$;

INSERT ... ON CONFLICT DO UPDATE ... RETURNING is one atomic operation, so concurrent calls serialise on the primary key and each gets a correct, distinct count. The function is security definer and keys off auth.uid(), so a user can only ever increment their own counter, and the table has no client-facing policies at all. The client just asks:

const { data: allowed } = await supabase.rpc("check_rate_limit", {
  p_action: "group_join", p_max: 5, p_window_secs: 60,
});
if (!allowed) throw new Error("Too fast, try again in a minute.");

The lesson

You do not need Redis to rate-limit a Postgres app. An atomic upsert that returns the count is race-safe by construction, and a security-definer function keyed to the user makes it impossible to bypass from the client or to spoof someone else’s counter.