require-drop-index-concurrently

What triggers it

Fires on a DropStmt whose removeType is OBJECT_INDEX and whose concurrent flag is not set — DROP INDEX (optionally with IF EXISTS) written without CONCURRENTLY.

What does not

DROP INDEX CONCURRENTLY returns immediately once the concurrent flag is seen. Every other DropStmt variant — DROP TABLE, DROP TYPE, DROP TRIGGER, and so on — is skipped too, since only OBJECT_INDEX is checked.

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 DROP INDEX takes ACCESS EXCLUSIVE on the table for the duration of the drop, blocking all reads and writes; CONCURRENTLY avoids that with a multi-phase drop that only briefly locks the table.

Why it matters

DROP INDEX acquires an ACCESS EXCLUSIVE lock on the table. DROP INDEX CONCURRENTLY only acquires SHARE UPDATE EXCLUSIVE, allowing concurrent reads and writes during index removal.

Unsafe, and safe

DROP INDEX idx_users_email;

DROP INDEX CONCURRENTLY idx_users_email;

What it assumes

The rule fires the same way whether the index is on a tiny lookup table or a busy hot table — it doesn't check size or query volume, so a drop that's genuinely low-risk is flagged identically to a dangerous one.

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

⚠ [MP009] WARNING (line 1)
  DROP INDEX "idx_users_email" without CONCURRENTLY acquires ACCESS EXCLUSIVE lock, blocking all reads and writes on the table.

  Safe alternative:
  DROP INDEX CONCURRENTLY idx_users_email

  Why: DROP INDEX without CONCURRENTLY takes an ACCESS EXCLUSIVE lock on the table, blocking all reads and writes until the index is fully dropped. DROP INDEX CONCURRENTLY avoids this by using a multi-phase approach.
  Docs: https://migrationpilot.dev/rules/mp009

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 MP009
DROP INDEX idx_users_email;

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

rules:
  MP009: false

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

• MP001require-concurrent-index-creationCREATE INDEX should also use CONCURRENTLY
• MP025ban-concurrent-in-transactionCONCURRENTLY cannot run in transactions
• MP004require-lock-timeoutset lock_timeout before DDL