Case Study 2 — The Table With Twenty Indexes

Indexes speed reads — so more indexes must be better, right? A team that indexed "just in case" discovered the other side of the trade: writes slowed to a crawl, storage ballooned, and many of the indexes were never even used. The cure was deleting indexes.

Background

An events table ingested telemetry — millions of INSERTs per day. Over time, every time someone wrote a slow query, the "fix" was to add an index. Nobody ever removed one. The table accumulated twenty indexes: single columns, multi-column combinations, a few on the same columns in different orders, several added speculatively "in case we query by this someday."

Then ingestion started falling behind. Inserts that used to take milliseconds took far longer; the write queue backed up; and the database's disk usage was dominated not by the data but by the indexes. Ironically, the team had optimized so hard for reads that the system could barely write.

What went wrong

The fundamental trade (this chapter): every index speeds reads but slows writes, because each INSERT/UPDATE/DELETE must update every index on the table. With twenty indexes, a single insert did twenty-one writes (the row + twenty index updates). At millions of inserts a day, that overhead dominated:

  • Write amplification. Each insert maintained 20 B-trees. Write throughput dropped roughly in proportion.
  • Storage bloat. The indexes collectively used several times the space of the table data itself. Backups grew, cache hit rates fell (less of the working set fit in memory), and even reads got slower as a result.
  • Unused indexes. Crucially, many indexes were never used by any query. They'd been added speculatively or for one-off investigations and forgotten — pure write-tax and storage with zero read benefit.

Diagnosis: which indexes earn their keep?

PostgreSQL tracks index usage in pg_stat_user_indexes. The team queried it to find indexes that were rarely or never scanned:

SELECT relname AS table, indexrelname AS index, idx_scan AS times_used,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'events'
ORDER BY idx_scan ASC;        -- idx_scan = 0 → never used

The result was damning: of twenty indexes, eight had idx_scan = 0 (never used since stats were reset) and several more were used only by ancient, retired reports. They were paying the full write and storage cost of twenty indexes for the read benefit of about six.

The fix

  1. Drop the unused indexes (after confirming over a representative time window that they truly weren't used):
DROP INDEX CONCURRENTLY idx_events_unused_1;   -- ×8, the never-scanned ones

Write throughput recovered dramatically — each insert now maintained ~6 indexes instead of 20 — and gigabytes of storage came back.

  1. Consolidate redundant indexes. Two indexes (a) and (a, b) are partly redundant — a query filtering on a alone can use the leftmost prefix of (a, b). They dropped the standalone (a) where (a, b) covered it, removing more write overhead.

  2. Adopt a discipline: add an index only in response to a measured slow query, and review index usage periodically (the pg_stat_user_indexes query above became a monthly check). Indexes, like any code, need maintenance and removal — they're not write-once.

The analysis

  1. Indexes are a trade, not a free win. Each one buys read speed with write speed, storage, and maintenance. Twenty indexes on a write-heavy table is twenty taxes on every insert. The right number is "as many as your queries justify, and no more."

  2. Unused indexes are pure cost. An index no query uses provides zero benefit while charging the full write/storage price. Speculative "might need it someday" indexes are a common, invisible drain — pg_stat_user_indexes (idx_scan = 0) finds them.

  3. Redundant indexes overlap. A multi-column (a, b) often makes a standalone (a) redundant (leftmost prefix). Auditing for overlap removes write overhead without losing read coverage.

  4. The symptom can be reads or writes. Case Study 1 was too few indexes (slow reads); this is too many (slow writes, bloat). Both are index problems; the balance is the skill. "More indexes = faster" is as wrong as "no indexes."

  5. Index management is ongoing. Add in response to measured need; review and prune periodically. An index added for a report that was retired a year ago is just tax. Treat the index set as something to curate, not accumulate.

Discussion questions

  1. Why did adding many indexes slow down inserts? Quantify the write amplification.
  2. How does pg_stat_user_indexes reveal which indexes to drop?
  3. Why is a standalone index on (a) often redundant with (a, b)?
  4. Contrast this case with Case Study 1. What single principle reconciles "add an index" and "drop an index"?
  5. ⭐ Propose a team policy for adding and removing indexes that keeps the set justified over time.