Storage & Data
Database Indexes
Why a query that's instant at 10,000 rows becomes a full table scan at 10 million, and why every index that fixes it has a cost on every write.
Storage & DataIntermediate5 min read
After this lesson, you should be able to:
- Explain why an index speeds up filtered reads but adds a cost to every write on that column.
- Recognize that adding more indexes isn't free optimization, it's a trade against write latency.
- Identify when a column is actually worth indexing versus when a table scan is still fast enough.
- Connect an index's storage cost to it being its own on-disk structure, not just metadata.
At a Glance
- A query with no matching index scans every row to find the ones it needs. Cost grows with the size of the table, not the size of the answer.
- An index speeds up exactly the reads that use it, and has to be updated on every write to that column, so more indexes means slower writes.
- Adding an index is a trade, not free optimization: write cost for read cost, on one specific, named query pattern.
01 The Problem
A support platform's tickets table starts small, and SELECT * FROM tickets WHERE customer_email = ? returns in a few milliseconds. A year and 40 million tickets later, the same query takes eight seconds, because nothing tells Postgres where to find rows with a given email except checking all 40 million of them, one at a time, in whatever order they happen to sit on disk.
An index on customer_email fixes that read in a fraction of a millisecond. Having learned the lesson once, the team starts indexing every column anyone's ever filtered by, "just in case": status, assigned agent, created date, priority, ten indexes on one table. Ticket creation, which used to take a few milliseconds, now takes forty, because every one of those ten indexes has to be updated on every single insert, whether or not that insert has anything to do with the queries those indexes were built for.
Insert cost: a few indexes vs. ten
Neither extreme is right. No indexes, and reads crawl as the table grows. An index on everything, and every write pays for indexes it never uses. This is the actual trade-off.
02 What It Is
An index is a separate, ordered structure built alongside a table that lets the database find rows matching a condition on an indexed column without checking every row, typically a B-tree, sorted, so the database can jump straight to the matching range instead of scanning start to finish. Without one, a query filtering on that column is a sequential scan: read every row, check the condition, discard what doesn't match. That cost grows directly with the size of the table, not the size of the answer. A query returning one row out of 40 million still touches all 40 million to find it.
An index isn't free storage or free maintenance. Every insert, update, or delete touching an indexed column also updates the index's own structure, keeping it sorted, on top of writing the row itself. An index that speeds up one query is quietly present on every write to that table, whether or not that write has anything to do with the query the index was built for.
In Plain Terms
A phone book sorted by last name lets you find "Martinez" in seconds by flipping straight to the M section. An unsorted stack of index cards with the same names on them means checking every card, in whatever order they were dropped in, until you happen to find the right one or run out of cards. The sorted phone book isn't free, though: every time someone moves, changes their name, or gets a new number, somebody has to find the right spot and reprint or re-sort the book to keep it usable. The unsorted stack just gets a new card tossed on top.
03 How It Works
Rendering diagram…
Rendering diagram…
04 Trade-offs
| No index | Index | |
|---|---|---|
| Read cost (filtering on this column) | Scans every row; cost grows with table size regardless of how few rows match | Jumps straight to matching rows; cost stays roughly flat as the table grows |
| Write cost | Nothing extra: a write only touches the table itself | Every insert, update, or delete on this column also updates the index |
| Storage | None beyond the table itself | The index is its own structure on disk, often a meaningful fraction of the table's size |
| Worth it when | The column is rarely filtered on, or the table is small enough that a scan is still fast | The column is filtered on constantly, and the table is large enough that a scan is measurably slow |
Watch out
It's tempting to treat indexes as pure upside: this query is slow, add an index, it's fast now, why not do that everywhere. An index that isn't backing an actual query is pure cost, it still gets updated on every write, it still takes disk space, and nobody's reads get faster because of it. The question isn't "could this column benefit from an index," almost any column technically could. It's "does a real query filter on this column often enough that the write cost is worth paying."
05 Where You'll Actually See This
The URL shortener case study depends on this silently: when Redis misses and Postgres has to answer GET /:code directly, that query is only fast because code is indexed, as the primary key or a unique constraint, not because Postgres is inherently fast at scanning. Without that index, a cache miss wouldn't be a slower path, it would be a full table scan on every single miss.
It's also worth separating this from database sharding: the two get confused because both involve a "key" decision, but they solve different problems. An index doesn't help you scale writes, indexing a column makes every write to it slower, not faster, it only ever helps reads. And sharding doesn't help you find a row fast within one shard, each shard is still a normal database that needs its own indexes to avoid a scan. You need both, for different reasons: sharding to spread write load across machines, indexes to make reads fast on any one of them.
How to reason about it
When asked whether you'd add an index, don't answer with "yes, indexes make things faster." Name the actual query that's slow, say what column it filters on, and say how often the table is written to, because that write volume is the cost you're weighing against the read you're fixing. An index nobody's read pattern needs is a tax on every write with no query it's making faster.
06 Quick Check
After adding indexes on five columns to speed up five different dashboard queries, a team notices that inserting a new row now takes noticeably longer than before, even though none of the five dashboard queries touch the insert path. Why?