Databases
Database Index Cleanup: Remove Unused Indexes Safely
Indexes make reads faster, but they are not free. Every extra index can add write overhead, storage cost, migration time, backup weight, and planner complexity. Over time, databases collect indexes from old features, emergency fixes, abandoned reports, and “just in case” tuning.
Database index cleanup is the practice of proving which indexes still serve real queries and removing the ones that do not.
Build an index inventory
Do not start by dropping indexes. Start by listing them.
Your inventory should include:
| Signal | Why it matters |
|---|---|
| Table and index name | Identifies the cleanup target |
| Columns and order | Shows whether it overlaps another index |
| Size | Helps rank cleanup value |
| Read usage | Shows whether the planner has used it recently |
| Write volume on table | Highlights expensive indexes on hot tables |
| Related queries | Connects the index to application behavior |
Usage counters are helpful, but they are not the whole truth. A quarterly report, maintenance job, or incident-only lookup may not appear in a short observation window. Treat counters as evidence, not as a verdict.
Look for overlap
Unused indexes are one cleanup category. Overlapping indexes are another.
Suppose a table has these indexes:
CREATE INDEX users_email_idx ON users (email);
CREATE INDEX users_email_status_idx ON users (email, status);Depending on the database and workload, the second index may cover queries that the first one was created for. Or both may still be useful because of query shape, selectivity, uniqueness, or planner behavior. The point is not to guess from the names. The point is to check real query plans.
Review slow queries, common queries, and high-write tables. The best cleanup target is an index that is large, rarely used, duplicated by a better index, and attached to a table with frequent writes.
Drop with a rollback path
Index cleanup should be boring. Use a safe removal process:
- Confirm the index has no constraint role, such as uniqueness or foreign key support.
- Check query plans for known queries that might use it.
- Observe usage across a representative window.
- Drop the index in a low-risk deployment window.
- Monitor latency, error rate, lock behavior, and write throughput.
- Keep the recreate statement ready.
For large production databases, online or concurrent index operations may be necessary. The exact command depends on your database engine, table size, and locking behavior. Test the operation before running it on the primary system.
Prevent index sprawl
New indexes should arrive with a reason and leave with an owner. When a pull request adds an index, ask for the query it supports, the expected cardinality, and the metric that should improve. When a feature is deleted, include its indexes in the cleanup checklist.
Databases tend to keep every historical decision unless someone removes it. Pruning unused indexes gives you faster writes, smaller backups, clearer query planning, and a schema that better reflects the product you run today.