Lesson 3.4 · 10 min read
Your application is fast. It has four hundred rows in it, and every page loads instantly, and you have never once thought about how the database finds anything.
Then it has four hundred thousand rows, and a page that took eighty milliseconds takes six seconds, and nothing in your code changed.
This post is about that transition, which arrives without warning and is entirely predictable in advance. It's also the most practical post in this module, because the two ideas in it, indexes and the N+1 problem, account for the overwhelming majority of slow queries in real applications. Not exotic problems. These two, over and over, everywhere.
And once they're understood, we can finally answer the question post 3.1 raised and dodged: if the data model is expensive to change, how does anyone ever change it?
Ask a database for the user whose email is a particular address. What does it actually do?
Without help, it does the only thing it can. It looks at the first row and checks. Then the second. Then the third. All the way to the end, because even after finding a match it may not know there isn't another one. This is a full table scan, and its cost grows in direct proportion to the size of the table. A thousand rows: instant. A million rows: noticeable. Ten million: your page is broken and your database is on fire.
Nothing about that is a flaw. The database is doing the only thing available to it, because it has no idea where the row might be, and the rows are not in any useful order.
Now think about how you find a word in a dictionary. You do not read from page one. The dictionary is sorted, so you can jump, narrow, jump again, and arrive in a handful of steps. Doubling the size of the dictionary adds one step, not double the time.
An index is that. It's a separate structure the database maintains, holding the values of one column in sorted order, each pointing back at the row it came from. Ask for an email address on an indexed column, and the database jumps to it, in a handful of steps, regardless of table size.
The difference is not a matter of degree. A scan of ten million rows might take seconds. An indexed lookup on the same table takes about as long as an indexed lookup on ten thousand rows, which is to say, no time at all. The index is why databases feel instant, and the absence of one is why they suddenly don't.
If indexes are this good, index everything. This is the wrong conclusion, and understanding why teaches you when to use them.
An index is a second structure holding a copy of some data, kept sorted. It takes space. And, more importantly, it must be maintained: every insert, update, and delete has to update every index on that table, keeping each one correctly sorted. A table with eight indexes does eight extra pieces of work on every single write.
So the trade is exactly this: indexes make reads fast and writes slower. Most applications read far more than they write, which is why indexing is usually a clear win. But a table taking heavy writes, an event log, a metrics table, should carry as few indexes as it can.
Which tells you where indexes belong. Index the columns you filter on, the ones appearing in WHERE clauses. Index the columns you sort on. And, most importantly and most often forgotten, index your foreign keys, the author_id and project_id columns from the relationships post. Those columns are what every join follows, and a join across an unindexed foreign key is a full table scan hiding inside an innocent-looking query. Primary keys are indexed for you automatically. Foreign keys, in many databases, are not.
There's one more thing worth knowing, because it explains a whole class of confusing behavior. An index on a column is sorted by that column, so it can help you find rows by that column, and it is useless for finding rows by a different one. An index on email does nothing for a query filtering on city. Indexes are specific, and a query is fast only if an index exists for the way that particular query is asking.
And there's a tool for seeing all of this. Every serious database will tell you how it plans to execute a query if you ask it, usually with a keyword like EXPLAIN placed before the query. It will say, in effect, "I'm going to scan this whole table" or "I'm going to use this index." You do not need to understand the whole output. You need to be able to see the words "sequential scan" on a large table and recognize that you have found your problem. That single skill, asking the database how it intends to answer, is worth more than any amount of guessing.
Now the other one, and it is the most common performance bug in web applications by a wide margin, because it is invisible in the code.
You have a page listing fifty posts, each showing its author's name. The code, quite reasonably, fetches the posts. Then, for each post, it fetches the author.
Count the queries. One to get the posts. Then fifty more, one per post. Fifty-one queries to render one page. That's the N+1: one query, plus one more for each of the N results.
Here is why it hides so well. In the code it looks like a loop over posts, and inside the loop something reads post.author.name, which reads like accessing a property. It doesn't look like a database query at all. Modern data access libraries make it look exactly like ordinary field access, and quietly issue a query each time. So the bug is invisible at the site where it occurs.
And it is invisible in development, because you have five posts, and fifty-one queries against a local database take four milliseconds. In production, with fifty posts on the page and a database across the network, it's fifty-one round trips, each with the latency of post 0.3, and your page takes two seconds while your database handles fifty times the load it should.
You already have the tools to see it. Post 5.6 taught you to ask, of every arrow on a diagram, how many times it gets traversed. This is the answer being fifty when you assumed it was one.
The fix is to ask for everything at once. Fetch the posts, collect the author ids, and fetch all those authors in a single query. Two queries total, regardless of whether the page shows fifty posts or five hundred. Most data libraries have a way to declare this, some phrasing meaning "when you load posts, load their authors too," and it turns fifty-one queries into two.
The diagnostic instinct is what matters, and it's simple. When you're inside a loop, you must not be querying. If you find yourself iterating over a collection and touching related data, ask whether that touch is a query. If it is, you have an N+1, and you should fetch the related data once, before the loop.
Post 3.1 told you that structure decides what's possible and that changing it later, with a million rows in the table, is a real operation with real cost. That was true and it was incomplete, because it left the impression that schemas are somehow permanent. They are not. They change constantly. There's simply a discipline for it.
A migration is a single, ordered, versioned change to the database schema, expressed as a file in your repository. Add a column. Create a table. Add an index. Each migration is written once, applied in order, and recorded, so the database knows which have already run.
Sit with what that gives you, because it is exactly the properties of Git applied to your database's structure. The schema is now in version control, alongside the code that depends on it. A fresh database can be built from nothing by running every migration in order. A colleague pulls your branch, runs the migrations, and their database matches yours. The deployment pipeline runs them before starting the new code. Nobody ever changes a production schema by hand, which is how schemas become mysteries nobody can reproduce.
The subtlety, and this is the part that makes migrations genuinely interesting engineering rather than paperwork, is that during a deployment, the old code and the new schema exist at the same time. For a period, some servers are running the previous version of the application against the migrated database. If your migration deletes a column the old code still reads, those servers throw errors for the duration of the deploy.
Which is why destructive changes are done in stages, and this pattern is worth knowing because it is how careful teams operate everywhere. To remove a column: first deploy code that no longer uses it. Then, later, once everything is running that code, deploy the migration that drops it. To rename one: add the new column, write to both, migrate the data, switch reads to the new one, stop writing the old, and only then drop it. Tedious, and each step is safe, and at no point is there a moment where running code expects something that isn't there.
And adding an index to an enormous table can lock it while it builds, which means every query waits, which means your application is down. Databases offer ways to build indexes without that lock, and knowing that this hazard exists is the important part.
The general principle is one you can carry: the safe schema change is the one that is compatible with both the old code and the new. Additive changes are safe. Removals are safe only after nothing reads them anymore. Every risky migration can be decomposed into a sequence of safe ones, and doing so takes an extra deploy and prevents an outage.
Three things, and they are among the most immediately useful in this series.
When something is slow, you now have two hypotheses before you have any evidence, and they are right most of the time. Either a query has no index and is scanning a table, or a loop is issuing a query per item. Check those two before considering anything else, and you will fix most performance problems you ever encounter.
When AI writes your data access code, you know what to look for. It will produce a WHERE clause on an unindexed column without comment, because it works. It will write the loop with the query inside it, because that's the readable way to express the intent, and it will be correct and slow. Neither is a bug in the sense of returning the wrong answer. Both are bugs that appear only under real data, which is exactly the class of failure the introduction to this series promised to prepare you for.
And when the schema needs to change, you know it can, safely, in versioned steps that leave no moment of inconsistency. The data model being expensive to change is not a reason to fear changing it. It's a reason to change it deliberately.
Do this before the next post
Ask any database you have access to how it plans to run a query, using EXPLAIN. Run it on a query filtering by a column that has no index, and see the words describing a full scan. Then add an index on that column and run it again. Watch the plan change. You have just seen, directly, the mechanism that determines whether real applications are fast, and it took two minutes.
Then go looking for an N+1 in something you've built. The fastest way is to turn on query logging, which most frameworks offer, so every database query is printed as it runs. Load a page with a list on it, and count the queries. If a page showing twenty items issued twenty-one queries, you have found one, and you will now see them everywhere for the rest of your life, which is exactly the intended effect.
Finally, look at any migration file in any real project. Read it as what it is: a single, ordered, reversible statement of how the shape of the world changed on one particular day. Then imagine a deploy where the old code is still running while that migration has already been applied, and ask whether it would survive. If the migration adds something, it survives. If it takes something away, it doesn't, and now you understand why the careful ones are boring on purpose.