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

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.

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.

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.

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.

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.

Those indexes work

helpdesk-run1 is 23 to 46 times faster on four of the nine queries I measured. 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']);

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.

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. Measured on CPU time alone there is nothing here to argue with.

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.

Moving an index onto the touched column costs every HOT update and adds 45% to the time. 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. When I throttled shared_buffers to 128MB with a cold cache, physical reads jumped by 6.7×, and the cached heap fell from 35 MiB to 9 MiB while the cached index went the other way.

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

"Make it production-ready" is a database decision

I ran two models again with the phrase "make it production-ready" removed, leaving nothing but "read this file, write the schema". That one phrase is worth about a fifth of the index count. 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.

Checking the write path in production

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;

Read the output before dropping anything: counters reset on restarts, replicas might use indexes the primary ignores, and unique indexes exist for data integrity.