Index Overuse

Index overuse is the mirror image of missing indexes: creating indexes indiscriminately, often one per column, on the theory that more indexes can only help reads. They cannot only help — every index is a structure the database must maintain on every write, and most speculative indexes are never chosen by the query planner.

Why It Happens

After being burned by a missing index, teams overcorrect and index everything. Schema tools and well-meaning advice suggest indexing all foreign keys and frequently queried columns without checking whether queries actually use them. Indexes accumulate over years as features come and go, but no one removes the ones tied to retired queries.

Why It Hurts

Every INSERT, UPDATE, and DELETE must update each affected index, so write throughput drops in proportion to index count — write amplification. Indexes consume storage and memory, evicting useful data pages from cache. A surplus of overlapping indexes can confuse the planner into choosing a worse plan or spending more time planning. Maintenance operations like vacuum, rebuild, and backup all grow with index volume. Many of these costs buy nothing because the indexes serve no real query.

Warning Signs

• Nearly every column on a table has its own index.
• Index-usage statistics show many indexes with zero or negligible reads.
• Write-heavy tables have degraded throughput correlated with index count.
• Storage is dominated by indexes rather than table data.

Better Alternatives

Design indexes from measured query patterns, not from speculation. Prefer a few well-chosen composite indexes that serve multiple queries over many single-column ones. Use the database's index-usage monitoring to see which indexes are actually read and drop the rest. Confirm with query plans that each index earns its place. Treat indexing as a balance between read speed and write cost, tuned to the workload.

How to Refactor Out of It

Pull index-usage statistics and list indexes with little or no read activity. Verify they are not needed for rare but critical queries or for enforcing uniqueness, then drop them in a controlled way, ideally one at a time with monitoring. Consolidate redundant overlapping indexes into composites. Re-measure write throughput and storage to confirm the gains. Establish a periodic review so indexes tied to deprecated features get cleaned up rather than accumulating.