warning
free in the CLI
Foreign key added without NOT VALID
Warning — this works, but blocks traffic or rewrites data at scale.
What it catches
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.
Fires on
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;Catch this before it ships
This rule runs locally in the free CLI — or with the full corpus through the hosted service: npx bolvrk check migration.sql