The unbearable lightness of one more index

A database engineer's audit of 30 model-generated PostgreSQL schemas found that coding agents over-index tables, with one support-tool schema using 16 indexes on the `tickets` table versus a hand-written baseline of 7, producing 1.8× the WAL and 1.9× longer per-update times. The engineer loaded 30 model-generated schemas into PostgreSQL and audited 838 indexes across twelve of them, finding only ten served no requirement and that all four models from three vendors handled GIN and GiST indexes cleanly. The extra indexes eliminate Heap-Only Tuple updates and add WAL records and VACUUM work because agents write indexes query by query without accounting for write traffic.

Being "the database guy" comes with a lot of questions, and over the last eight months those questions changed. The repetitive ones disappeared, nobody asks how to avoid putting things in the database any more, and the code arriving for review got noticeably more polished. Then this summer a schema landed in front of me with twelve proposed index drops on a single table, which is when I started assuming coding agents over-index. The next schema I looked at had the same shape. Passing this off as AI slop would be too easy, because most of those changes were competent. So I built a harness and measured it. I loaded 30 model-generated schemas into PostgreSQL and audited 838 indexes across twelve of them. The competence caught me off guard. Only ten served no requirement I could find; the rest showed solid craft. All four models handled GIN and GiST indexes cleanly, built partial indexes with sensible predicates, and got multi-tenant composite keys in the right order. The baseline SQL quality is much better than what agents wrote a year ago. The cost of an extra index Indexes are great, until you pile them onto the single table taking all your writes. On quiet tables you will never notice the difference. On hot tables, every index is extra work on every write. In one support-tool schema, a model created sixteen indexes on tickets alone. Six of them indexed last activity at , a column that updates every time an agent touches a ticket. Compared to my hand-written baseline with seven indexes, the generated schema wrote 1.8× the WAL, took 1.9× longer per update, and pushed up VACUUM time just as much. Those sixteen indexes were not dumb mistakes. For read queries, they run fast. The problem is that coding agents write indexes query by query, without thinking about write traffic. What actually happens on disk when you touch that row: - No more HOT updates. If any index touches the modified column, Heap-Only Tuples is out the window. Postgres can't keep the new row version confined to the original 8 KB data block without updating index pointers. - Every index on the table whose WHERE predicate matches the new tuple gets a new entry written into it—not just the index covering the column you changed. - Extra WAL records for every single one of those index inserts. - Dead tuple cleanup: the old row version sits in the heap until VACUUM cleans it up, but VACUUM has to cycle through every secondary index to prune the pointers pointing at it. More indexes, slower vacuum sweeps, even if only three rows changed. I never got the vacuum cost isolated from the WAL volume; the two kept confounding. Treat the vacuum numbers below as direction, not measurement. The setup I created six fictional SaaS products for the review. - Shared inbox support tool - Class booking system for gyms - Product analytics tool - Marketplace app - Veterinary practice system - Freight management board Andrey Grunev https://agrunev.com/ , so I didn't have to buy another subscription. I later wanted more data for that model and ran it again myself. No table names, no column hints, and no database constraints, except that Postgres will be used. Also, there's no mention of anyone counting indexes later. I wrote the first four specifications myself only polished them with an LLM . The last two were written by an independent model from a short description of what I wanted. One of the six specs is thinner than the others and it shows in the output. I left it in place rather than change it. The revisit would contaminate the run with all the bias of things I learned reviewing it. Each specification went to new model instances without schema templates or index hints. Design the PostgreSQL schema, write SQL queries, and don't ask questions. Four models from three vendors created the schemas. To focus on the database instead of a model leaderboard, I label them Model A to Model D . This way, there are no names and no ways to identify the models. Twenty-six of the thirty runs carried one extra line at the end of the prompt: make it production-ready . That is what people actually type into a coding agent, so I left it in, and later re-ran Models B and D without it as a separate condition. Thirty runs in total were loaded into PostgreSQL 18.6. All metrics come from pg index and pg stat on a live database. Schemas, specifications, harnesses, raw CSVs and the pre-registration file are at github.com/boringSQL/vibe-coded-indexes https://github.com/boringSQL/vibe-coded-indexes . Indexes pile onto one table Average index density looks fine on a summary slide. The catch is where those indexes land. Every agent dumps its indexes onto the single table with the most writes: helpdesk runs hit tickets , fitness hits class occurrence , freight hits loads . | app | most-indexed table | indexes created | |---|---|---| | helpdesk 7 runs | tickets | 10–16 | | fitness 3 runs | class occurrence | 6–10 | | analytics 3 runs | events | 5–8 | | marketplace 7 runs | products | 8–11 | | vet 6 runs | appointment s | 5–11 | | freight 4 runs | loads | 7–15 | The counts are approximate—in a couple of runs there were overlapping partial indexes I wasn't entirely sure how to count. Each app keeps its core data in those tables. The models read requirements feature by feature: every filter or sort rule prompts another index, and no agent checks what is already there. Interestingly, no model indexed foreign keys by reflex; if anything, they under-indexed them. Partial, expression, GIN, GiST, INCLUDE, tenant-prefixed composites: all syntactically fine. Here is what that looks like on one table. Fifteen indexes; the sixteenth is the primary key. | index | key columns | only lists rows where… | |---|---|---| | tickets open queue idx | workspace id, last activity at | status is open-ish | | tickets open by status idx | workspace id, status, last activity at | status is open-ish | | tickets open by assignee idx | workspace id, assignee id, last activity at | status is open-ish | | tickets open by team idx | workspace id, team id, last activity at | team id set, status open-ish | | tickets unassigned idx | workspace id, priority, last activity at | assignee id IS NULL, status open-ish | | tickets org idx | workspace id, organization id, last activity at | organization id set | | tickets first response due idx | workspace id, first response due at | unanswered, status not solved/closed | | tickets resolution due idx | workspace id, resolution due at | solved at IS NULL, deadline set | | tickets solved idx | workspace id, solved at | solved at IS NOT NULL | | tickets requester idx | workspace id, requester id, created at | all rows | | tickets created idx | workspace id, created at | all rows | | tickets subject trgm idx | subject | all rows | | tickets subject fts idx | workspace id, subject tsv | all rows | | tickets number key | workspace id, number | all rows , unique | | tickets tenant key | workspace id, id | all rows , unique | status and last activity at mutate on every reply, assignment, close, and reopen; nine indexes key on one or predicate on it, so any of those writes kills HOT. Closing a ticket inserts it into tickets solved idx and leaves dead entries behind in the four open-queue partials for VACUUM to collect. The analytics app collected a comparable pile of indexes on events and pays almost none of this, because nothing there is ever updated; append-only tables get their indexes close to free. And every index in the list above would pass review on its own. Same as it would do when everything was coded and reviewed by hand. The sum of all things is where the problem is. What sixteen indexes cost I ran a synthetic benchmark on a million-row tickets table with a realistic support mix: roughly 60% customer replies, 25% status updates, and 15% ticket reassignments. This was on PostgreSQL 18.6 with fillfactor=90 . Autovacuum was disabled to keep row layout predictable, capturing WAL with EXPLAIN ANALYZE, BUFFERS, WAL averaged over three runs. | index set | secondary indexes | index size before | WAL written | update time | index size after | VACUUM | |---|---|---|---|---|---|---| | hand baseline | 7 | 176 MiB | 436.0 MiB | 4,850 ms | 230 MiB | 264 ms | | helpdesk-run3 | 9 | 173 MiB | 426.2 MiB | 4,377 ms | 227 MiB | 236 ms | | helpdesk-run2 | 15 | 221 MiB | 647.8 MiB | 8,599 ms | 324 MiB | 394 ms | | helpdesk-run1 | 15 | 259 MiB | 776.8 MiB | 9,013 ms | 361 MiB | 436 ms | Notice run 3: nine indexes, yet it generated slightly less WAL than my 7-index baseline and beat it on update time. Why? Strict partial predicates: three of its indexes had restrictive WHERE clauses that touched almost none of 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' ; My hand-crafted baseline had wider, unconditional indexes that forced more full-page writes. In other words, WAL volume tracks index footprint and page dirtiness, not raw index count. Still, when models go up to fifteen indexes run 1 and run 2 , the cumulative penalty adds up: 1.8× the WAL, double the latency, 1.6× the on-disk footprint, and an extra 65% on VACUUM , all for the exact same 200,000 updates. WAL does not stop at the primary. Everything written there goes over the network to every replica and then into the backup archive, so the extra volume gets paid for three times over. 1.75 KB extra per update sounds small, but at 100 updates a second that is 15 GB of extra WAL per day on a single table. But again. I can't say whetever your busiest table is taking that sort of traffic. The multiplier is the point. Those indexes work helpdesk-run1 is 23 to 46 times faster on four of the nine queries I measured: the team filter drops from 0.76 ms to 0.02 ms, filter-by-organization from 1.20 ms to 0.03 ms, while mean planning time rises from 0.30 ms to 0.44 ms. All nine, computed from the unrounded means: Eight of the nine queries perform as expected. The ninth is the SLA sweep, and it runs 111 times slower on the generated schema. The index meant to serve it looks right: CREATE INDEX tickets first response due idx ON tickets workspace id, first response due at WHERE first response at IS NULL AND first response due at IS NOT NULL AND status < ALL ARRAY 'solved','closed' ; Ask for one workspace and match every condition, and Postgres reads 8 buffers. Drop the status predicate and it can't use the index at all: a sequential scan over 43,727 buffers. Keep the status predicate but sweep across workspaces and it does use the index, and that is worse, at 43,699 buffers, and slower than the scan it replaced. My baseline indexes first response due at with no tenant prefix and answers the same sweep in 102 buffers. An SLA sweep is inherently cross-tenant, and the leading workspace id that is right everywhere else on this table is wrong here. Where it breaks even Leaving out that SLA query, helpdesk-run1 saves 0.554 ms per execution on average across the other eight queries. Planning overhead takes back 0.146 ms : more indexes give the planner more choices to evaluate on unprepared queries. Net gain: 0.408 ms saved per read . On the write side: 9,013 − 4,850 ms ÷ 200,000 comes out to 0.021 ms added per update . Comparing read savings against write penalties, the fifteen-index schema wins on CPU time as long as you do fewer than twenty updates for each read, and in a support app, where agents refresh their queues constantly and the updates arrive in bursts between those refreshes, twenty to one is not a hard ratio to stay under. Measured on CPU time alone there is nothing here to argue with. Except CPU is not the whole bill. That calculation ignores the 15 GB of extra WAL streaming to replicas and backups each day. And it treats traffic patterns as fixed. The moment a feature ships that updates tickets in bulk, the math shifts, yet schema indexes are rarely revisited when write volume changes. Which column the index sits on last activity at is a key column in six of the sixteen indexes, and that placement costs more than the count does. I built a small table to test it: six secondary indexes either way, the same UPDATE ... SET last seen at = now over 300,000 rows, and the only difference is whether one of those six sits on the column being written. | the updated column is | HOT updates | HOT % | update time | |---|---|---|---| | not indexed | 138,468 | 46.2% | 2,743 ms | | indexed | 0 | 0.0% | 3,979 ms | The count stays the same and so does the workload; only the placement of one index changes. Moving it onto the touched column costs every HOT update and adds 45% to the time. A synthetic table is the only way to isolate that effect, keeping everything else fixed. So workspace id, status, last activity at DESC deserves a second look. It's a sensible queue index; it's the one you would write by hand. It also means every ticket touch is a non-HOT update, with a fresh entry in every index whose predicate the new row still matches, up to all sixteen, and a dead tuple for VACUUM to collect later. My baseline deserves the same look. All four index sets in the head-to-head ran at 0% HOT , my hand-written seven included. A queue index on the activity timestamp is obvious enough that I wrote one too. HOT was lost either way. The generated sets then keep eight more indexes up to date on every write. Check it live: 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 cue: which index is on the column your UPDATE touches? What the indexes evict Once HOT is disqualified, every secondary index you add costs about 26 MiB of extra WAL across the same 200,000 updates, averaged over twelve of them. It is not a flat rate: the per-index cost ran from 18 to 46 MiB depending on how many full-page images that step happened to trigger, and you pay it on every write from then on. The cache impact was worse. The benchmarks above ran with shared buffers=512MB , plenty of headroom. When I throttled shared buffers to 128MB with a cold cache, the tables turned: | index set | blocks read from disk | index MiB cached | heap MiB cached | |---|---|---|---| | baseline 7 | 53,089 | 92.6 | 35.2 | | helpdesk-run1 15 | 357,022 | 118.7 | 9.2 | Physical reads jumped by 6.7× , and the buffer columns say why: the extra index pages have to live somewhere, and what they push out is the heap. Cached heap fell from 35 MiB to 9 MiB out of the same 128 MiB pool while the cached index went the other way, and the update time gap widened from 1.9× to 2.1×. None of that happened at 512 MB, where everything fit, so read the 128 MB run as a demonstration and not as a measurement of your own server: it shows which way index pages push the cache when there isn't room for both. Nobody drops the twelfth index The other half of the problem is the next commit. I gave the sixteen-index tickets table and an ordinary feature request to six fresh instances, three each from two vendors, one of which had no part in the thirty. Filter the ticket list by tag, or add a "waiting on customer" queue. The prompt mentions neither performance targets nor existing indexes. Not a single run removed the twelfth index. Five of the six added a new one, every time on a mutable column together with a mutable predicate. CREATE INDEX CONCURRENTLY tickets waiting on customer idx ON tickets workspace id, COALESCE last customer message at, created at WHERE status = 'pending'; Two runs from different vendors arrived at that index independently, down to the COALESCE and the predicate. All six put tags in a junction table rather than an array column on tickets , so not one of them widened the hot row. Only one explained the choice: it rejected the array column because that would break HOT updates on every tag edit, then went ahead and added the partial index on status = 'pending' anyway, which also breaks HOT updates. It spotted the risk for one column and missed the same risk on another. One run answered the same requirement with a view over the existing indexes and added nothing, which is the right answer. I like to think I'd have accepted the view in review. Given that it arrived with no diff to the schema at all, I'm honestly not sure I'd have read past the file list. last idx scan . It was right, and the test was re-run with honest provenance. I returned the same table as a performance review instead: "write latency is creeping up, vacuum takes longer every week," with no stats included. All four runs caught it. They found last activity at in six indexes, concluded no update on the table could ever be HOT, and proposed drops without seeing a single query plan. Every one of them asked for pg stat user indexes before dropping anything. Give them the counters and the advice gets sharper still. "Make it production-ready" is a database decision So, back to make it production-ready , the phrase the eighteen Models B and D runs all ended with. I ran those two models again on the vet and freight specifications with the phrase removed, nothing left but "read this file, write the schema". | condition | vet, indexes/table | freight, indexes/table | |---|---|---| | Models B/D, with wrapper | 1.77 – 1.93 | 2.33 – 3.14 | | Specification only | 1.33 – 1.59 B/D | 2.17 – 2.46 B | | Model A, specification only | 1.05 – 1.06 | not run | | Model C, specification only | 0.55 | 0.96 | That one phrase is worth about a fifth of the index count. 20% on vet, 17% on freight. Three words at the end of a prompt, which nobody thinks of as a schema decision, move the write cost of the busiest table in the application. Neither number is artificial. The wrapper is what people actually type, so those are the realistic ones. The bare specification is the fair comparison between models. The model differences remain, even if narrower. Specification-only, Models B/D against Model A is 1.4× instead of 1.8×. Against Model C the gap stays large: 2.7× on vet, 2.4× on freight. About a third of that wider gap came from the prompt; the rest is just the model. That pattern holds across all seven hot tables: discretionary indexes on the vet tables ran 16 to 20 for Models B/D, 8 to 10 for Model A, and 5 for Model C. Vendor differences didn't change everything. No model indexed foreign keys by reflex, regardless of density. Redundancy also didn't track with index count: the two runs with the most redundant indexes sat in the middle of the pack, and the single densest schema had only one redundant index out of 163. Meanwhile, the leanest freight schema created four partial unique indexes on load assignments all predicated on status . Those indexes enforce data correctness and cannot be dropped, but they kill HOT updates just like the optional ones. Eighteen of the thirty schemas come from a single vendor. That density is that model's, not a property of all models: it varies between models by a factor of three, and the prompt moves it again. The write costs land the same way whoever wrote the schema. Checking the write path in production None of this is an argument for index minimalism. On read-heavy tables without churn, indexes are practically free. If your choice is between a 40-second report or one more index on an append-only log, add the index. The real problem is adding index after index onto write-heavy operational tables. In my helpdesk replay, two indexes handled 90% of all lookups. Meanwhile, two large GIN indexes on the ticket subject took 130 MiB of disk and had to be maintained on every insert and subject edit, yet saw zero scans in normal workflow tests. On a live database, check pg stat user indexes before touching anything: SELECT s.relname, s.indexrelname, pg size pretty pg relation size s.indexrelid AS size, i.indisunique AS uniq, s.idx scan, s.last idx scan FROM pg stat user indexes s JOIN pg index i ON i.indexrelid = s.indexrelid WHERE NOT i.indisprimary AND s.idx scan = 0 ORDER BY pg relation size s.indexrelid DESC; dryrun https://boringsql.com/products/dryrun/ users: detect kind=unused indexes asks the same question from a schema snapshot, offline, so it can run at change time rather than after. Same counters, so the same caveats apply. Read the output before dropping anything: counters reset on restarts, replicas might use indexes the primary ignores, and unique indexes exist for data integrity. In my test run, tickets subject fts idx showed zero scans simply because queries filtered by workspace id first hitting 500 rows out of a million , so Postgres skipped full-text search entirely. Zero scans in one benchmark doesn't mean drop the index.