Add constraints as NOT VALID, then VALIDATE
Adding a foreign key or check constraint scans the whole table under a lock. Split it in two.
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;The first statement is instant and enforces the rule for new rows. The second scans existing rows with a weaker lock that lets writes through.
postgresqlmigrations