Safe DDL patterns: NOT VALID, CONCURRENTLY, and unsafe-op fixes

A team runs CREATE INDEX ON orders(user_id) at 10:00 AM. On a 200M-row table this holds ACCESS EXCLUSIVE for 20 minutes — reads and writes blocked. The same index built with CREATE INDEX CONCURRENTLY takes 45 minutes but blocks nothing. Same index, same data, wildly different production impact.

CREATE INDEX CONCURRENTLY

Regular CREATE INDEX takes ACCESS EXCLUSIVE for the full build duration. On large tables this blocks reads and writes for minutes.

CREATE INDEX CONCURRENTLY builds the index in two phases, both under SHARE UPDATE EXCLUSIVE (does not block DML):

1. Phase 1: scan the table and build a draft index, while normal traffic continues.
2. Phase 2: catch up on writes that happened during phase 1; mark index valid.

Trade-offs:

• Takes 2–3× longer than non-concurrent.
• Cannot run inside a transaction block — if your migration tool wraps migrations in BEGIN..COMMIT, you need to annotate this migration as non-transactional.
• Can fail mid-build and leave an INVALID index (visible in pg_indexes where indisvalid = false).

Production runbook for CONCURRENT index:

1. Always use CREATE INDEX CONCURRENTLY in production.
2. After the migration, verify: SELECT indexname, indisvalid FROM pg_indexes JOIN pg_class ... WHERE indisvalid = false — should return nothing.
3. If you find an INVALID index: DROP INDEX CONCURRENTLY (also non-blocking), then retry.

ADD CONSTRAINT NOT VALID + VALIDATE CONSTRAINT

Adding a CHECK or FOREIGN KEY constraint normally acquires ACCESS EXCLUSIVE and scans the entire table.

The two-step pattern:

-- Step 1: add constraint, enforce only for NEW rows (instant, brief ACCESS EXCLUSIVE)
ALTER TABLE orders
  ADD CONSTRAINT orders_status_valid
  CHECK (status IN ('pending', 'active', 'closed')) NOT VALID;

-- Step 2: verify existing rows (SHARE UPDATE EXCLUSIVE — does not block DML)
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_valid;

NOT VALID means: enforce for new rows from this moment; existing rows are not checked. VALIDATE CONSTRAINT then scans all rows under SHARE UPDATE EXCLUSIVE, which does not block INSERT, UPDATE, or DELETE. The scan can run while traffic is live.

This pattern applies to:

• CHECK constraints
• FOREIGN KEY constraints: ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID + VALIDATE CONSTRAINT
• NOT NULL via CHECK (col IS NOT NULL) NOT VALID (see lesson 02)

Unsafe operations and their safe equivalents

ALTER COLUMN TYPE: the binary-coercible exception

Most type changes rewrite the table. Exceptions are binary-coercible types — the same physical representation:

• varchar(50) → varchar(100) (wider, same bytes)
• varchar → text (equivalent storage)
• text → varchar (same)

Postgres detects these and skips the rewrite — brief ACCESS EXCLUSIVE, no rows written. Non-coercible changes (int → bigint, text → int, json → jsonb) require either a full rewrite or the shadow-column expand-contract pattern (lesson 05).