I tried to build a DNA-inspired database. I accidentally made a Postgres index checker.

This project was supposed to be a quick experiment.

I had been reading about DNA storage and got carried away with the idea. DNA can pack a ridiculous
amount of information into a tiny space. I started wondering if a database could behave a bit like
a living system: the schema as its basic code, the queries as signals, and indexes changing as the
workload changed.

I spent two or three days building around that idea.

Then I had to admit something fairly obvious. Most of what I was reading described DNA as
an archival medium. It involves writing and
reading physical DNA. That is nowhere near the speed or simplicity needed by a normal application
database.

Also, I am not a PostgreSQL expert. I was building an algorithmic-trading system, learning as I went,
and trying to solve a problem I did not fully understand yet.

When the sprint ended, I could not tell whether I had built something useful or just wrapped a lot of
code around a clever-sounding metaphor.

The literal DNA idea had to go. One smaller question survived:

When somebody adds a new Postgres index, how do we know it is useful and not just valid-looking SQL?

That question became IndexPilot.

The problem I kept running into

Adding an index looks simple:

CREATE INDEX orders_customer_created_idx
ON public.orders (customer_id, created_at);

I could read that line. I could understand the intention. What I could not tell was whether the real
database needed it.

Maybe the query it is meant to help hardly ever runs. Maybe another index already covers the same
columns. Maybe PostgreSQL would ignore it. Maybe it helps reads but adds more cost to every write.

The SQL can look completely reasonable while the decision is still wrong.

That felt like a useful place for a small tool. Not a tool that changes the database automatically.
Just something that collects enough evidence to make the next review less guessy.

What IndexPilot does

IndexPilot reviews the exact CREATE INDEX statement in a migration.

It can compare that proposed index with:

• query patterns from pg_stat_statements;
• indexes that already exist;
• an optional hypothetical plan from HypoPG.

It returns a short verdict and the evidence behind it. The result can be written as JSON, Markdown,
or SARIF for CI and pull requests.

It does not apply the migration. It does not create or delete an index. It does not run
EXPLAIN ANALYZE, and it does not claim that a planner estimate is real production speed.

The most positive verdict is worth_benchmarking. That wording is deliberate. It means the idea has
enough evidence to test properly. It does not mean the index is safe to ship.

You can try it without a database

I wanted the first run to be easy because I hate installing a tool only to discover that it needs
six other things before it can show anything.

The repository includes a cleaned-up workload snapshot, so this example needs no credentials,
Docker, extension, or running database:

git clone https://github.com/eyeinthesky6/indexpilot.git
cd indexpilot
uvx --from "indexpilot==1.1.0a5" indexpilot review \
  --migration-file examples/quickstart/migration.sql \
  --snapshot-file examples/quickstart/workload-snapshot.json \
  --output artifacts/first-review.json \
  --markdown-output artifacts/first-review.md \
  --stdout

The example returns:

Index statements reviewed: 1
Verdicts: {'existing_overlap': 1}

The proposed index overlaps one already present in the sample catalog. IndexPilot does not tell you
to delete either one. It tells you that the new index needs a better reason before both are carried
forward.

Why not use the advanced Postgres tools?

You should use them.

Migration linters can catch unsafe DDL. Index advisers can suggest indexes. HypoPG can show a plan
with a hypothetical index. A proper production-like benchmark can measure latency, write cost,
index size, build time, and rollback.

IndexPilot is not trying to replace those tools. It sits earlier in the process and asks a narrower
question: does this exact index proposal have enough evidence to spend time benchmarking it?

That is probably most useful in a pull request, especially when the migration was suggested by an AI
coding agent or by an application developer who does not spend all day tuning Postgres.

In other words, somebody like me.

What I still do not know

I do not have a dramatic performance story or thousands of users. I have a public tool, a working
database-free example, tests, and a question I think other developers may recognize.

The tool also has clear limits. It does not prove production speed. It does not measure physical
bloat or write overhead. Some index types and shapes are still unsupported because pretending to
understand them would be worse than returning an honest "unsupported" result.

I am publishing it now because keeping it private will not answer the main question: is this review
step actually useful to people who work with PostgreSQL?

If you review database migrations, I would genuinely like to know:

What do you check before accepting a new index, and which part of that review is still annoyingly
manual?

You can run the small example in the
IndexPilot repository. If it fails, the failure itself is
useful feedback. If it works, tell me whether the verdict helped or merely stated the obvious.

Real project, real story, guided by human, drafted by AI.

Comments

[Mads Hansen]

The "valid-looking SQL" framing is spot on. Index review is one of those areas where syntax correctness is almost the least interesting signal.

I like that the strongest verdict is worth_benchmarking. That wording keeps the tool honest: pg_stat_statements and HypoPG can tell you whether an idea deserves attention, but they should not turn into an auto-merge button.

One thing that might make the evidence even more useful in PRs: show the write-side cost explicitly next to the read-side promise. Even a rough "this table gets N inserts/updates per day" signal helps reviewers avoid indexes that optimize a rare query while taxing a hot table forever.