db-index-hygiene

To find unused and redundant indexes in a relational database, run an index audit that scans the database system's catalog and usage statistics to identify exact duplicates, overlapping prefix indexes, and indexes with zero reads.

Database index hygiene is the practice of removing unused and redundant indexes to maintain optimal database health. It is important for scaling because it reduces write amplification and storage costs, directly improving overall performance.

You audit database index health by analyzing the database system's catalog and stats to detect unnecessary indexes. Removing these unused structures reduces write amplification, which actively improves rather than impacts write performance.

Yes, an index audit requires direct access to the database system's catalog and statistics. This access is necessary to accurately detect redundant prefix indexes and track whether existing indexes are actually being used by queries.

Detecting redundant prefix indexes relies entirely on the accuracy of the database system's catalog and current statistics. A limitation is that recently created indexes may not have accumulated enough usage stats to be accurately classified as redundant or unused.