System Design: a free course from zero to interview-ready

Think of it like this

Analogy

Finding every mention of “caching” in a 900-page book by reading every page is a full table scan. The index at the back is sorted alphabetically and tells you the page numbers directly. Building that index took the publisher extra work, and every new edition has to update it, but readers save enormous time.

The problem it solves

Without an index, SELECT * FROM orders WHERE user_id = 7 has to read every row in the table and check each one. That's a full table scan. On a thousand rows nobody notices. On a hundred million rows it takes seconds, burns CPU and disk, and, under load, takes the whole database down with it.

Missing indexes are one of the most common causes of a slow app.

A real example

Example

Support looks up an order by email

A support agent types a customer's email to find their orders. The orders table has 50 million rows and no index on email, so the database reads every row. It takes 30 seconds, and while it runs it slows down everyone else.

The team adds an index on email. The same lookup now walks a B-tree in a few steps and returns in about 5 ms.

Two things change. Every new order is slightly slower to insert, because the index must be updated too. And a search like email LIKE '%@gmail.com' still scans the whole table, because the index is sorted by the start of the email, not the end.

How it works

An index is a separate structure, kept sorted by one or more columns, with pointers to the matching rows.

B-tree indexes

Most databases use a B-tree: a shallow tree of sorted pages. Each page holds hundreds of keys, so three or four levels cover hundreds of millions of rows.

A B-tree lookup for user_id = 7: three page reads instead of scanning the table.

Because the keys are sorted, B-trees handle exact matches (=), ranges (BETWEEN, >), prefixes (LIKE 'abc%') and ORDER BY on the indexed columns.

Composite indexes and column order

A composite index covers several columns, sorted by the first, then by the second within it, and so on. Think of a phone book sorted by last name, then first name.

An index on (user_id, created_at):

Query	Uses the index?
`WHERE user_id = 7`	Yes
`WHERE user_id = 7 AND created_at > '2026-01-01'`	Yes, fully
`WHERE user_id = 7 ORDER BY created_at DESC LIMIT 20`	Yes, and no sort needed
`WHERE created_at > '2026-01-01'`	No, it skips the first column

Rule of thumb: put columns you filter with = first, then the column you range over or sort by.

Covering indexes

If the index contains every column the query needs, the database answers from the index alone, never touching the table. Many databases let you add extra columns to an index for exactly this.

Other index types

Hash indexes: fast exact lookups, no ranges.
Full-text indexes: search words inside text. Large-scale text search usually goes to a separate search engine like OpenSearch.
Geospatial indexes: “what's near this point” (lesson 30).
Secondary indexes in NoSQL stores: let you query by something other than the main key, at extra write cost.

Finding the right indexes

Find slow or frequent queries in the slow-query log or monitoring.
Run EXPLAIN (or EXPLAIN ANALYZE) to see whether the database scans or uses an index.
Add an index that matches the query's filters and sort.
Measure again, and remove indexes nothing uses.

EXPLAIN ANALYZE
SELECT id, total FROM orders WHERE user_id = 7 ORDER BY created_at DESC LIMIT 20;
-- Before: Seq Scan on orders (rows=100000000) ... 4200 ms
-- After CREATE INDEX ON orders (user_id, created_at): Index Scan ... 0.3 ms

On large, busy tables, create indexes without blocking writes (for example CREATE INDEX CONCURRENTLY in PostgreSQL).

Trade-offs

What you gain

Turns seconds into milliseconds for the queries they match.
Lets the database return sorted results without sorting.
Lowers CPU and disk load, so the same database serves far more traffic.

What it costs you

Every write must update every index on the table, so writes get slower.
Indexes take disk space and memory.
The wrong column order, or a function wrapped around the column, means the index silently isn't used.

When not to use it

Tiny tables, where a scan is already instant.
Columns with only a few distinct values (like a true/false flag), which rarely help on their own.
Write-heavy tables that are almost never queried by that column.

In the cloud

Relational (SQL) databasesEvery major cloud sells this as a managed service, so you configure it rather than build it.

AWS2

Amazon RDS (opens vendor docs)
Managed PostgreSQL, MySQL, MariaDB, SQL Server, Oracle and Db2.
Amazon Aurora (opens vendor docs)
AWS's MySQL- and PostgreSQL-compatible database with storage replicated across three zones.

Azure2

Azure SQL Database (opens vendor docs)
Managed SQL Server database engine.
Azure Database for PostgreSQL (opens vendor docs)
Managed PostgreSQL (flexible server).

Google Cloud2

Cloud SQL (opens vendor docs)
Managed PostgreSQL, MySQL and SQL Server.
AlloyDB for PostgreSQL (opens vendor docs)
Google's PostgreSQL-compatible database tuned for heavy transactional and analytical work.

How you’d set it up

One writable primary with a standby in another zone for failover, plus read replicas.

App servers
RDS primary (Multi-AZ)
Read replicas

Interview questions

Try answering out loud first, then open the model answer.

A query got slow as the table grew. How do you fix it?

Model answer

Run EXPLAIN ANALYZE to see the plan. If it's a full scan or a big sort, add an index matching the WHERE clause and ORDER BY, with equality columns first and the range or sort column last, and consider including the selected columns so it's covering. Check that the query doesn't wrap the column in a function or cast that prevents index use. Create it concurrently on a live system, then confirm the new plan and latency. If it's still slow, look at returning fewer rows, caching, or a read replica.

How do indexes affect write performance?

Model answer

Each insert, update or delete must also update every index that includes the changed columns, which costs extra disk writes and can create lock or page contention. A table with ten indexes may write several times slower than one with two. So you index for the queries you actually run and remove unused indexes. For very write-heavy data, structures like LSM trees (used by Cassandra and RocksDB) trade some read speed for much faster writes.

What's a covering index?

Model answer

An index that includes every column a query needs, so the database can answer from the index without fetching rows from the table. For example, an index on (user_id, created_at) that also includes total covers SELECT created_at, total FROM orders WHERE user_id = 7. It saves a lookup per row, which matters a lot when returning many rows.

Quiz

Pick an answer to see whether it’s right and why.

0 of 6 answered

Key terms

Index: A sorted lookup structure that lets the database find rows without reading the whole table.
B-tree: The balanced tree structure most database indexes use, so any value is found in a few disk reads.
Full table scan: Reading every row of a table to answer a query, because no index helps.
Composite index: An index on several columns in a set order, such as (user_id, created_at).
Database: Software that stores data on disk in an organized way so it can be queried and survives restarts.