large-table-ddl

What triggers it

Fires when production tableStats are available, ctx.lock.longHeld is true for the statement, and the table row count meets the 1,000,000-row threshold.

What does not

Returns immediately with no --database-url-sourced tableStats, on statements whose lock isn't flagged as long-held, and on tables under the 1,000,000-row threshold regardless of lock type.

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

The exact lock mode comes from the underlying statement (ACCESS EXCLUSIVE for most flagged DDL); what the rule adds is that duration scales with table size, and whether it blocks reads too — not just writes — depends on ctx.lock.blocksReads for that lock.

Why it matters

Operations that rewrite or scan large tables take proportionally longer, extending the lock duration. Production context from pg_class reveals actual table sizes.

Unsafe, and safe

-- users has 50M rows
ALTER TABLE users ALTER COLUMN bio TYPE text;

-- A build that does not hold its lock for the length of the table
-- keeps the cost proportional to the work, not to the row count.
CREATE INDEX CONCURRENTLY idx_users_last_seen ON users (last_seen_at);

What it assumes

The 1,000,000-row cutoff is a single fixed number with no notion of index count, row width, or disk type, so a 1M-row table of small integers and a 1M-row table of large JSONB blobs are treated identically even though their rewrite times differ enormously — and, like MP013, the rule says nothing without --database-url.

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

⚠ [MP014] WARNING (line 2)
  Long-held ACCESS EXCLUSIVE on a table with 50,000,000 rows (42.9 GB, 4 indexes). Lock duration will scale with table size, blocking ALL reads and writes.

  Safe alternative:
  -- For large tables, consider:
  -- 1. Set a lock_timeout to fail fast:
  SET lock_timeout = '5s';
  ALTER TABLE users ALTER COLUMN bio TYPE text
  RESET lock_timeout;
  
  -- 2. Run during maintenance windows
  -- 3. If this is an index creation, ensure CONCURRENTLY is used
  -- 4. For column additions with defaults, consider adding without default then backfilling

  Why: DDL operations on large tables take proportionally longer. Lock duration scales with row count and table size. What takes seconds on a small table can take minutes or hours on a table with millions of rows.
  Docs: https://migrationpilot.dev/rules/mp014

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 MP014
ALTER TABLE users ALTER COLUMN bio TYPE text;

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

rules:
  MP014: false

# or keep it, and downgrade it
rules:
  MP014:
    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-timeoutset lock_timeout before DDL
• MP020require-statement-timeoutset statement_timeout for long operations