Why every extra index in PostgreSQL costs dearly on writes

Radim Marek loaded 30 schemas generated by AI agents into PostgreSQL and audited 838 indexes across twelve of them, measuring what cost remains when they hit the database's hottest table.

Adding an index to speed up a query looks like a cheap decision. It is cheap on a table almost nobody writes to. On the table that receives all the write traffic, every extra index is additional work on every INSERT, UPDATE, and DELETE, and that cost disappears from the execution plan of the query you were optimizing. This is the asymmetry that Radim Marek dissected in the article The unbearable lightness of one more index, published on boringSQL and syndicated by Planet PostgreSQL.

Marek's hook is timely: over the past few months, he started receiving schemas generated by coding agents with a suspicious number of indexes. Instead of dismissing them as "AI slop," he built a harness, loaded 30 schemas generated by four models from three vendors into PostgreSQL 18.6, and audited 838 indexes across twelve of those schemas. The uncomfortable conclusion: most of the indexes were competent. Only ten, out of hundreds, served no requirement at all. The models got GIN, GiST, partial indexes with sensible predicates, and multi-tenant composite keys in the right order. The problem isn't a wrong index. It's the sum of them all landing on the same hot table.

What happens on disk when you touch the row

The technical point underpinning the article is the HOT (Heap-Only Tuples) mechanism. When an UPDATE changes a column that no index references, PostgreSQL can keep the new version of the row inside the same 8 KB block, without touching any secondary index. It's the optimization that keeps write costs in check on tables with many indexes.

It only takes one index touching the modified column for HOT to die. From then on, every write pays:

• A new entry in every index whose WHERE predicate matches the new tuple, not just the index on the changed column.
• Additional WAL records for each of those index insertions.
• Dead tuples that sit in the heap until VACUUM runs, and VACUUM has to sweep every secondary index to clean up the pointers. More indexes, slower sweep, even if only three rows changed.

That's the cost that doesn't show up when the agent (or the human) writes indexes query by query, without looking at the table's write traffic.

The numbers for the tickets table

Marek ran a synthetic benchmark on a tickets table of one million rows, with a realistic support mix: ~60% customer replies, 25% status changes, and 15% reassignments. PostgreSQL 18.6, fillfactor=90, autovacuum disabled to keep the layout predictable, WAL captured via EXPLAIN (ANALYZE, BUFFERS, WAL), and the average of three runs.

| Index set | Secondary indexes | WAL written | UPDATE time | VACUUM | |---|---|---|---|---| | baseline (hand-written) | 7 | 436.0 MiB | 4,850 ms | 264 ms | | helpdesk-run3 | 9 | 426.2 MiB | 4,377 ms | 236 ms | | helpdesk-run2 | 15 | 647.8 MiB | 8,599 ms | 394 ms | | helpdesk-run1 | 15 | 776.8 MiB | 9,013 ms | 436 ms |

The 15-index schemas wrote 1.8x more WAL, doubled UPDATE latency, and increased VACUUM time by about 65%, all for the same 200,000 updates. Marek is honest about the fragility: WAL volume was stable across repetitions (0.3 MiB variation), but execution times swung by up to 9%, and he couldn't isolate the VACUUM cost from the WAL volume. The relative multiplier is the durable finding, not the absolute milliseconds.

The most instructive detail is run3: nine indexes yet still slightly less WAL than the seven-index baseline. The reason is restrictive partial predicates, indexes with a WHERE clause that barely touch the updated rows:

CREATE INDEX tickets_unassigned_urgent_idx ON tickets (workspace_id, priority, created_at)
  WHERE assignee_kind IS NULL
    AND status <> ALL (ARRAY['solved','closed']);

In other words: WAL volume tracks the index's footprint and page churn, not the raw count. A well-designed partial index can cost almost nothing on writes. A wide, unconditional index forces more full-page writes.

WAL is paid three times

Marek raises a warning that every DBA who runs replicas needs to internalize: WAL doesn't stop at the primary. Everything written there crosses the network to each replica and then to the backup archive. The extra volume is paid three times. The additional 1.75 KB per update look irrelevant, but at 100 updates per second they become 15 GB of extra WAL per day on a single table. He isn't claiming your busiest table has that traffic; he's claiming the multiplier holds wherever it does.

Where the index column matters more than the count

The most elegant experiment isolates the costliest variable. Marek built a small table with six secondary indexes on both sides and the same UPDATE ... SET last_seen_at = now() over 300,000 rows. The only difference: whether one of those six indexes sits on the written column or not.

| Updated column | HOT updates | HOT % | UPDATE time | |---|---|---|---| | not indexed | 138,468 | 46.2% | 2,743 ms | | indexed | 0 | 0.0% | 3,979 ms |

Same index count, same load. Moving a single index onto the touched column zeroes out HOT and adds 45% to the time. That's why a seemingly sensible queue index like (workspace_id, status, last_activity_at DESC) deserves a second look: last_activity_at changes on every reply, assignment, closure, and reopening. Putting it in the key turns every touch on the ticket into a non-HOT update.

Marek doesn't spare himself: his own seven-index baseline also ran at 0% HOT, because a queue index on the activity timestamp is too obvious not to write. The difference is that the generated schema keeps eight extra indexes updated on every write.

The query to check this in production is straightforward:

SELECT s.relname, s.n_tup_upd, s.n_tup_hot_upd,
       round(100.0 * s.n_tup_hot_upd / NULLIF(s.n_tup_upd,0),1) AS hot_pct,
       (SELECT count(*) FROM pg_index i WHERE i.indrelid = s.relid) AS indexes
FROM pg_stat_user_tables s
WHERE s.n_tup_upd > 10000
ORDER BY hot_pct;

A low hot_pct on a hot table is the real signal. The next question is: which index sits on the column your UPDATE touches?

What indexes push out of cache

There's a cost the benchmark with shared_buffers=512MB hid, because everything fit in memory. Marek throttled shared_buffers down to 128MB with a cold cache, and the picture changed:

| Set | Blocks read from disk | Index in cache (MiB) | Heap in cache (MiB) | |---|---|---|---| | baseline (7) | 53,089 | 92.6 | 35.2 | | helpdesk-run1 (15) | 357,022 | 118.7 | 9.2 |

Physical reads went up 6.7x. The extra index pages have to live somewhere, and what they push out is the heap: heap cache dropped from 35 MiB to 9 MiB within the same 128 MiB pool. The update time gap widened from 1.9x to 2.1x. None of this happened at 512 MB. It's a demonstration of direction, not a measurement of your server, but it shows which way indexes push the cache when space runs short.

Where it still pays off, and where it becomes a trap

Marek didn't give in to the temptation of condemning indexes. On pure CPU, the 15-index schema wins: it saves 0.408 ms per read and adds only 0.021 ms per update. That means it comes out ahead as long as there are fewer than 20 updates for every read, and in a support app, where agents update their queues all the time, 20-to-1 isn't hard to maintain.

Except CPU isn't the whole bill. That calculation ignores the 15 GB of daily WAL going to replicas and backups, and treats the traffic pattern as fixed. The day a feature ships that updates tickets in bulk, the math flips, and schemas are rarely revisited when write volume changes.

There's also the case where the generated index simply fails. Of the nine measured queries, eight performed as expected, some 23 to 46 times faster. The ninth, an SLA sweep, ran 111 times slower on the generated schema. The index looked correct, but it had workspace_id as the leading column. An SLA sweep is inherently cross-tenant: the tenant prefix that's right everywhere else in the table is wrong there. The baseline indexed first_response_due_at with no prefix and answered the same sweep in 102 buffers versus 43,699.

Nobody removes the twelfth index

The other half of the problem is the next commit. Marek handed the 16-index table and a common feature request to six new model instances. None of them removed a single index. Five of the six added a new one, always on a mutable column with a mutable predicate. One model even rejected an array column because it would break HOT on every tag edit, and then added a partial index on status = 'pending', which also breaks HOT. It saw the risk in one column and repeated it in another.

A detail Marek highlights: the phrase "make it production-ready," which 26 of the 30 runs carried at the end of the prompt, by itself accounts for about a fifth of the index count (20% in the veterinary schema, 17% in the freight one). Three words that nobody thinks of as a schema decision move the write cost of the system's busiest table.

What this leaves for whoever runs the database

Marek's message isn't "turn off the agents" or "stop creating indexes." It's that the evaluation yardstick is in the wrong place. Every index on the list would pass an individual review, exactly as it would if a human had written it. The problem is the sum, and the sum only shows up when you look at the write path: how many indexes touch the column that changes, how much WAL that generates, how much cache the heap loses. Before accepting the next index, it's worth running the two queries against pg_stat_user_tables and pg_stat_user_indexes, checking the hot_pct of the hot table, and asking whether the read gain survives the real write volume. The schemas, specs, harnesses, and raw CSVs are in the github.com/boringSQL/vibe-coded-indexes repository, for anyone who wants to reproduce the measurement in their own environment.