# Bapu Class: How Amazon Finds Your Order in One Second (Database Indexes, Made Simple)

*By Devang Pandya · Cloud Tech by Devang · Cloud • DevOps • AI — Made Simple*

> This post is the full breakdown for the **"Bapu Class" Reel on database indexes**. If you came here from the Reel after commenting **INDEX**, welcome!

**The story in the Reel:** a young engineer asks his grandfather, *"Bapu, Amazon has crores of orders. How does my order show up in one second?"* Bapu answers in the rain:

- Without an index, you have to check every shelf. That is a **full table scan**.
- With an index, like the index at the back of a book, you jump straight to the right page.
- A database index is a **sorted B-tree**. Even with crores of rows, the database reaches your row in about **4–5 steps**.
- But do not index every column. **Every index slows down writes and takes space.** Index only the columns you actually search or filter on.

Below is the detailed version, with SQL you can try yourself.

---


You open "Your Orders" on a shopping app and your list shows up almost instantly. Behind that screen there could be a table with billions of order rows. The database does not read all of them. It uses an **index**.

Indexes are one of the most asked topics in backend and system design interviews, and one of the most common causes of real production slowdowns. In 14+ years of working with cloud systems, I have seen more "the site is slow" incidents fixed by one correct index than by any amount of extra hardware.

In this post I will explain, in simple English, what an index is, why a B-tree is so fast, how to design a composite index, why you should not index every column, and how to answer the classic interview question:

> *"Your orders query on 1 crore rows takes 4 seconds. Fix it."*

A quick note before we start: Amazon does not publish the exact database design behind its "Your Orders" page. I use an "orders table" as an **example** because everyone understands it. The index ideas here are general and work the same way in PostgreSQL, MySQL and most relational databases.

---

## 1. The problem: a full table scan

Imagine this table:

```sql
CREATE TABLE orders (
  order_id    BIGINT PRIMARY KEY,
  user_id     BIGINT NOT NULL,
  order_date  TIMESTAMP NOT NULL,
  status      TEXT NOT NULL,
  total       NUMERIC(10,2)
);
```

And this query, which runs every time a user opens their orders page:

```sql
SELECT order_id, order_date, total
FROM orders
WHERE user_id = 42
ORDER BY order_date DESC
LIMIT 20;
```

Without a useful index, the database has only one option: read **every row** in the table, keep the ones where `user_id = 42`, sort them by date and return the top 20. This is called a **sequential scan** (or full table scan). With 1 crore (10 million) rows, that is a lot of disk and memory work for 20 rows of output. With more users opening the page at the same time, the database CPU climbs and every query gets slower.

---

## 2. Step one in any slow-query problem: EXPLAIN

Before you add anything, look at what the database is actually doing. In PostgreSQL:

```sql
EXPLAIN ANALYZE
SELECT order_id, order_date, total
FROM orders
WHERE user_id = 42
ORDER BY order_date DESC
LIMIT 20;
```

`EXPLAIN` shows the **query plan**: the steps the database chose. `EXPLAIN ANALYZE` also runs the query and shows real timings. If you see `Seq Scan on orders`, the database is reading the whole table. That is your first clue.

This is also the first thing to say in an interview. Interviewers like candidates who measure before they change things. "I would run EXPLAIN ANALYZE and check whether it is doing a sequential scan" is a much better start than "I would add Redis".

---

## 3. What an index actually is

Think about the index at the back of a textbook. If you want every page that mentions "TCP", you do not read the whole book. You go to the index, find "TCP", and it tells you the page numbers. You jump straight there.

A database index is the same idea. It is a separate data structure that keeps the values of one or more columns **in sorted order**, along with a pointer to where each full row lives. The most common type is the **B-tree**. PostgreSQL, MySQL (InnoDB), SQL Server and Oracle all use B-tree style indexes as the default.

### How a B-tree is shaped

A B-tree has three kinds of nodes:

- **Root node:** the top. It holds a few keys that split the whole range into big chunks.
- **Branch nodes:** the middle levels. Each splits its chunk into smaller chunks.
- **Leaf nodes:** the bottom. They hold the sorted index entries and point to the actual rows. Leaf nodes are linked to each other, so the database can also walk through them in order.

To find `user_id = 42`, the database starts at the root, picks the right branch, goes down one level at a time, and lands on the leaf that holds 42. Then it follows the pointer to the row.

### Why it is so fast: the tree stays shallow

Each node holds many entries (often hundreds), so the tree is very wide and very short. Markus Winand, in *Use The Index, Luke!*, explains that the tree depth grows **logarithmically** with the number of rows, and that real-world indexes with millions of rows usually have a depth of only **four or five levels**. That means the database needs just a handful of page reads to find any value, instead of reading millions of rows.

You may have seen a hook online that says an index lets the database "read about 30 rows". That is not quite right. The useful mental model is: a few levels of tree lookups to find the starting point, then it reads only the matching entries.

---

## 4. Designing the right index for the query

For our query, the database needs to:

1. Find rows where `user_id = 42`.
2. Return them sorted by `order_date`, newest first.
3. Stop after 20.

A single index on `user_id` helps with step 1, but the database still has to sort all of that user's orders. A better choice is a **composite (multicolumn) index**:

```sql
CREATE INDEX idx_orders_user_date
  ON orders (user_id, order_date DESC);
```

Now the index entries are sorted first by `user_id`, and within each user by `order_date` (newest first). The database jumps to user 42, reads the first 20 entries in order, and stops. No full scan, no big sort.

### Column order matters

The PostgreSQL documentation explains that a multicolumn B-tree index is most efficient when there are constraints on the **leading (leftmost) columns**. Think of a phone book sorted by last name, then first name. It is great for "Sharma, Rohit" and good for "all Sharmas", but useless for "everyone named Rohit".

A simple rule that works for most queries:

- Put **equality** columns first (`user_id = 42`).
- Then the **sort** or **range** column (`ORDER BY order_date`, `order_date > '2026-01-01'`).

If you flip the order to `(order_date, user_id)`, the index is sorted by date first, and user 42's orders are spread all over it. The database cannot use it well for this query.

### Covering indexes (a bonus)

If the index also contains every column the query needs, the database may answer the query from the index alone, without visiting the table. PostgreSQL calls this an **index-only scan**, and you can add extra columns with `INCLUDE`:

```sql
CREATE INDEX idx_orders_user_date_cover
  ON orders (user_id, order_date DESC) INCLUDE (total);
```

Use this carefully. Wider indexes take more space and more write work.

---

## 5. Why not index every column?

This is the follow-up question the interviewer in the Reel asks, and it is a great one.

Indexes are **not free**:

- **Every write gets slower.** When you `INSERT` a row, the database must also add an entry to every index on that table. When you `UPDATE` an indexed column or `DELETE` a row, every affected index must change too. Ten indexes means roughly ten extra pieces of work on each write.
- **More storage.** Each index is a full extra structure on disk and in memory. Big indexes push useful data out of the cache.
- **Not every index helps.** A column with very few distinct values (like `status` with only "placed", "shipped", "delivered") rarely helps alone, because the database would still read a huge part of the table.
- **More choices for the planner.** Many overlapping indexes make plans harder to predict and maintain.

The PostgreSQL docs say it directly: indexes with more than three columns are unlikely to be helpful unless the table usage is very stylised, and indexes should be used sparingly.

So the rule is: **index for your real query patterns**, not "just in case".

---

## 6. How I approach a slow query in real life

Here is the checklist I use with teams:

1. **Find the real slow queries.** Use your database's slow query log or tools like `pg_stat_statements` in PostgreSQL. Do not guess.
2. **Run EXPLAIN ANALYZE.** Look for `Seq Scan` on big tables, big sorts, and row estimates that are far from actual rows.
3. **Match the index to the WHERE and ORDER BY.** Equality columns first, then range or sort columns.
4. **Create the index safely.** On a busy PostgreSQL table, use `CREATE INDEX CONCURRENTLY` so you do not block writes for a long time.
5. **Run EXPLAIN ANALYZE again.** Confirm it now uses an `Index Scan` or `Index Only Scan`, and compare timings.
6. **Watch write latency and storage.** Make sure the new index did not hurt inserts.
7. **Remove unused indexes.** Many databases track index usage. Dropping dead indexes speeds up writes.

---

## 7. Where indexes fit in a bigger design

Indexes are the first tool, not the only one. As data and traffic grow, you may also use:

- **Read replicas** to spread read load.
- **Caching** for very hot data (a cache in front of the database).
- **Partitioning** big tables by date, so old data does not slow new queries.
- **Sharding** across many database servers when one server cannot hold the data or the write load.
- **Purpose-built stores** like key-value databases for simple lookups by key at massive scale.

But in interviews and in real life, the order matters. Measure first, fix the query and indexes next, and only then add more infrastructure.

---

## 8. The example visuals

The "1 crore rows" and "4 seconds" numbers in the Reel are an **example scenario**, the way interviewers phrase it. The "Amazon orders" framing is also an example, because Amazon does not publish its exact order database design. The B-tree depth of four to five levels, the leftmost-column rule and the write cost of indexes are verified from the sources below.

---

## 9. How to answer this in an interview

**Question:** *"Your orders query on 1 crore rows takes 4 seconds. Fix it."*

**Strong answer:**

> "First I would run EXPLAIN ANALYZE to see the plan. If it is doing a sequential scan, the database is reading every row. The query filters by user_id and sorts by order_date, so I would add a composite B-tree index on (user_id, order_date DESC). Equality column first, then the sort column. A B-tree stays only a few levels deep even for millions of rows, so the database can jump to that user's newest orders and stop after 20. I would create it concurrently to avoid blocking writes, then run EXPLAIN again to confirm an index scan. I would not index every column, because every insert and update has to update every index, which slows writes and uses storage."

**Follow-ups to expect:**

- *Why not index every column?* Write cost, storage, and low-selectivity columns do not help.
- *What is a covering index?* An index that includes every column the query needs, so the table is not read.
- *Why does column order matter in a composite index?* The index is sorted by the first column first. It is most efficient for conditions on the leftmost columns.
- *The index exists but the query is still slow. Why?* Wrong column order, a function on the column (`WHERE LOWER(email) = ...`), outdated statistics, or the query returns a large share of the table anyway.

---

### Sources
- Markus Winand, *Use The Index, Luke!*, "The Search Tree (B-Tree) Makes the Index Fast" (logarithmic growth, real-world depth of four or five for millions of rows): https://use-the-index-luke.com/sql/anatomy/the-tree
- PostgreSQL documentation, 11.3 "Multicolumn Indexes" (leading columns, more than three columns rarely helpful, use sparingly): https://www.postgresql.org/docs/current/indexes-multicolumn.html
- PostgreSQL documentation, "Using EXPLAIN": https://www.postgresql.org/docs/current/using-explain.html
- PostgreSQL documentation, "Index-Only Scans and Covering Indexes": https://www.postgresql.org/docs/current/indexes-index-only-scans.html
- PostgreSQL documentation, "CREATE INDEX" (CONCURRENTLY): https://www.postgresql.org/docs/current/sql-createindex.html

---

> **Preparing for a Cloud, DevOps or SRE interview?** Book a **Free 15-min Cloud & DevOps Interview Guidance Call** with me: what to study, how to answer system-design and on-call questions, plus a CV and roadmap check 👉 [topmate.io/cloudtechbydevang](https://topmate.io/cloudtechbydevang/2358399)

