warn-backfill-no-batching

What triggers it

A DeleteStmt with no whereClause at all — a bare DELETE FROM <table> that removes every row.

What does not

Any DELETE that has a WHERE clause, no matter how broad — the check is only if (del.whereClause) return null, so it never evaluates whether that clause is actually selective.

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 DELETE holds ROW EXCLUSIVE for the whole run, which blocks other writers and any DDL wanting a conflicting lock, and it generates a WAL entry per deleted row rather than the near-instant metadata-only operation TRUNCATE performs.

Why it matters

A full-table DELETE generates a WAL entry for every row, bloats the table with dead tuples, and holds a ROW EXCLUSIVE lock for the entire duration. On tables with millions of rows, this can take hours, cause replication lag, and exhaust disk space.

Unsafe, and safe

DELETE FROM users;

-- For full table delete, use TRUNCATE (much faster, minimal WAL):
TRUNCATE users;

-- For partial deletes, batch with WHERE + LIMIT:
DELETE FROM users WHERE ctid IN (
  SELECT ctid FROM users LIMIT 10000
);

What it assumes

The rule assumes deleting every row was accidental or should have been batched; on a genuinely small table, or when the whole table really is meant to go, a single unbatched DELETE — or better, TRUNCATE — can be entirely appropriate, and the warning is just noise.

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

⚠ [MP067] WARNING (line 1)
  DELETE on "users" without a WHERE clause removes every row in a single transaction. Use TRUNCATE for full deletes or batch with WHERE + LIMIT.

  Safe alternative:
  -- For full table delete, use TRUNCATE (much faster, minimal WAL):
  TRUNCATE users;
  
  -- For partial deletes, batch to reduce lock duration:
  DELETE FROM users WHERE ctid IN (
    SELECT ctid FROM users LIMIT 10000
  );

  Why: A full-table DELETE generates a WAL entry for every row, bloats the table with dead tuples, and holds a ROW EXCLUSIVE lock for the entire duration. On tables with millions of rows, this can take hours, cause replication lag, and exhaust disk space. Use batched deletes or TRUNCATE instead.
  Docs: https://migrationpilot.dev/rules/mp067

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 MP067
DELETE FROM users;

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

rules:
  MP067: false

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