How to Explain a Database Index Choice in a Design Review in English
Learn the English phrases for justifying a database indexing decision during a design review — explaining trade-offs, query patterns, and write-performance costs clearly to reviewers.
Explaining an indexing decision in a design review is different from explaining it in a casual chat — reviewers expect you to justify the trade-off, not just state the choice. A good explanation names the query pattern you’re optimising for, the write-performance cost you’re accepting, and why you rejected the alternatives. This guide gives you the English for that conversation.
Key Vocabulary
Query pattern — the specific, expected shape of queries your index needs to serve well, which should drive the indexing decision rather than indexing “just in case.”
“The dominant query pattern here is filtering by tenant_id and sorting by created_at, so I’m proposing a composite index on exactly those two columns, in that order.”
Composite index — an index spanning multiple columns, where column order matters for which queries it can efficiently serve.
“Because we always filter by tenant_id first, it needs to be the leading column in the composite index — otherwise the index can’t be used efficiently for that filter.”
Selectivity — how effectively a column narrows down the result set; low-selectivity columns (like a boolean flag) make poor leading index columns.
“status has low selectivity — almost every row is ‘active’ — so indexing it alone wouldn’t meaningfully narrow the search.”
Write amplification (index overhead) — the cost of maintaining an index on every insert, update, or delete, which grows with the number of indexes on a table. “We already have four indexes on this table — adding a fifth means every insert now updates five structures instead of four, which isn’t free.”
Covering index — an index that includes all the columns a query needs, letting the database satisfy the query from the index alone without a separate lookup to the table.
“By including email in the index itself, this becomes a covering index for our most common lookup — we skip the extra round trip to read the row.”
Presenting the Proposal
- “The main access pattern for this table is looking up orders by customer ID and filtering by date range — I’m proposing a composite index on
(customer_id, created_at)to serve that directly.” - “I considered indexing
statusas well, but given its low selectivity, it wouldn’t meaningfully reduce the scanned row count — I left it out.” - “This does add write overhead on every insert, but given the table’s read-to-write ratio is roughly 50:1, the trade-off clearly favours optimising reads.”
Justifying Trade-Offs to Reviewers
- “Yes, this does slow down inserts slightly — our benchmark showed about a 3% increase in insert latency, which we consider acceptable given the read-heavy access pattern.”
- “We’re intentionally not indexing this column yet — there’s no query using it as a filter today, and adding a speculative index just adds maintenance cost without a measured benefit.”
- “I chose a composite index over two separate single-column indexes because the query planner can use the combined index far more efficiently for our specific filter-and-sort pattern.”
Handling Pushback
- “That’s a fair concern about table bloat — I can share the projected index size based on current row counts if that would help the decision.”
- “You’re right that this doesn’t help the analytics query — that query runs on the read replica, and we’re planning a separate, purpose-built index there instead of over-indexing the primary table.”
- “I hadn’t considered that migration path — let me check whether adding this index requires a table lock, and I’ll follow up with the numbers before we approve this.”
Professional Tips
- Always name the specific query pattern you’re optimising for. “This will make things faster” is vague; “this serves our
customer_id + date rangelookup without a full table scan” is a concrete claim reviewers can evaluate. - State the write-side cost honestly, even if it’s small. Naming the trade-off upfront builds more trust than only mentioning benefits and letting a reviewer discover the cost themselves.
- Explain what you deliberately did NOT index and why. This shows the decision was reasoned, not a default “index everything” habit.
Practice Exercise
- Write a two-sentence justification for a composite index, naming the specific query pattern it serves.
- Explain, in plain terms, why a low-selectivity column makes a poor leading index column.
- Draft a response to a reviewer asking why you didn’t index an additional column that seems related.