DDL Locking: Why ALTER TABLE Can Freeze Your App

The one thing to understand first

Most forms of ALTER TABLE take an ACCESS EXCLUSIVE lock — the strongest table lock, conflicting with every other lock mode including the ACCESS SHARE that a plain SELECT takes. While held, no session can even read the table. On a busy table this turns a "quick schema change" into a full outage.

The damage isn't the lock you take — it's the queue that forms behind a DDL statement that is itself waiting. A single slow query in front of your ALTER can freeze every new read of the table, so the whole art of safe DDL is never letting a strong-lock request block.

The hidden danger: the lock queue

The worst part is not the lock itself but the queue. PostgreSQL grants lock requests roughly in order. When your ALTER requests ACCESS EXCLUSIVE, it must wait for existing readers to finish — and meanwhile every new query queues behind your waiting ALTER, even simple SELECTs that would not normally conflict with each other. One long-running query in front of your ALTER can therefore stall the entire application:

-- Session A: a slow report holds ACCESS SHARE for 30s
-- Session B: ALTER TABLE ... (waits for A, holding the queue)
-- Session C..Z: every new SELECT now waits behind B

What is cheap vs expensive

Not all DDL is equal. Operations that only change catalog metadata are fast; those that rewrite the table or validate data are slow:

• Cheap (metadata only): ADD COLUMN with a non-volatile default (modern PG stores it as metadata), DROP COLUMN (marks it dead), renaming, adding a NOT VALID constraint.
• Expensive (table rewrite): changing a column type that requires conversion, SET DEFAULT with a volatile expression on old versions, adding a column with a volatile default.
• Expensive (full scan): adding a CHECK or FOREIGN KEY normally validates every existing row under a strong lock.

Layer 3 — Watch it happen on your own database

-- Before any DDL: are there long transactions that would block
-- (and then be blocked behind by) your ALTER?
SELECT pid, now() - xact_start AS age, state, query
FROM   pg_stat_activity
WHERE  state <> 'idle'
ORDER  BY age DESC;

-- Reproduce the queue poisoning in two psql sessions:
-- A:  BEGIN; SELECT * FROM orders;            -- holds ACCESS SHARE
-- B:  ALTER TABLE orders ADD COLUMN notes text;  -- waits for A
-- C:  SELECT count(*) FROM orders;            -- now stuck behind B!

Run the inspection query first — any old transaction is a landmine. Then reproduce the effect: open the slow reader (A), fire the ALTER (B) and watch it wait, then issue an ordinary SELECT (C) and watch it block too. That third session proves the headline danger: a waiting strong-lock request blocks the entire queue behind it, including reads that don't conflict with each other.

Layer 4 — The levers this hands you

The universal safety valve is to bound how long DDL will wait, so a blocked ALTER fails fast instead of building a queue:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN notes text;
-- If the lock can't be acquired in 2s, the statement errors out
-- and the application keeps running. Retry later.

• **lock_timeout** — the single most important DDL safety setting; pair it with a short statement_timeout for any rewrite step. Try, fail fast, retry in a quieter window.
• Prefer cheap (metadata-only) DDL: ADD COLUMN with a non-volatile default, DROP COLUMN, renames, and adding constraints as NOT VALID — all avoid a table rewrite/scan.
• Defer the expensive half. Split type changes and constraint validation into a fast metadata step plus a separate lock-light validation step (next articles in this pathway).
• Clear the runway. Run DDL when long transactions are absent; check pg_stat_activity and wait out or terminate blockers first.

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

DDL locking surprises Oracle DBAs in both directions:

• No automatic online DDL. Oracle offers ALTER TABLE ... ONLINE, DBMS_REDEFINITION, and edition-based redefinition; PostgreSQL gives you lock_timeout + expand/contract patterns instead, and you assemble online DDL by hand.
• The queue-poisoning behaviour is sharper. A waiting ACCESS EXCLUSIVE request blocking all subsequent reads is more aggressive than Oracle's typical DDL behaviour — this catches Oracle migrants off guard constantly.
• **ADD COLUMN with default is cheap on both modern engines** — Postgres (11+) stores a constant default as catalog metadata, similar to Oracle 11g+'s fast add-column.
• **NOT VALID ≈ ENABLE NOVALIDATE.** Adding a constraint without scanning existing rows, then validating separately, mirrors Oracle's novalidate-then-validate flow.

Key takeaway

Most ALTER TABLE takes ACCESS EXCLUSIVE, which conflicts with reads; worse, a DDL statement waiting for that lock blocks every query that queues behind it. Inspect pg_stat_activity for long transactions first, always set a low lock_timeout so a blocked ALTER fails fast, prefer metadata-only DDL, and split expensive changes into cheap-plus-validate steps. This is the foundation for every zero-downtime migration technique that follows.