hard DDL Locking & Online Schema Change

Why a one-line ALTER can take your database down

A schema change runs inside a transaction and, like any statement, must take a lock on the object it touches. What decides whether that ALTER is invisible or catastrophic is which lock level it needs and how long it holds it. In PostgreSQL most ALTER TABLE forms grab an ACCESS EXCLUSIVE lock — the strongest lock there is, conflicting with every other lock including plain reads. If the change is metadata-only it holds that lock for microseconds and nobody notices; if it rewrites the table it holds it for the whole rewrite and the table is unusable the entire time. Same syntax, wildly different blast radius. The general principle is engine-independent: know the lock level and the duration before you run DDL in production.

The cheap changes: metadata-only

Since PostgreSQL 11, ADD COLUMN with no default or a constant (non-volatile) default is a pure catalog change. The engine records the default as a single "missing" attribute value attached to the column definition; existing rows are not touched, and reads synthesize the value on the fly. Adding a column to a billion-row table this way finishes in milliseconds.

Before PG 11 this was a famous foot-gun: ADD COLUMN … DEFAULT 0 rewrote every row. Many teams still carry the old habit of adding the column and back-filling the default separately — unnecessary now for a constant default, still necessary for a volatile one.

The expensive changes: full rewrites and index builds

Two categories hold a strong lock for a long time:

• Full table rewrites. ADD COLUMN with a volatile default (DEFAULT gen_random_uuid(), DEFAULT clock_timestamp()) must give every row a different value, so it rewrites the whole table under ACCESS EXCLUSIVE. Changing a column's type generally rewrites too.
• Index builds. A plain CREATE INDEX takes a SHARE lock on the table. SHARE permits concurrent reads but blocks all writes (INSERT/UPDATE/DELETE conflict with it) for the entire build — minutes to hours on a large table.

CREATE INDEX CONCURRENTLY — build without blocking writes

CREATE INDEX CONCURRENTLY takes only a SHARE UPDATE EXCLUSIVE lock, which does not conflict with reads or writes — the table stays fully writable while the index builds. It buys that by working harder:

• Two table scans. One scan builds the index against a snapshot; a second scan adds rows that changed during the first pass.
• It waits for old transactions. Between the passes it must wait for every transaction that could still see the pre-index state to finish — so a single long-running transaction stalls the whole build.
• It can fail dirty. If the build fails (a uniqueness violation, a cancel, a crash), it leaves behind an INVALID index that still costs write overhead but is never used for queries. You must DROP it and retry; it cannot be resumed.
• Not inside a transaction block; only the first of its two table scans can use parallel workers.

So the trade is explicit: CONCURRENTLY is slower and more fragile in exchange for not blocking writes.

The queued-lock pitfall — the one that surprises people

Even a fast metadata-only ALTER must still acquire ACCESS EXCLUSIVE, and that acquisition is where production incidents come from. PostgreSQL grants locks roughly first-come-first-served. If a long query is holding a weak lock, your ALTER cannot get ACCESS EXCLUSIVE, so it waits at the head of the lock queue. The trap: every request that arrives after it — even ordinary reads and writes that would never have conflicted with the running query — now queues behind the pending ALTER. One 30-second report plus one blocked ALTER freezes the entire table.

Traced: what actually happens, second by second

t	Event	State of table orders
0s	Analytics SELECT starts (holds ACCESS SHARE)	Reads + writes flowing normally
2s	ALTER TABLE … ADD COLUMN … DEFAULT gen_random_uuid() requests ACCESS EXCLUSIVE	ALTER blocked; enters queue head
2.1s	App INSERT (ROW EXCLUSIVE) arrives	Blocked — queued behind the ALTER, though it never conflicted with the SELECT
2s–30s	All new reads/writes pile up	Table effectively down
30s	SELECT finishes → ALTER runs → but it rewrites every row…	Still locked for the whole rewrite

The fix is a short lock timeout plus a retry loop, so the DDL fails fast instead of holding the queue open:

SET lock_timeout = '2s';
-- if the ALTER can't get its lock within 2s it errors out (55P03),
-- releasing the queue; a wrapper retries with backoff.
ALTER TABLE orders ADD COLUMN status int NOT NULL DEFAULT 0;

The expand/contract (parallel-change) pattern

The professional way to make a breaking schema change with zero downtime is to never break compatibility in a single step. Split it into additive, individually-safe deploys where old and new code both work against the intermediate schema:

1. Expand. Add the new structure additively — a new nullable column, or a new table — using only cheap/online operations. Old code ignores it.
2. Migrate. Deploy code that writes to both old and new, then back-fill the new column in batches (not one giant UPDATE). Now both shapes are correct.
3. Contract. Once all readers use the new shape, drop the old column/constraint. Each step is independently deployable and reversible.

To add a genuinely NOT NULL column with a volatile value without a rewrite: expand (add nullable) → migrate (back-fill in batches) → add a NOT VALID check constraint, then VALIDATE CONSTRAINT (which takes only SHARE UPDATE EXCLUSIVE, not ACCESS EXCLUSIVE) — contracting the window to nearly nothing.

Pitfalls

• The queued lock, not the ALTER itself, causes the outage. A "trivial" ADD COLUMN with a constant default is instant — but it still needs ACCESS EXCLUSIVE, so it can still get stuck behind a slow query and take everything with it. Always set lock_timeout.
• Forgetting CONCURRENTLY leaves invalid indexes. A failed concurrent build silently leaves an INVALID index; a monitoring blind spot. Check pg_index.indisvalid.
• Batched back-fills, not one UPDATE. A single UPDATE over a billion rows holds row locks and bloats the table; back-fill in bounded batches with commits between.
• Long transactions poison online DDL. CONCURRENTLY and VALIDATE wait for old transactions; an idle-in-transaction connection can stall your migration indefinitely.
• ORM auto-migrations don't know this. Framework "add column with default" or "add index" often emit the blocking form; review the generated SQL.

Judgment — online/concurrent vs offline

The real decision is can you afford a maintenance window, and how big is the table?

	Offline (plain DDL / maintenance window)	Online (CONCURRENTLY, expand/contract, gh-ost, pt-online-schema-change)
Speed	Fast — one pass, no snapshot juggling	Slow — extra scans, waits, batched back-fill
Availability	Downtime / table locked	No downtime, table stays writable
Complexity & risk	Simple, one step	Complex — multi-deploy, invalid-index cleanup, triggers/shadow tables

Choose offline when a window is acceptable and the table is small enough that the rewrite fits the window (dev/internal systems, batch platforms, a 10k-row config table) — the speed and simplicity are worth more than avoiding a 5-second blip. Choose online/concurrent for large, always-on OLTP tables where any lock is an outage; you accept the slower, more fragile, multi-step process because downtime is not on the menu.

Named alternative for MySQL: where PostgreSQL uses CONCURRENTLY + expand/contract, MySQL/InnoDB has native ALGORITHM=INPLACE online DDL, but many changes still take a metadata lock or fall back to a copy; teams reach for gh-ost (GitHub) or pt-online-schema-change (Percona), which build a shadow table, sync it via triggers or the binlog, and atomically rename. Trade-off vs native concurrent DDL: shadow-table tools give finer throttling and are pausable/resumable, at the cost of doubling storage during the migration and heavy write amplification.

Takeaways

• Same ALTER syntax, different worlds: know the lock level and whether it rewrites before you run it.
• ADD COLUMN with a constant default is instant metadata (PG 11+); a volatile default rewrites the whole table under ACCESS EXCLUSIVE.
• CREATE INDEX blocks writes; CREATE INDEX CONCURRENTLY does not, but is slower, double-scans, and can leave an invalid index — always set lock_timeout to dodge the queued-lock trap.
• Breaking changes on live tables go through expand → migrate → contract; online is safe/slow/complex, offline is fast/simple/downtime.

---

Synthesized from the PostgreSQL documentation (Explicit Locking, ALTER TABLE, CREATE INDEX), the "Zero-Downtime Migrations" literature (Braintree, GitHub gh-ost, Percona pt-online-schema-change), and Kleppmann, Designing Data-Intensive Applications (schema evolution). Re-authored and diagrams hand-authored (SVG) for this guide.

I just read a lesson on **DDL Locking & Online Schema Change** (System Design) and want to truly understand it. Explain DDL Locking & Online Schema Change from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **DDL Locking & Online Schema Change** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **DDL Locking & Online Schema Change** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **DDL Locking & Online Schema Change** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.