Why some questions are cheap and some are ruinous
Indexes explained properly, plus the query pattern that quietly destroys apps.
Without help, answering "find the user with this email" means reading every row until a match is found. With ten rows that is instant. With ten million it is a disaster. An index is the help.
That last clause is the part people miss. An index on email does nothing for a search by phone number. Every index also has to be updated on every write, so indexing every column makes reads fast and writes slow. Indexes are a deliberate trade, chosen per query pattern.
The N+1 query problem
This one deserves its own name because it is so common and so invisible in development. You fetch a list of 50 posts: that is one query. Then, for each post, your code fetches the author. That is 50 more queries. You wrote what looked like a simple loop and issued 51 round trips to the database.
- In development
5 posts, local database, 6 queries, 8ms total. Feels fine. - In production
500 posts, database over the network, 501 queries, 12 seconds. - Under load
Every visitor does this at once. The database saturates and everything stops.
How many database round trips does this page make?
It is a question with a countable answer, and the count should not grow with the number of items on the page.
What to remember
- An index makes one kind of lookup fast, not all lookups.
- Indexes cost write speed and storage, so they are chosen, not sprinkled.
- A database call inside a loop is the N+1 pattern and it scales terribly.
Terms in this lesson
Field notes
Loaded from a deliberately slow source. The lesson above was already readable while this was still travelling. That is streaming, and it is the same trick a chat interface uses.
Fine at 500 rows, dead at 500,000
A list page loaded posts, then fetched each author separately. Development had eleven posts. The launch had four thousand, and every visitor issued four thousand queries. The database saturated eight minutes after the announcement.
The N+1 problem, at scale
The rename that took the site down
A column was renamed in a single migration during a deploy. For forty seconds the old code was still running and querying a column that no longer existed. Expand-then-contract exists precisely for those forty seconds.
Postmortem, e-commerce team
resolved in 900ms · region iad1
Hide field notes toggles a search param the loader reads. With it off, the slow promise is never created, so nothing streams.