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 to add Redis or reach for Upstash, a whole extra service with its own credentials and its own failure mode, in order to count to five. For an indie app already on Supabase that felt excessive. I wanted rate limiting with what I already had, which was Postgres.

What was 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. Doing the check in the client is no check at all, since 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 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.