An index is a data structure that lets the database find rows without scanning the whole table, like the index at the back of a book. Instead of reading every page to find "normalization", you look it up and jump straight to page 149. When you sketch a schema in an interview, call out which columns are indexed and why.
Index for your access patterns#
Indexes should directly support your most important queries. For a social media app:
| Query | Index |
|---|---|
| All posts by a user | posts(user_id) |
| Recent posts, newest first | posts(created_at) |
| A user's recent posts | Composite posts(user_id, created_at) |
CREATE INDEX idx_posts_user_created
ON posts (user_id, created_at DESC);
-- Served by the composite index:
-- filter on user_id, already sorted by created_at
SELECT * FROM posts
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;Column order matters in a composite index. (user_id, created_at) helps queries that filter on user_id, with or without created_at. It doesn't help a query that filters only on created_at.
The trade-off#
Every index speeds up some reads but adds work to every insert and update, because the index has to be updated too. It also takes storage. Index the columns your key queries filter, join or sort on, and skip the rest. In a write-heavy system, dropping unused indexes is one of the cheapest wins (see Scaling Writes).
In an interview#
Connect each index to an endpoint: "GET /users/{id}/posts needs an index on posts.user_id" shows you're thinking about real query performance.
Key takeaways#
- An index trades extra write work and storage for much faster lookups.
- Choose indexes from your access patterns, not from the schema alone.
- A composite index serves queries that use its leading column(s).
- Name the endpoint each index supports.