There Is No Free Lunch
A common mistake among junior engineers is to treat indexes as purely additive — "more indexes = faster queries." This is dangerously wrong. Every index you create is a liability that must be paid for on every write operation.
1. Write Amplification (The Primary Cost)
Every INSERT, UPDATE, or DELETE must update the main table and every index that covers the modified columns.
Table with 10 indexes:
1 INSERT → 1 heap write + 10 index writes = 11 physical writes
For a write-heavy workload (e.g., a high-throughput event ingestion pipeline), this write amplification can reduce throughput by an order of magnitude. The effect compounds under lock contention: index updates hold locks, and more indexes mean more lock contention.
2. Space Amplification
Indexes consume disk space. More importantly, they compete for space in the Buffer Pool (PostgreSQL's shared memory cache, shared_buffers).
The Buffer Pool is finite. If it is filled with rarely-used "junk" indexes, the frequently-used "hot" indexes get evicted to disk. The next query that needs them causes a cache miss — a slow disk read. This is how over-indexing causes read performance to degrade, not just write performance.
3. Query Planner Confusion
The PostgreSQL query planner uses statistics (maintained by ANALYZE) to choose the best index. When multiple similar indexes exist, the planner must evaluate more candidates. With stale statistics or overlapping indexes, it can choose a suboptimal plan — sometimes a sequential scan is faster than a bad index choice, but the planner picks the index anyway.
Finding and Dropping Unused Indexes
PostgreSQL tracks index usage in pg_stat_user_indexes. An index with idx_scan = 0 since the last statistics reset has never been used — filtering that view for idx_scan = 0 while excluding indexes that back a PRIMARY KEY or UNIQUE constraint (via pg_constraint), and sorting by size, surfaces the biggest unused-index candidates first.
DBRE Rule: Run this audit quarterly. Drop indexes with idx_scan = 0 after confirming they are not required for constraints or rare-but-critical queries (e.g., monthly reports). Always drop in a maintenance window and monitor for query regressions.
Interviewers at senior/staff level often ask: "Walk me through how you would audit and clean up indexes on a production database." The answer has three parts: (1) query pg_stat_user_indexes for idx_scan = 0, (2) exclude constraint-backing indexes via pg_constraint, (3) drop candidates during low-traffic windows and watch pg_stat_statements for query plan regressions. Mentioning that statistics reset on server restart (so idx_scan = 0 could be misleading on a recently restarted server) shows senior-level awareness.