Columns
ADD COLUMN ... DEFAULT <volatile expression>
Lock mode ACCESS EXCLUSIVE
Risk HIGH
Blocks reads, writes, other DDL
pgfence rule
add-column-non-constant-default ALTER TABLE orders ADD COLUMN external_id text DEFAULT gen_random_uuid();What this does
A default that is not a plain constant (a function call like gen_random_uuid(), a subquery, most non-constant expressions) forces Postgres to evaluate and write the default into every existing row, a full table rewrite held under ACCESS EXCLUSIVE for the entire time.
Safe rewrite
Add the column without a default, backfill in batches out-of-band, then set the default for future rows
ALTER TABLE orders ADD COLUMN IF NOT EXISTS external_id text;
-- Backfill out-of-band in batches (repeat until 0 rows updated):
-- WITH batch AS (
-- SELECT ctid FROM orders WHERE external_id IS NULL LIMIT 1000 FOR UPDATE SKIP LOCKED
-- )
-- UPDATE orders o SET external_id = gen_random_uuid() FROM batch WHERE o.ctid = batch.ctid;
ALTER TABLE orders ALTER COLUMN external_id SET DEFAULT gen_random_uuid();