add-column-volatile-default
errorADD COLUMN with a volatile default
Why it's unsafe
ADD COLUMN with a volatile DEFAULT (e.g. random(), gen_random_uuid()) rewrites every existing row under an ACCESS EXCLUSIVE lock.
Safe rewrite
Add the column nullable with no default, backfill existing rows in batches, then ALTER COLUMN ... SET DEFAULT for new rows (add NOT NULL via the safe two-step if needed).
Example
Try it in the playground →Unsafe
ALTER TABLE users ADD COLUMN token uuid DEFAULT gen_random_uuid();Safe
ALTER TABLE users ADD COLUMN token uuid;
-- backfill in batches, then: ALTER TABLE users ALTER COLUMN token SET DEFAULT gen_random_uuid();