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.

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.