ADD COLUMN: instant in PG 11+ vs rewrite in older Postgres

A team deploys ALTER TABLE orders ADD COLUMN status TEXT NOT NULL DEFAULT 'pending' on a 50M-row table. On Postgres 10 the database freezes for 15 minutes. On Postgres 11+, the same statement finishes in milliseconds. Same SQL, completely different outcome.

How Postgres 11 made ADD COLUMN instant

Before PG 11, ALTER TABLE t ADD COLUMN c TEXT DEFAULT 'x' rewrote every row to materialise the default value. On a 100M-row table that meant minutes holding AccessExclusiveLock — every reader and writer blocked for the duration.

Since PG 11, when the default is non-volatile (a literal string, number, boolean, or stable expression), Postgres stores the default in pg_attribute system catalog:

• pg_attribute.atthasmissing = true
• pg_attribute.attmissingval = 'x'

Existing rows keep their original physical storage — the column is not written. When you SELECT an old row, Postgres reads attmissingval and synthesises the value at read time. New rows inserted after the migration store the column normally. The lock hold time drops from minutes to milliseconds.

Volatile defaults still rewrite

DEFAULT clock_timestamp(), DEFAULT random(), DEFAULT gen_random_uuid() — any volatile function — cannot be stored as a single missing value because each row needs a different result. Postgres must materialise the default per-row, which means a full table rewrite under AccessExclusiveLock.

Fix: add the column without a default (instant), then backfill existing rows with an UPDATE in batches, then set the default for future inserts with ALTER TABLE t ALTER COLUMN c SET DEFAULT gen_random_uuid().

Adding NOT NULL safely on a large table

ADD COLUMN c TEXT NOT NULL DEFAULT 'x' is fine on PG 11+ for a constant default — instant, no rewrite. The danger is adding NOT NULL to an existing nullable column that may have NULLs:

-- WRONG on a large table: scans every row under AccessExclusiveLock
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;

The safe multi-step pattern (covered in depth in lesson 04):

1. Add the column without NOT NULL: instant.
2. Backfill existing NULLs in small batches.
3. Add CHECK (col IS NOT NULL) NOT VALID — enforces for new rows only, no table scan.
4. VALIDATE CONSTRAINT — scans under a lighter lock that does not block writes.
5. ALTER COLUMN SET NOT NULL — fast because the constraint already proves validity.