The Hidden Cost of Indexes

Indexes are often treated as "cheap insurance" for slow reads, but they are actually a permanent tax on write operations. Every index is a separate, sorted data structure that must be updated whenever a row is inserted, updated, or deleted. Adding an index is a deliberate trade-off: you gain read speed for a specific query shape at the cost of slower writes for the lifetime of that index.

Identifying and Eliminating Dead Weight

Many performance issues stem from "zombie" indexes—those that were added to solve a specific problem but are no longer used because the application code or query patterns have evolved.

  • Audit Usage: Use database-native tools to identify unused indexes. In MongoDB, use $indexStats to check access counts. In Postgres, query pg_stat_user_indexes to find indexes with idx_scan = 0.
  • Remove Redundancy: Compound indexes (e.g., {a, b}) cover queries that filter on the prefix (e.g., {a}). If you have a separate index on {a}, it is redundant and should be dropped, as it is being updated on every write without providing unique value.
  • Verify Before Dropping: Before deleting, confirm the index isn't supporting a unique constraint or a rare, high-value quarterly report. If there is no clear purpose, it is a candidate for removal.

Optimizing Write Impact

Not all indexes impact writes equally. You can minimize the "write tax" by being strategic about index design:

  • Partial Indexes: Instead of indexing an entire table, use partial indexes (e.g., WHERE status = 'active'). This ensures the database only maintains the index for relevant rows, drastically reducing overhead.
  • Analyze Write Paths: Understand which columns are updated most frequently. An index on a column that is rarely updated is nearly free, whereas an index on a column updated in every request is expensive.
  • Leverage HOT Updates: In Postgres, "Heap-Only Tuple" (HOT) updates allow the database to skip index maintenance entirely if no indexed columns are modified and there is sufficient page space. Designing your schema to keep frequently updated columns out of indexes can significantly improve throughput.