unbatched-data-backfill

What triggers it

Fires on any UpdateStmt with no whereClause at all — an UPDATE table SET ... with no filtering condition of any kind.

What does not

Any UPDATE that has a WHERE clause is skipped outright — the rule only checks whether whereClause is present, not what it contains, so a WHERE that matches effectively every row (WHERE 1=1, WHERE id > 0) still passes through unflagged.

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 full-table UPDATE takes ROW EXCLUSIVE, which doesn't block reads but does conflict with other writers touching the same rows; the bigger cost is the single long-running transaction — massive WAL generation, table bloat, and replication lag while it holds a lock and an open transaction ID for the entire duration.

Why it matters

A full-table UPDATE locks every row, generates massive WAL, and can cause replication lag. Batch updates with WHERE and LIMIT to control impact.

Unsafe, and safe

UPDATE users SET status = 'active';

-- Batch in chunks
UPDATE users SET status = 'active'
  WHERE id IN (SELECT id FROM users WHERE status IS NULL LIMIT 1000);

Deploy and transaction boundaries

The batched-loop replacement commits each batch separately with a short pause between them, rather than running as one long transaction — there's no application-code deploy involved, just breaking the single UPDATE into many small, independently-committed ones.

What it assumes

The check is row-count agnostic: an UPDATE with no WHERE on a hundred-row table is flagged exactly the same as one on a hundred-million-row table, even though only the latter genuinely risks the WAL, bloat, and replication problems the rule describes.

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

⚠ [MP011] WARNING (line 1)
  UPDATE on "users" without a WHERE clause rewrites every row in a single transaction. For large tables, this generates massive WAL, can bloat the table, and holds ROW EXCLUSIVE lock for the entire duration.

  Safe alternative:
  -- Backfill in batches to reduce lock duration and WAL volume:
  DO $$
  DECLARE
    batch_size INT := 10000;
    rows_updated INT;
  BEGIN
    LOOP
      UPDATE users
      SET <column> = <value>
      WHERE <column> IS NULL  -- or your condition
      AND ctid IN (
        SELECT ctid FROM users
        WHERE <column> IS NULL
        LIMIT batch_size
        FOR UPDATE SKIP LOCKED
      );
      GET DIAGNOSTICS rows_updated = ROW_COUNT;
      EXIT WHEN rows_updated = 0;
      COMMIT;
      PERFORM pg_sleep(0.1);  -- Brief pause to let other queries through
    END LOOP;
  END $$;

  Why: A full-table UPDATE generates massive WAL, bloats the table, and holds a ROW EXCLUSIVE lock for the entire duration. On tables with millions of rows, this can take hours and cause replication lag, disk pressure, and degraded performance.
  Docs: https://migrationpilot.dev/rules/mp011

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 MP011
UPDATE users SET status = 'active';

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

rules:
  MP011: false

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

• MP003volatile-default-table-rewritevolatile defaults may need backfills
• MP007no-column-type-changetype changes require batched backfills
• MP010no-rename-columncolumn renames require data migration