Column type change forcing a table rewrite

Warning — this works, but blocks traffic or rewrites data at scale.

What it catches

ALTER COLUMN TYPE takes ACCESS EXCLUSIVE and, unless the change is binary-coercible, rewrites every row. The table is completely unavailable — reads included — for the duration.

Fires on

ALTER TABLE orders ALTER COLUMN id TYPE bigint;

Do this instead

For a widening change on a busy table, expand/contract instead of retyping in place: add a new column, keep it in sync with a trigger, backfill in batches, then swap names and drop the old column across later releases. Only binary-coercible changes (e.g. varchar(n) to text) skip the rewrite and are safe done directly with a lock_timeout guard.

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN id_new bigint;
-- keep id_new in sync with a trigger, backfill in batches from a job,
-- then swap column names and drop the old one in later migrations

Catch this before it ships

This rule runs locally in the free CLI — or with the full corpus through the hosted service: npx bolvrk check migration.sql

Browse all 69 rules

← BV003 BV005 →