01 / Proposal
Review the migration
Parse the supplied PostgreSQL CREATE INDEX locally. Unsupported shapes fail clearly instead of being approximated.
PostgreSQL index review for migration PRs
Before an index reaches production, make it prove its case.
IndexPilot checks the exact CREATE INDEX in your migration against observed workload, comparable indexes, and optional hypothetical plans. It then leaves a review artifact your team can inspect.
- Read-only evidence path
- JSON · Markdown · SARIF
- MIT licensed
illustrative review receipt
$ indexpilot review \
--migration-file migrations/add_orders_index.sql \
--hypopg --sarif-output artifacts/indexpilot.sarif
--migration-file migrations/add_orders_index.sql \
--hypopg --sarif-output artifacts/indexpilot.sarif
proposal 01
public.orders (tenant_id, created_at)
- workload
- observed
- overlap
- none found
- hypopg
- selected
Verdictworth_benchmarking
Advisory only. Planner cost is not production latency.
The missing decision layer
An index is cheap to propose. It is expensive to keep.
Every accepted index adds write work, storage, cache pressure, backup weight, and maintenance. Migration linters check whether DDL is safe to run. Advisers suggest possible indexes. IndexPilot asks the in-between question: does this exact proposal have enough evidence to deserve a real benchmark?
02 / Evidence
Interrogate reality
Read pg_stat_statements, catalog overlap, and optional session-local HypoPG plans in a read-only transaction.
03 / Record
Leave a decision trail
Write stable JSON, human-readable Markdown, and SARIF that can meet a reviewer in the pull request.
The origin
It started with DNA storage. A live database changed the question.
DNA's information density inspired the first database-genome experiment. But synthesis and sequencing suit archival storage, not the millisecond path of an algorithmic-trading system. That system exposed a more immediate problem: a plausible index can still be the wrong tradeoff.
IndexPilot became the useful part of that experiment: a narrow, read-only review step for the exact index a team is considering. It does not store data in DNA.
Evidence in. A cautious next step out.
No autopilot. No mystery score.
What your database actually runs
Aggregated pg_stat_statements become query fingerprints. Raw workload SQL is not written to the report.
What exists and what might change
Compare valid B-trees, then optionally ask whether PostgreSQL selects the exact hypothetical shape with EXPLAIN instead of ANALYZE.
What a human should do next
Receive one bounded verdict, its limitations, and a practical next step. Positive means benchmark it, never ship it blindly.
Quick start
One CLI. Your migration. A read-only role.
The alpha is distributed as a GitHub release artifact while PyPI publishing is prepared. The core CLI does not need the dashboard, API, Docker, or ML extras.
Alpha boundary: plain-column, non-unique, ascending B-trees. HypoPG review plans one representative query per candidate; it is not a full workload regression test.
shellPython 3.10+
$ pipx install \
"https://github.com/eyeinthesky6/indexpilot/releases/download/v1.1.0a1/indexpilot-1.1.0a1-py3-none-any.whl"
$ indexpilot doctor --schema public --min-calls 10
$ indexpilot review \
--migration-file migrations/add_orders_index.sql \
--hypopg \
--markdown-output artifacts/indexpilot.md \
--sarif-output artifacts/indexpilot.sarifFour bounded verdicts
Designed to slow down the wrong certainty.
A completed review can be useful even when the answer is “not enough evidence.” Teams can opt into CI failure with repeatable --fail-on flags.
The exact hypothetical shape was selected and cleared the advisory planner threshold.
Next: Benchmark latency, writes, size, build time, and rollback on a production copy.
A comparable existing B-tree already covers the proposal's leading prefix.
Next: Inspect both shapes. This is review evidence, never automatic drop advice.
The planner did not select the exact shape or its improvement stayed below threshold.
Next: Inspect the plan or test another shape. Do not infer that the index is harmful.
Workload, permissions, or planner evidence was missing or insufficient.
Next: Collect representative traffic or repair evidence access before deciding.
Where it fits
A narrow wedge, on purpose.
IndexPilot complements mature migration linters, index advisers, and database monitoring. It owns the review checkpoint for an index somebody already wants to merge.
Tool categoryThe question it answers
Migration linterIs this DDL operationally safe to run?
Index adviserWhat indexes might improve this workload?
IndexPilotDoes this exact migration index have enough evidence to benchmark?
Read-only is a product boundary, not a footnote.
- Never creates, drops, or reindexes a physical index
- Parses migration SQL locally
- Uses EXPLAIN, never EXPLAIN ANALYZE
- Resets session-local HypoPG state
- Excludes raw workload SQL from reports
- Rejects unsupported index shapes clearly
Open-source alpha