Stop Adding Indexes to Fix Slow Queries — You’re Quietly Killing Your Writes

Every index you add is a tax. Most teams only ever count the discount.

There’s a moment almost every backend engineer has lived through.

A query is slow. Someone opens a dashboard, sees a sequential scan, and does the obvious thing: adds an index. The query gets fast. Everyone moves on. A few weeks later, someone adds another. Then another. Indexes are cheap insurance, right? You add one whenever something feels slow, and you never really think about it again.

Then one day your write throughput falls off a cliff, your p99 on inserts doubles, and nobody can explain why. The queries are all fast. The dashboards are green. And yet the system feels like it’s wading through mud.

Here’s the part the tutorials skip: every index you add makes every write slower. Not a little slower in theory — measurably slower, on every single insert, update, and delete, forever. You’ve been counting the speedup on reads and silently ignoring the bill on writes.

This isn’t a database-specific quirk. It’s true in Postgres, it’s true in MongoDB, it’s true everywhere. So let’s talk about the tax you’re paying — and how to stop overpaying it.

The mental model: an index is a second copy you have to keep in sync

Strip away the B-tree internals and an index is just this: a second, sorted copy of part of your data, maintained separately from the table itself.

That’s why reads get faster — the database can jump straight to the rows it wants instead of scanning everything. But it’s also why writes get slower. The moment you change a row, the database doesn’t just write the row. It also has to go update every index that points at it, keeping each of those sorted copies correct.

One index? You write the row, then update one index. Fine. Ten indexes? You write the row, then update ten separate structures, each one a little bit of extra I/O. Your “simple insert” is now eleven writes wearing a trench coat.

MongoDB’s own documentation says it plainly: each index on a collection adds overhead, and for every insert or delete the database adds or removes the corresponding keys from each index. Postgres is the same — every INSERT, UPDATE, and DELETE has to maintain the indexes alongside the table.

So the real cost of an index isn’t the disk space (though that’s real too). It’s that you’ve signed up to do extra work on every write for the rest of that index’s life, in exchange for faster reads on one query shape.

That trade is often worth it. The problem is that nobody’s actually checking whether it is worth it — they’re just adding indexes whenever a read feels slow and never removing them.

The trap: indexes you’re paying for and not using

Here’s the most common version of the disease.

Someone adds an index to speed up a query. Later, the query changes — a new filter, a different sort, a refactor. The old index no longer matches the query, so the database stops using it. But the index is still there. It still gets updated on every single write. You’re paying full price and getting nothing back.

Multiply that across a few years and a few engineers, and you end up with tables carrying a dozen indexes where three are doing real work and the rest are pure overhead — dead weight that slows every write and helps no read.

If you take only one thing from this article, take this: an unused index is strictly worse than no index. It has all of the write cost and none of the read benefit. Finding and deleting those is usually the single highest-leverage database change you can make, and it costs you nothing but the courage to drop something.

That’s the whole idea, and if you stop reading here you’ve got the important part. But if you want to actually find these indexes, measure the real cost, and stop creating redundant ones — that’s the rest of the article.

Going deeper: how to find the indexes that are costing you

Both databases will tell you exactly which indexes are being used, if you ask.

MongoDB ships $indexStats, which gives you an access count per index:

db.orders.aggregate([{ $indexStats: {} }])

You get back something like:

[
  { name: "status_1",     accesses: { ops: 0,     since: "2026-01-01" } },
  { name: "createdAt_1",  accesses: { ops: 45230, since: "2026-01-01" } },
  { name: "legacyField_1", accesses: { ops: 0,    since: "2026-01-01" } }
]

Any index sitting at ops: 0 over a representative window — make sure you've covered a full business cycle, including the monthly report that runs at 2 a.m. — is a candidate for removal.

Postgres exposes the same truth through pg_stat_user_indexes:

SELECT relname AS table, indexrelname AS index, idx_scan AS scans
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

An index with idx_scan = 0 that's been accumulating stats for a while is, with rare exceptions, doing nothing but slowing your writes.

The one caveat for both: an index can have zero scans and still be load-bearing — a unique index enforcing a constraint, or one supporting a query that only runs quarterly. Don’t drop blindly. Confirm what each index is for before you remove it. But “what is this for?” with no good answer is your signal.

The write cost isn’t uniform — and that’s where it gets interesting

A naive reading of “every index slows every write” overstates things, and a sharp reviewer will call you on it. The reality is more nuanced, and the nuances are exactly where good index hygiene lives.

In Postgres, an update only touches the indexes whose columns actually changed. If you have an index on created_at and you update a row's status but never its created_at, that index doesn't need rewriting. Better still, Postgres has an optimization called a HOT update (heap-only tuple): when no indexed column changes and there's room on the same page, the update can skip index maintenance entirely. So a table can carry several indexes and still update cheaply — as long as your hot write path isn't touching the indexed columns.

This flips a common piece of advice. The question isn’t just “how many indexes do I have?” It’s “do my most frequent writes touch indexed columns?” An index on a column you rarely update is nearly free on writes. An index on a column you update on every single request is expensive on every single request.

Partial indexes push this further. In Postgres:

CREATE INDEX idx_active_orders ON orders (created_at)
WHERE status = 'active';

This index only needs maintenance for rows where status = 'active'. If 95% of your rows are archived, you've cut the write overhead of that index by roughly 95% while keeping it fast for the queries that only ever look at active orders. MongoDB has the same tool in partial and sparse indexes — it only updates them when the affected document actually qualifies for the index.

The lesson: before you delete an index to save write cost, ask whether you can make it smaller instead. A partial index often gives you the read you need at a fraction of the write tax.

The redundant index nobody notices

Here’s a free win that’s almost always hiding in a mature codebase.

Compound indexes work on prefixes. An index on {a: 1, b: 1} can serve queries that filter on a alone, because a is the leading column. Which means if you also have a separate index on just {a: 1}, that second index is redundant — the compound one already covers everything it does.

But the redundant {a: 1} index still gets fully maintained on every write. You're paying for two index updates to get the capability of one.

This happens constantly: someone adds {a: 1} early on, then later someone adds {a: 1, b: 1} for a new query, and nobody goes back to remove the now-pointless single-column index. Auditing for prefix-redundant indexes — in either database — routinely turns up indexes you can drop with zero loss of read performance and immediate gains on writes.

The same prefix logic is also why column order in a compound index matters enormously, but that’s a whole article of its own.

A practical audit you can run this week

You don’t need a project for this. You need about an hour.

1. Pull index usage stats. $indexStats in MongoDB, pg_stat_user_indexes in Postgres. Make sure the stats window covers a representative period, not just the last hour.
2. Flag the zero-use indexes. For each one, find the answer to “what query or constraint is this for?” No answer → strong drop candidate.
3. Hunt for prefix redundancy. Any single-column index whose column is the leading field of an existing compound index is almost certainly droppable.
4. Check your hot write path. Which columns get updated most often? Indexes on those are your most expensive ones — make sure each is genuinely earning its keep, and consider whether a partial index would do.
5. Drop in staging first, measure, then production. Watch write latency and the read queries you care about. Indexes are reversible — you can always recreate one — so this is low-risk if you measure.

Do this once and most teams reclaim real write headroom they didn’t know they’d given away.

The takeaway

Indexes aren’t free, and they aren’t a default. Each one is a standing trade: faster reads on a specific query shape, paid for with slower writes on every operation that touches it, forever. That trade is frequently worth making — but only if you’re actually making it deliberately, and only as long as the read benefit still exists.

Most slow-write mysteries aren’t mysteries. They’re a pile of indexes that someone added to fix a read months ago, that no longer match any query, quietly taxing every write in the system.

Stop adding indexes reflexively. Start auditing the ones you have. The fastest write is the one that doesn’t have to update an index nobody’s reading from.

If you found this useful, the same “you’re probably doing it wrong” energy is in my piece on why storing JWTs in localStorage is a security mistake — same instinct, different layer of the stack.

https://levelup.gitconnected.com/stop-using-jwt-for-authentication-the-way-you-learned-it-e96af029f256