Does adding a foreign key lock both tables?

Foreign key added without NOT VALID

Warning: this works, but blocks traffic or rewrites data at scale.

What happens

Adding a validated foreign key scans every existing row while holding a SHARE ROW EXCLUSIVE lock on both tables. With NOT VALID the constraint applies to new writes instantly, and VALIDATE CONSTRAINT can scan later with a much weaker lock.

Why it is dangerous on a populated table

It completes, but the time it holds its lock or rewrites rows scales with the table, and on a busy one that is long enough for traffic to queue.

Measured

Both forms ran against the same table (20M rows, 1.7 GB) on Postgres 18.6, with one session inserting a row and another reading one row every 50 ms. Worst wait is the slowest of those probe calls while the statement ran. This run is from Sep 16, 2026; the whole benchmark has every scenario and how to run it.

	Statement	Time	Worst write wait	Worst read wait	Lock
Fires	ADD CONSTRAINT ... FOREIGN KEY (validates under lock)	10.8 s	10.8 s	4 ms	AccessShareLock, ShareRowExclusiveLock
Safe	NOT VALID, then VALIDATE CONSTRAINT	7.9 s	9 ms	3 ms	AccessShareLock, ShareRowExclusiveLock, ShareUpdateExclusiveLock

Fires on

ALTER TABLE orders ADD CONSTRAINT fk FOREIGN KEY (user_id) REFERENCES users (id);

The safe pattern

Add the constraint NOT VALID, it applies to new writes immediately without scanning existing rows, then VALIDATE CONSTRAINT in a later migration; validation takes only SHARE UPDATE EXCLUSIVE on the table and ROW SHARE on the referenced table, so traffic continues. Index the referencing column first (see BV017).

-- migration 1 (alone, outside a transaction): cover the column first
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders (user_id);
-- migration 2: the constraint, instant for existing rows
--    SET lock_timeout = '5s';
--    ALTER TABLE orders ADD CONSTRAINT orders_user_id_fkey
--      FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
-- migration 3, non-blocking scan:
--    ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (1)

validated fk

ALTER TABLE orders ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users (id);

Stays silent (2)

fk on new table

CREATE TABLE order_items (id bigint PRIMARY KEY, order_id bigint);
ALTER TABLE order_items ADD CONSTRAINT items_order_fk FOREIGN KEY (order_id) REFERENCES orders (id);

not valid fk

ALTER TABLE orders ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;

How to check locally

Catch this before it ships

This rule runs locally in the free CLI, or with the full corpus through the hosted service. No install, nothing leaves your machine:

npx bolvrk check migration.sql

Browse all 135 rules

← BV007 BV009 →