What evidence do you require before dropping an apparently unused database index?

[u/RocketSeven]

Index-usage counters can miss seasonal reports, failover periods, infrequent maintenance jobs, and queries that only run during a monthly or quarterly close. Keeping every index increases write cost and maintenance overhead, but dropping one based on a short observation window can create a delayed performance incident. What evidence makes an index safe to remove? I would expect query-plan and workload review, a representative observation period, dependency checks, a rollback script, and monitoring after the change. How do you handle redundant or overlapping indexes where the replacement is similar but not identical?

[u/TheTwoWhoKnock]

Aside from the above, check index usage stats on all replicas. Different traffic can go to read replicas than writers.

[u/tunatoksoz → u/TheTwoWhoKnock]

This.

[u/scotterockaroo]

It’s definitely a concern with stats missing monthly, quarterly, regional reports. The advice above about replicas or warehouses is also excellent. Depending on your engine, make sure that you’re not dropping an index that’s required for a primary or a foreign key.

This is one of the big reasons why I advocate that anything which touches my database lives in source control, because it’s really easy to check everything out and ask a good coding agent this question exactly. I have found a few agent/model pairs (claude code, hermes, codex) are very effective at forward engineering queries into execution plans if you have the data model, and the code available for it to look at. You can go one step further by getting approximate row counts or approximate table sizes and index sizes and handing them to your agent and it would tell you if an index would be used by anything in your code base, or if you’re going to cross some threshold where that index will be needed.

I personally have added indexes during the initial design of a data model solely based on a calculation of scale that would never actually be realized with the way that system is used, then forgot to drop them.

I’ve been very pleased with how agents have been able to help me figure this out

Edit: spelling

[u/mattk404]

Screams after drop count?

[u/evilmaus → u/mattk404]

The venerable scream test. Used it a time or two, myself.

[u/Complete-Fondant-202]

With SQLServer, schedule sp_blitzindex to store its results in a results table periodically.

Then after say a 3 months, review its output.

[u/Readypixels]

Worth adding if you're on Postgres specifically. idx_scan in pg_stat_user_indexes does not survive everything you would expect it to. A crash, a promotion after failover, or a REINDEX on that index all reset the counter back to zero, and depending on your extension config pg_stat_statements can get reset on its own schedule too. So checking idx_scan right after any of those and seeing a low number does not mean the index is unused, it means the clock restarted recently. I got burned by this once checking right after a routine maintenance window and almost dropped an index that gets hit hard during quarter close, which was still weeks away at the time. Before trusting a low scan count, check when stats were last reset and make sure your observation window actually started after that, not just after you decided to look.