no-exclusive-lock-high-connections

What triggers it

Fires when production context includes activeConnections at or above the 20-connection threshold and the statement’s computed lock type is exactly ACCESS EXCLUSIVE — covering AlterTableStmt, IndexStmt, DropStmt, and RenameStmt.

What does not

Returns immediately when activeConnections was never supplied or is under the 20-connection threshold, when the lock type isn’t ACCESS EXCLUSIVE, and on plain CreateStmt (CREATE TABLE) — a brand-new table has no existing connections referencing it.

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

ACCESS EXCLUSIVE blocks every reader and writer; with many active connections already open, each one queues behind the lock the instant it's acquired, so a lock that's a non-issue with five connections becomes a cascading pile-up with fifty.

Why it matters

When many connections are active on a table, acquiring ACCESS EXCLUSIVE causes all of them to queue up, creating a cascade of timeouts and connection pool exhaustion.

Unsafe, and safe

-- 200 connections are active against users right now
ALTER TABLE users ALTER COLUMN email TYPE varchar(320);

-- An operation that never takes ACCESS EXCLUSIVE has no queue to
-- form behind it, however many connections are open.
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

What it assumes

The 20-connection threshold is a single fixed number, not scaled to the database's configured max_connections or the table's actual traffic pattern — twenty idle connections and twenty mid-query connections are treated the same, and the rule has nothing to say without --database-url supplying activeConnections.

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

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

⚠ [MP019] WARNING (line 2)
  ACCESS EXCLUSIVE lock on "users" while 200 active connections exist. All 200 connections will queue, causing cascading timeouts.

  Safe alternative:
  -- Run during a low-traffic window and use a short lock_timeout:
  SET lock_timeout = '3s';
  ALTER TABLE users ALTER COLUMN email TYPE varchar(320)
  RESET lock_timeout;
  
  -- If lock acquisition fails, retry with exponential backoff.
  -- Consider: is there a non-locking alternative? (e.g., CONCURRENTLY for indexes)

  Why: Taking an ACCESS EXCLUSIVE lock when many connections are active means all those connections will queue waiting for the lock. This causes cascading timeouts, connection pool exhaustion, and can take down dependent services.
  Docs: https://migrationpilot.dev/rules/mp019

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 MP019
ALTER TABLE users ALTER COLUMN email TYPE varchar(320);

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

rules:
  MP019: false

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

• MP013high-traffic-table-ddlDDL on high-traffic tables
• MP004require-lock-timeoutalways set lock_timeout before DDL
• MP014large-table-ddllarge tables amplify lock impact