Over-Indexing Cost

Indexes have write cost.

Overview

Over-indexing cost recognises that indexes have write overhead and storage cost. Adding an index to fix a slow query feels free; the cost lands on every subsequent write, on the storage budget, and on vacuum maintenance. The discipline pairs each index with a justifying query.

• Indexes have write cost. Every insert/update/delete updates indexes; write throughput drops with index count.
• Storage overhead. Indexes can exceed table size; the storage line item compounds across the cluster.
• Vacuum overhead. More indexes mean more vacuum work; the maintenance cost grows non-linearly.
• Unused index detection plus per-query justification. pg_stat_user_indexes reveals waste; each index supports a specific query.

The approach

The practical approach: monitor unused indexes, justify each index against a query, drop stale ones quarterly, prefer composite indexes, document per-index rationale. The team’s discipline produces lean databases instead of index sprawl.

• Monitor unused indexes. pg_stat_user_indexes with idx_scan=0; catches waste at the source.
• Justify each index. Per-index supporting query named; if no query justifies it, the index goes.
• Drop stale quarterly. Per-quarter unused index removal; the cleanup keeps the index list bounded.
• Composite indexes plus documented policy. Combine indexes where possible to reduce count; per-index rationale committed for operational reviews.

Why this compounds

Over-indexing discipline compounds across services. Each removed index produces ongoing write performance; the team’s database hygiene grows; new tables inherit the index discipline from day one.

• Better write performance. Fewer indexes means faster writes; throughput tracks the actual workload, not historical accretion.
• Better storage efficiency. Less index storage; the storage bill drops without losing query performance.
• Better vacuum performance. Less vacuum work; the maintenance window shrinks and the cluster stays stable.
• Institutional knowledge. Each cleanup teaches database patterns; the team’s database engineering muscle grows.

Over-indexing discipline is a database discipline that pays off across years. Nova AI Ops integrates with database telemetry, surfaces patterns, and supports the team’s database engineering discipline.