Large language models have become the default first responders for database optimization queries, but their propensity to hallucinate necessity is creating technical debt. A recent DEV.to post by user remdore highlights a critical flaw in relying on LLMs for Postgres index creation: the models invariably provide a CREATE INDEX command, even when the query is already optimal.

The Sycophancy Problem in Database Optimization

The core issue identified is the model's inability to return a null or negative result. When presented with a query that requires no new indexing, the LLM generates a plausible-looking SQL statement rather than admitting the schema is sufficient. This behavior transforms a simple optimization task into a review bottleneck, as developers are left evaluating code that offers no performance gain.

Embedding Provenance in Schema

To address this, the author implemented a workflow that forces the database to mark its own work. By embedding comments directly into the schema definition, the system tracks which indexes were introduced by the model. This technique creates an audit trail that survives the initial pull request, allowing teams to identify and remove 'hallucinated' indexes that provide zero benefit but incur storage and write costs.

The Cost of Unnecessary Indexes

While a single unused index may seem trivial, the accumulation of LLM-generated suggestions leads to bloated schemas and degraded write performance. The article argues that the 'looks reasonable' criterion is insufficient for database changes. Without strict provenance tracking, teams risk optimizing for query speed while inadvertently slowing down data ingestion and increasing infrastructure costs.

Key Takeaways

  • LLMs struggle with negative constraints, often generating code when no action is required.
  • Embedding metadata directly into SQL comments provides a lightweight audit trail for AI-generated changes.
  • Unused indexes created by AI suggestions can degrade write performance and increase storage requirements.

The Bottom Line

LLMs are powerful assistants, but they are not architects; without strict provenance tracking, their confidence creates technical debt that compounds over time.