Indexes are the most commonly mentioned and least explained element in system design interviews. “I’d add an index on user_id” is a complete sentence in most answers. It tells the interviewer almost nothing.
Here’s the complete framework for talking about indexes in an interview — specific enough to signal expertise, concise enough to not derail the conversation.
B-tree index (the default)
A B-tree index stores values in sorted order as a balanced tree. Lookups, range queries, and ORDER BY operations all use it efficiently.
Use for: equality queries (WHERE user_id = 123), range queries (WHERE created_at BETWEEN x AND y), sorting (ORDER BY created_at DESC).
The B-tree is the default index type in Postgres, MySQL, and most relational databases. When you say “I’d add an index” without specifying a type, this is what you’re getting.
Hash index
A hash index maps each value to a hash. Lookups by exact equality are O(1). Range queries are impossible — the hash has no ordering.
Use for: pure equality lookups where range queries will never be needed. Session ID lookups. Token validation. Cache key lookups.
In practice: Redis and most key-value stores use hash indexes internally. Postgres supports hash indexes but they’re rarely the right choice over B-tree because B-tree handles both equality and range queries while hash handles only equality.
Composite index
A composite index covers multiple columns: CREATE INDEX ON orders (user_id, status, created_at).
The critical rule: composite indexes follow the left-prefix rule. This index serves queries on (user_id), (user_id, status), and (user_id, status, created_at). It does NOT serve queries on (status) alone or (created_at) alone.
Use for: queries that always filter by a leading column and sometimes filter by subsequent columns. “Give me all pending orders for user X from the last 7 days” — (user_id, status, created_at) is the right index.
The order of columns in a composite index matters: put the most selective columns first if you want to cover a range of queries, or put the equality predicates before the range predicates.
Partial index
A partial index covers only a subset of rows, defined by a WHERE clause.
CREATE INDEX ON orders (created_at) WHERE status = 'pending'
This index only stores rows where status is ‘pending’. It’s much smaller than a full index on created_at. Queries that filter by status = 'pending' and then sort by created_at will use this index. Queries that don’t filter by status won’t.
Use for: high-cardinality tables where most queries filter on the same condition. “Show pending orders” is a common query; “show completed orders” is rare. Index the pending subset specifically.
Covering index
A covering index includes all columns needed to satisfy a query, so the database can answer the query entirely from the index without touching the underlying table.
If your query is SELECT user_id, status FROM orders WHERE user_id = 123, an index on (user_id, status) is a covering index — it has both columns. The query engine never touches the orders table rows.
The performance difference is significant: a covering index means one I/O operation (read the index), not two (read the index, then follow the pointer to the table row for each result).
Use for: frequently-run queries on large tables where you want to eliminate the table access entirely. Add the extra columns at the cost of a slightly larger index.
How to talk about indexes in an interview
The right pattern: identify the hot queries first, then derive the indexes.
“The hot query is: all pending orders for a given user, sorted by creation time. I’d create a composite index on (user_id, status, created_at). This serves the user_id equality, the status equality, and the created_at range all from one index. I’d make it a partial index filtered to status=’pending’ to reduce the index size since orders spend very little time in pending state at scale.”
Four sentences. Names the query. Names the index type. Names the column order reasoning. Names the optimization.
That’s the L5 format for any index discussion. L6 adds: name the trade-off (write overhead — every INSERT and UPDATE on the indexed columns must also update the index) and the conditions under which you’d remove it (if write performance degrades and the query is no longer hot).
CTA: The cohort curriculum goes beyond surface-level statements — walking through exact index selection for high-throughput queries, ordering composite keys, leveraging partial and covering indexes to drop table read I/O, and evaluating the write-amplification trade-offs at scale.
Subscription link
https://systemdr.systemdrd.com/subscribe
Interested in mastering this topic? Unlock premium content with deeper explanations and real implementation patterns.