warn-partitioned-parent-fanout

What triggers it

ctx.tableFacts with relKind of p and a partitionCount of 20 or more, plus a statement that fans out: an AlterTableStmt, reported as ALTER TABLE, or a non-concurrent IndexStmt, reported as CREATE INDEX. The lock named in the message is whatever ctx.lock.lockType classified for that statement, not a value this rule decides.

What does not

Every run without --database-url — nothing in the file says how many partitions exist, and that count is the entire finding (requiresDatabaseUrl is set). Ordinary tables, where relKind is not p, and parents with fewer than 20 partitions. Parents an extension manages: isHypertable or isPartmanParent short-circuits to null so MP105 and MP108 can say something more specific. CREATE INDEX CONCURRENTLY, for which fanoutOperation() returns null, since PostgreSQL rejects concurrent builds on a partitioned parent anyway.

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 for the ALTER TABLE case, taken on the parent and on every partition and held until the statement commits; a plain CREATE INDEX takes SHARE with the same fan-out. Two consequences follow from the count rather than the statement: the blocking window is set by the slowest partition, and the lock table needs an entry per partition, so a wide enough parent can exhaust max_locks_per_transaction and fail outright.

Why it matters

DDL on a partitioned table recurses: PostgreSQL locks the parent and every partition, and holds all of those locks until the statement commits. The blocking window is therefore set by the slowest partition, not by the statement, and a lock_timeout only helps if it fires before the queue behind the parent lock has stalled everything else. The lock count scales with the partition count too, so on a table with a partition per day and a couple of years of history a single ALTER can exhaust max_locks_per_transaction and fail outright.

Unsafe, and safe

-- events is partitioned by day, 365 partitions live
ALTER TABLE events ADD COLUMN note text;

-- Confirm the fan-out and that the lock table can hold it before
-- writing the DDL:
SELECT count(*) AS partitions FROM pg_inherits WHERE inhparent = 'events'::regclass;
SHOW max_locks_per_transaction;

Deploy and transaction boundaries

The per-partition alternative is a sequence: CREATE INDEX CONCURRENTLY on each partition, each outside a transaction block, then CREATE INDEX ... ON ONLY the parent, then one ALTER INDEX ... ATTACH PARTITION per child. The parent index stays invalid until every partition has a matching index attached.

What it assumes

20 partitions is a flat cutoff with no relationship to the server's max_locks_per_transaction, to partition sizes, or to how busy any of them are — 21 empty daily partitions trip it and 19 partitions of a terabyte each do not. partitionCount counts direct children from pg_inherits, so a sub-partitioned tree is undercounted at the top level. The rule does not separate a metadata-only ALTER from one that rewrites every partition, and it never reads relation.inh, so ALTER TABLE ONLY parent — which does not recurse — is reported exactly like the recursing form.

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

What the CLI prints

⚠ [MP110] WARNING (line 2)
  ALTER TABLE on partitioned table "events", which has 365 partitions. PostgreSQL takes the ACCESS EXCLUSIVE lock on the parent and on all 365 partitions and holds them until the statement commits, so the real blocking window is set by the slowest partition, not by the one line in this file.

  Safe alternative:
  -- Confirm the fan-out before running it:
  SELECT count(*) AS partitions FROM pg_inherits WHERE inhparent = 'events'::regclass;
  
  -- Make sure the lock table can hold one entry per partition:
  SHOW max_locks_per_transaction;
  
  -- Fail fast instead of stalling every query queued behind the parent lock:
  SET lock_timeout = '5s';
  ALTER TABLE events ADD COLUMN note text
  RESET lock_timeout;
  
  -- For an index, build on each partition first, then attach them to a parent
  -- index created ONLY, which keeps each blocking window to a single partition:
  --   CREATE INDEX CONCURRENTLY idx_part_1 ON events_p1 (col);
  --   CREATE INDEX idx_parent ON ONLY events (col);
  --   ALTER INDEX idx_parent ATTACH PARTITION idx_part_1;

  Why: DDL on a partitioned table recurses. PostgreSQL locks the parent and each partition, and it holds all of those locks until the statement commits, so the blocking window is set by the slowest partition and the number of locks by the partition count. Two things follow: the statement can exhaust max_locks_per_transaction on a table with many partitions, and a lock_timeout only helps if it is short enough to fire before the queue behind the parent lock has stalled every query.
  Docs: https://migrationpilot.dev/rules/mp110

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 MP110
ALTER TABLE events ADD COLUMN note text;

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

rules:
  MP110: false

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

• MP049require-partition-key-in-pkpartition key requirements
• MP072warn-partition-default-scanATTACH PARTITION scans the DEFAULT partition
• MP105warn-timescale-hypertable-ddlthe same fan-out for hypertables
• MP107warn-citus-distributed-ddlthe same fan-out across worker nodes