R001 · default CAUTION

ADD COLUMN NOT NULL without DEFAULT

Adding a NOT NULL column without a DEFAULT requires Postgres to rewrite the entire table under AccessExclusiveLock.

Why this is a problem

PG 11+ optimizes ADD COLUMN with a constant DEFAULT to metadata-only. Without DEFAULT (or with a volatile DEFAULT), Postgres rewrites every row. On a 240M-row table, this is multi-minute downtime — and it cascades because every subsequent query queues behind the FIFO lock.

Don't do this

ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL;

Do this instead

-- 1. Add nullable (instant)
ALTER TABLE orders ADD COLUMN status VARCHAR(20);

-- 2. Backfill in batches
UPDATE orders SET status = 'pending' WHERE status IS NULL AND id BETWEEN 0 AND 100000;

-- 3. NOT VALID + VALIDATE (low-lock)
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null
  CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_status_not_null;

References

• PostgreSQL ALTER TABLE →
• Strong Migrations: Adding NOT NULL →