Database Indexes That Hurt: When Adding an Index Slows You Down

The schoolroom version of database performance is simple: queries are slow because the database is doing a sequential scan, you add an index, the database uses the index, and the queries are fast. This is true often enough to be a useful default, and it leads most teams to a habit of adding indexes whenever a slow query shows up. The habit produces real wins for years before the cost side starts mattering, and by the time it does, the indexes have been there long enough that nobody remembers which ones are paying their way.

The cost side is the focus of this post. We've added and removed indexes across DocuMint, CronPing, FlagBit, and WebhookVault, and the indexes that hurt have a recognizable shape.

Write amplification is the price of every index

An index is a sorted data structure that the database maintains alongside the table. Every INSERT, UPDATE that touches an indexed column, and DELETE has to update the table and every relevant index. With one B-tree index, a write touches two structures. With ten indexes, a write touches eleven. The CPU and IO cost compound linearly.

For read-heavy workloads, the trade is usually fine. Each index pays for itself across many reads. For write-heavy workloads, the math changes. A high-frequency table with ten indexes might spend most of its IO budget maintaining indexes the queries don't actually use. The signal that this is happening is write throughput dropping after an index is added, not query throughput improving.

The mitigation is to audit indexes against the queries that actually run. PostgreSQL's pg_stat_user_indexes view exposes idx_scan per index — the number of times each index has been used since stats were last reset. Indexes with zero scans over a multi-week period are paying their write cost and returning nothing.

Redundant indexes that nobody noticed

An index on (a) is redundant if there's also an index on (a, b). Any query that uses the (a) index can use the (a, b) index instead, with at most a small constant overhead. The (a) index is paying its full write cost while the planner ignores it.

This pattern accumulates silently. A team adds an index on (user_id) for one query. Later, a different team adds an index on (user_id, created_at) for a different query. Nobody removes the first index because nobody is sure it's safe to. Over years, the indexes pile up.

The detection query is straightforward: list all indexes, identify pairs where one is a prefix of the other, and verify that the prefix index has no unique constraint or other special property. PostgreSQL's pg_indexes system view plus a few CTEs gets you there in twenty lines of SQL. Running this query on a long-lived production database almost always finds candidates.

Plan regressions when an index is added

Adding an index gives the planner a new option. The planner is supposed to pick the best option, but the cost model is approximate, and approximations sometimes go wrong. An index that should make queries faster sometimes makes them slower, because the planner now picks the new index for queries that were running fine without it.

The classic case is an index on a low-selectivity column. A table with 10 million rows and a status column with three distinct values doesn't benefit much from an index on status — most queries will return millions of rows, and reading them via the index is slower than reading the table sequentially. But the planner sees the index, estimates a low cost based on stale statistics or correlated columns, and picks the index. Query time goes up by an order of magnitude.

The mitigation is to run EXPLAIN ANALYZE on representative queries before and after adding an index, and to monitor query times for the first few days. If a previously-fast query gets slower, the new index is the most likely cause. The fix is usually to add a partial index covering only the predicate the query actually filters on, rather than indexing the whole column.

Indexes that compete for the same buffer cache

The database keeps recently-used pages in memory. The buffer cache is a fixed-size resource shared across all tables and indexes. Every additional index increases the working set, and once the working set exceeds the buffer cache size, performance degrades sharply because pages have to be read from disk on a cache miss.

The visible symptom is that queries get slower over time as the database grows, even though no individual query has changed. The underlying cause is often that the indexes have grown to a size that no longer fits in cache. Removing unused indexes can produce a dramatic improvement that looks magical: nothing about the query changed, but suddenly it's three times faster, because the relevant index now fits in cache where it didn't before.

Index bloat from update-heavy patterns

PostgreSQL's MVCC implementation creates a new tuple for every UPDATE; the old tuple is marked dead and eventually cleaned up by VACUUM. Index entries point to specific tuple versions, so an UPDATE that changes an indexed column has to insert a new index entry alongside the new tuple. The old index entry stays around until VACUUM removes it.

For tables with frequent updates on indexed columns, the index can grow much larger than the table itself. We've seen 5GB indexes on 500MB tables in pathological cases. The bloat hurts both write throughput (more pages to update) and read throughput (less cache efficiency).

The mitigations are: avoid indexing columns that update frequently if possible; tune autovacuum to be more aggressive on the affected tables; and use REINDEX CONCURRENTLY periodically on heavily-updated indexes. For tables with predictable update patterns, BRIN indexes (block-range indexes) can be a good alternative to B-trees because they're much smaller and don't suffer the same bloat.

Indexes that don't help because they can't

An index on (a) helps queries that filter on a, sort by a, or join on a. It doesn't help queries that filter on a function of a (like LOWER(email)) unless the index is built on the same expression. It doesn't help queries that use a leading wildcard in LIKE (like email LIKE '%@example.com') because B-tree indexes can't support that. It doesn't help queries that compare across types unless the comparison is type-stable.

The planner won't use an index it can't use, so these cases produce sequential scans. The mitigations are case-specific: expression indexes for function predicates, trigram indexes (pg_trgm) for wildcard patterns, careful type matching in queries. The general principle is that indexes are matched to specific query shapes, and a query that doesn't fit any existing index won't benefit from any number of unrelated indexes.

The discipline that actually works

The pattern we've settled on across the four products is: indexes are added with the query they support documented in the migration, indexes are reviewed quarterly against pg_stat_user_indexes for unused entries, redundant-index detection is part of the schema lint, and adding an index is treated as a change requiring the same EXPLAIN-before-and-after care as a query rewrite.

The deeper observation is that indexes are infrastructure, not optimization. They have a creation cost, an ongoing maintenance cost, and a removal cost. Treating them as cheap and additive — the implicit assumption when "add an index" is the universal answer to "this query is slow" — accumulates technical debt that compounds with every additional index. Treating them as deliberate engineering decisions, with the same care you'd apply to adding a column or a foreign key, produces a schema that ages well. The first habit feels productive in the moment. The second one keeps the database fast at year three.