Over-Indexing

Introduction

Over-indexing is the accumulation of more indexes than a table's real workload justifies. Indexes can make selected reads faster, but each one also consumes storage and adds maintenance work to writes, vacuuming, replication, and query planning.

Mental model: an index is a second data structure that must stay in sync with the table. Add one to solve a measured query problem, and remove or consolidate it when the workload no longer pays for its maintenance cost.

TL;DR

• Every index you add costs a write amplification tax: for every INSERT, UPDATE, or DELETE, Postgres must also update all indexes on that table. A table with 12 indexes incurs 12 index writes per row insertion.
• Indexes are not free storage. Each B-tree index stores entries for the indexed keys and row pointers in ordered, balanced pages. A table with many indexes can be double or triple its base table size.
• The query planner has to choose between available indexes at query time. A large index set can add planning choices and may still lead to a suboptimal plan if statistics or cost assumptions are wrong.
• The discipline: add indexes that are directly justified by a specific slow query with EXPLAIN ANALYZE evidence. Do not add indexes "just in case."
• The fix: audit with pg_stat_user_indexes, treat indexes with 0 scans as removal candidates after a representative window, consolidate single-column indexes into composites, and use partial indexes for hot subsets.

What It Is

Before: overlapping indexes added speculatively

It's 3 a.m. and your on-call engineer gets paged. The order processing pipeline is backing up. Write latency on the orders table has crept from 2ms to 45ms over the past month. Nobody noticed because reads were still fast. The culprit? Twelve indexes on a single table, most added "just in case" during code reviews over the past year.

A developer reviewing a schema adds "helpful" indexes to a large orders table:

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_created_at ON orders (created_at);
CREATE INDEX idx_orders_updated_at ON orders (updated_at);
CREATE INDEX idx_orders_amount ON orders (amount);
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
CREATE INDEX idx_orders_status_created ON orders (status, created_at);
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);
-- ... 5 more indexes for other query shapes

The table has 50 million rows. Each order insertion now updates 13 indexes. In this illustrative workload, a 1ms primary key insert becomes a 12ms multi-index update, write throughput drops by 10x, and WAL volume increases enough to cause replication lag. VACUUM also takes longer because index entries must be cleaned. A hot promotion flow that processes 5,000 orders/second may struggle to keep up with 500/second.

A representative remediation is to audit every index, drop candidates without a matching query, and consolidate overlapping indexes into composites.

Here's a representative before-and-after comparison:

Write amplification per index

For a B-tree index:

• Every INSERT inserts a key into the B-tree: O(log n) per index
• Every DELETE removes the key from the B-tree: O(log n) per index
• Every UPDATE on an indexed column deletes the old key and inserts the new key: O(log n) per index, twice

At 50M rows, each B-tree operation is log₂(50,000,000) ≈ 26 comparisons. With 13 indexes: 338 comparisons per write in this simplified model. The page cache must hold many more index pages, and dirty pages from all 13 index structures compete for the shared buffer pool.

The directional math is simple: if you have N maintained indexes, write work generally increases with N, although the exact cost depends on indexed columns, updates, page locality, and the database engine. For many OLTP tables, 3-5 well-chosen indexes is a useful starting point, not a universal limit.

Why It Happens

Every one of these decisions sounds reasonable in isolation:

• A developer adds an index during a code review because "we might query on this column someday." It takes 10 seconds to write the migration.
• The DBA adds indexes during a performance incident to fix a slow query. The crisis passes, but the indexes stay forever. Nobody wants to drop an index that "fixed" something.
• An ORM auto-generates indexes for every foreign key, and nobody reviews the migration output. Django and Rails projects can accumulate 2-3 auto-generated indexes per model relationship.
• A team copies the indexing strategy from a read-heavy OLAP workload onto a write-heavy OLTP table. Different workloads need different indexing strategies.
• Someone reads a "SQL performance tips" blog post that says "always index your WHERE columns" and applies it literally to every column.