require-concurrent-index-creation

What triggers it

Any CREATE INDEX or CREATE UNIQUE INDEX statement whose concurrent flag is not set — the rule matches the IndexStmt parse node and checks that one field.

What does not

CREATE INDEX CONCURRENTLY. The rule returns as soon as it sees the concurrent flag, so the table, the index type and the column list never enter into it.

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

A plain index build holds ACCESS EXCLUSIVE on the table for the whole build, which conflicts with every other lock mode — readers and writers both wait.

Why it matters

Without CONCURRENTLY, PostgreSQL holds a SHARE lock on the table for the entire index build. Reads keep working; every INSERT, UPDATE and DELETE blocks until the build finishes. On a table with millions of rows that is minutes of writes queued behind one statement, and every connection waiting on them.

Unsafe, and safe

CREATE INDEX idx_users_email ON users (email);

SET lock_timeout = '5s';
DROP INDEX CONCURRENTLY IF EXISTS idx_users_email;
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

What it assumes

The rule fires on every table regardless of size, so an index on a small or empty table is flagged the same as one on a large table. Without --database-url it has no way to tell them apart.

What backs this rule

Every rule is a claim about PostgreSQL, so it carries what the claim rests on: a handbook chapter that cites the manual, the incidents that put it there, and the version it was last checked against.

What the CLI prints

✗ [MP001] CRITICAL (line 1)
  CREATE INDEX "idx_users_email" without CONCURRENTLY will lock all writes on "users" for the entire duration of index creation.

  Safe alternative:
  SET lock_timeout = '5s';
  -- Clear an invalid index left by an earlier failed attempt. Do not use
  -- IF NOT EXISTS here: it would skip the build and report success over it.
  DROP INDEX CONCURRENTLY IF EXISTS idx_users_email;
  CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
  -- If a UNIQUE or PRIMARY KEY constraint owns idx_users_email, the drop is refused.
  -- Rebuild in place instead: REINDEX INDEX CONCURRENTLY idx_users_email;

  Why: Without CONCURRENTLY, PostgreSQL holds a SHARE lock on the table for the entire index build. Reads keep working; every INSERT, UPDATE and DELETE blocks until the build finishes. On a table with millions of rows that is minutes of writes queued behind one statement, and every connection waiting on them.
  Docs: https://migrationpilot.dev/rules/mp001

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 MP001
CREATE INDEX idx_users_email ON users (email);

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

rules:
  MP001: false

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

• MP009require-drop-index-concurrentlyDROP INDEX should also use CONCURRENTLY
• MP023require-if-not-existsindexes should use IF NOT EXISTS for idempotency
• MP042require-index-nameindexes should have explicit names