How Do You Implement API Rate Limiting With Database-Backed Counters?
Learn how to implement API rate limiting using atomic database counters, avoid race conditions, and handle window rollover correctly.
Expected Interview Answer
Database-backed rate limiting stores a per-client request counter and a time window in a row, atomically incrementing that counter on each request and rejecting the call once it exceeds a fixed threshold within the window.
A typical design keys a table on client identifier and window start time, using an atomic UPSERT plus increment (or a single UPDATE ... WHERE count < limit) so concurrent requests cannot both read the same stale count and both slip through. When the window expires, a new row starts fresh, either via a rolling window recomputed from timestamps or a fixed window reset on a schedule. The database's atomicity and row-level locking are what make the counter correct under concurrency; a naive read-then-write in application code is a race condition that lets clients exceed the limit.
- Prevents traffic spikes from overwhelming downstream services
- Gives predictable, auditable per-client usage limits
- Atomic increments avoid race conditions under concurrency
- Can be tuned per client tier without code changes
AI Mentor Explanation
A cricket ground allows each team exactly ten reviews per innings, and the umpire's tablet ticks off one review the instant a captain signals for it, refusing any further review once the count hits ten. Two captains cannot both claim the same last review, because the tablet locks and updates the count as one action, not two separate steps. A database-backed rate limiter works identically: it atomically checks and increments a request counter so no client can sneak past the cap during a race.
Step-by-Step Explanation
Step 1
Design the counter table
Create a table keyed by client ID and window start, with a counter column and an expiry timestamp.
Step 2
Atomically check and increment
Use a single UPDATE ... WHERE count < limit statement, or an UPSERT with ON CONFLICT, so the read-check-write happens as one operation.
Step 3
Handle window rollover
When the current window expires, insert a fresh row for the new window rather than reusing a stale count.
Step 4
Reject or throttle over-limit requests
If the atomic update affects zero rows, the request is over quota and the API returns a 429 response.
What Interviewer Expects
- Recognition that read-then-write in application code is a race condition
- A concrete atomic SQL pattern (UPDATE ... WHERE, UPSERT, or similar)
- Awareness of fixed vs sliding window trade-offs
- Discussion of what happens under high concurrency across multiple app servers
Common Mistakes
- Reading the count, checking it in application code, then writing back the increment separately
- Forgetting to expire or roll over old window rows, causing unbounded table growth
- Not indexing the client ID and window columns, causing slow lookups under load
- Assuming a single database instance removes the need for atomic operations
Best Answer (HR Friendly)
“Database-backed rate limiting keeps a counter row per client that tracks how many requests they have made in the current time window, and every request atomically checks and increments that counter in one database operation. Because the check and the increment happen together, two requests arriving at the same instant cannot both slip past the limit, which is the core correctness guarantee this pattern provides.”
Code Example
-- Ensure one row per client per window
CREATE TABLE rate_limits (
client_id TEXT NOT NULL,
window_start TIMESTAMPTZ NOT NULL,
request_count INT NOT NULL DEFAULT 0,
PRIMARY KEY (client_id, window_start)
);
-- Atomically increment only if still under the limit
UPDATE rate_limits
SET request_count = request_count + 1
WHERE client_id = 'client_42'
AND window_start = date_trunc('minute', now())
AND request_count < 100;
-- If this UPDATE affects 0 rows, either the window row
-- does not exist yet (insert it) or the limit is reached (reject).Follow-up Questions
- How would you implement a sliding window instead of a fixed window?
- How does this pattern change if requests come from many stateless app servers?
- What are the trade-offs of using Redis versus a relational table for this counter?
- How would you clean up expired window rows without locking the whole table?
MCQ Practice
1. Why must the rate-limit check and increment happen as a single atomic operation?
A separate read-then-write allows two concurrent requests to both see a count below the limit and both increment, letting the client exceed its quota.
2. In the example UPDATE statement, what does it mean if zero rows are affected?
A WHERE clause with request_count < limit affects zero rows either when the window row does not exist yet or when the count is already at the cap.
3. What is a key downside of a fixed-window counter compared to a sliding window?
A client can send the limit worth of requests at the end of one window and again at the start of the next, producing a burst near the boundary.
Flash Cards
What is a database-backed rate limiter? — A counter row per client and time window that is atomically checked and incremented on each request.
Why is atomicity critical here? — Without it, concurrent requests can race past the limit by both reading a stale count before either writes back.
Fixed window vs sliding window? — Fixed window resets at a boundary and can allow boundary bursts; sliding window recomputes based on rolling timestamps for smoother enforcement.
What SQL pattern enforces this atomically? — A single UPDATE ... WHERE count < limit statement, or an UPSERT with a conditional increment.