I Let a Free Model Approve Postgres Indexes for 48 Hours. The Human Still Owned the Mistakes.

Could a free model act as a database advisor without a license fee or a human babysitter? I had my doubts, but I had a more pressing problem: a staging database that kept missing query deadlines. So for the next 48 hours, I handed my slow-query log to a free model through MonkeyCode's free model access, ran the whole pipeline on a free server, and treated every suggestion like a suspect until EXPLAIN proved otherwise.

Disclosure: This article was prepared as part of MonkeyCode's product outreach.

The Setup

I wrote a small Python script that wakes up on a free server every four hours, pulls the top slow queries from pg_stat_statements, and then feeds them into the free model with a strict prompt. The output had to be JSON: table name, column list, index type, and a one-line reason for each suggestion. The script never touched production; it only wrote its verdicts to a local Markdown report that I would read over coffee.

The surprising part was not that the model could generate SQL. The surprising part was that it sometimes generated better SQL than I would have written under pressure. But it also hallucinated tables that did not exist, proposed duplicate indexes, and ignored existing partial indexes entirely. Here is the exact prompt that worked best after two rounds of tweaking:

def build_prompt(existing_indexes, slow_queries):
    return f"""
You are a Postgres performance advisor.
Existing indexes on relevant tables: {existing_indexes}
Slow queries (from pg_stat_statements): {slow_queries}
For each query, propose up to two CREATE INDEX strategies.
Return JSON: [{{"table": ..., "columns": [...], "type": "btree|partial|covering", "reason": ...}}]
"""

I measured every proposal by running EXPLAIN (ANALYZE, BUFFERS) against a cloned staging table. My validation loop was simple: capture the baseline plan, create the suggested index, capture the new plan, then decide whether the change earned its write amplification.

The 48-Hour Log

Hours 0–12: First Impressions

The first batch produced nineteen suggestions. Of those, eleven were reasonable single-column indexes, five were duplicates of indexes that already existed, and three referenced a table named users_archive that I had deleted two months earlier. The model clearly needed better context, so I added the output of \d+ for every candidate table to the prompt.

Hours 13–24: The Partial Index Moment

After the context fix, the model caught something real. A recurring status-filter query on our deliveries table was scanning 4.2 million rows because the only index was on created_at. The model proposed a partial index on WHERE status = 'pending' with id as a covering column. The scan dropped to a bitmap heap scan in staging, and the estimated cost fell by about 80%. That single suggestion justified the whole experiment.

Hours 25–36: Confident but Wrong

The model proposed a composite index on (customer_id, created_at DESC) for an order-history query. It sounded perfect. But EXPLAIN showed the planner ignoring it because a filter on status was still forcing a seq scan. The model had no way to see the query’s where clause nuance unless I explicitly included the full SQL text. So I changed the prompt to require the full predicate, not just a summary.

Hours 37–48: The Home Stretch

By the end, I had applied four indexes to staging and verified each one with EXPLAIN. Two were useful, one was redundant, and one was neutral. The model’s hit rate was around 50%, which is better than random but nowhere near trustworthy enough to run without a review gate.

What Broke

• Context amnesia: When the prompt grew beyond a few thousand tokens, the model forgot the existing index list and started recommending duplicates.
• Schema blindness: It once suggested an index on status as a standalone, even though a partial index already existed on the same column with a more specific predicate.
• No write amplification awareness: The model never asked whether a new index would slow down insert-heavy tables. That trade-off is still a human decision.

What I’d Repeat

Feed the model small, dense context: schema definitions, existing indexes, and the top five slow queries with their full predicates. Never let it propose more than two indexes per query, otherwise the signal-to-noise ratio collapses. Always validate every suggestion with a staged EXPLAIN run, and use CREATE INDEX CONCURRENTLY with a rollback plan if you ever move to production.

Who Should Not Use This

If your workload is write-intensive, every extra index is a tax on every insert, update, and delete. If you have no staging environment, do not let a free model design indexes for you. And if you lack the patience to read a second opinion, you will end up blaming the model for what was really a missing human review step.

The Takeaway

After 48 hours, I still believe databases need humans. But I also believe a free model can act as a tireless draft-puller, one that throws out ideas you might have dismissed too quickly. The best part is that the model never gets tired, never asks for a raise, and never gets embarrassed when you swap its composite index for a better one. You still own the mistakes — and the indexes. If you have run a similar experiment, I would love to hear how many suggestions you actually applied.