Skip to content
Interview rounds

Databases & caching · Concept deep-dive

Why not put an index on every column?

A query is slow and someone proposes an index on every column. What does an index cost, and how do you decide which ones to have?

BackendMid12 min
Interview-style scenarioLast reviewed: Checked against official documentation

Concept deep-dive · indexes and query plansIllustrative scenario, not a real incident

Review: "add an index on each of the 14 columns of orders, so that any query finds one"

The orders table in PostgreSQL has 14 columns and 120 million rows. It takes 2,500 inserts a second at a p99 of 4 ms, and has two indexes: the primary key and one on customer_id.

The page that lists a customer's orders from the last 30 days, newest first, has a p99 of 1.8 seconds. A change is up for review that would index all the other columns.

What the dashboards and logs show

Table
orders: 14 columns, 120 million rows, 2 indexes
Inserts
2,500 a second, p99 4 ms
Order list query
p99 1.8 s; filters on customer and date, sorts by date
Proposal
12 more single-column indexes

5 questions on one topic, each at least as hard as the last. Answer each before the next appears. The last one plays out.

Question 1 of 5 · Mid · scored under "Trade-offs stated"

What does each extra index on a table cost while the system is running?

Your rubric score, the model answer, the follow-up questions and the related lessons appear once you have answered the last step.