Why We Will Not Drop Your Unused Index

Unused indexes are a genuine cost. Every one of them is maintained on every insert, update and delete against its table, consumes storage, and occupies cache that live data could be using. Finding candidates looks trivial: the statistics views record how many times each index has been scanned, and the ones sitting at zero stand out immediately.

From there, dropping them automatically looks like an obvious win. It is not, and the reasons are worth stating precisely, because they are the same reasons that decide which actions any automated system should be allowed to take on its own.

Index usage counters are per node. They record scans performed on the instance you are reading, and they do not aggregate across a replication topology. An index that shows zero scans on the primary may be serving every analytical query on a read replica, where the reporting traffic was deliberately sent.

Drop it on the primary and the change propagates. The index disappears on the replica too, along with the query plans that depended on it, and the first sign is a reporting workload that has quietly fallen off a cliff. Answering "unused" correctly requires a model of the topology, not a single instance's counters.

The counters are cumulative since the last statistics reset. A low number can mean an idle index, or it can mean the statistics were reset recently — after a failover, a major version upgrade, or someone running a reset while debugging something unrelated.

Even with a long, unbroken history, the window matters. An index that exists for a quarterly reconciliation job looks abandoned for eleven months of the year. The observation window has to be longer than the longest period between uses, and nothing in the database tells you what that period is.

A unique index may exist to enforce a constraint rather than to accelerate a lookup. Primary keys are backed by indexes. Exclusion constraints require one. These can show few scans or none while being structurally load-bearing, and removing one does not slow a query down — it changes what the database will accept.

"Nothing scans it" and "nothing needs it" are different statements, and only the first one is visible in the statistics.

So the honest division is to detect and explain, and leave the drop as a human decision. Our system can surface the candidates, show how long it has been observing, state what it cannot see — other nodes, constraint backing, the length of the longest quiet period — and attach a confidence level that reflects those gaps rather than hiding them.

The asymmetry settles it. Keeping an unnecessary index costs write throughput and storage continuously, and it is recoverable at any time. Dropping a necessary one on a large table costs a rebuild, and until that rebuild finishes the workload that needed it is degraded. An action whose result cannot be verified, and whose reversal is expensive, does not belong in the set a system performs on its own.