Indexing Like a Jedi: Mastering Database Speed

The Quest Begins (The "Why")

I still remember the night our API started to choke. Users were complaining about latency spikes every time a burst of traffic hit our rate‑limiter endpoint. The limiter was simple: for each user ID we stored a row with a timestamp of every request, then we counted how many rows fell inside the last minute. The query looked like this:

SELECT COUNT(*)
FROM request_log
WHERE user_id = :uid
  AND ts >= NOW() - INTERVAL '1 minute';

At first the table was tiny, so the query flew. But as we grew, the table swelled to millions of rows. Suddenly each request was doing a full table scan, and our latency went from a few milliseconds to over a second. I felt like I was stuck in a boss fight where the boss kept gaining health every time I swung my sword — frustrating and utterly demoralizing.

We tried throwing more hardware at the problem, we sharded the table, we even cached the counts in Redis, but the core issue remained: the database was doing too much work to find the tiny slice of data we actually needed.

The Revelation (The Insight)

The breakthrough came when I realized we weren’t asking the database to do anything exotic. We just needed to locate, for a given user_id, all rows whose ts fell within a recent window. If the rows were physically stored together, the engine could jump straight to the right spot and scan only a handful of rows instead of millions.

That’s exactly what a B‑tree index does. When we create an index on (user_id, ts), the database builds a sorted structure where:

1. All rows for the same user_id are grouped together.
2. Within each user_id group, the rows are ordered by ts.

So the query engine can:

• Navigate the tree to the first user_id = :uid entry (logarithmic time).
• Then walk forward until ts falls outside the window — again, only the relevant rows.

The magic is that the index is covering for our query: it contains both columns we filter on, so the engine never has to touch the main table (the heap) at all. No more full scans, no more random I/O.

Trade‑off? Indexes cost write performance and storage. Every insert now has to update the B‑tree, which adds a little overhead. But for a rate limiter where reads vastly outnumber writes (we read on every request, we write only when a request arrives), the trade‑off is a net win. And the storage cost? A few extra megabytes for millions of rows — peanuts compared to the savings in latency.

Here’s a quick ASCII picture of what the index looks like:

(user_id, ts) B‑tree (simplified)

          [ (100, 12:00) ]
         /                \
   [(100,11:58) ...]      [(101,12:01) ...]
   /        \               /        \
 [...(100,11:55) ]   ...   [...(101,11:59) ] ...

All rows for user_id = 100 sit together, ordered by timestamp. When we ask for the last minute, the engine hops to the first 100 entry and scans forward until the timestamp is too old.

Wielding the Power (Code & Examples)

Before – The Struggle

-- No index (or a single-column index on just user_id)
EXPLAIN ANALYZE
SELECT COUNT(*)
FROM request_log
WHERE user_id = 42
  AND ts >= NOW() - INTERVAL '1 minute';

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

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

A sequential scan — every row is read, checked, and discarded if it doesn’t match. Latency: ~800 ms.

After – The Victory

-- Create the composite B‑tree index
CREATE INDEX idx_request_log_user_ts
    ON request_log (user_id, ts);

Now the same query:

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

Typical output:

Index Scan using idx_request_log_user_ts on request_log  (cost=0.42..12.34 rows=1 width=8)
  Index Cond: ((user_id = 42) AND (ts >= now() - '00:01:00'::interval))

The planner now does an index scan — only the matching leaf nodes are visited. Latency drops to ~2 ms. That’s a 400× speed‑up!

Common Traps to Avoid

1. Indexing the wrong column order – An index on (ts, user_id) would still help the timestamp filter, but the engine would have to scan all timestamps in the range and then filter by user_id. For a high‑cardinality user_id column, that’s wasteful.
2. Forgetting to keep the index covering – If you add extra columns to the SELECT (e.g., SELECT user_id, ts, ip) that aren’t in the index, the engine must jump back to the heap for each row, eroding the gain. Keep the index covering for the columns you actually need.
3. Over‑indexing – Adding indexes on every column you think might be useful hurts write throughput and bloats storage. Benchmark! In our limiter, the single composite index gave us the best read/write balance.

Why This New Power Matters

With the right index in place, our rate limiter went from a liability to a smooth, predictable component. We could handle traffic spikes without auto‑scaling the database layer, and we saved on cloud costs because the existing instance could sustain a much higher QPS.

More broadly, the lesson is portable: whenever you have a query that filters on equality on one column and a range on another (think “find all events for this user in the last hour”, “get products in this price range for a given store”, “lookup logs for a service within a time window”), a composite B‑tree index on the equality column first, then the range column, is often the winning move.

It’s like discovering a hidden shortcut in a maze — suddenly the path is clear, the monsters (slow queries) are gone, and you can focus on building the next feature instead of fighting the database.

Your Turn

Grab a table in your own project where you’re doing an equality + range filter. Run EXPLAIN ANALYZE on the current query, add a composite index following the rule “equality first, range second”, and watch the plan flip from a Seq Scan to an Index Scan. Share your before/after numbers in the comments — I’d love to hear how much you shaved off your latency!

Happy indexing, and may your queries always be swift! 🚀