The Matrix: How Database Indexing Saved My Rate Limiter

The Quest Begins (The "Why")

I was tasked with protecting a public API from abuse. The idea was simple: give each authenticated user a quota of 100 requests per minute and return 429 Too Many Requests when they go over. I sketched out a rate limiter that wrote a row for every request into a PostgreSQL table:

CREATE TABLE api_requests (
    id          BIGSERIAL PRIMARY KEY,
    user_id     UUID NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

And the check looked like this:

SELECT COUNT(*) 
FROM api_requests 
WHERE user_id = $1 
  AND created_at >= now() - interval '1 minute';

Locally, with a few hundred rows, it felt snappy. Deployed to staging, the latency crept up to 200 ms per request during a modest load test. The API started to feel like a tired hobbit trudging through the Shire—slow, weary, and definitely not ready for battle. I knew the problem wasn’t the logic; it was the way we were asking the database to find the relevant rows.

The Revelation (The Insight)

The “dragon” I needed to slay was a full table scan. Without any help, PostgreSQL had to read every row that matched the user_id condition, then filter by time. As the table grew, that linear scan became the bottleneck.

The secret weapon? An index—specifically a composite B‑tree index on (user_id, created_at). Think of the index as a sorted phone book: you can jump straight to the section for a given user and then flip to the dates you care about, instead of leafing through every page.

Here’s what the index looks like conceptually:

user_id   | created_at
----------------------
abc123    | 2025-10-31 09:00:00
abc123    | 2025-10-31 09:00:05
abc123    | 2025-10-31 09:00:12
...       ...
def456    | 2025-10-31 09:00:01
def456    | 2025-10-31 09:00:07
...

With this structure, the planner can:

1. Locate the first entry for user_id = $1 (log‑N step).
2. Scan forward only until created_at falls outside the one‑minute window.

The cost drops from O(N) to roughly O(log N + M), where M is the number of matching rows (usually tiny for a rate limiter).

Wielding the Power (Code & Examples)

Before: the struggle

-- No index – slow as a snail
EXPLAIN ANALYZE
SELECT COUNT(*) 
FROM api_requests 
WHERE user_id = 'abc123-...' 
  AND created_at >= now() - interval '1 minute';

Output (excerpt)

Seq Scan on api_requests  (cost=0.00..1245.67 rows=1200 width=0)
  Filter: ((user_id = 'abc123-...'::uuid) AND (created_at >= ...))

After: adding the index

-- Migration (run once)
CREATE INDEX idx_api_requests_user_created 
ON api_requests (user_id, created_at);

Now the same query:

EXPLAIN ANALYZE
SELECT COUNT(*) 
FROM api_requests 
WHERE user_id = 'abc123-...' 
  AND created_at >= now() - interval '1 minute';

Output (excerpt)

Index Scan using idx_api_requests_user_created on api_requests  
  (cost=0.42..8.23 rows=10 width=0)
  Index Cond: ((user_id = 'abc123-...'::uuid) AND (created_at >= ...))

The planner now uses an Index Scan and the execution time fell from ~200 ms to 2 ms in my benchmarks—a 100× speed‑up.

Common traps (the “bosses” to avoid)

Trap	Why it hurts	Fix
Missing the column order – creating CREATE INDEX ON api_requests (created_at, user_id)	The index can’t quickly locate a specific user; you still scan many dates.	Put the equality column (user_id) first, then the range column (created_at).
Over‑indexing – adding indexes on every column you think might help	Each write (INSERT) now has to update all indexes, increasing latency and storage.	Index only the columns used together in WHERE/JOIN clauses; monitor write‑throughput.
Forgetting to vacuum – letting dead rows bloat the table	Even with an index, a bloated table means more pages to read.	Schedule regular VACUUM (or rely on autovacuum) in production.

The rate‑limiter code (Node.js/pg)

const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function allowRequest(userId) {
  const oneMinAgo = new Date(Date.now() - 60_000);
  const res = await pool.query(
    `SELECT COUNT(*) FROM api_requests
     WHERE user_id = $1 AND created_at >= $2`,
    [userId, oneMinAgo]
  );
  const count = parseInt(res.rows[0].count, 10);
  return count < 100; // true → allow, false → block
}

async function recordRequest(userId) {
  await pool.query(
    `INSERT INTO api_requests (user_id) VALUES ($1)`,
    [userId]
  );
}

With the index in place, each call to allowRequest stays under a few milliseconds even when the table holds tens of millions of rows.

Why This New Power Matters

That little index turned my rate limiter from a bottleneck into a shield that could handle traffic spikes without breaking a sweat. Latency dropped, CPU usage on the DB server fell, and our cloud bill shrank because we needed fewer read replicas to keep up with demand.

More than the numbers, the feeling was empowering: I’d taken a vague “it’s slow” complaint and turned it into a concrete, measurable improvement using a tool that’s been around since the early days of relational databases. It reminded me that sometimes the biggest wins come not from rewriting the whole system, but from understanding how the data is stored and accessed.

Your Turn

Grab a table in your own project that’s being queried with a filter on two columns (e.g., WHERE account_id = X AND event_time > Y). Run an EXPLAIN to see if it’s doing a sequential scan. If it is, add a composite index like we did and watch the query time plummet.

What’s the first table you’ll index? Drop a comment below—I’d love to hear about your quest and the dragons you slay! 🚀