warn-redundant-index

What triggers it

An IndexStmt where ctx.existingIndexes — the live catalog indexes for the table the engine resolved as target — contains one that covers it. Covering means: the same access method (accessMethod, defaulting to btree), the new key list being a prefix of the existing keyColumns after normalizeKey() lowercases and strips quotes, and, when the new index is unique, the existing one being unique with exactly the same number of key columns.

What does not

Every run without --database-url: existingIndexes is empty and the rule returns null on the first line (requiresDatabaseUrl is set). It also stands down when the new index has a whereClause, when indexKeyColumns() returns null because some IndexElem has no name (an expression key), when the covering candidate isPartial, when the access methods differ, and when a new UNIQUE index is matched against a non-unique or differently-wide index.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It needs --database-url: without a connection it has nothing to read and stays silent.

The lock, and what it blocks

No lock claim of its own. CREATE INDEX takes SHARE and CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE, and this rule fires on both without distinguishing them — it is about the index being unnecessary, not about how it is built. MP001, MP101 and MP104 cover the locking and the duration.

Why it matters

PostgreSQL can use the leading columns of a composite index on their own, so an index on (tenant_id) adds no lookup path that an existing index on (tenant_id, created_at) did not already provide. What it does add is a full build, permanent disk, and work on every INSERT, UPDATE and DELETE for as long as it exists. The migration file cannot tell you this on its own — it needs the catalog of what is already there, so this rule only fires with --database-url.

Unsafe, and safe

-- Production already has:
--   CREATE INDEX idx_users_tenant_created ON users (tenant_id, created_at);

CREATE INDEX CONCURRENTLY idx_users_tenant ON users (tenant_id);

-- A different leading column adds a lookup path the composite index
-- cannot serve on its own, so this one earns its keep.
CREATE INDEX CONCURRENTLY idx_users_created ON users (created_at);

What it assumes

Redundancy is judged from key columns alone. The migration side reads only IndexElem.name, so ASC/DESC, NULLS FIRST, opclass, collation and storage parameters are invisible — an index that exists purely to give the planner a different sort order is reported as redundant. INCLUDE columns are excluded from the catalog keyColumns, so a new covering index that differs only by its INCLUDE list also matches. Nothing is size-aware: a narrow (tenant_id) index that is genuinely much smaller and hotter than the (tenant_id, created_at) covering it is still flagged. Catalog tables are matched by bare relation name, so same-named tables in different schemas are not separated, and the catalog reflects the database you connected to rather than the one the migration will run against.

This rule reads live catalogue state, so it says nothing at all without --database-url. That is the trade: no connection, no guess.

What the CLI prints

⚠ [MP100] WARNING (line 4)
  "idx_users_tenant" on "users" indexes (tenant_id), which is already the leading column of existing index "idx_users_tenant_created" (tenant_id, created_at). PostgreSQL can serve those lookups from "idx_users_tenant_created". The new index adds build time, disk, and write overhead without adding a lookup path.

  Safe alternative:
  -- "idx_users_tenant_created" already covers (tenant_id):
  -- CREATE INDEX idx_users_tenant_created ON public.users USING btree (tenant_id, created_at)
  
  -- Confirm the planner agrees before adding another index:
  EXPLAIN (BUFFERS) SELECT * FROM users WHERE tenant_id = $1;
  
  -- Keep the new index only if it differs in a way that matters: a smaller index
  -- for a hot lookup, a different access method, or a different sort order.

  Why: A B-tree index on (a) is already covered by an existing index on (a, b): PostgreSQL can use the leading columns of a composite index on their own. Creating the narrower index buys nothing and costs a full build, permanent disk, and extra work on every INSERT, UPDATE, and DELETE. Redundant indexes also slow down planning, since the planner considers each one.
  Docs: https://migrationpilot.dev/rules/mp100

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here. The catalogue figures come from the production context this rule documents.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP100
CREATE INDEX CONCURRENTLY idx_users_tenant ON users (tenant_id);

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP100: false

# or keep it, and downgrade it
rules:
  MP100:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.

Related rules

• MP016require-fk-indexthe opposite problem, a missing index
• MP101warn-index-on-write-hot-tablewhat an extra index costs on the write path
• MP042require-index-nameindexes should have explicit names