Advanced Index Tuning

Advanced Index Tuning: Pruning Unused Indexes

In this lesson, you will learn how to identify and prune redundant or unused indexes to reclaim disk space and significantly boost your application’s write throughput.

The Cost of Over-Indexing

While indexes are essential for speeding up SELECT queries, every index you create imposes a "write tax." Every time you perform an INSERT, UPDATE, or DELETE operation, the database engine must not only modify the record in the main table but also update every associated index structure. If you have unused or redundant indexes, your Node.js application is paying a performance penalty on every write without receiving any benefit in return.

Identifying the Waste

Before deleting anything, you must use your database's internal statistics. For example, in PostgreSQL, you can query pg_stat_user_indexes to view idx_scan counts:

SELECT relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 
  AND indisunique IS FALSE;

If idx_scan is zero, your application hasn't utilized that index since the last statistics reset. However, be cautious: some indexes might only be used during batch jobs or end-of-month reporting that happens outside of daily traffic cycles.

The Pruning Strategy

Pruning should be treated with the same rigor as a production migration. Instead of simply dropping the index, follow these steps:

1. Mark as Invisible (if supported): Databases like MySQL and PostgreSQL (via SET enable_indexscan = off) allow you to make an index "invisible" to the query planner without deleting it. This lets you observe the performance impact for a few days.
2. Monitor Performance: Watch your Node.js application’s telemetry. If you don't see an uptick in "Query Latency" or "Read Timeouts" in your monitoring dashboard, the index was likely unnecessary.
3. Drop: Once you are confident, proceed to DROP INDEX.

By removing indexes that no longer support your query patterns, you reduce the I/O overhead on your database engine, allowing it to process write-heavy workloads much more efficiently.

Key Takeaway: Pruning unused indexes reduces write amplification and disk I/O, providing a "free" performance boost to your application’s write operations without compromising read speeds.