R002 · default CAUTION

ALTER COLUMN TYPE (incompatible)

ALTER COLUMN TYPE that requires re-encoding rows holds an AccessExclusiveLock for the duration of the rewrite.

Why this is a problem

Compatible changes (e.g., VARCHAR(N) → VARCHAR(M) where M ≥ N, or VARCHAR → TEXT) are metadata-only. Anything with a USING clause or a different storage representation (INTEGER → BIGINT, BYTEA → TEXT) requires a full table rewrite.

Don't do this

ALTER TABLE orders ALTER COLUMN user_id TYPE BIGINT;

Do this instead

-- Add new column nullable
ALTER TABLE orders ADD COLUMN user_id_new BIGINT;
-- Backfill in batches
UPDATE orders SET user_id_new = user_id WHERE user_id_new IS NULL AND id BETWEEN 0 AND 100000;
-- Switch reads, then drop + rename
BEGIN;
  ALTER TABLE orders DROP COLUMN user_id;
  ALTER TABLE orders RENAME COLUMN user_id_new TO user_id;
COMMIT;

References

• PG ALTER COLUMN TYPE →