warn-redundant-index

What It Detects

The new index duplicates the leading columns of an index that already exists on the table.

Why It's Dangerous

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.

Bad Example

-- 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);

Good Example

-- 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);

Configuration

Disable this rule:

# .migrationpilotrc.yml
rules:
  MP100: false

Or change its severity:

# .migrationpilotrc.yml
rules:
  MP100:
    severity: warning