Does ALTER COLUMN TYPE rewrite the whole table?

Column type change forcing a table rewrite

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

What happens

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.

Why it is dangerous on a populated table

It completes, but the time it holds its lock or rewrites rows scales with the table, and on a busy one that is long enough for traffic to queue.

Measured

Both forms ran against the same table (20M rows, 1.7 GB) on Postgres 18.6, with one session inserting a row and another reading one row every 50 ms. Worst wait is the slowest of those probe calls while the statement ran. This run is from Sep 16, 2026; the whole benchmark has every scenario and how to run it.

	Statement	Time	Worst write wait	Worst read wait	Lock
Fires	ALTER COLUMN customer_id TYPE bigint (rewrite)	11.4 s	11.2 s	11.2 s	AccessExclusiveLock, ShareLock, rewrite
Safe	expand step: ADD COLUMN customer_id_new bigint	3 ms	2 ms	2 ms	AccessExclusiveLock

Fires on

ALTER TABLE orders ALTER COLUMN id TYPE bigint;

The safe pattern

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

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (1)

int to bigint

ALTER TABLE orders ALTER COLUMN id TYPE bigint;

Stays silent (1)

varchar to text with snapshot

ALTER TABLE orders ALTER COLUMN region TYPE text;

How to check locally

Catch this before it ships

This rule runs locally in the free CLI, or with the full corpus through the hosted service. No install, nothing leaves your machine:

npx bolvrk check migration.sql

Browse all 135 rules

← BV003 BV005 →