Audit unused indexes as carefully as missing ones

An unused index is not free. It slows every write operation and permanently inflates your database backup size. Reviewing index usage statistics needs to be a scheduled, proactive process, not just something you think about when adding a new one reactively.

When a backend engineer investigates a slow API endpoint, the workflow is almost always identical. They identify the long running query, run an explain plan, and realize a sequential scan is bottlenecking the database. They add a targeted index, the query execution time drops from three seconds to ten milliseconds, and the pull request is approved.

Engineers are heavily incentivized to add indexes to solve immediate read performance issues. They are rarely incentivized to go back and remove them when the application code changes a year later.

The Silent Write Penalty Every time you add an index to a relational database, you are making a deliberate trade. You are purchasing faster read performance by sacrificing write throughput.

When an application inserts a new row or updates an indexed column, the database cannot just write the raw data to disk. It has to synchronously traverse and update the underlying data structures for every single index attached to that table. If a table has ten outdated indexes, every single write operation is doing ten times the structural work it actually needs to. In a high concurrency environment, that unnecessary overhead translates directly into database CPU spikes and connection pool exhaustion.

Memory and Storage Starvation The secondary cost is memory starvation. Relational databases rely heavily on holding frequently accessed data in RAM to maintain performance. Indexes compete directly with your actual table data for space in that buffer pool. An unused index does not just waste disk space. If the database engine occasionally loads it, it actively evicts useful working data from memory, causing a cascade of slow cache misses for your actual production traffic.

The Programmatic Audit Treating indexes as append-only infrastructure is a massive operational risk. Modern database engines track index usage natively through internal statistics tables.

Building a resilient data layer requires querying these statistics on a strict schedule. If an index has zero recorded scans over a rolling ninety day period, it is dead weight. It should be flagged, evaluated, and explicitly dropped.

Database performance optimization is not just about finding the indexes you are missing. It is equally about having the engineering discipline to delete the ones you no longer need.