Columns
ADD COLUMN ... DEFAULT on PostgreSQL < 11
PostgreSQL 10 and earlier
Lock mode ACCESS EXCLUSIVE
Risk HIGH
Blocks reads, writes, other DDL
pgfence rule
add-column-default-pre-pg11 ALTER TABLE orders ADD COLUMN currency text DEFAULT 'usd';What this does
Fast-default storage for ADD COLUMN ... DEFAULT was introduced in PostgreSQL 11. On any earlier version, even a constant default rewrites the entire table under ACCESS EXCLUSIVE, the same cost as a volatile default on modern Postgres.
Safe rewrite
Add the column without a default, backfill in batches, then set the default
ALTER TABLE orders ADD COLUMN IF NOT EXISTS currency text;
-- Backfill out-of-band in batches, then: ALTER TABLE orders ALTER COLUMN currency SET DEFAULT 'usd';
-- Or upgrade to PostgreSQL 11+, where this pattern is unnecessary.