You run ALTER TABLE ... ADD CONSTRAINT to add a foreign key to a table holding 500 million rows in production, and the application immediately starts timing out. Explain what the database is doing and why the impact is so wide.
answer
- Two costs: strong lock + full validation scan
- Scan runs while holding the lock
- FK also locks the referenced parent table
- Lock queue: waiters pile behind pending DDL
- lock_timeout + retry; add NOT VALID then validate
basics
~20 sAdding a constraint takes a strong table lock and then scans every existing row to prove they all satisfy it, holding the lock for the whole scan. On a huge table that is minutes to hours, and queued queries pile up behind the lock.
solid answer
~60 sTwo things happen together, and it is the combination that hurts. **A strong lock.** `ADD CONSTRAINT` is DDL, so it takes an exclusive-class lock on the table (and for a foreign key, a lock on the referenced table too, since new triggers are attached there). Nothing else may read or write it, depending on engine and lock mode. **A full validation scan.** The engine must prove that all 500 million existing rows already satisfy the rule — for a foreign key, that every non-NULL value has a matching parent. That is a sequential scan plus per-row lookups, and it runs *while holding the lock*. The reason the blast radius is wider than "that table is slow" is lock queueing: the ALTER waits for the current transactions to finish, and every query arriving afterwards queues behind it, including plain SELECTs. One long-running read can therefore delay the ALTER, which then blocks the entire application against that table. Mitigations: short `lock_timeout` with retry, and adding the constraint unvalidated first, then validating separately under a weaker lock.
code
sql · 5 linesSET lock_timeout = '2s';
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id)
REFERENCES customers (id);
-- on failure: retry rather than waiting indefinitelygo deeper
Know that ADD CONSTRAINT locks the table and checks every existing row, so it is slow on big tables.
Explain both costs and that the scan happens while the lock is held; mention that a foreign key also locks the referenced table.
Explain lock queueing and how a single long-running reader turns the DDL into an application-wide outage; prescribe lock_timeout with retry and the add-unvalidated-then-validate split.
Frame it as a migration policy: DDL runbooks with lock timeouts, session hygiene, data cleaned before validation, and a distinction between scan-only and full-rewrite ALTERs.
## The two costs, separately Adding a constraint to a populated table has two distinct costs that people tend to blur together. **Cost 1 — the lock.** `ALTER TABLE` is DDL and mutates the catalog. In PostgreSQL that means an `ACCESS EXCLUSIVE` lock on the table for a plain `ADD CONSTRAINT`, which conflicts with everything including `SELECT`. Adding a foreign key additionally needs a lock on the *referenced* table, because enforcement triggers are attached there — a detail that surprises people when adding a child-table constraint stalls traffic on the parent. Other engines take equivalent strong locks; the exact mode varies but the shape does not. **Cost 2 — the validation scan.** A constraint is a statement about *all* rows, so the engine must verify the existing ones. For a CHECK constraint that is a sequential scan evaluating the expression. For a foreign key it is a scan plus, for every non-NULL value, a lookup in the referenced table's unique index. For a unique constraint it is a full index build. On 500 million rows this is minutes at best, often much longer if the table does not fit in cache. The damage comes from doing cost 2 *while holding* cost 1. ## Lock queueing: why everything stops, not just that table's writers Most engines queue lock requests fairly. Your ALTER cannot start until the transactions currently holding conflicting locks finish. While it waits, it sits in the queue holding its place — and every subsequent request that conflicts with the *pending* ALTER queues behind it, including ordinary SELECTs that would have been perfectly compatible with the running transactions. So the failure mode is: one long-running analytics query holds a read lock; your ALTER queues behind it; the entire application's traffic to that table queues behind your ALTER; connections pile up; the pool exhausts; unrelated endpoints fail. The DDL that was supposed to take "a few minutes" has taken the site down, and it has not even begun scanning yet. This is why the standard hygiene is a short `lock_timeout` (a second or two) with retry in a loop: if the lock cannot be taken almost immediately, give up and try again rather than parking a poison pill in the queue. ## What makes it worse - **Long-running readers.** Reporting queries, open psql sessions, idle-in-transaction connections from a leaky pool. Kill or wait them out before running DDL. - **Foreign keys touching hot parents.** The lock on the referenced table means a constraint on a small child table can stall the busiest table in the schema. - **Doing several ALTERs in one transaction.** Locks accumulate and are all held until the transaction ends. - **Rewrites.** Some ALTERs rewrite the whole table rather than just scanning it (changing a column type, historically adding a column with a default). A rewrite is far worse than a scan: it doubles the storage temporarily and takes proportionally longer. ## The shape of the fix The general strategy is to separate the two costs so that the strong lock is held only for a moment and the long scan runs under a weak one: 1. **Add the constraint without validating existing rows.** In PostgreSQL this is `ADD CONSTRAINT ... NOT VALID`, which takes the strong lock only briefly, because it does no scan. From that instant, all new and modified rows are enforced. 2. **Validate separately.** `VALIDATE CONSTRAINT` scans the table under a much weaker lock (`SHARE UPDATE EXCLUSIVE`) that permits concurrent reads and writes. SQL Server's analogue is `WITH NOCHECK` to add without verifying, and later `WITH CHECK CHECK CONSTRAINT` to verify and mark the constraint trusted. For unique constraints the two-step trick does not exist, because the artefact being built *is* the index — there the answer is to build the unique index concurrently/online first and then attach it as a constraint. ## Operational checklist - Run DDL with `lock_timeout` set and a retry loop; never let it queue indefinitely. - Check for long-running and idle-in-transaction sessions first, and be prepared to terminate them. - Add unvalidated first, validate in a separate transaction, off-peak. - Clean the offending data *before* validating — validation that finds violations fails and leaves you back where you started. - Know your table size and expected scan duration before you start; "it was fast in staging" with a thousand rows tells you nothing. The candidate who only says "it locks the table" has half the answer. The other half — the full validation scan held under that lock, and the queueing that widens the blast radius to the whole application — is what separates someone who has read a manual from someone who has caused, or avoided, an outage.
- Why can a plain SELECT be blocked by an ALTER TABLE that has not even started yet?Lock requests are queued fairly. The ALTER waits behind whatever holds a conflicting lock, and while it waits it occupies the queue, so later requests that conflict with it queue behind it too — even reads that would have been compatible with the current holder. That is why one long-running reporting query can convert a routine DDL into an application-wide stall.
- Why does adding a foreign key also affect the referenced table?Enforcement requires triggers or equivalent machinery on the referenced side, so the engine must take a lock there as well to attach them. A constraint added to a small child table can therefore stall traffic on a very busy parent, which is a common surprise during a migration window.
saying these in an interview costs you the question
- Saying it only locks writers, so reads are unaffected
- Forgetting the full validation scan and mentioning only the lock
- Not knowing the referenced table is locked too for a foreign key
- Assuming a fast run in staging predicts production behaviour
- Running DDL with no lock_timeout and letting it queue indefinitely