Skip to main content

PG.L7.UNBATCHED_MASS_DML — A backfill that nothing bounds

  • Category: safety
  • Level: 7
  • Confidence: heuristic
  • Downtime class: blocking
  • Stability: stable
  • Suites: lint
  • Applies to: PostgreSQL 18 and newer

A data write in a migration that runs as one statement over however many rows the table happens to hold — the backfill every project writes once, with a predicate and nothing bounding it.

Reported:

DB::table('orders')->where('status', 'old')->update(['status' => 'new']);

Not reported:

// PostgreSQL has no LIMIT on UPDATE, so Laravel compiles this into a ctid subselect —
// `where "ctid" in (select … limit 1000)` — which is exactly the bound the rule reads:
DB::table('orders')->where('status', 'old')->limit(1000)->update(['status' => 'new']);
// …or walk a key range, which bounds it just as well:
// DB::table('orders')->whereBetween('id', [$from, $to])->update(['status' => 'new']);

PostgreSQL cannot write LIMIT on an UPDATE, so a chunk looks like this instead​

This is what makes the rule's signal different from its MySQL sibling's while the hazard is identical. Read out of Laravel's own PostgresGrammar rather than guessed at:

// compileUpdateWithJoinsOrLimit()
return "update {$table} set {$columns} where {$this->wrap('ctid')} in ({$selectSql})";

so ->limit(1000) compiles to

update "orders" set "status" = ? where "ctid" in (select "orders"."ctid" from "orders" where … limit 1000)

The rule matches that as a shape, not by looking for the word LIMIT. A keyword search would also accept UPDATE … WHERE id IN (SELECT id FROM other LIMIT 10), where the limit bounds the subquery and says nothing at all about how many rows are written.

It is a heuristic, and the finding says so​

What the rule can see is that the statement carries no bound. What it cannot see is how many rows that means — a hundred rows and a hundred million produce identical SQL, and the difference between a non-event and an outage is entirely in the table. Reading the table's size would mean querying the server, which the lint suite deliberately does not do.

That is also why it sits at level 7: the band a project turns on when it wants estimates as well as facts. Lower, and an unbounded write on a ten-row lookup table would fail a level-2 gate.

Where the statistics arm it​

The deploy suite is allowed to look at the server, and this rule declares the backfill operation class so that it can. A row estimate does not create the finding and cannot remove it — it raises the severity of the finding already made, records the threshold that fired, and keeps the base severity so a baseline written today still matches tomorrow.

That pairing is the whole design: the lint suite says this write is unbounded, honestly and without guessing, and the deploy suite says and this table has 480 million rows.

What silences it​

  • a bound the engine can express — the ctid form above;
  • a key range: ->whereBetween('id', [$from, $to]). Accepting this is a deliberate leniency, because a key-range walk is exactly the chunking pattern the finding recommends, and a linter that flags its own advice is one a team switches off. It errs toward a false negative — a BETWEEN on a non-key column bounds nothing, and the rule will not say so;
  • a write against a table this same migration created: there are no accumulated rows to sweep.

Where the boundary with the other write rules runs​

Three rules can look at one data statement and they ask three different questions:

ruleasks
GEN.L1.DML_WITHOUT_WHEREcorrectness — there is no predicate at all
GEN.L3.DML_ON_SCHEMA_CHANGED_TABLEatomicity — the write shares a schema change's lock window
this rulesize — the predicate is there and the write is still unbounded

The first is a genuine carve-out: a statement with no WHERE is not reported here, because its problem is not that it is unbatched. The second is not — a write can be both mixed with DDL and unbounded, and the two findings sit at different levels and say different things.

Suppressing it​

Level 7 is opt-in. Within it, the ordinary routes apply: a baseline entry, an ignore rule in config/sqlens.php, or lowering the level below 7. Bounding the write is better than suppressing it — and moving a large backfill into a queued job is better than either, because then the deploy is not waiting on it at all.

Sources​

The fix material this rule carries​

A finding from this rule carries machine-readable fix material, using this sequence:

  • batched_backfill — Move the data in bounded batches from a job rather than in one migration statement.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.