Does adding a foreign key lock both tables in PostgreSQL?

Short answer

ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers (id) takes a SHARE ROW EXCLUSIVE lock on both orders and customers. Reads of either table carry on; INSERT, UPDATE and DELETE on either wait. Then PostgreSQL reads all of orders to check each customer_id exists, and the writes stay blocked until that check is done: about 0.25 s for our 3 million rows, and proportionally longer on a big table.

Split it in two so the slow part doesn’t block writes:

ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;   -- instant
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;   -- scans, writes carry on

What it locks

SHARE ROW EXCLUSIVE conflicts with the ROW EXCLUSIVE lock every INSERT, UPDATE and DELETE takes, but not with the ACCESS SHARE lock of a SELECT. SHARE UPDATE EXCLUSIVE and ROW SHARE conflict with neither, which is why validation doesn’t get in anyone’s way.

Dropping a foreign key is the surprise: it takes ACCESS EXCLUSIVE on both tables, so even reads wait for it. It’s instant, but it can still queue behind a long query.

Does it rewrite the table?

No. Neither table’s data file changes. Adding the key (or validating it) runs one query that reads the whole referencing table and checks every value against the referenced table.

NOT VALID means “not checked for existing rows yet”. New and changed rows are checked from the moment it’s added: an insert with a missing customer fails straight away. If VALIDATE finds an existing row that breaks the rule, it fails and the constraint stays NOT VALID:

ERROR:  insert or update on table "c" violates foreign key constraint "c_p_id_fkey"
DETAIL:  Key (p_id)=(5000) is not present in table "p".

Run it safely

1. Index the referencing column first, concurrently. PostgreSQL doesn’t create one for you. The key works without it, but every DELETE from customers (or change of customers.id) then has to scan orders to find matching rows:

CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);

See CREATE INDEX CONCURRENTLY.

2. Add the key NOT VALID, with lock_timeout. It needs locks on two tables, so it has two chances to queue behind a long transaction:

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

If it fails with ERROR: canceling statement due to lock timeout, nothing changed; retry after a pause. See lock timeout.

3. Fix orphans, then validate. Find rows that would fail:

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

Then validate. It reads all of orders, but reads and writes on both tables continue:

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;

Watch out for deadlocks if a migration takes locks on several tables in a different order from your application. Keep each migration step to one constraint, in its own transaction. See deadlock detected.

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac). A 1,000-row referenced table and a 3-million-row referencing one:

CREATE TABLE seo_mig.p (id int PRIMARY KEY, name text);
INSERT INTO seo_mig.p SELECT g, 'p' || g FROM generate_series(0, 999) g;
CREATE TABLE seo_mig.big (id bigint, n int, s text);
INSERT INTO seo_mig.big SELECT g, g % 1000, md5(g::text) FROM generate_series(1, 3000000) g;

BEGIN;
ALTER TABLE seo_mig.big ADD CONSTRAINT big_n_fkey FOREIGN KEY (n) REFERENCES seo_mig.p (id);
SELECT relation::regclass, mode FROM pg_locks
WHERE pid = pg_backend_pid()
  AND relation IN (SELECT oid FROM pg_class WHERE relnamespace = 'seo_mig'::regnamespace);
SELECT seq_scan FROM pg_stat_xact_user_tables WHERE relid = 'seo_mig.big'::regclass;
ROLLBACK;

pg_locks also showed ACCESS SHARE locks on both tables and the parent’s primary key index, taken by the check query; they don’t change what’s blocked. Times are the median of three runs.

Then a second session held each statement open (child table of 100,000 rows) while a third ran statements with lock_timeout = '1s'. Identical on all five versions:

In Inlet

The structure editor shows the ALTER TABLE … ADD CONSTRAINT before it runs. If a foreign key migration seems stuck, the Activity monitor shows which session blocks which across both tables, and lets you cancel or terminate the one in the way.