AlgoInsight

System Design

The Hidden Cost of Database Indexes

Every junior engineer learns indexes make queries fast, so they add them everywhere. Then writes slow to a crawl and nobody knows why. At the scale I work at, that 'free' speedup has a very real price. Here's the cost nobody mentions.

By Admin2026-06-1814 min readdatabase-indexperformancesystem-designdatabaseswrite-performancedata-engineering

Every engineer learns the same lesson early: queries are slow, so you add an index, and the query gets fast. It feels like magic, and the natural conclusion is that indexes are free speed - so you add them to every column you ever filter on. I made exactly this mistake earlier in my career, and I've watched countless engineers make it since. Then one day the writes to your database slow to a crawl, your ingestion pipeline starts falling behind, and nobody can figure out why.

The answer, more often than not, is the indexes. They are not free. At the scale I work at - pipelines ingesting billions of events a day - the hidden cost of indexes is not a footnote; it's a number that shows up directly in how much data we can write per second. This is the cost nobody mentions when they teach you that indexes make things fast.

First, what an index actually is

To understand the cost, you have to understand what an index is. A database index is a separate data structure that stores a sorted copy of certain columns, with pointers back to the full rows - much like the index at the back of a book. Without it, finding rows matching a condition means scanning the entire table (reading every row to check). With it, the database can jump straight to the matching rows using the sorted structure, the way you'd use a book's index to jump to a page instead of reading every page.

That's the benefit, and it's real and enormous: an index can turn a query that scans millions of rows into one that finds its answer almost instantly. For reads, indexes are transformative.

But notice the key phrase: an index is a separate data structure that stores a copy of the data. That copy has to be kept up to date. And that is where the cost lives.

The hidden cost: writes pay for reads

Here is the truth that the "indexes make things fast" lesson leaves out: an index speeds up reads by slowing down writes.

Think about what has to happen when you write to a table. Without indexes, inserting a row means writing that one row. Simple. But every index on that table is a separate sorted structure that must also be updated to include the new row - placed in the right sorted position, pointers adjusted. So if a table has five indexes, a single insert now means writing the row plus updating five separate index structures. Every insert, every update, every delete pays this tax, multiplied by the number of indexes.

graph TD
    W[Write one row] --> T[(Update the table)]
    W --> I1[(Update index 1)]
    W --> I2[(Update index 2)]
    W --> I3[(Update index 3)]
    W --> I4[(Update index 4)]
    W --> I5[(Update index 5)]
    T --> DONE[Write complete - 6 structures touched]
    I1 --> DONE
    I5 --> DONE

This diagram shows the hidden cost of indexes on writes. Writing a single row doesn't just update the table - it must also update every index on that table. With five indexes, one write touches six structures: the table plus all five indexes. Every insert, update, and delete pays this cost, multiplied by the number of indexes.

For a read-heavy system where writes are rare, this trade is wonderful - you pay a small write cost occasionally to make frequent reads fast. But flip the workload. In a write-heavy system - and event ingestion is the most write-heavy thing there is - that multiplication is brutal. Every one of those billions of daily events is a write, and if the table it lands in carries several indexes, each event now triggers several index updates. The write throughput you can sustain drops in direct proportion to how many indexes you're maintaining.

Important: The core trade-off, stated plainly: indexes make reads faster and writes slower. They are a read optimization paid for with write performance (and storage). In a read-heavy workload, that trade is usually a bargain. In a write-heavy workload, every index is a tax on your write throughput. The mistake is treating indexes as universally free, when they're actually a trade you should make deliberately, per the workload.

What this looks like at scale

Let me make this concrete with the kind of situation I deal with. Imagine a table receiving a massive stream of incoming events - millions of writes per second at peak. The pressure on this system is overwhelmingly about how fast we can write. Now someone, with the best intentions, adds a few indexes to that table because a dashboard query was slow. Suddenly every one of those millions of writes per second has to also update those indexes, and the ingestion pipeline - which was keeping up - starts falling behind. The queue backs up. Events pile up faster than we can write them. And the root cause is a handful of indexes added to make a read faster, which quietly throttled the writes the entire system depends on.

This is the lesson scale teaches viscerally: on a write-heavy table, indexes are not a free win you sprinkle on. Each one is a deliberate decision that costs write throughput, and at high write volumes that cost is the difference between keeping up and falling behind. We are sometimes deliberately sparing with indexes on our highest-write tables, accepting slower analytical queries (which we handle separately, often by moving the data to a system built for querying) so the ingestion path stays fast.

The other costs

Write performance is the big hidden cost, but there are two more worth knowing:

So an unused or rarely-used index isn't neutral - it's actively costing you on writes, storage, and memory, while providing no read benefit. Unused indexes are pure cost.

A concrete picture of the cost

Let me make the write cost tangible. Imagine a table with no indexes: inserting a row is one write operation - append the row, done. Now add three indexes. Inserting a row becomes four operations: write the row, then update each of the three indexes (find the right sorted position in each B-tree, insert, rebalance if needed). The write does roughly four times the work.

Now scale that. A table taking 10,000 inserts per second with no indexes is doing 10,000 write operations per second. Add those three indexes, and it's effectively doing around 40,000 underlying write operations per second to keep everything current - for the same 10,000 logical inserts. If your storage and database sustain a certain number of operations per second, you've just used four times as much of that budget per insert. On a write-heavy table, this is exactly how adding indexes to speed up a read query quietly cuts your sustainable write throughput by a large factor. The numbers are illustrative, but the shape is real: write cost scales with the number of indexes, and at high write volumes that multiplication is the whole ballgame.

The read side has nuance too

"Indexes make reads fast" isn't unconditional either. An index helps a read only if the query can use it - and not every query can. An index on a column doesn't help a query that filters on a different column, or one that transforms the column in a way the index can't match. A query that ends up reading most of the table anyway may be slower with an index than a straight scan, because the database does index lookups and row fetches. So indexes speed up the specific reads whose access pattern matches the index - not all reads. This matters for the cost calculation: an index that's slowing all your writes while only helping a query you rarely run, or one the database doesn't even use, is almost pure cost. (The companion detailed guide covers selectivity and verifying usage.)

Deciding what to index on a write-heavy table

So how do you decide, when writes are precious? The discipline I apply:

This separation - lean, lightly-indexed tables for fast writes, and separate query-optimized stores (with the indexes those queries need) for reads - is one of the most important patterns for high-write systems, and it directly resolves the tension between "I need to write fast" and "I need to query this richly."

What this cost taught me

The lesson landed for me viscerally the first time I watched an ingestion pipeline fall behind for no obvious reason. Everything had been keeping up, and then it wasn't - the lag on the event stream climbing steadily, events backing up faster than we could write them. We checked the usual suspects: more traffic? a slow downstream? a bad deploy? None of it. The change that did it was small and well-intentioned - someone had added a couple of indexes to a high-write table to make an internal dashboard query faster. Those indexes, helping one infrequent read, were taxing every one of the millions of writes per second flowing through that table, and the cumulative cost pushed the write path past its limit.

The fix was almost anticlimactic: drop the indexes from the write table, and serve the dashboard query from a separate replica where the indexes could live without touching the ingestion path. Writes caught back up almost immediately. But the lesson stuck - at high write volume, an index is never "just a small thing to speed up a query." It's a standing cost on every write, and on a table doing millions of writes per second, even a couple of indexes can be the difference between keeping up and drowning. Now, on any high-write table, indexes are a deliberate, justified decision - never a reflex.

The unused-index problem

One specific cost worth calling out, because it's pure waste: the unused index. Over a system's life, indexes accumulate - added for queries later changed or removed, added "just in case," added by someone who's since moved on. Each still costs write performance, storage, and memory on every write, while providing zero benefit because nothing queries against it. Unused indexes are the clearest example of cost with no upside. Part of maintaining a healthy database is periodically finding indexes nothing uses and dropping them - reclaiming the write throughput, storage, and memory they silently consume. Most databases can tell you which indexes are actually used; auditing for and removing the dead ones is one of the cheapest performance wins available on a write-heavy system.

How to think about indexes correctly

None of this means "don't use indexes" - indexes are essential, and the right ones are transformative. It means add indexes deliberately, based on the workload, not reflexively. The principles I work by:

The mental model that fixes the "add indexes everywhere" instinct: every index is a standing trade - faster reads for slower writes and more storage. Make that trade where the reads matter and the writes can afford it; refuse it where the writes are precious.

Interview: In a system design interview, casually noting the write cost of indexes is a strong signal of real-world experience. When you say "I'll index this for fast lookups," a sharp interviewer might ask about the downside - and "indexes speed reads but slow writes, so on this write-heavy table I'd be careful about how many I add" is exactly the kind of nuanced answer that separates someone who's only read about indexes from someone who's felt their cost. Knowing that indexes are a read optimization with a write price, not free speed, is genuine depth.

The bottom line

Indexes are taught as free speed, and they're not. An index is a separate sorted copy of your data, and keeping that copy current means every write to the table must also update every index on it - so indexes speed up reads by slowing down writes, and they cost storage and memory besides. In a read-heavy workload that trade is a bargain; in a write-heavy one, every index is a tax on the throughput the system depends on. The fix isn't to avoid indexes - they're essential - but to add them deliberately, for the queries you actually run, weighing the write cost, and to remove the ones nothing uses.

Treat each index as a standing trade rather than free magic, and you'll avoid the slow-write mystery that catches so many systems. For the mechanics of how indexes work under the hood, see the detailed indexes guide. We cover database performance and the trade-offs that come with scale across System Design Fundamentals.

Go deeper

More articles