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

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.

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:

   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.

1. 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:

   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.

1. Check how many indexes the table already carries and how much each is used:

   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.

1. 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.

1. 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/psttq9tBldAeHKL-7kYC4QUQ