high-traffic-table-ddl

What triggers it

Fires when production context includes affectedQueries for the target table, the statement is DDL with a lock stronger than ACCESS SHARE, and the summed calls across all affected queries meets the 10,000-call threshold.

What does not

Returns immediately with no --database-url-sourced affectedQueries, on non-DDL statements, on DDL that only takes ACCESS SHARE, and whenever the summed query-call count across affected queries is under the 10,000 threshold.

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 specific lock varies by statement — anything stronger than ACCESS SHARE qualifies — but the danger isn't the lock mode alone: even a brief lock causes a pile-up when thousands of queries per second are already hitting the table, since each one queues behind the DDL.

Why it matters

Acquiring locks on heavily-queried tables affects more concurrent operations. Production context from pg_stat_statements reveals the real traffic impact.

Unsafe, and safe

-- pg_stat_statements shows ~50,000 calls/hour against users
ALTER TABLE users ADD COLUMN last_seen_at timestamptz;

-- See what is actually hitting the table before you schedule the DDL,
-- then take the lock in a window where this traffic is quiet.
SELECT calls, mean_exec_time, query
FROM pg_stat_statements
WHERE query ILIKE '%users%'
ORDER BY calls DESC
LIMIT 10;

What it assumes

The 10,000-call threshold is a fixed constant applied to whatever window pg_stat_statements happens to cover, so it doesn't distinguish 10,000 calls over a minute from 10,000 over a week — and the rule is silent entirely without --database-url supplying affectedQueries.

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

⚠ [MP013] WARNING (line 2)
  DDL acquires ACCESS EXCLUSIVE lock on a table with 50,000 queries from api. Top query: "SELECT * FROM users WHERE id = $1..." (50,000 calls, 0.8ms avg).

  Safe alternative:
  -- Set a short lock_timeout to fail fast instead of blocking queries:
  SET lock_timeout = '3s';
  ALTER TABLE users ADD COLUMN last_seen_at timestamptz
  RESET lock_timeout;
  
  -- Consider running during low-traffic hours.
  -- If lock acquisition fails, retry with exponential backoff.

  Why: Running DDL on tables with high query volume amplifies the blast radius. Even brief locks cause significant query queuing when thousands of queries per second hit the table, leading to cascading timeouts across dependent services.
  Docs: https://migrationpilot.dev/rules/mp013

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 MP013
ALTER TABLE users ADD COLUMN last_seen_at timestamptz;

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

rules:
  MP013: false

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

• MP014large-table-ddlDDL on large tables has similar risks
• MP019no-exclusive-lock-high-connectionslock contention with many connections
• MP004require-lock-timeoutalways set lock_timeout before DDL