MP092warning

require-partitioned-index-strategy

CREATE INDEX on a partitioned parent cannot use CONCURRENTLY and recursively locks every partition.

operation
Indexes
lock taken
ACCESS EXCLUSIVE
remediation
Manual rewrite
category
Partitioning

What triggers it

CREATE INDEX (IndexStmt) targeting a table this migration itself establishes as a partitioned parent — via CREATE TABLE ... PARTITION BY, a child's PARTITION OF naming it, or an ATTACH PARTITION onto it — in either of two shapes: CONCURRENTLY is set, or the index targets the table recursively (no ONLY) without CONCURRENTLY.

What does not

CREATE INDEX on a table the rule can't identify as a partitioned parent from this migration's own statements — it only reads what the file itself declares, not the database. And CREATE INDEX ... ON ONLY parent without CONCURRENTLY is explicitly exempt — that's the recommended first step, not the problem.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

ACCESS EXCLUSIVE — the same lock a plain CREATE INDEX takes on an ordinary table, except here it's held across every partition built by the same statement, not released between them, so the whole hierarchy is unavailable until the last partition's index finishes.

Why it matters

CONCURRENTLY is simply not available on a partitioned parent — PostgreSQL answers "cannot create index on partitioned table concurrently" and the migration fails at run time, after it has started. Drop the CONCURRENTLY and it is accepted, which is the trap: one statement then builds an index on every partition, holding locks across the whole hierarchy until the last one completes, with no way to stop partway or observe progress. CREATE INDEX ON ONLY parent creates a catalog entry with no storage, which each partition then fills in one at a time.

Unsafe, and safe

Flagged

CREATE TABLE events (id BIGINT, ts TIMESTAMPTZ) PARTITION BY RANGE (ts);

CREATE INDEX idx_events_id ON events (id);
-- Accepted, but builds on every partition inside one lock window

Safe alternative

CREATE TABLE events (id BIGINT, ts TIMESTAMPTZ) PARTITION BY RANGE (ts);

-- Catalog-only parent index: instant, marked invalid until filled in
CREATE INDEX idx_events_id ON ONLY events (id);

-- One partition at a time, each build online
CREATE INDEX CONCURRENTLY idx_events_id_2026_01 ON events_2026_01 (id);
ALTER INDEX idx_events_id ATTACH PARTITION idx_events_id_2026_01;

What it assumes

Assumes the migration file is the only source of truth for whether a table is a partitioned parent. A table partitioned in an earlier, separate migration isn't recognized, so an unsafe CREATE INDEX on it can pass silently, while the same operation on a table partitioned in this file gets caught correctly.

What the CLI prints

migrationpilot analyze migration.sql
⚠ [MP092] WARNING (line 3)
  CREATE INDEX "idx_events_id" on partitioned table "events" recursively builds an index on every partition, holding locks across the whole hierarchy until the last one completes.

  Safe alternative:
  -- Build the parent index as a catalog-only placeholder, then fill it in
  -- one partition at a time so each build stays online:
  CREATE INDEX idx_events_id ON ONLY events (<columns>);
  
  -- For each partition:
  CREATE INDEX CONCURRENTLY idx_events_id_p1 ON events_p1 (<columns>);
  ALTER INDEX idx_events_id ATTACH PARTITION idx_events_id_p1;
  
  -- The parent index becomes valid once every partition index is attached.

  Why: PostgreSQL rejects CREATE INDEX CONCURRENTLY on a partitioned table outright, so the habit that keeps index builds online everywhere else fails here, and it fails at run time, after the migration has started. The plain form is accepted but builds an index on every partition in one statement, holding locks across the whole hierarchy until the last partition finishes; on a table partitioned by month over three years that is thirty-six index builds inside one lock window. Building the parent index ON ONLY first, then each partition concurrently, keeps every individual build online and lets you stop between partitions.
  Docs: https://migrationpilot.dev/rules/mp092

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.

Turning it off

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

-- migrationpilot-disable MP092
CREATE TABLE events (id BIGINT, ts TIMESTAMPTZ) PARTITION BY RANGE (ts);

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

.migrationpilotrc.yml
rules:
  MP092: false

# or keep it, and downgrade it
rules:
  MP092:
    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.

Run MP092 in the playground

Related rules

← All rulesThis rule's docRule source