# Index recommendation that made writes 3x slower

**TL;DR:** Measure write throughput before keeping any recommended index. Every index taxes every insert, update, and delete on the table, so a read win can hide a write loss. Drop or replace the index if the write cost outweighs the read gain, and make the write benchmark part of the recommendation loop.

```text
agent recommended adding an index that made writes 3x slower -- it only measured read latency
```

## Steps

1. Confirm the new index is the cause. Check when it was created and compare write latency before and after:
   ```sql
   SELECT indexrelid::regclass AS index_name, idx_tup_read, idx_tup_fetch
   FROM pg_stat_user_indexes
   WHERE relid = 'your_table'::regclass;
   ```
   Expected: the new index shows up with a recent creation time in `pg_indexes`, matching the moment writes slowed.

2. Measure the write cost directly. Run your normal write workload (or pgbench with your insert pattern) with the index present, then drop it in a transaction and rerun:
   ```sql
   BEGIN;
   DROP INDEX your_new_index;
   -- run write workload here --
   ROLLBACK;
   ```
   Expected: write latency drops back toward the pre-index baseline, confirming the index was the tax.

3. Check how many indexes the table already carries and how much each is used:
   ```sql
   SELECT indexrelid::regclass AS index_name, idx_scan
   FROM pg_stat_user_indexes
   WHERE relid = 'your_table'::regclass
   ORDER BY idx_scan;
   ```
   Expected: indexes with `idx_scan = 0` or near zero are candidates for removal regardless - they cost writes and buy nothing.

4. If the read win is real and worth keeping, shrink the cost instead of dropping it: replace with a partial index on just the rows the slow query touches, or a smaller composite. Partial indexes only tax writes on matching rows.
   Expected: write latency lands between the no-index and full-index numbers, and the slow SELECT stays fast.

5. Add a write check to the agent's recommendation rule: before any index ships, the agent must report (a) read latency improvement on the target query and (b) write latency impact on a 1-minute sample of production-shaped inserts/updates.
   Expected: future recommendations arrive with both numbers, and write-regressing indexes get flagged before deploy.

## Use this when

- Write latency (inserts, updates, deletes) rose right after a recommended index was created.
- The recommendation was justified with read-only benchmarks (SELECT timing, EXPLAIN output).
- `pg_stat_user_indexes` shows the index gets few scans relative to the table's write rate.
- You want to know whether to keep, shrink, or drop the index.

## Not for this skill when

- Reads are slow and writes are fine - that is a missing-index problem, not an over-indexing one.
- The slowdown comes from lock contention or autovacuum, not from index maintenance (check `pg_stat_activity` for lock waits first).
- The table is read-mostly (analytics replica, reporting table) - write tax there rarely matters.

## Variant phrasings

- "agent added an index and now inserts are slow"
- "index slowed down writes postgres"
- "every new index makes updates slower, how many indexes is too many"
- "read latency improved but write latency regressed after index"

## Why it happens

A btree index is a second data structure the database must keep in sync on every write. Each insert adds an entry to every index on the table; each update that touches an indexed column writes new entries and leaves dead ones for vacuum. Agents usually optimize what they can measure easily - a SELECT before and after - and skip the write half because it needs a workload replay. So the recommendation looks like a pure win until production write traffic pays the tax.

## Edge cases

- Hot tables with heavy updates can suffer even from a single extra index: 3 indexes is fine, 12 on a write-heavy table is a smell.
- Partial indexes still cost on matching-row writes; if every row matches, the partial buys nothing.
- Dropping an index in production takes an ACCESS EXCLUSIVE lock briefly - do it in a low-traffic window or accept the short stall.
- Write benchmarks on an idle staging box understate the cost; use production-shaped concurrency, not a single-threaded loop.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_tq9tBldAeHKL-_7kYC4QUQ