The Vibe Father
An AI agent can produce an index recommendation before you finish describing the slow query. That does not make the recommendation safe. Database tuning is one of the places where a fluent suggestion can create a costly regression, extra write overhead, a lock on a large table, an index that never gets used, a query plan that changes after data grows, or a “fix” that only works on a toy dataset.
The rising interest in database optimization is deserved. The reliable workflow is simple, establish a baseline, inspect the real plan, make one hypothesis, prove the result, and preserve a rollback. Use an agent to accelerate the investigation—not to replace the evidence.
Start with the question, not the index
“Optimize the database” is not an agent task. Begin with one user-visible problem, a dashboard query that times out, a report that slows after a table crosses a threshold, or a write path that holds a lock too long. Capture the exact query shape, representative parameters, current latency, frequency, and the data volume that makes it fail.
Then write a bounded brief
Outcome
- Reduce the p95 latency of <query or endpoint> without changing results.
Ground truth
- Current query and representative parameters
- Current EXPLAIN / EXPLAIN ANALYZE output
- Table sizes, indexes, and relevant write volume
Constraints
- No production DDL without review and an approved rollout window
- Preserve query results and authorization behavior
Definition of done
- Benchmark before and after on representative data
- Explain the plan change
- Include migration, rollback, and validation stepsThat is a version of the AI coding task brief tailored to a high-blast-radius surface.
Let the query plan tell you what is happening
PostgreSQL and MySQL both provide EXPLAIN to show how the optimizer intends to execute a statement. PostgreSQL’s documentation emphasizes that the planner chooses among possible plans based on the query and properties of the data, MySQL similarly exposes join order and access choices. The plan is the evidence an agent needs before it suggests an optimization.
Ask the agent to identify, in plain language
- which table dominates the work,
- whether the estimate and actual row counts diverge,
- where filtering, sorting, joining, or aggregation becomes expensive,
- whether an existing index is usable for the actual predicate and ordering, and
- which assumption would make the proposed change wrong.
For PostgreSQL, use EXPLAIN (ANALYZE, BUFFERS) only in a safe environment or with a query you understand, because ANALYZE executes the statement. For MySQL, use EXPLAIN and, where appropriate, its execution-analysis tooling. Do not point an eager agent at production and call it observability.
The one-change experiment
Pick the smallest change that tests a specific hypothesis. Maybe it is an index that matches a selective predicate, a rewrite that moves a non-sargable expression, a missing foreign-key index, fresher statistics, or a pagination shape that stops scanning rows it will never return.
Change one thing. Then compare
| Before | After | Question |
|---|---|---|
| Representative latency | Representative latency | Did the user-visible path improve? |
| Plan node and row estimate | Plan node and row estimate | Did the optimizer choose the intended access path? |
| Rows read and buffers / I/O | Rows read and buffers / I/O | Did we reduce work, not just move it? |
| Write latency and storage | Write latency and storage | What did the new index cost? |
| Result fixture | Result fixture | Did behavior stay correct? |
If the gain is not measurable, do not merge it because the model’s explanation sounded clever. Revert it and investigate the next hypothesis.
Where agents are genuinely useful
Agents are excellent at the mechanical parts of an investigation, collecting query shapes, normalizing plan output, finding queries with similar predicates, generating a reproducible benchmark harness, drafting a migration, and explaining an unfamiliar plan node. They can also propose alternatives you might not think to test.
They are poor at declaring that an optimization is safe without the workload data. An LLM has not seen your skewed tenant distribution, your production connection pressure, or the monthly job that writes ten times more data than today’s sample. Let it generate hypotheses, make measurements decide.
Database changes need a stricter definition of done
- Correctness the query returns the same authorized result set for representative cases.
- Performance the measured target improves on production-shaped data, not only locally.
- Cost write overhead, storage, locks, and cache effects were considered.
- Rollout the migration is safe for the engine and table size, with an owner and maintenance window where needed.
- Recovery the team can roll back or disable the change without guessing.
That is the same verification-first discipline described in our definition-of-done guide, with higher stakes because a database mistake can affect every request at once.
The rule worth remembering
Never merge an AI-suggested index because it looks intuitively right. Merge it because a representative plan and benchmark show that it improves the target workload, the write cost is acceptable, and you can undo it safely. In database work, the query plan is the reviewer who does not care how persuasive the explanation sounded.
