Indexes Are Not Free and Your Write Path Knows It

Every index is a bet that you will read from the table more often, and more painfully, than you write to it. That bet is usually right. It is not always right, and nobody checks.

What an index costs

An index is a second data structure that must be kept consistent with the first. Every INSERT writes the row and every index entry. Every UPDATE on an indexed column removes and reinserts an index entry. Every DELETE removes from all of them.

Concretely, for a table with six single-column indexes:

| Operation | Write amplification |
| --- | --- |
| INSERT | 1 row + 6 index entries |
| UPDATE indexed_col | 1 row + 2 index entries (delete + insert) |
| DELETE | 1 row tombstone + 6 index tombstones |

On a read-heavy table this is irrelevant. On a write-heavy table with a high insert rate — an events table, an audit log, a metrics buffer — it can be the difference between sustaining the load and not.

Finding indexes nobody uses

PostgreSQL tracks index usage per table:

SELECT
    schemaname,
    relname              AS table_name,
    indexrelname         AS index_name,
    idx_scan             AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Anything with idx_scan = 0 after a full traffic cycle — including your monthly report run — is pure cost. It is slowing every write and occupying storage none of your reads benefit from.

Finding indexes that are redundant with each other

A composite index on (a, b) already serves every query that would use an index on (a) alone. If both exist, the single-column one is strictly dead weight.

SELECT
    a.indexrelid::regclass AS redundant_index,
    b.indexrelid::regclass AS covering_index,
    pg_size_pretty(pg_relation_size(a.indexrelid)) AS wasted_space
FROM pg_index a
JOIN pg_index b
  ON a.indrelid = b.indrelid
 AND a.indexrelid <> b.indexrelid
 AND a.indkey::text LIKE b.indkey::text || '%'
WHERE a.indexrelid <> a.indisprimary;

The leading-token match is what makes (a) redundant against (a, b) but not the reverse.

Right-sizing the index you keep

Column order in a composite index is not arbitrary. The leftmost column must be the one your queries filter on, and the order after that should follow selectivity:

-- Serves: WHERE status = ? AND published_at > ?
CREATE INDEX posts_status_published_at_index
    ON posts (status, published_at DESC);

-- Also serves: WHERE status = ?
-- Does NOT serve: WHERE published_at > ?
```

status has low cardinality — maybe four distinct values — but it is almost always present in the filter, so it leads. published_at is highly selective and used for ordering, so it follows and provides the sort for free.

Partial indexes for skewed data

When queries only ever touch a small subset of rows, index only that subset:

-- Only published posts are ever read publicly.
CREATE INDEX posts_published_recent_index
    ON posts (published_at DESC)
    WHERE status = 'published' AND deleted_at IS NULL;

If ninety percent of rows are drafts and are never publicly queried, this index is a fraction of the size and faster to maintain — because the database never writes an entry for a row it will never be asked about.

The trade is that the query must include the exact same predicate for the planner to use it. An equivalent WHERE status = 'published' will, but WHERE status IN ('published') will not.

The measurement loop

Do not add an index because a query is slow. Do this instead:

1. Capture the actual plan for the actual query with realistic data volume: EXPLAIN (ANALYZE, BUFFERS).
2. Identify whether the cost is a sequential scan (wants an index), a bad row estimate (wants ANALYZE or a statistics target), or an N+1 at the application layer (wants with(), not an index).
3. Add the index only for case one.
4. Measure the write path before and after.

Step four is the one people skip, and it is the one that matters, because it is the only step that tells you what you paid.

The rule

Indexes are a budget, not a checklist. The read side has a plan you can inspect; the write side has a cost you have to choose to measure. Teams that only ever look at EXPLAIN on slow queries end up with twenty indexes on a table that gets four thousand inserts a second, wondering why the writes are queueing.