Skip to main content

PG.L2.TYPE_CHANGE_REWRITE — A type change can rewrite the entire table

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

Changing a column's type rewrites the whole table — unless the new type is binary-coercible from the old one, in which case it is a metadata change and finishes instantly. Two statements that look the same in a migration can therefore differ by minutes of downtime, and which one you have depends on the pair of types.

A USING clause forces the rewrite unconditionally, whatever the types are.

This is the rule most likely to have looked instant in development, where the table had forty rows.

Why the confidence is heuristic​

The rule reads a migration, not a catalog. It knows the type pair the statement names and it applies PostgreSQL's coercibility matrix to it — but a domain, an extension type or a column whose current type the migration never states can leave it without a definite answer. Where it cannot decide, it says so: an undetermined finding with a named reason, not a guess in either direction.

A numeric target with a precision is one of those. Growing the precision at the same scale keeps every stored value and rewrites nothing, while changing the scale rewrites the table, and the statement names only the target. So $table->decimal('total', 12, 2)->change() is undetermined: compare it with the column's current precision and scale before you deploy. A timestamp, timestamptz or time target with a precision is the same case: a larger precision of the same type rewrites nothing and a smaller one rewrites the table. For a timestamp or timestamptz target the finding also asks for the session's TimeZone: since PostgreSQL 12 a change between the two is metadata-only when the session that runs the migration is on UTC, and a rewrite otherwise.

Laravel's ->change() is the other. It names the column's type on every call, so $table->bigInteger('views')->nullable()->change() compiles to TYPE bigint beside DROP NOT NULL whether the column was an integer or already a bigint. Restating the type a column already has rewrites nothing, and turning an integer into a bigint rewrites the table. The rule recognizes that form by the nullability clause for the same column in the same statement, reports it as undetermined, and attaches no plan. A TYPE clause on its own, or one with USING, is still reported as the rewrite it is.

deterministic would have been a stronger word than the evidence supports.

Flagged​

DB::statement('ALTER TABLE orders ALTER COLUMN total_cents TYPE bigint');

Preferred​

// Add a widened column, backfill it in batches, then swap in a later migration —
// never rewrite the table in place under a lock:
Schema::table('orders', fn (Blueprint $table) => $table->bigInteger('total_cents_new')->nullable());

The full pattern is four deploys, and each one is boring on its own: add the column · backfill in bounded batches while both columns are written · switch the readers · drop the old column behind the destructive opt-in. Nothing in that sequence holds a lock for longer than a single batch.

The pairs that are free​

varchar(n) → varchar(m) with m > n, or → text, are binary-coercible and do not rewrite. int → bigint is not: the on-disk width differs, so every row is rewritten. That specific pair is the common surprise, because the change reads as "just make it bigger".

  • PG.L4.TYPE_NARROWING covers the other direction, where the risk is data loss rather than lock time.
  • PG.L3.MISSING_LOCK_TIMEOUT bounds how long a statement like this may wait for its lock — which is worth setting even when the rewrite itself is unavoidable.

Sources​

  • PostgreSQL 18 — ALTER TABLE — changing a column's type rewrites the table unless the new type is binary-coercible from the old one; a USING clause forces the rewrite unconditionally

The fix material this rule carries​

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

  • rewrite_avoidance — Reach the same end state without the operation that rewrites the whole table.

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.