Skip to main content

GEN.L3.DML_ON_SCHEMA_CHANGED_TABLE — the backfill runs while the schema lock is still held

  • Category: safety
  • Level: 3
  • Confidence: deterministic
  • Downtime class: blocking
  • Stability: stable
  • Suites: lint
  • Applies to: PostgreSQL 18 and MySQL 8.4

Adding a column and backfilling it in the same migration reads as one job, and it is — but the lock does not know that. The schema change takes its lock; the transaction wrapping the migration holds that lock until the whole migration commits; and the backfill runs inside it.

So a one-millisecond ADD COLUMN blocks the table for however long the UPDATE over every row takes. The expensive half inherits the cheap half's lock, and the table is unavailable for both.

Flagged​

Schema::table('orders', fn (Blueprint $table) => $table->string('status')->nullable());
DB::table('orders')->update(['status' => 'new']);

Preferred​

// Migration 1 — schema only:
Schema::table('orders', fn (Blueprint $table) => $table->string('status')->nullable());
// Migration 2 (or a queued job), after the first has committed — backfill in batches:
// DB::table('orders')->whereNull('status')->limit(1000)->update(['status' => 'new']);

Two migrations, and the second one can take as long as it likes: by the time it runs, the schema lock is long gone and each batch takes only its own row locks.

Why not a DEFAULT instead​

On PostgreSQL 11+ and MySQL 8.0+, adding a column with a default is a metadata-only operation — the server records the default and applies it on read rather than rewriting the table. Where that fits, it removes the need for a backfill entirely:

Schema::table('orders', fn (Blueprint $table) => $table->string('status')->default('new'));

It does not fit when the value differs per row, which is when the two-migration split is the answer.

A queued job is a legitimate third option​

A backfill that will take hours does not belong in a migration at all. Ship the schema change, then run the fill as a job that can be paused, resumed and observed — and let the deploy finish in the meantime.

  • PG.L3.RISKY_OPS_SINGLE_TX — the same lock-duration arithmetic, for two schema changes rather than a schema change and a write.
  • GEN.L1.DML_WITHOUT_WHERE — the batching argument, in the rule that is only about the write.

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.