Which type changes are free

Can the bytes stay where they are?

PostgreSQL skips the rewrite when the new type is binary-coercible from the old one — when the bytes already on disk are a valid representation of the new type. Nothing has to be read, so nothing has to be written.

varchar(32) to varchar(64) is free: the stored bytes are unchanged and the only thing that moved is the constraint on new values. varchar(n) to text is free for the same reason.

int4 to int8 is not free. A four-byte integer is not a valid eight-byte one, so every row is rewritten — and every index on that column is rebuilt with it.

ALTER TABLE orders ALTER COLUMN note TYPE varchar(128);

ALTER TABLE orders ALTER COLUMN customer_id TYPE integer;

Two widenings

Both of these make a column able to hold more than it could before. Both are one statement. Neither loses data.

Direction matters

Growing a varchar limit is free. Shrinking it is not: every existing value has to be checked against the new limit, and any that do not fit make the statement fail. Same statement, opposite cost, decided entirely by which way you went.

The same asymmetry applies to numeric. Increasing the precision is free; changing the scale is not.

What a rewrite does downstream

A rewrite writes the table twice: once into the new file and once into the write-ahead log, because the change has to be crash-safe and replayable. That second copy is the one that hurts somebody other than you.

Every replica has to replay those bytes. A five-gigabyte table rewritten in six minutes is ten gigabytes of WAL, and a replica that was keeping up fine at a hundred transactions a second is now an hour behind. Read traffic pointed at it is serving an hour-old view of the world.

The migration finishing is not the end of the incident. Replication lag outlives it, and nothing on the primary tells you so.