Database Indexes Are Not Free: The Read, Write, and Storage Trade-offs You Need to Know

Databases built for extreme write volumes often use storage structures designed to absorb writes efficiently rather than the B-tree indexes that relational databases rely on, precisely because the index maintenance cost on writes is so significant at scale.

Learn more about Read-heavy vs. Write-heavy workloads.

Where This Matters Most

The indexing trade-off is not equally important everywhere. It becomes critical in systems with high write volume or where write latency matters, and several common system types fall into this category.

Order history is a system where this trade-off appears constantly. Orders are written once and then read many times for display and reporting, which makes it read-heavy and a good candidate for indexing the access patterns. But the indexes must still match the actual queries, or they add write cost for no read benefit.

Payment transactions raise the stakes. This data is critical and often write-intensive, and write latency matters because users wait on payment confirmation. Here, indexes must be chosen with care, because every unnecessary index adds latency to a path where latency is felt directly.

Chat messages are a high write-volume system. Messages arrive in enormous numbers, and the write path is hot. Carrying many indexes on a messages table directly slows down the rate at which messages can be stored, so indexing must be minimal and deliberate.

User activity logs and analytics events are perhaps the clearest cases. These are append-heavy systems that ingest a constant, massive stream of writes. Every index on the ingestion path slows down how fast events can be recorded. Over-indexing these tables can throttle the entire ingest pipeline, which is why such systems often index sparingly and rely on separate systems optimized for querying the data later.

The pattern across all of these is that the more a system writes, the more carefully it must index. The reflexive addition of indexes that might be harmless on a small read-heavy table can be genuinely damaging on these high-volume write paths.

The Rules for Indexing Well

Given the trade-off, a set of practical rules guides good indexing. Following them keeps the read benefits while avoiding the worst of the write costs.

Index the Queries You Actually Run

The first rule is to index based on real access patterns, not speculation.

An index is only valuable if queries actually use it, and an index that no query uses provides no read benefit while still taxing every write. Before adding an index, identify the specific queries it will accelerate. If you cannot name the query, you should not add the index.

Avoid Indexing Frequently Changing Columns

The second rule is to be cautious about indexing columns that change often. Recall that updating an indexed column requires updating the index, removing the old value and inserting the new one.

A column that is updated constantly therefore makes its index expensive to maintain. Indexing a stable column like a creation timestamp is cheap to keep current, while indexing a rapidly changing column means paying the index maintenance cost on a huge fraction of writes.

Avoid Indexing Low-Cardinality Fields

The third rule concerns cardinality, which is the number of distinct values in a column.

A high-cardinality column like a user identifier or an email address has many distinct values, so an index on it is highly selective, meaning a lookup narrows down to very few rows. This is exactly what indexes are good at.

A low-cardinality column is the opposite.

Consider a status field whose value is one of active or inactive. Indexing this is usually a poor idea, because a query for active rows might match a large fraction of the entire table, and at that point an index provides little benefit over a scan.

The database's query planner often recognizes this and ignores such an index entirely, which means it adds write cost while providing no read benefit.

When you do need to query a low-cardinality field, a more targeted solution like a partial index, which indexes only the rows matching a specific condition, is often far more efficient than a full index on the column.

Measure Before Adding More

The fourth rule is to measure rather than guess. Before adding several more indexes, examine how queries actually execute, using the database's tools to see whether a query is using an index or scanning. Add indexes deliberately, one at a time, in response to a real performance need, and verify that each one is used and helps. Periodically review existing indexes and remove ones that are unused or duplicated, because they are pure cost. Indexing is an ongoing decision informed by measurement, not a one-time act of adding everything that might help.

A Few Refinements Worth Knowing

Beyond the core rules, a few refinements separate competent indexing from expert indexing.

A composite index on multiple columns can serve queries that filter on those columns together, but the order of columns matters, since such an index helps queries that use its leftmost columns and not ones that use only the later columns.

A covering index that includes all the columns a query needs can answer the query without touching the table at all, which is a powerful optimization for hot queries. And a partial index that covers only a subset of rows keeps the index small and cheap while still accelerating the queries that matter. Knowing these tools lets you get the read benefit while minimizing the write and storage cost.

The Answer That Wins Interviews

All of this leads to the practical payoff, which is how to handle indexing in a system design interview.

The reflexive answer marks a candidate as junior, while the considered answer signals real understanding.

The weak answer is simply, "We'll add an index." It is not wrong, but it treats the index as free and reveals no awareness of the cost. Interviewers hear this constantly, and it does nothing to distinguish a candidate.

The strong answer names the trade-off explicitly. It sounds like, "I'll add an index to support this access pattern, but I'll be deliberate about it, because each index improves reads at the cost of write latency and storage. Since this is a write-heavy table, I'll keep the indexes minimal and only index the queries we actually run." This answer shows that you understand both sides of the decision and that you are weighing them against the specific workload.

This is exactly the kind of reasoning interviewers want to hear, because it demonstrates trade-off thinking, which is the single clearest signal of senior-level judgment in a system design interview.

The candidate who explains why and at what cost stands out from the many who simply reach for the index.

The same principle applies in real engineering, where the difference between a healthy database and a struggling one is often the discipline to index deliberately rather than reflexively.

Key Takeaways

• Indexes are not free, because they speed up reads but slow down writes, consume storage, and compete for memory.

• An index is a separate sorted structure, usually a B-tree, that the database must keep in sync with the table on every write.

• Every write pays for every index, since each insert, update, and delete must maintain the table and all of its indexes, which is why ten indexes can cripple write performance.

• The core trade-off is reads versus writes, so the read-to-write ratio of a table should be the first thing you consider before indexing.

• The trade-off matters most in write-heavy systems like chat messages, activity logs, and analytics events, where over-indexing throttles ingestion.

• Good indexing follows clear rules, namely indexing real query patterns, avoiding frequently changing and low-cardinality columns, and measuring before adding more.

• The interview-winning answer names the trade-off, explaining that you will index the access pattern carefully because each index trades write latency and storage for read speed.

An index is one of the most useful tools a database offers, but it is not a free one.

It buys read speed with write latency, storage, and memory, and the value of that trade depends entirely on the workload.

The engineers who use indexes well are the ones who understand both sides of the bargain, index deliberately in response to real query patterns, and stay mindful of the cost on every write. In an interview and in production alike, the goal is never just to add an index.

It is to index the access pattern with full awareness of what it costs, which is exactly the kind of trade-off thinking that separates strong engineers from the rest.