The Quest Begins (The "Why")

I was building a simple rate limiter for a side‑project API. The idea was dumb‑simple: every time a request came in, I’d insert a row into a request_log table with the user ID and a timestamp, then count how many rows existed in the last minute to decide whether to allow or block the call.

At first, with a handful of requests per second, it felt like magic. Then the traffic started to creep up—first a few hundred, then a few thousand requests per second. The API latency began to climb, and the database CPU spiked. I opened the slow‑query log and saw the same offender over and over:

SELECT COUNT(*) 
FROM request_log 
WHERE user_id = 123 
  AND request_time > NOW() - INTERVAL 1 MINUTE;

The planner was doing a full table scan on request_log every single time. It was like trying to find a specific LEGO brick in a dumpster after a party—possible, but painfully slow and exhausting. I knew I needed a better way to locate the relevant rows without dragging the whole table through the mud.

The Revelation (The Insight)

The breakthrough came when I remembered what a database index actually does: it creates a searchable structure (usually a B‑tree) that lets the engine jump straight to the rows that match a predicate, instead of scanning everything.

For my rate limiter, the query always filters on two columns:

1. user_id – an equality check
2. request_time – a range check (greater than a sliding‑window start time)

If I create a composite index on (user_id, request_time), the B‑tree first narrows down to the exact user, then walks the ordered timestamps to find the window’s start. The engine can then count the entries in that tiny slice without touching unrelated rows.

Here’s an ASCII sketch of what the index looks like:

user_id | request_time
-------------------------
   1    | 2025-09-24 10:00:01
   1    | 2025-09-24 10:00:03
   1    | 2025-09-24 10:00:07
   2    | 2025-09-24 10:00:02
   2    | 2025-09-24 10:00:05
   ...

The index stores the rows sorted first by user_id, then by request_time. When the planner sees user_id = 123 AND request_time > …, it jumps to the first entry for user 123, then scans forward until the timestamp falls out of the one‑minute window—nothing else.

Trade‑offs (because nothing is free):

In practice, the write penalty is negligible compared to the win we get on the read‑heavy rate‑limiting path. The alternative—creating two single‑column indexes (user_id and request_time)—doesn’t help much because the planner can only use one of them per query, leading to a bitmap‑or‑filter step that still ends up scanning many rows. The composite index is the sweet spot.

Wielding the Power (Code & Examples)

The “before” – no index (the struggle)

-- Table definition (simplified)
CREATE TABLE request_log (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    request_at  TIMESTAMPTZ NOT NULL
);

-- The painful query
EXPLAIN ANALYZE
SELECT COUNT(*)
FROM request_log
WHERE user_id = 42
  AND request_at > NOW() - INTERVAL '1 minute';

Typical output (on a table with ~10 M rows):

Aggregate  (cost=124567.89..124567.90 rows=1 width=8)
  ->  Seq Scan on request_log  (cost=0.00..124567.89 rows=1245 width=0)
        Filter: ((user_id = 42) AND (request_at > (now() - '00:01:00'::interval)))

A sequential scan — every row inspected.

The “after” – adding the composite index (the victory)

-- Create the magic index
CREATE INDEX idx_request_log_user_time
    ON request_log (user_id, request_at);

Now the same query:

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM request_log
WHERE user_id = 42
  AND request_at > NOW() - INTERVAL '1 minute';

Yields something like:

Aggregate  (cost=8.42..8.43 rows=1 width=8)
  ->  Index Scan using idx_request_log_user_time on request_log  (cost=0.42..8.42 rows=10 width=0)
        Index Cond: ((user_id = 42) AND (request_at > (now() - '00:01:00'::interval)))

The planner now does an index scan, touching only the rows that match the user and the time window—often just a handful.

Common pitfalls (the traps to avoid)

1. Wrong column order – CREATE INDEX ON request_log (request_at, user_id);

   The index would first sort by timestamp, making the equality on user_id useless for pruning; you’d still scan a large range of timestamps.

2. Over‑indexing – adding indexes on every column you ever query.

   Each extra index slows down inserts and updates. For a rate limiter, the composite index above is usually enough; you rarely need a separate index on just request_at.

3. Forgetting to purge old data – without a periodic DELETE, the table (and index) will grow forever.

   A cleanup job that uses the same indexed columns stays cheap:

   DELETE FROM request_log
   WHERE request_at < NOW() - INTERVAL '1 hour';

Because the leading column in the index is user_id, you might think this delete would be slow, but PostgreSQL can still use the index on the trailing column (request_at) when the leading column isn’t constrained—though it’s less efficient. A better approach is to partition by time or add a secondary index on request_at just for cleanup, but that’s a topic for another adventure.

Why This New Power Matters

With the composite index in place, my rate limiter went from hundreds of milliseconds per request under load to sub‑millisecond latency, even at 10 k RPS. The database CPU dropped from a constant 80 % to under 15 %, freeing up resources for the rest of the application.

More importantly, the design scales. As the user base grows, the index keeps the lookup cost logarithmic rather than linear, so latency stays flat while throughput climbs. The trade‑off—a modest increase in write latency and disk usage—is a tiny price to pay for the massive win on the read path, which is exactly what a rate limiter needs: lots of reads, relatively few writes (the inserts themselves are still cheap compared to the scan they replaced).

If you’re building any feature that filters on an equality column plus a range column (think: “show me the latest N events for a given user”, “find active sessions in the last 5 minutes”, “count recent login attempts”), ask yourself: do I have a composite index that matches that pattern? If not, you’re probably doing a full table scan and leaving performance on the table.

Your turn

Grab a table in your own project that you query with a pattern like WHERE const_col = ? AND range_col > ?. Run EXPLAIN ANALYZE before and after adding a composite index. Measure the difference in latency and CPU. Share your numbers in the comments—I’d love to hear how the index changed your game!

Now go forth, add that index, and watch your queries dodge the bullet like Neo in the Matrix. 🚀