Live project activity

PostgreSQL index review for migration PRs

Stop bad PostgreSQL indexes before production.

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.

Catch an overlap in 60 seconds Watch the actual review

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

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.

Start with your problem

What are you trying to decide?

Should I add this PostgreSQL index?

Check whether a proposed PostgreSQL index is supported by the queries your database actually runs before merging it.

See the decision path

Why is PostgreSQL not using my index?

Test whether PostgreSQL selects a proposed index for representative query shapes before keeping it as production overhead.

See the decision path

Does this migration add a duplicate PostgreSQL index?

Find leading-prefix overlap between a proposed B-tree index and indexes already present in PostgreSQL.

See the decision path

Test a PostgreSQL index before creating it

Use HypoPG and real workload evidence to evaluate a proposed PostgreSQL index without creating physical DDL.

See the decision path

Review PostgreSQL index migrations in CI

Add a read-only evidence gate for CREATE INDEX migrations and retain review artifacts with each pull request.

See the decision path

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?

01 / Proposal

Review the migration

Parse the supplied PostgreSQL CREATE INDEX locally. Unsupported shapes fail clearly instead of being approximated.

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.

Read the build story Read the evidence limits

Evidence in. A cautious next step out.

No autopilot. No mystery score.

Observed workload

What your database actually runs

Aggregated pg_stat_statements become query fingerprints. Raw workload SQL is not written to the report.

Catalog + HypoPG

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.

Portable evidence

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 · Early alpha

Catch an overlapping index in 60 seconds.

Run the sanitized example with no PostgreSQL service or database secret. It should flag an existing overlap and write a review artifact you can inspect.

Installation guide Release notes Watch the actual review Share a sanitized result

Early alpha supports plain-column, non-unique, ascending B-trees. This example proves the review path, not production performance. When ready, continue with the live read-only PostgreSQL workflow.

shellPython 3.10+

$ git clone --depth 1 https://github.com/eyeinthesky6/indexpilot.git
$ cd indexpilot

$ uvx --from "indexpilot==1.1.0a8" 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

Actual-use walkthrough

See the migration, command, overlap verdict, and saved evidence.

Open in a new tab

This walkthrough uses the bundled sanitized quickstart and never connects to a PostgreSQL database. It shows the same existing_overlap result you can reproduce locally.

Four 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.

worth_benchmarking

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.

existing_overlap

A comparable existing B-tree already covers the proposal's leading prefix.

Next: Inspect both shapes. This is review evidence, never automatic drop advice.

not_supported_by_current_planner_evidence

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.

inconclusive

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

Team workflow dipstick

Tell us where repeated index review breaks.

The CLI and Action exist today. Shared policy, pull-request summaries, and retained team decisions are still a hypothesis. One public form now tests that gap with real, sanitized workflows.

Share a team pain See the evidence gates

Current

Free advisory CLI and Action

Hypothesis

Repeatable team policy and decision history

Actual asks

Aggregate fixed choices in the public rollup

Follow live project activity, then tell us which repeated team pain should shape the next upgrade.

Open source · read only

Put one proposed index on trial.

Get started Share a team pain