2026-09-16·3 min read·Simon

Adding a foreign key to a big table without locking both of them

A plain ADD CONSTRAINT ... FOREIGN KEY validates every row while holding locks on the child and the parent. The NOT VALID and VALIDATE split that avoids it, the index nobody adds, and why the same trick works for CHECK and NOT NULL.

postgres

migrations

foreign-keys

locks

Foreign keys are the constraint people add late. The table already has a few million rows in it, someone notices that order_items.order_id can point at nothing, and the fix is one line.

ALTER TABLE order_items
  ADD CONSTRAINT order_items_order_id_fkey
  FOREIGN KEY (order_id) REFERENCES orders (id);

This statement does two things. It records the constraint, which is instant, and it validates it, which means scanning every row of order_items and looking each order_id up in orders. While it does that it holds a SHARE ROW EXCLUSIVE lock on both tables. Reads continue. Every write to either table waits. On a busy orders table a validation that takes ninety seconds is ninety seconds where nobody can place an order.

Split the two things

Postgres lets you separate recording the constraint from validating it.

-- 1. record it; new and updated rows are checked from now on, existing rows are not yet
ALTER TABLE order_items
  ADD CONSTRAINT order_items_order_id_fkey
  FOREIGN KEY (order_id) REFERENCES orders (id) NOT VALID;

-- 2. validate the existing rows, under a lock that lets writes through
ALTER TABLE order_items VALIDATE CONSTRAINT order_items_order_id_fkey;

Step one still takes a brief lock on both tables, but it holds it for milliseconds because there is nothing to scan. Step two does the scan under SHARE UPDATE EXCLUSIVE, which blocks other schema changes and not much else: inserts, updates and deletes all proceed. The end state is identical to the one-line version. The only difference is who waited. BV008 is the rule for the one-line version.

If validation finds a violating row, it fails, and the constraint stays in place as NOT VALID. That is a feature: new writes are already protected, and you can fix the bad rows at leisure and validate again.

The index nobody adds

Postgres creates an index for a primary key and for a unique constraint. It does not create one for the referencing side of a foreign key. So after the statements above, orders.id is indexed and order_items.order_id is not, and every DELETE FROM orders or update to orders.id has to scan order_items to check that nothing still points at the row. On a large child table a single parent delete becomes a sequential scan, and a batch of them becomes a lock queue.

CREATE INDEX CONCURRENTLY order_items_order_id_idx
  ON order_items (order_id);

BV017 flags a foreign key whose referencing columns have no index. It is one of the few rules where the fix is a statement you add rather than a statement you change, and it is also one where the checker benefits from a live schema: with a read-only connection it can see whether the index already exists rather than guessing from the file.

The same trick, twice more

The NOT VALID then VALIDATE split is not special to foreign keys. A CHECK constraint takes it in exactly the same form. And a NOT NULL constraint, which has no NOT VALID form of its own, can borrow it: add CHECK (col IS NOT NULL) NOT VALID, validate that, and from Postgres 12 onward SET NOT NULL will see the validated check and skip its own full-table scan under ACCESS EXCLUSIVE. That scan is BV011, and it is the reason the three statements are worth the extra lines.

The principle underneath all three: any constraint whose validation needs a scan should be recorded first and validated seperately, so the scan runs under the weakest lock that is correct. Postgres gives you the syntax. It just does not make it the default.

The rules behind this post

warning

PostgreSQL

free in the CLI

BV008 Foreign key added without NOT VALID

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.

What is wrong

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

Do this instead

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;

Read the rule

Catch this before it ships: npx bolvrk check migration.sql runs it locally, no account needed. Sign in for the full corpus, pull-request comments and live-schema checks.

note

PostgreSQL

BV017 Foreign key without an index on the referencing columns

Postgres indexes the referenced side of a foreign key (it must be unique) but never the referencing side. Without that index, every UPDATE or DELETE on the parent table sequential-scans the child table to enforce the constraint, fine in dev, a scan-per-row regression in production.

What is wrong

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

Do this instead

Index the referencing column(s): built CONCURRENTLY in its own migration so the build never blocks writes. With the index in place, parent-side UPDATE/DELETE enforce the constraint via index lookups instead of sequential scans of the child table.

-- In its own migration file, run outside a transaction:
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

Read the rule

Catch this before it ships: npx bolvrk check migration.sql runs it locally, no account needed. Sign in for the full corpus, pull-request comments and live-schema checks.

warning

PostgreSQL

free in the CLI

BV011 SET NOT NULL scanning the table under ACCESS EXCLUSIVE

SET NOT NULL must verify every existing row, and it does so while holding ACCESS EXCLUSIVE — the table is fully unavailable for the scan. The safe path is a CHECK (col IS NOT NULL) NOT VALID constraint, VALIDATE CONSTRAINT (which does not block), then SET NOT NULL, which sees the validated constraint and skips the scan.

What is wrong

ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

Do this instead

Add CHECK (col IS NOT NULL) NOT VALID (instant), VALIDATE it in a later migration (non-blocking scan), then SET NOT NULL — on Postgres 12+ it sees the validated constraint and skips its own scan, making the ACCESS EXCLUSIVE window a metadata flip. Drop the helper constraint afterwards.

SET lock_timeout = '5s';
ALTER TABLE orders ADD CONSTRAINT orders_region_nn
  CHECK (region IS NOT NULL) NOT VALID;
-- later migrations:
--    ALTER TABLE orders VALIDATE CONSTRAINT orders_region_nn;  -- non-blocking
--    ALTER TABLE orders ALTER COLUMN region SET NOT NULL;      -- scan skipped (PG12+)
--    ALTER TABLE orders DROP CONSTRAINT orders_region_nn;

Read the rule

Catch this before it ships: npx bolvrk check migration.sql runs it locally, no account needed. Sign in for the full corpus, pull-request comments and live-schema checks.