Rate limiting looks like a solved problem until you write one. Then you find two traps: the algorithm most people reach for permits twice the traffic it advertises, and the natural way to implement the better algorithm in SQL loses writes under exactly the load it exists to survive.
I hit both while building a flood guard for a webhook receiver after a partner platform delivered 487,000 events in eleven hours. Here's what actually works.
Trap 1: the fixed window is a 2× burst
The obvious limiter is a counter and a window start:
if window_started_at < now - 1 hour:
window_started_at = now
count = 1
else:
count += 1
reject if count > limit
Ten thousand an hour, easy to reason about, one row. And it allows twenty thousand requests in two minutes, forever, on a schedule.
A client that spends its full allowance at 11:59 gets a completely fresh
allowance at 12:01. The window doesn't slide; it resets wholesale. So the
worst-case instantaneous rate is 2 × limit delivered across the boundary, and
a client that discovers this — deliberately or by accident, because its cron
fires on the hour like everyone else's — gets to do it every hour.
That matters because damage is done by instantaneous rate, not by hourly total. Nothing falls over because it processed 10,000 events in an hour. It falls over because 10,000 arrived in ninety seconds and the queue depth went vertical.
The token bucket
The bucket separates the two numbers a fixed window conflates:
- rate — how fast allowance accrues (sustained throughput)
- burst — how much allowance can pile up (instantaneous ceiling)
You hold tokens and refilled_at. On each request you add the elapsed time's
worth of tokens, cap at burst, and spend one:
tokens = min(burst, tokens + elapsed_seconds × rate) − 1
allowed = tokens >= 0
There is no boundary to game because there is no boundary. A client that idles
accrues at most burst, and a client that hammers gets exactly rate.
Two details that matter in practice:
Pick burst deliberately. Setting burst = rate × 3600 reproduces the fixed
window's problem — an idle client can spend a full hour's allowance in one
second. A minute of the rate is a good default: enough that a legitimate batch
doesn't trickle, small enough that the instantaneous ceiling is 1/60th of the
hourly one.
Floor the debt. If you let tokens go arbitrarily negative, a client that
floods for eleven hours accrues eleven hours of debt and stays locked out long
after it stops. Clamp at -1: overshooting costs exactly one token, so
recovery is prompt but not free.
Trap 2: the SQL that loses writes
Here is the implementation that looks right and is wrong:
WITH refilled AS (
SELECT LEAST(:burst, tokens + EXTRACT(EPOCH FROM (:now - refilled_at)) * :rate)
AS tokens
FROM buckets WHERE id = :id
)
UPDATE buckets b
SET tokens = r.tokens - 1, refilled_at = :now
FROM refilled r
WHERE b.id = :id
RETURNING b.tokens;
One statement, so it must be atomic — right?
No. The CTE performs a read against the statement's snapshot, and the
UPDATE then writes a value derived from that read. Two concurrent requests
both read tokens = 5 and both write 4. You have spent two tokens and
recorded one. Under the burst you are trying to survive, the counter drifts
increasingly optimistic and the limiter stops limiting.
This is a read-modify-write wearing a single statement as a disguise. I have watched the same shape, in an unrelated counter in the same codebase, lose 86% of its increments under eight concurrent writers.
The version that works
Reference the column in its own SET expression, with no join and no CTE:
UPDATE buckets
SET tokens = GREATEST(
-1.0,
LEAST(:burst, tokens + EXTRACT(EPOCH FROM (:now - refilled_at)) * :rate) - 1
),
refilled_at = :now
WHERE id = :id
RETURNING tokens;
Under READ COMMITTED, when this UPDATE finds a row another transaction has
concurrently modified, Postgres blocks on the row lock, then re-reads the
updated row and re-evaluates the SET expressions against it (the
EvalPlanQual path). tokens on the right-hand side is the winner's value, not
the stale one. Concurrent spenders serialize instead of racing.
The rule generalises: SET x = f(x) is concurrency-safe; SET x = <value read in a separate scan> is not. UPDATE … FROM another_table is fine. UPDATE … FROM the_same_row is the trap.
A non-negative balance after spending means the token was there to spend, so the return value is both the decision and the new state — one round trip, no transaction, no advisory lock, no Redis.
Put the bucket in its own table
The tempting shortcut is to hang tokens and refilled_at off the row you
already have — the account, the API key, the integration. Don't, if that row is
wide.
Those rows usually carry JSON config, arrays, long text. Updating one on every single request means:
- TOAST churn — out-of-line values get rewritten or re-referenced
- index maintenance on every index covering the table
- bloat proportional to request volume, on a row that is also read constantly
A dedicated three-column table (scope, tokens, refilled_at) keeps updates
HOT — the new tuple version lives on the same page, no index touches
required — and gives autovacuum far less to chase on the hottest write in the
system.
It also lets one implementation serve several scopes. Per-key limits alone don't bound an account: ten keys at 9,999/hour each is 100,000/hour with nothing over its limit. With a generic bucket table you check the account bucket first (cheaper rejection, and the only one that sees a fan-out), then the key's.
Rate-limit your own alerting
The limiter that rejects silently is barely better than no limiter — you find out when a customer asks why their data is stale.
But the naive fix is worse. Alerting from the rejection path means an eleven-hour flood pages you fifty thousand times, which is functionally identical to never paging you at all.
Decide inside the same statement, comparing against the pre-update
timestamp. RETURNING hands back new values, so returning
notified_at = :now reports "notified" on every call that happens to carry the
same clock reading. Read the old value with a self-join:
UPDATE buckets AS cur
SET throttled_count = cur.throttled_count + 1,
notified_at = CASE
WHEN old.notified_at IS NULL OR old.notified_at <= :cutoff
THEN :now ELSE old.notified_at END
FROM buckets AS old
WHERE cur.id = :id AND old.id = :id
RETURNING (old.notified_at IS NULL OR old.notified_at <= :cutoff) AS notified;
Yes — this reintroduces the read-modify-write for throttled_count, so that
counter can undercount under heavy concurrency. That is an acceptable trade
when it is a visibility counter and the tokens are what enforce anything.
Know which of your numbers has to be exact.
Return 429, not disabled
Last one, and it is a product decision more than a technical one.
When a client floods you, flipping it to disabled is tempting and usually
wrong. Back-pressure is recoverable and self-clearing: the bucket refills, the
client comes back, nobody does anything. A disabled integration needs a human
to notice and re-enable it, and the failure mode is a silent multi-day outage
for a customer whose only crime was a bad afternoon.
Send 429 with a Retry-After computed from the bucket —
(1 − tokens) / rate, floored at one second so a client honouring the header
can't busy-loop on sleep 0. Then make sure someone can see that it is
happening.
Sizing the limit is a capacity question, not a taste question. Work out the arrival rate your workers can actually drain with Little's Law before you pick a number.