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.

The statistics go with every type change​

ALTER COLUMN … TYPE removes the column's planner statistics, also when it rewrites nothing, and the statement does not count toward the number of changed rows at which autovacuum analyzes the table again. Until an ANALYZE runs, PostgreSQL plans every query on that column from default estimates. So every finding of this rule ends by asking for an ANALYZE on the table right after the statement, and the staged plan it attaches analyzes the table once the backfill has finished and the new column has its indexes, before a release reads it.

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. Where an earlier migration of the same run created or retyped the column, the run knows the type it has, and the rule judges the pair: restating that type is silent, and changing it is reported as the change it is. A run over every migration, as CI runs them on an empty database, knows every column that way. The run knows the size a column was written with as well: restating it is silent, a longer varchar, more precision at the same scale or a more precise time type is free, and a shorter or less precise one rewrites the table. Where the run does not say, the rule reports the form 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, in a deploy whose migration analyzes the table first · 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. They still remove the column's statistics: PG.L7.TYPE_CHANGE_DROPS_STATISTICS reports such a change when no ANALYZE of the table follows it in the migration. 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.L7.TYPE_CHANGE_DROPS_STATISTICS reports the statistics a free type change removes, where this rule has nothing to say.
  • 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; and every type change removes the column's statistics, with an ANALYZE recommended afterwards

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.