Schemas and the index that saves the query
A query without an index is a full scan, and a full scan is a latency cliff.
What you will be able to do
- Explain why an index turns a scan into a lookup
- Order columns correctly in a composite index
- Balance read speed against write amplification
- Choose keys and constraints that keep data correct
What an index is
Rows in a table are stored in no particular order. Without an index, finding the ten rows where user_id = 42 means reading every row — a full scan. An index is a separate sorted structure mapping column values to row locations, so the database descends a tree instead of sweeping the table.
The interview move is to say which query you are optimising. "The hot path is fetching a user's recent orders, so I would index (user_id, created_at DESC) and paginate by keyset on that pair." A bare "add an index" is not a design decision.
Composite order matters
A composite index on (a, b, c) can serve queries filtering on a, on a and b, or on all three — in that prefix order. It cannot efficiently serve a query filtering only on b. This is why the column order is a design decision, not a detail.
| Query | Index (user_id, created_at) | Why |
|---|---|---|
user_id = 42 ORDER BY created_at DESC LIMIT 20 | Perfect | Equality on the prefix, sort on the second column |
user_id = 42 AND created_at > '2026-01-01' | Good | Prefix equality then range |
created_at > '2026-01-01' | Poor | The leading column is missing; the index cannot be used well |
status = 'paid' | Unusable | No shared prefix with this index |
(user_id, created_at, total_cents) lets a recent-orders list render without row lookups. That is often a bigger win than adding a cache.The write side of the ledger
Indexes are maintained on insert, update, and delete. Ten indexes on a hot table means ten structures to update per write, more memory pressure, and slower vacuuming. On a write-heavy event table, the correct answer is usually a small number of carefully chosen indexes and an append-only design rather than an index for every query.
- Low-cardinality columns (status with five values) rarely help alone; combine them with a selective column.
- Partial indexes — index only rows where
status = 'pending'— are small and ideal for queues. - Text search with
LIKE '%term%'cannot use a normal index; that is a search engine or trigram index conversation. - Index bloat from updates is real: watch size growth, not just query plans.
Schema decisions that age well
created_at, updated_at, and deleted_at support auditing, pagination, and recovery. Every query then needs deleted_at IS NULL, so a partial index handles it cleanly.Worth remembering
- An index is a sorted structure on a column, so range and equality lookups avoid scanning the table.
- In a composite index, put equality columns first and the range or sort column last.
- Every index adds work to every write. Index what your queries need, not what might be nice.
- Pick primary keys that are stable and reasonably distributed — UUIDs cost space and page splits.