Database Indexes

Pradeep P
4 days ago
4 min read

Series: Modern System Design · Layer 1 — The building blocks

Layer 1 · Post 13 of 15

← Previous: SQL vs NoSQL → Next: Replication

Layer 1 — The building blocks · Post 13 of 88

An index is a shortcut the database keeps so it does not have to scan every row. The tradeoff is extra storage and slower writes.

What you'll learn

What a full table scan is, and why it explodes with growth
How a B-tree index lets you jump to a row
Why "index everything" makes writes and storage worse

The idea in one minute

Without an index, finding email = 'a@b.com' means reading every row (a sequential scan). That is fine for 200 users. It is not fine for 200 million.

An index is extra data the database maintains — often a B-tree — that is sorted by the column (or columns) you care about. Lookup becomes "walk a short tree," not "read the pile."

You pay for that shortcut on every write: inserts, updates, and deletes must update the indexes too.

Why it matters

"The database is slow" is often "we forgot an index" or "we have the wrong index."

Indexes are also how you encode access patterns in SQL land. If you always load orders by user_id and created_at, that pair wants an index. If you never filter on favorite_color, indexing it is dead weight.

In interviews, saying "we'll add indexes on the lookup keys" is the difference between a toy table and a design that survives a year of growth.

How it works

Table scan vs index lookup

Imagine a phone book.

Scan: start at A, read every name until you find yours. Cost grows with the book.
Index: the book is already sorted by name. You jump near the right page.

A typical B-tree index keeps keys in sorted order in a disk-friendly tree. The database follows a few pages down to the matching key, then uses a pointer to the row (or finds the row in a covering index).

Hash indexes (less common as a default) are great for exact equality, not for WHERE created_at > ....

Primary keys

The primary key is an index (and more): it uniquely identifies a row. InnoDB even stores the table in primary-key order (clustered index). Random UUIDs as PKs can fragment that layout; sequential or time-ordered ids are often kinder. This is a real-world detail, not trivia.

Composite indexes

Index on (user_id, created_at) helps:

WHERE user_id = 9
WHERE user_id = 9 ORDER BY created_at
WHERE user_id = 9 AND created_at > '2026-01-01'

It does not help much with WHERE created_at > '2026-01-01' alone. Leftmost prefix matters. Order the columns the way you filter.

Covering indexes

If the index contains every column the query needs, the database might not visit the table at all. Fast. Bigger index.

Selectivity

An index on is_deleted when 99% of rows are false is often useless: you still look at almost everyone. Indexes want selective predicates — values that cut the pile down.

A simple example

orders has 50 million rows.

GET /users/42/orders runs WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20.

Without an index, Postgres might scan a huge chunk of the table. With INDEX (user_id, created_at DESC), it walks straight to user 42's newest orders.

Someone adds WHERE status = 'shipped' without extending the index. If most of user 42's orders are shipped, still fine. If not, the planner might still use (user_id, created_at) and filter. If you filter by status without user_id, you need a different index — or you accept a scan.

Common mistakes

Indexing every column "just in case." Writes slow down, backups grow, the planner gets confused, and nothing gets faster.

Functions on the column: WHERE LOWER(email) = ... may not use an index on email unless you have an index on LOWER(email).

Leading wildcards: LIKE '%smith' cannot use a normal B-tree like a prefix search can. Search engines exist for a reason.

Checking production with SELECT * and no LIMIT. You will not notice index issues until the table is large. Test with realistic volume.

Duplicate overlapping indexes. (a) and (a,b) — the second can serve many of the first's queries. Measure before dropping.

How this shows up in real systems

EXPLAIN ANALYZE: the tool. "Seq Scan" on a huge table is a smell for that query.
Migrations: adding an index on a huge table can lock or slow writes; online/create-concurrently patterns exist for a reason.
NoSQL: you still index — partition key, sort key, GSI in DynamoDB, TTL indexes in Mongo. Same idea, different names.

When you design a table, list queries first, then indexes that match them. Not the other way around.