Checkout writes hang in the middle of a deploy. The only change in that deploy is the migration your coding agent wrote when you asked it to link orders to customers, and it looked textbook: ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers (id);.

Per the PostgreSQL 18 ALTER TABLE docs, ADD FOREIGN KEY takes a SHARE ROW EXCLUSIVE lock on orders and on customers, then scans the table to check every existing row. The same page warns that while that scan runs, "other updates to the table are locked out until the ALTER TABLE ADD CONSTRAINT command is committed." On a big orders table, that is every checkout and every customer-profile edit waiting on one migration.

The fix is two statements and an index the agent also skipped, and you can watch the stall happen on your laptop first.

Why one line locks two tables

Postgres table locks come in eight modes, and the explicit locking chapter lists which ones conflict. Three matter here.

INSERT, UPDATE, DELETE and MERGE take ROW EXCLUSIVE on their target table. SHARE ROW EXCLUSIVE conflicts with ROW EXCLUSIVE; the docs describe it as a mode that "protects a table against concurrent data changes." So while the foreign-key migration holds it, writes to both tables queue.

Reads are fine. Plain SELECT takes ACCESS SHARE, which conflicts with ACCESS EXCLUSIVE only. That is why this failure is sneaky: the dashboards that read keep rendering while every write to those two tables hangs.

And the lock does not end when the scan ends. Per the same chapter, "once acquired, a lock is normally held until the end of the transaction." If your migration runner wraps each migration in a transaction, and many do, the window is the scan plus everything else in that migration file.

Two database tables chained together by one padlock while a queue of writers waits outside
One ALTER statement, two tables locked against writes until it commits.

Reproduce the stall in Docker

Start a throwaway Postgres 18 from the official image (POSTGRES_PASSWORD is required) and open a psql shell:

docker run --name fk-lab -e POSTGRES_PASSWORD=lab \ -d postgres:18 docker exec -it fk-lab psql -U postgres

Create the two tables with no relationship yet: 100,000 customers and a million orders that all point at real customers.

CREATE TABLE customers ( id bigint PRIMARY KEY, name text NOT NULL ); CREATE TABLE orders ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id bigint NOT NULL, total_cents integer NOT NULL ); INSERT INTO customers SELECT g, 'customer ' || g FROM generate_series(1, 100000) AS g; INSERT INTO orders (customer_id, total_cents) SELECT (random() * 99999)::bigint + 1, (random() * 10000)::int FROM generate_series(1, 1000000); ANALYZE;

Open three more terminals (four sessions in all), each running the same docker exec -it fk-lab psql -U postgres. In session 1, run the agent's migration inside a transaction and leave it open. That mimics a migration runner that has more statements to go, and it keeps the lock around long enough to look at:

BEGIN; ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers (id); -- do not COMMIT yet

In session 2, place an order. In session 3, edit a customer. Both statements hang:

-- session 2 INSERT INTO orders (customer_id, total_cents) VALUES (42, 1999); -- session 3 UPDATE customers SET name = 'renamed' WHERE id = 7;

A plain SELECT against either table, say SELECT count(*) FROM orders;, still returns, for the ACCESS SHARE reason above.

Watch the blocked writers

In session 4, ask pg_stat_activity who is waiting and pg_blocking_pids() who they are waiting on:

SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) AS blocked_by, left(query, 45) AS query FROM pg_stat_activity WHERE wait_event_type = 'Lock';

Sessions 2 and 3 should show up with wait_event_type = 'Lock' and wait_event = 'relation', which the monitoring docs define as "waiting to acquire a lock on a relation." Both blocked_by arrays should hold session 1's pid.

Then look at the lock table itself with pg_locks:

SELECT pid, relation::regclass AS rel, mode, granted FROM pg_locks WHERE locktype = 'relation' AND relation IN ('orders'::regclass, 'customers'::regclass) ORDER BY rel, granted DESC;

Expect session 1 holding ShareRowExclusiveLock with granted = true on both tables, and sessions 2 and 3 each asking for RowExclusiveLock with granted = false. Those are the two halves of the conflict table, live.

Run ROLLBACK; in session 1. Sessions 2 and 3 finish right away.

pg_blocking_pids() also reports "soft" blocks: a session waiting for a lock that would conflict with yours, and that is ahead of you in the queue. So a foreign-key migration that is itself waiting behind one long-running write transaction can stall every new writer that arrives after it. lock_timeout in migration 2 is the guard against that.

The safe way: index, NOT VALID, VALIDATE

Three migrations instead of one. Run them in this order, each in its own migration file.

Migration 1: the index the agent forgot. The constraints chapter notes that "the declaration of a foreign key constraint does not automatically create an index on the referencing columns." Without one, every DELETE from customers, and every update of customers.id, has to scan orders for matching rows. Build it without blocking writes:

CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);

CREATE INDEX CONCURRENTLY cannot run inside a transaction block, so this migration has to opt out of the runner's transaction. If it fails partway, it leaves an index marked INVALID; the docs recommend dropping it and running the build again.

Migration 2: add the constraint, skip the scan.

SET lock_timeout = '3s'; ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

This still asks for SHARE ROW EXCLUSIVE on both tables, but NOT VALID skips "this potentially-lengthy scan," so the lock is held for a moment instead of a full pass over a million rows. New rows are checked from now on. The ALTER TABLE page says subsequent inserts and updates "fail unless there is a matching row in the referenced table." Try it:

INSERT INTO orders (customer_id, total_cents) VALUES (999999999, 100);

That insert is rejected with a foreign-key violation. Old rows are not trusted yet, and pg_constraint records that:

SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'orders'::regclass AND contype = 'f';

convalidated is false until the next step.

lock_timeout is there for the queue problem from the last section. It aborts "any statement that waits longer than the specified amount of time while attempting to acquire a lock" on a table, index, row or other object. Its default is 0, which means wait forever. With three seconds, a migration stuck behind a long transaction fails fast and frees the queue, and you retry it. Set it per session, as here; the docs say setting it in postgresql.conf is not recommended because it would affect all sessions.

Migration 3: validate, with writes still flowing.

SET lock_timeout = '3s'; ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;

This is the full scan, but under weaker locks. Validation takes SHARE UPDATE EXCLUSIVE on orders and ROW SHARE on customers. Neither conflicts with ROW EXCLUSIVE, so inserts, updates and deletes on both tables keep going while it reads. It does conflict with other schema changes and with VACUUM on orders, which is the trade you want.

Repeat the four-session test to see it: wrap the VALIDATE in BEGIN without committing, then run the session 2 and 3 writes. They complete. The session 4 query finds no one waiting.

A bridge between two tables drawn first as a dotted outline, then filled in solid while traffic keeps crossing
NOT VALID draws the outline for new rows; VALIDATE fills it in while writes keep moving.

If an existing order points at a customer that does not exist, VALIDATE fails and convalidated stays false. Find the orphans first:

SELECT o.id, o.customer_id FROM orders o LEFT JOIN customers c ON c.id = o.customer_id WHERE c.id IS NULL LIMIT 20;

Keep migrations 2 and 3 in separate transactions. Run both statements in one transaction and the SHARE ROW EXCLUSIVE from step 2 is still held through the step 3 scan, because locks live until the transaction ends. You would be back to the one-line version with more typing.

A rule to hand your agent

Agents tend to copy the shape they have seen most, and the one-line ADD FOREIGN KEY is everywhere. Put the safe shape where the agent reads before it writes a migration. In Claude Code, a file in .claude/rules/ with a paths: block loads only when the agent works on matching files:

--- paths: - "db/migrations/**" --- # Postgres migration rules  - Never add a foreign key to an existing table in one statement. Write two migrations:  1. ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID  2. VALIDATE CONSTRAINT ..., in its own transaction - Every new foreign key gets an index on the referencing column, built with CREATE INDEX CONCURRENTLY in a migration that runs outside a transaction. - Every DDL migration starts with SET lock_timeout = '3s'. A timeout means retry, not raise the timeout. - Before VALIDATE, run an orphan LEFT JOIN query and report the count.

The same doc page says Claude treats its instruction files "as context, not enforced configuration," so back the rule with a check that fails the build. Squawk has a rule named adding-foreign-key-constraint that flags this pattern and suggests the NOT VALID then VALIDATE split. The agent learns the shape from the rule file, and CI catches the times it forgets.

If this was useful

Picking the store, shaping the schema and planning how it changes under live traffic are what the Database Playbook walks through, store by store. If you let an agent write your migrations, it is the background that lets you review those diffs in a minute instead of finding out from the on-call channel.

Database Playbook: Choosing the Right Store for Every System You Build