Index Write Amplification

An index is usually discussed as a read optimisation, and the discussion stops there. Every index is also a second tree that every write must maintain, inside the same transaction, because the index has to stay consistent with the table.

The arithmetic is unforgiving on a write-heavy table. Six indexes against 40,000 writes a minute is 240,000 additional index maintenance operations a minute, each dirtying pages and each generating write-ahead log records. On a table taking sustained writes, a handful of indexes can halve write throughput.

There is a second cost that is larger and quieter. Index pages compete with table pages for the buffer pool. Adding tens of gigabytes of index to a 400 GB table evicts the data pages that were making everything else fast, and the symptom is not a slow query but a database that got generally slower with nothing to blame.

Why it matters

It makes "add an index" a decision with two sides, and only one side appears in the proposal. The query that motivated the index gets faster by an amount that is easy to demonstrate; the writes get slower by an amount nobody measures, spread across every other workload on the table.

It is also the reason index proposals should include a deletion. Mature tables reliably carry indexes that nothing has used in a year, the database records usage statistics that would say so, and almost nobody looks — so the cheapest way to afford a new index is usually to drop two old ones.

Implementation patterns

• Prefer one composite index to several single-column ones. Three queries filtering on the same leading column often share an index if the column order is chosen for them rather than for one of them.
• Check prefix redundancy. An index on (a) is dead weight when (a, b) exists, and this is the most common finding in an index audit.
• Use partial indexes where a constant predicate exists: WHERE status = 'pending' on a table that is 99% completed gives an index a hundredth of the size with almost all of the benefit, and a hundredth of the maintenance cost.
• Cover the query with included columns rather than adding a second index, which removes the heap fetch at the cost of one wider index instead of two.
• Verify with the planner on a representative copy. An index the optimiser does not choose is pure write cost, which happens more often than expected once predicates involve functions or type coercion.
• Read the index usage statistics before proposing, and again a month after.

Industry example

Every mainstream relational engine documents this trade in production and exposes the evidence for it: index size, index usage counters and buffer-pool hit ratios are standard instrumentation, and the standard operational advice for high-ingest tables has long been to index minimally and to drop unused indexes. It is the same structural reason that analytical stores separate the write path from the read path entirely — at ingest volumes where maintaining secondary structures per row is impossible, the answer is to build them asynchronously in a different layer rather than to tune them.

Failure scenarios

• Write throughput halving after a batch of well-intentioned indexes, attributed to traffic growth.
• Buffer-pool eviction making unrelated queries slower, with no query plan change to point at.
• Lock and latch contention on index pages under concurrent inserts on a monotonically increasing key, where every insert touches the same rightmost page.
• Replication lag, because index maintenance is part of the log the replica must apply.
• An index nothing uses, created for a query that was rewritten a year ago, still paid for on every write.

Trade-offs

When not to use it

On a read-mostly table the analysis does not apply. A dimension table, a reference dataset loaded nightly, or a materialised view refreshed off-peak pays essentially no write cost, and the right posture there is to index every access pattern you have. Applying write-heavy caution to a static table is cargo-culted prudence that leaves queries slow for no saving.

The same holds for a small table that fits in memory entirely, where index and heap alike are cached and the scan the index would avoid is a few microseconds.

Interview question

Q: A team proposes six new indexes on a 400 GB table taking 40,000 writes a minute, one per slow query. Review the proposal.

What a strong answer covers: the write-side arithmetic and the log volume it implies; the buffer-pool competition that shows up as a general slowdown with no query to blame; consolidating into composite indexes and checking prefix redundancy; partial and covering alternatives; verifying each candidate is actually chosen by the planner on a copy; asking which existing indexes can be dropped, using the engine's own usage statistics; and the case where six indexes is simply correct, which is a read-mostly table.

Quick check

Quiz: Why can adding indexes slow down queries that do not use them? Index pages compete for the buffer pool, so a large new index evicts table pages that other queries were relying on being cached.

Flashcard: What does a proposal to add an index need beyond the query plan? The write cost — maintenance operations per minute and log volume — the buffer-pool impact, proof the optimiser actually chooses it, and a list of existing indexes that can be dropped to pay for it.