---
title: "Index Management: Too Few Slow Reads, Too Many Kill Writes"
description: "A missing index slows the query; too many indexes make every write expensive. Striking the right balance by measurement, not by guesswork"
url: https://sade.dev/en/notes/index-management/
lang: en
author: "Muhammet Şafak"
published: 2026-09-05
section: Note
tags: ["postgresql","database","performance","indexing"]
---

# Index Management: Too Few Slow Reads, Too Many Kill Writes

> A missing index slows the query; too many indexes make every write expensive. Striking the right balance by measurement, not by guesswork

I've seen two opposite teams. One had put no indexes on the table at all — every query a sequential scan, every list page taking seconds. The other had done the exact opposite: "just in case," it had created an index on every column, and now every `INSERT` crawled along.

Both are two ends of the same fallacy: thinking an index is a free source of speed.

## An index is not free speed

An index speeds up reads, because instead of scanning the whole table the database looks at an ordered structure. But that ordered structure doesn't stay current on its own: an `INSERT` writes an entry into **every index** on the table, and an `UPDATE` does the same as soon as it touches an indexed column. There are exceptions — a HOT update that changes no indexed column leaves the indexes alone, a partial index is skipped for rows outside its `WHERE` clause, and a `DELETE` leaves its index entries behind for `VACUUM` to clean up later — but they are exceptions, not the default.

So every index is a trade: you buy read speed with write cost. Inserting a single row into a table with five indexes means updating six structures at once. An index isn't "free read speed," it's "read speed paid for with writes."

## A missing index: measure, don't guess

Find a missing index from the query plan, not from a hunch:

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending';
```

If you see a `Seq Scan` on a large table in the output and the query returns a small fraction of the rows, an index is probably missing. Scan the `pg_stat_user_tables` table for large tables with a high `seq_scan` count — those are your candidates.

Add the index for a real, slow query. An index added "in case we need it later" is the database-layer version of [speculative generality](/en/journal/the-cost-of-just-in-case-code).

## Too many indexes: find the unused ones

In the other direction, indexes that are never used make every write more expensive for nothing. Find those by measurement too:

```sql
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
```

An index with `idx_scan = 0` means it's speeding up no reads at all but adding cost to every write. (Keep the indexes for primary keys and unique constraints, of course.) This is the first cleanup listed at the write breaking point of [data-intensive systems](/en/systems/data-intensive-systems-breaking-points).

## Choosing the right index

An index isn't just "present or absent"; choosing the right type and shape matters:

- **Column order in a composite index.** An `(a, b)` index serves equality on `a` + a range query on `b`; a lookup on `b` alone can still use the index, but it doesn't narrow the portion that gets scanned — before PostgreSQL 18 the whole index is scanned, and from 18 on the skip scan optimization narrows it down. Put the column filtered by equality first, the one filtered by range last.
- **Partial index.** If the query always looks at the same subset, limit the index to that subset too: `CREATE INDEX ... WHERE status = 'active'`. A smaller index, cheaper maintenance.
- **Covering index.** With `INCLUDE` you can add frequently read columns to the index and keep the database from going to the table at all.
- **Non-B-tree types.** B-tree for equality/ordering; GIN for `jsonb` and full text; small, cheap BRIN for purely ordered, append-heavy data.

## Duplicate and overlapping indexes

An index on `(a)` is redundant if you already have an `(a, b)` index — the composite index serves queries starting with `a` too. Clean up overlaps like these periodically; each one is a silent write tax.

## Balance is set with the query plan

One rule: don't add an index by guesswork, and don't drop one by guesswork either. An index is added because a real query plan asks for it; an index is dropped because the statistics show no one is using it. The balance between too few and too many is struck with `EXPLAIN ANALYZE` and `pg_stat_user_indexes`, not with gut feeling.

---

Index management starts with shedding the belief that "more indexes is better." Every index is a read gain and a write cost; good management is keeping the two in balance by measuring them.

A missing index slows the query; too many slow the whole table. Measurement guards against both.