R003 · default CAUTION

ADD FOREIGN KEY without NOT VALID

Adding a FK without NOT VALID locks both child and parent tables during validation.

Why this is a problem

PG validates every existing row when adding a FK constraint, holding ShareRowExclusiveLock on both tables for the scan duration. With NOT VALID, validation is skipped initially; future inserts are still checked. Then VALIDATE runs under ShareUpdateExclusiveLock — concurrent reads + writes still work.

Don't do this

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

Do this instead

-- 1. Metadata-only
ALTER TABLE orders
  ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

-- 2. Validate (low-lock)
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

References

• Strong Migrations: Foreign keys →