Episode 24 · 500 milliseconds per guest

Indexes: from 500 ms to 10 ms

Monday, 9:05: the "My bookings" page takes 500 ms and the database CPU sits at 90%. Nobody changed the code; since last week the page reads the new bookings table: 5 million rows, and only a primary key.

Why one small change can make a slow page 50 times faster, and why you shouldn't make that change everywhere.

30 min intermediateDatabase indexesComposite indexes and column orderSelectivity and the query plannerReading EXPLAIN query plansThe write and storage cost of indexesBig-O intuition: O(n) vs O(log n)

Problem
The "My bookings" page takes 500 ms and the database CPU sits at 90% as the bookings table passes 5 million rows.
Why the simple system fails
Without an index, every lookup reads the whole table: work grows linearly with data, so a query that was instant at launch gets slower every day and burns CPU for everyone else.
Engineering concept
Keep a sorted structure (a B-tree) of the filtered columns that points to rows, so the database jumps to matches in a handful of page reads instead of scanning.
Technology
B-tree indexes in PostgreSQL/MySQL, composite and covering indexes, partial indexes, EXPLAIN (ANALYZE) and the query planner's cost model.
Trade-offs
Faster reads in exchange for slower writes, more storage and memory, and indexes that can mislead you if they don't match the query's shape or selectivity.

The problem

2 min

Monday, 9:05. The new normalized tables went live last week.

Omar · On-call SRE
"My bookings" takes 500 ms. Database CPU at 90%.
You · Engineer at StayHub
The query is exactly the same as before. The database got slower on its own?
Lina · Senior engineer, your mentor
The new bookings table holds 5 million rows, and the migration created only its primary key. Guess first.

The big idea

4 min

Reading 5 million rows to find 3

The big idea

An changes the kind of work: a few page reads whatever the table size, instead of work that grows with every row.

A bigger server would make the scan a bit faster, and next year it's 10 million rows. So change the work, not the machine. But every index is updated on every write, so each one has a cost.

How it works

5 min

Without an index, PostgreSQL does a full table scan (a Seq Scan): it reads every row to find the matches. An index is a separate sorted structure of column values, each pointing to its row, like the index at the back of a book.

How an index finds guest 81723

1
Ask
WHERE guest_id = 81723. The planner sees an index on guest_id and estimates it's cheaper than a scan.
2
Root page
The root holds ~200 sorted keys. 81723 falls between two of them: follow that pointer.
3
Inner page
Same again one level down. With 200 keys per page, 3 levels cover 8 million rows.
4
Leaf page
The leaf lists every 81723 entry (Ana's 3 bookings) with a pointer to each row.
5
Fetch rows
Read those 3 rows. About 4–6 page reads in total, instead of ~62,000. The page drops from 500 ms to about 10 ms.

Two catches. A covers several columns in a fixed order, like (guest_id, check_in): it serves "guest 81723" but not "everyone checking in on June 1" alone. And matters: if 60% of rows share a value (confirmed), scanning is cheaper and the index is ignored. To see what the database really did, run EXPLAIN ANALYZE: it runs the query and shows the plan with real timings.

Watch a decision being made

4 min

Worked example

Lina fixes "My bookings"

Database CPU is at 90% and "My bookings" takes 500 ms. Find the query responsible, fix it without blocking bookings, and prove the fix.

1Find the query eating the most total time
Sort pg_stat_statements by total time (calls × mean time), not by what feels slow.
Why: A 500 ms query run 14 times a second matters far more than a 3 s report run once a day.
2Read its plan
EXPLAIN ANALYZE shows Seq Scan on bookings, Rows Removed by Filter: 4999997, 498 ms.
Why: Reading 5 million rows to return 3 points straight at a missing index.
3Shape the index like the query, and build it safely
The query filters guest_id = ? and sorts by check_in DESC. Equality column first, then the sort column.
sql
```
CREATE INDEX CONCURRENTLY bookings_guest_checkin_idx
  ON bookings (guest_id, check_in DESC);
```
Why: Rows come out already sorted, so the sort step disappears. CONCURRENTLY lets checkout keep writing while it builds.
4Measure again
New plan: Index Scan, 0.08 ms. Page ~10 ms. Database CPU 90% → 30%.
Why: The plan proves the index is used; the CPU graph proves it was the problem. If CPU hadn't dropped, the next query on the list would be the culprit.

In real life

3 min

Find a few rows fast, or read everything fast?

PostgreSQL / MySQL apps: B-tree indexes

Built for many small queries: this guest's bookings, this hotel's arrivals
Each lookup is a few page reads; each index adds write cost

Fits apps answering thousands of small, selective questions a second.

Google BigQuery: no classic indexes

Publicly documented as scanning columns in parallel, paying per data scanned
Great for "revenue per country last year", slow and costly for one guest's row

Fits analytics that read most of a huge table anyway.

What goes wrong

3 min

Indexing every column "just in case"

Each index slows every write and takes RAM; most never get used.

Instead

Index the shapes of your top queries; drop unused indexes regularly.

Wrapping the indexed column in a function

WHERE date(created_at) = … or lower(email) = … can't use a plain index.

Instead

Rewrite as a range, or create an expression index.

Plain CREATE INDEX on a busy table

It blocks writes for the whole build.

Instead

CREATE INDEX CONCURRENTLY, at a time you choose.

Trade-offs

3 min

Option	Fits when	You pay
No index (scan)	Small tables, rare or bulk queries	Cost grows with the data
Targeted composite index	Frequent, selective queries on big tables	Slower writes, disk and RAM
Partial / covering / expression	A measured hot query still dominates	Very specific; easy to forget why it exists

Thursday, 15:30. Support wants a sixth index on bookings.

Maya · Product manager
Agents search bookings by email and date: 4 seconds each, 300 times a day. Add an index.
Omar · On-call SRE
bookings already has 5 indexes, all updated on every insert. Find the guest by email first: guests.email is already indexed.
Maya · Product manager
That's a code change in the tool, and agents wait meanwhile.

Your job as tech lead

4 min

Claude Code can write the code for this. You still own the decisions, and you have to know what good looks like to accept or reject its work.

Decisions you own

Which queries deserve an index, picked from measurements of total query time, not from columns that "look important".
How indexes are built on production: always CONCURRENTLY, at a time you choose, never by the agent on its own.
Which filters are worth indexing at all: guest_id yes; status, where 60% of rows are 'confirmed', no.

Tell Claude Code

"My bookings" runs SELECT … FROM bookings WHERE guest_id = ? ORDER BY check_in DESC LIMIT 20 on 5 million rows: a ~500 ms Seq Scan, and database CPU at 90%. Propose one index for exactly this query shape and explain the column order. Show EXPLAIN ANALYZE on staging before and after. Give me the CREATE INDEX CONCURRENTLY command for production, but don't run it yourself. Also list indexes on bookings unused in the last 30 days.

Check before you accept

Before: "Seq Scan" around 500 ms. After: "Index Scan" under 1 ms, with no sort step.
Column order is (guest_id, check_in DESC): equality first, sort second.
After release, database CPU drops toward 30% and insert latency doesn't rise noticeably.

Red flags

It proposes indexes on many columns "to be safe".
It runs a plain CREATE INDEX on a busy production table.
It claims the query is faster without showing the plan.

Check yourself

2 min

Q1/3Reasoning

bookings has an index on (hotel_id, check_in). Which queries can use it efficiently? (Select all that apply.)

Select all that apply.

How confident are you?

Next episode: one room left in Lisbon for 14 March, and two guests press Book within the same 50 milliseconds.

Finished?

Mark it complete to update your skill map.

Next: Transactions and ACID: never double-book the last room

Go deeper (optional)You don't need this to understand the lesson. Open it when a project needs the details: Technical deep dive, Interactive simulation, Visual diagram, Analogy, Check your understanding, Practice ladder, Explain it yourself.

Technical deep dive

Proving the index works

Worked example

Lina fixes "My bookings"

Database CPU is at 90% and "My bookings" takes 500 ms on a 5-million-row table. Find the query responsible, fix it without blocking bookings, and prove the fix.

1Find the expensive query
Ask PostgreSQL which queries use the most total time (calls × mean time), not which one feels slow.
sql
```
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 5;
```
Why: A 500 ms query run 14 times a second matters far more than a 3 s report run once a day. Total time finds the real CPU eater.
2Read its plan
Run EXPLAIN ANALYZE on the top query. It shows Seq Scan on bookings, Rows Removed by Filter: 4999997, Execution Time: 498 ms.
Why: The plan tells you why it's slow. Here: reading 5 million rows to return 3. That points straight at a missing index.
3Design the index for the query's shape
The query filters guest_id = ? and sorts ORDER BY check_in DESC. Index (guest_id, check_in DESC): equality column first, then the sort column.
Why: Matching the sort order lets the database read rows already in order and stop after 20, so the sort step disappears too.
4Build it without blocking
CREATE INDEX CONCURRENTLY bookings_guest_checkin_idx ON bookings (guest_id, check_in DESC);
Why: A plain CREATE INDEX blocks all writes to bookings while it builds. CONCURRENTLY is slower but lets checkout keep working.
5Measure again
New plan: Index Scan using bookings_guest_checkin_idx, 0.08 ms. Page time ~10 ms. Database CPU 90% → 30%.
Why: The plan proves the index is used; the CPU graph proves it was the problem. If CPU hadn't dropped, the next query on the list would be the culprit.

sql

EXPLAIN ANALYZE
SELECT * FROM bookings WHERE guest_id = 81723 ORDER BY check_in DESC LIMIT 20;

-- Limit  (actual time=498.120..498.124 rows=3 loops=1)
--   ->  Sort  (actual time=498.118..498.120 rows=3 loops=1)
--         Sort Key: check_in DESC
--         ->  Seq Scan on bookings  (cost=0.00..118340.00 rows=4 width=96)
--                                   (actual time=12.402..498.071 rows=3 loops=1)
--               Filter: (guest_id = 81723)
--               Rows Removed by Filter: 4999997
-- Execution Time: 498.2 ms

Before: a Seq Scan reads 5M rows to return 3. Numbers are illustrative.

sql

-- CONCURRENTLY avoids blocking writes while building on a live table
CREATE INDEX CONCURRENTLY bookings_guest_checkin_idx
  ON bookings (guest_id, check_in DESC);

-- Limit  (actual time=0.041..0.052 rows=3 loops=1)
--   ->  Index Scan using bookings_guest_checkin_idx on bookings
--         (cost=0.43..12.51 rows=4 width=96) (actual time=0.039..0.049 rows=3 loops=1)
--         Index Cond: (guest_id = 81723)
-- Execution Time: 0.08 ms

After: a composite index serves both the filter and the ORDER BY. No sort node.

Composite indexes and the leftmost-prefix rule. An index on (hotel_id, check_in, status) is sorted by hotel_id first, then check_in within each hotel, then status. It can serve:

WHERE hotel_id = 42
WHERE hotel_id = 42 AND check_in = '2026-06-01'
WHERE hotel_id = 42 AND check_in BETWEEN … AND … (range on the second column)

It cannot efficiently serve WHERE check_in = '2026-06-01' alone, because those entries are scattered across every hotel's section. Rule of thumb: equality columns first, then the range or sort column.

sql

-- Partial index: only the rows a hot query cares about (pending is ~0.5% of rows)
CREATE INDEX bookings_pending_idx ON bookings (created_at) WHERE status = 'pending';

-- Covering index: INCLUDE extra columns so the query never touches the table
CREATE INDEX bookings_hotel_night_cov ON bookings (hotel_id, check_in) INCLUDE (status, total_cents);

-- Expression index: the query filters on lower(email), so index exactly that
CREATE INDEX guests_email_lower_idx ON guests (lower(email));

Three specialised index types worth knowing.

Situation	Index helps?	Why
guest_id = ? (≈ 3 rows of 5M)	Yes, hugely	Highly selective: tiny fraction of rows
status = 'confirmed' (60% of rows)	No, planner scans	Fetching 3M rows via index costs more than reading the table sequentially
status = 'pending' (0.5%)	Yes, ideally partial	Rare value is selective even if the column overall isn't
check_in = ? with index on (hotel_id, check_in)	Not efficiently	Not a leftmost prefix
lower(email) = ? with index on email	No	The expression doesn't match the indexed value; index lower(email)
Small table (2k rows)	Rarely matters	A scan of a few pages is already fast

Index usefulness depends on selectivity and on the query matching the index's sort order.

Interactive simulation

Visual diagram

Bookings API

Query planner

Table (heap)

B-tree root

Internal page

Leaf page

20 rows

Click a component

How PostgreSQL answers WHERE guest_id = 81723 on a 5M-row bookings table: plan, then either scan or descend the B-tree.

Analogy

In a phone bookIn a database

In a phone bookReading every page to find "Martinez"In a databaseA full table scan
In a phone bookThe letter tabs on the sideIn a databaseAn index that jumps to the right place
In a phone bookSorted by last name, then first nameIn a databaseA composite index (several columns, in order)
In a phone bookLooking for everyone named "Ana"In a databaseA query the index can't help with
In a phone bookPrinting a new edition for each new residentIn a databaseThe cost of updating indexes on every write

Where the analogy breaks: a phone book is reprinted once a year, but a database updates its indexes instantly, on every single write. That's why each extra index has a real, ongoing cost.

Check your understanding

Q1/3Reasoning

A B-tree page holds ~200 keys. How many levels (root to leaf) are needed to index 8 million rows? (200³ = 8,000,000.)

levels

How confident are you?

Practice ladder

Step 1 · Trace

Which queries can use the index?

Code Warm-up

bookings (5 million rows) has one composite index: (hotel_id, check_in). For each query, say whether the planner can use the index efficiently, and why.

WHERE hotel_id = 42
WHERE hotel_id = 42 AND check_in >= '2026-06-01' AND check_in < '2026-06-08'
WHERE check_in = '2026-06-01'
WHERE hotel_id = 42 AND status = 'pending'
WHERE hotel_id = 42 AND date_trunc('month', check_in) = '2026-06-01'

Step 2 · Complete

Finish Lina's note on the arrivals page

Debugging Practice

Hotels' front desks open "Tonight's arrivals" about 30 times a second at 15:00. It takes 900 ms. The query:

SELECT * FROM bookings
WHERE hotel_id = ? AND check_in = CURRENT_DATE AND status = 'confirmed';

Existing indexes: primary key, (guest_id, check_in DESC), (room_id).

Lina's note has gaps in steps 2, 3 and 4:

Find the expensive query: pg_stat_statements puts it 2nd by total time.
Read its plan: ___ (what will EXPLAIN ANALYZE most likely show, and why?)
Design the index: ___ (which columns, in which order?)
Build it: ___
Measure again: check the plan uses it and CPU drops.

Step 3 · Solve

The hotel dashboard queries

Design Practice

The Hotel Partners team launches a dashboard. On a bookings table that will reach 20 million rows next year, it runs:

WHERE hotel_id = ? AND check_in BETWEEN ? AND ? ORDER BY check_in: 50 times a second
WHERE hotel_id = ? AND status = 'pending' (pending is ~0.5% of rows): 10 times a second
WHERE lower(guest_email) = ? on guests (support search): 2 times a second
A monthly finance export reading every booking of last year: once a month

Existing indexes: primary key, (guest_id, check_in DESC), (room_id), (hotel_id, check_in).

Propose the minimum set of new indexes (or none) for each query, and justify each choice.

Step 4 · Decide

A covering index for the revenue report?

Decision Stretch

Finance runs a revenue report every 5 minutes on a wall screen:

SELECT hotel_id, SUM(total_cents) FROM bookings
WHERE check_in >= CURRENT_DATE AND check_in < CURRENT_DATE + 30 AND status = 'confirmed'
GROUP BY hotel_id;

It takes 6 s and touches ~400,000 rows. A teammate proposes a covering index: (check_in) INCLUDE (hotel_id, status, total_cents), which would let it run as an Index Only Scan in ~300 ms. The index would be about 400 MB; the database has 64 GB of RAM and bookings already has 6 indexes. Inserts: ~2,500 a day, peaking at 3 a second.

Write a decision: "We chose X over Y because…, accepting the risk of…". Consider at least one alternative to the index.

Explain it yourself

Explain how a B-tree index takes a query from 500 ms to a few ms, and when adding an index would be the wrong move.

0 words