The Indexing Awakens: A Star Wars Guide to Database Indexing

The Quest Begins (The "Why")

I was building a tiny rate‑limiter for a side‑project API. The idea was simple: every time a request came in, I’d insert a row into a requests table with user_id, endpoint, and ts (timestamp). To decide whether to allow or block, I’d count how many rows existed for that user in the last minute.

At first, with a few hundred rows, everything felt snappy. Then I invited a few friends to hammer the endpoint with a script that simulated 10 k requests per second. My API started to choke, latency spiked to seconds, and the CPU on my DB node went through the roof. I opened the slow‑query log and saw the same statement over and over:

SELECT COUNT(*) 
FROM requests 
WHERE user_id = $1 
  AND ts >= NOW() - INTERVAL '1 minute';

It was doing a full table scan on a table that was growing by millions of rows a day. I felt like Luke staring at the Death Star trench run—there had to be a better way to hit the target without getting blown up.

The Revelation (The Insight)

The magic wasn’t in rewriting the query; it was in giving the database a map to find the right rows fast. An index is essentially a sorted data structure (usually a B‑tree) that lets the engine jump straight to the subset of rows matching a predicate, instead of scanning every row.

For our rate‑limiter, the predicate is user_id = ? AND ts >= ?. If we create an index that starts with user_id and then orders by ts, the database can:

1. Locate the first entry for that user_id (log‑N hop).
2. Walk forward in the index until the timestamp falls outside the one‑minute window.

No need to touch rows belonging to other users, no need to sort or hash anything on the fly.

Here’s a quick ASCII sketch of what a B‑tree index looks like for our composite key (user_id, ts):

          [ (user_id, ts) ]
         /        |        \
   user_id=1    user_id=2   user_id=3
     |            |            |
  ts:10  ts:20  ts:15  ts:25  ts:5  ts:30

When we ask for user_id=2 AND ts >= NOW()-1m, the engine jumps straight to the user_id=2 node and then walks the timestamp leafs in order—boom, we have our count in microseconds.

Wielding the Power (Code & Examples)

The Struggle – No Index

-- Table definition (no indexes)
CREATE TABLE requests (
    id          BIGSERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL,
    endpoint    TEXT    NOT NULL,
    ts          TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- The slow query we ran every request
EXPLAIN ANALYZE
SELECT COUNT(*) 
FROM requests 
WHERE user_id = 42 
  AND ts >= NOW() - INTERVAL '1 minute';

Explain output (truncated):

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

A sequential scan (Seq Scan) over the whole table—ouch.

The Victory – Adding the Index

-- Create a composite index that matches our query pattern
CREATE INDEX idx_requests_user_ts 
    ON requests (user_id, ts DESC);

Note the DESC on ts. Storing timestamps in descending order lets the engine stop scanning as soon as it hits a row older than the window, because everything after that point is guaranteed to be out of range.

Now the same query:

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

Explain output (truncated):

Aggregate  (cost=8.42..8.43 rows=1 width=8)
  ->  Index Scan using idx_requests_user_ts on requests  (cost=0.42..8.42 rows=12 width=0)
        Index Cond: ((user_id = 42) AND (ts >= (now() - '00:01:00'::interval)))

We went from a sequential scan (cost >100k) to an index scan (cost ~8). In my benchmark, latency dropped from ~210 ms per request to ~1.3 ms—a 160× speed‑up. The index added ~15% storage overhead, but the write penalty was negligible because our insert rate was far lower than the read rate for the limiter.

Common Traps (the “Boss Levels”)

1. Index column order matters – If I’d created (ts, user_id), the engine would still have to scan all timestamps within the window across all users before filtering by user_id. Always put the equality column first (user_id) then the range column (ts).

2. Over‑indexing kills writes – Adding indexes on every column you think you might query can turn inserts into a slog. Measure your workload; for a write‑heavy logging table you might skip indexes altogether and rely on periodic aggregation.

3. Forgetting to keep stats fresh – After a massive bulk load, run ANALYZE; (or let autovacuum do its job) so the planner knows the index is actually useful. I once ignored this and wondered why the planner kept choosing a seq scan—turns out the stats were stale.

Why This New Power Matters

With a proper index in place, my rate‑limiter could survive traffic spikes that used to melt my database. The same principle applies to any system that needs fast look‑ups by a combination of equality and range predicates:

• Cache lookup tables – find a key plus a version timestamp.
• Feature flag stores – check user_id and release_date.
• Leaderboards – fetch scores for a given game mode within a time window.

The insight is simple: design your index to mirror the shape of your most frequent query. Once you do that, the database does the heavy lifting, and you get to focus on building features rather than fighting performance dragons.

Your Turn – The Challenge

Grab a table you use for a frequent “count‑last‑N‑minutes” or “get‑latest‑by‑user” query. Run EXPLAIN ANALYZE on it today, notice if it’s doing a sequential scan, then add a composite index that matches your query pattern. Measure the before/after latency—share your numbers in the comments!

If you’re feeling daring, try experimenting with index INCLUDE columns or covering indexes and see how they affect both read speed and write overhead. May your queries be swift and your indexes ever‑balanced. Happy indexing!