Volatile column default forcing a table rewrite

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

What it catches

Adding a column with a constant default is instant on modern Postgres, so teams assume all defaults are. A volatile default must be evaluated per existing row, so Postgres rewrites the whole table under ACCESS EXCLUSIVE — reads and writes blocked for the duration.

Fires on

ALTER TABLE users ADD COLUMN uid uuid DEFAULT gen_random_uuid();

Do this instead

Add the column nullable (instant), backfill the volatile values in batches from a job, then enforce NOT NULL via a validated CHECK if needed. A non-volatile default (a constant, or now() — stable within the statement) stays metadata-only on Postgres 11+; only volatile defaults like gen_random_uuid() or random() force the rewrite.

SET lock_timeout = '5s';
ALTER TABLE users ADD COLUMN uid uuid;
-- backfill from a job, in batches:
--    UPDATE users SET uid = gen_random_uuid()
--    WHERE id IN (SELECT id FROM users WHERE uid IS NULL LIMIT 5000);
-- then enforce NOT NULL via a validated CHECK (see BV011)

Catch this before it ships

This rule runs in the hosted service on Startup and above — add --remote with a team token, or use the GitHub Action: npx bolvrk check migration.sql

Browse all 69 rules

← BV006 BV008 →