Adding Columns and Constraints Without Downtime

The one thing to understand first

The previous article showed why strong locks held for a long time are dangerous. The art of online schema change is converting an operation that scans or rewrites the table into one that only touches the catalog — or splitting it into a fast lock plus a slow, lock-light scan.

Every safe schema change follows the same shape: make the metadata change instantly under a brief lock, then do the heavy data work (backfill or validation) separately without blocking reads and writes. Constant defaults, NOT VALID constraints, and validated-CHECK-then-SET NOT NULL are all instances of that one idea.

Adding a column with a default

Modern PostgreSQL stores a constant default as catalog metadata (a "missing value") rather than rewriting every row. So this is fast even on huge tables:

ALTER TABLE orders ADD COLUMN status text DEFAULT 'pending';
-- Metadata only: existing rows report the default virtually.

The trap is a volatile default, which must be evaluated per row and forces a rewrite:

-- DON'T: rewrites the whole table under a strong lock
ALTER TABLE orders ADD COLUMN token uuid DEFAULT gen_random_uuid();

-- DO: add the column cheap, then backfill in batches
ALTER TABLE orders ADD COLUMN token uuid;       -- fast, nullable
-- backfill in chunks (see batching article), then optionally set default
ALTER TABLE orders ALTER COLUMN token SET DEFAULT gen_random_uuid();

NOT VALID: the key to online constraints

Adding a CHECK or FOREIGN KEY normally validates every existing row while holding a strong lock. The two-step NOT VALID → VALIDATE pattern avoids that:

-- Step 1: add the constraint without checking existing rows.
-- Brief strong lock, but no full scan. New/updated rows ARE checked.
ALTER TABLE orders
  ADD CONSTRAINT orders_amount_chk CHECK (amount >= 0) NOT VALID;

-- Step 2: validate existing rows. Takes only a SHARE UPDATE EXCLUSIVE
-- lock — concurrent reads AND writes continue.
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_chk;

The same pattern works for foreign keys: add NOT VALID, then VALIDATE CONSTRAINT separately. From the moment of step 1, integrity is enforced going forward; step 2 simply confirms the historical rows.

Adding NOT NULL safely

SET NOT NULL historically required a full table scan under a strong lock. The safe, online recipe uses a CHECK constraint as a stand-in:

-- 1. Enforce the rule going forward without a blocking scan
ALTER TABLE users
  ADD CONSTRAINT users_email_nn CHECK (email IS NOT NULL) NOT VALID;

-- 2. Backfill any NULLs, then validate (lock-light)
ALTER TABLE users VALIDATE CONSTRAINT users_email_nn;

-- 3. On modern PostgreSQL, SET NOT NULL can use the validated CHECK
--    to skip its own scan:
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
-- 4. The redundant CHECK can then be dropped.
ALTER TABLE users DROP CONSTRAINT users_email_nn;

Changing a column type

A type change that requires conversion rewrites the table. The online approach is the expand/contract pattern: add a new column of the target type, backfill and keep it in sync (trigger or application dual-write), switch reads, then drop the old column. Covered in the expand/contract article.

Layer 3 — Watch it happen on your own database

-- Prove a constant default is metadata-only (no rewrite):
ALTER TABLE orders ADD COLUMN status text DEFAULT 'pending';
SELECT attname, atthasmissing, attmissingval
FROM   pg_attribute
WHERE  attrelid = 'orders'::regclass AND attname = 'status';
--   atthasmissing = t, attmissingval = {pending}   <- stored as metadata

-- Prove VALIDATE takes a weak lock (run in a transaction and inspect):
BEGIN;
ALTER TABLE orders VALIDATE CONSTRAINT orders_amount_chk;
SELECT mode FROM pg_locks
WHERE  relation = 'orders'::regclass AND mode LIKE '%Exclusive%';
--   ShareUpdateExclusiveLock  <- reads AND writes continue
COMMIT;

The atthasmissing/attmissingval columns are the smoking gun that a default add was metadata-only — no table rewrite happened. And inspecting pg_locks during VALIDATE CONSTRAINT confirms it holds only ShareUpdateExclusiveLock, so the expensive row-checking step never blocks ordinary traffic.

Layer 4 — The levers this hands you

The full toolkit for online column and constraint changes:

• Constant defaults are free; volatile defaults rewrite. ADD COLUMN ... DEFAULT 'x' is metadata-only; DEFAULT gen_random_uuid() rewrites — add the column nullable, backfill in batches, then set the default.
• **NOT VALID + VALIDATE CONSTRAINT** for every CHECK and FK: a brief lock to start enforcing forward, then a lock-light scan to confirm history.
• **Split SET NOT NULL** into a validated CHECK (col IS NOT NULL) NOT VALID first; modern PostgreSQL then lets SET NOT NULL reuse that validated CHECK to skip its own scan.
• Type changes use expand/contract — never an in-place converting ALTER COLUMN ... TYPE on a large table.
• **Always set a low lock_timeout** and backfill in batches, never one giant statement.

Layer 5 — What an Oracle DBA should expect vs what they get

Oracle automates much of this; in Postgres you assemble it from primitives:

• Fast default add exists in both. Postgres's metadata "missing value" mirrors Oracle 11g+'s ability to add a NOT NULL column with a default without rewriting — same optimisation, different internals.
• **NOT VALID → VALIDATE ≈ ENABLE NOVALIDATE → ENABLE VALIDATE.** The two-phase constraint pattern is conceptually identical to Oracle's novalidate flow.
• No online type change built in. Where Oracle has DBMS_REDEFINITION for column type changes, Postgres requires the manual expand/contract dance with dual-writes.
• Forward enforcement is immediate. As in Oracle, a NOT VALID constraint still checks new and updated rows from the moment it's added — only historical rows are unverified until VALIDATE.

Key takeaway

Turn every schema change into a fast metadata step plus a separate lock-light data step: add columns with constant (not volatile) defaults, add CHECK/FK constraints NOT VALID then VALIDATE CONSTRAINT, and reach NOT NULL via a validated CHECK. Verify with pg_attribute.atthasmissing and pg_locks that you really avoided a rewrite and a strong lock. Always cap waits with lock_timeout and backfill in batches.