The Hidden Cost of AI Code: How Coding Agents are Over-Indexing PostgreSQL Databases

For engineering teams everywhere, the archetype of “the database guy” has become intimately familiar with a shifting landscape of technical questions. Over the past eight months, however, those inquiries have undergone a profound transformation. The most repetitive, basic questions have vanished entirely. Developers no longer ask how to avoid putting data structures into a database, and the code arriving for peer review has grown noticeably more polished.

Yet, beneath this veneer of superior craftsmanship, a subtle architectural pathology has emerged. This past summer, a schema landed in front of reviewers featuring twelve proposed index drops on a single table—an immediate red flag suggesting that modern coding agents tend to over-index. When subsequent schemas exhibited the exact same structural shape, a deeper investigation became inevitable.

Dismissing these occurrences as mere "AI slop" would be far too easy. The truth is much more nuanced: the vast majority of these agent-generated changes are technically competent. To understand the true depth of this phenomenon, a controlled empirical harness was constructed to measure the real-world impact of AI-driven database design.

Main Facts: The Rise of the Hyper-Indexed Schema

The core revelation of the study is simple yet alarming: modern large language models (LLMs) are exceptionally good at writing isolated SQL statements, but they lack a holistic understanding of write-traffic dynamics.

When thirty model-generated schemas were loaded into a PostgreSQL 18.6 environment, auditing 838 indexes across twelve of them revealed a high baseline of technical competence. Only ten indexes served no discernible requirement; the rest demonstrated solid craft. All four tested models handled GIN and GiST indexes cleanly, constructed partial indexes with sensible predicates, and positioned multi-tenant composite keys in the right order. The baseline SQL quality produced by modern agents vastly outperforms anything written by AI a year ago.

However, indexes are exceptionally beneficial only until they are piled onto a single, high-traffic table that absorbs the bulk of writes. On quiet tables, the performance impact of an extra index goes completely unnoticed. On hot operational tables, conversely, every single index represents additional computational overhead on every write operation.

In one support-tool schema evaluation, an agent created sixteen indexes on the tickets table alone. Six of those indexes targeted last_activity_at, a column updated dynamically every time a support agent touches a ticket. Compared to a hand-written baseline featuring seven indexes, the generated schema produced 1.8 times the Write-Ahead Log (WAL) volume, took 1.9 times longer per update, and significantly escalated VACUUM overhead.

Chronology of an Empirical Audit

To arrive at these conclusions, a rigorous testing methodology was established to track how AI models approach relational database schemas over time.

The Setup

Six fictional SaaS products were conceptualized for the review:

1. A helpdesk application
2. A fitness management platform
3. An analytics engine
4. A marketplace
5. A veterinary clinic system
6. A freight and logistics tracker

One test run was sourced from Andrey Grunev, while subsequent runs for that model were executed independently. The specifications provided to the models purposely excluded table names, column hints, and database constraints, noting only that PostgreSQL would be the target engine.

The first four product specifications were written directly (and polished via LLM), while the final two were drafted by an independent model based on short descriptive prompts. Each specification was handed to fresh model instances without schema templates or index hints, with strict instructions to design the PostgreSQL schema, write necessary SQL queries, and execute without asking clarifying questions.

Four models from three vendors participated. To maintain neutrality and focus purely on database performance rather than vendor leaderboards, they were anonymized as Model A through Model D.

Crucially, twenty-six of the thirty runs included an extra line appended to the prompt: "make it production-ready." This reflects standard human behavior when interacting with coding agents. Later, Models B and D were re-run without this prompt wrapper to measure its specific influence.

In total, thirty runs were loaded into PostgreSQL 18.6, gathering metrics directly from pg_index and pg_stat_* on a live database.

Supporting Data: The Anatomy of a Write Penalty

The performance penalties associated with AI over-indexing are quantifiable, structural, and severe.

Index Concentration on Hot Tables

Summary slides often mask index density behind averages. The hidden catch is where those indexes accumulate: every coding agent dumps its secondary indexes onto the single table experiencing the highest write frequency. Helpdesk runs concentrated hits on tickets; fitness apps targeted class_occurrence; analytics apps hammered events.

The Cost of Sixteen Indexes

A synthetic benchmark executed on a million-row tickets table simulated a realistic support workload (60% customer replies, 25% status updates, 15% reassignments) under PostgreSQL 18.6 with fillfactor=90. Autovacuum was disabled to preserve predictable row layouts, measuring WAL output via EXPLAIN (ANALYZE, BUFFERS, WAL) averaged across three runs.

As the data demonstrates, when models scale up to fifteen secondary indexes, the cumulative penalty reaches staggering levels: 1.8x the WAL volume, nearly double the execution latency, 1.6x the on-disk footprint, and an extra 65% in VACUUM processing time for the exact same workload of 200,000 updates.

Cache Eviction and Memory Pressures

When system resources are constrained—tested by throttling shared_buffers down to 128MB with a cold cache—the performance degradation compounds:

• Physical reads spiked by 6.7x.
• Cached heap data dropped drastically from 35 MiB to 9 MiB out of the 128 MiB pool.
• The update time gap widened from 1.9x to 2.1x as bloated index pages systematically pushed the core table heap out of active memory.

Official Responses and Iterative Testing

To verify whether models could self-correct when presented with diagnostic data, the over-indexed tickets table was fed back to fresh AI instances under the guise of a performance review: "write latency is creeping up, vacuum takes longer every week," completely devoid of raw statistics.

All four tested models successfully diagnosed the root cause. They identified last_activity_at across multiple indices, correctly deduced that no row updates could ever execute as Heap-Only Tuple (HOT) updates, and proactively proposed index drops without reviewing a single execution plan. Furthermore, when prompted with explicit counters from pg_stat_user_indexes, their architectural advice grew even sharper.

However, a concerning behavioral trait surfaced during subsequent feature additions. When given an ordinary feature request (such as filtering a ticket list by tag or adding a pending queue) to the already bloated sixteen-index schema, not a single model run removed an existing index. Instead, five of the six models introduced yet another mutable index containing volatile predicates:

CREATE INDEX CONCURRENTLY tickets_waiting_on_customer_idx
  ON tickets (workspace_id, COALESCE(last_customer_message_at, created_at))
  WHERE status = 'pending';

Implications for Modern Engineering Teams

The widespread adoption of AI coding agents requires a fundamental shift in how database schemas are reviewed and deployed. Several clear takeaways emerge for software architects and database administrators:

1. The Prompt Wrapper Matters: Appending ubiquitous phrases like "make it production-ready" to an LLM prompt is not a neutral act. Empirical testing proved this single phrasing choice artificially inflates index creation counts by roughly 20%, directly impacting operational write costs.
2. Read Efficiency vs. Write Penalty: While individual AI-generated indexes frequently make read queries 20 to 46 times faster, they operate under the dangerous assumption that write traffic is static and cheap. In environments characterized by high-frequency mutations, cumulative index maintenance suffocates database throughput.
3. The Death of HOT Updates: Placing indexes on frequently mutated columns (such as timestamps or statuses) instantly disqualifies PostgreSQL from utilizing efficient Heap-Only Tuple updates, forcing full index-pointer rewrites and triggering massive bloat for the background vacuum process.
4. Proactive Monitoring is Mandatory: Engineering teams can no longer rely on coding agents to prune historical technical debt. Regular audits of pg_stat_user_indexes and strict manual oversight during code review remain the ultimate defense against the creeping, silent performance drain of vibe-coded database architectures.