Adding a foreign key constraint requires a table scan and a
SHARE ROW EXCLUSIVE lock on both tables, which blocks writes to each table.
This means no writes will be allowed to either table while the table you're altering is scanned to validate the constraint.
To prevent blocking writes to tables, add the constraint as
NOT VALID in one transaction, then
VALIDATE CONSTRAINT in another.
NOT VALID prevents row updates while running, it commits instantly if it can get a lock (see "Safety requirements").
VALIDATE CONSTRAINT allows row updates while it scans
See "How not valid constraints work" for more information on adding constraints as
Add the foreign key constraint as
NOT VALID to prevent locking the
"user" tables while "email" rows are checked against "user".
VALIDATE CONSTRAINT to scan the
"email" table in the background while reads and writes continue.
Both of these examples have the same amount of locking. Since the newly created table has no rows, you don't need to add a foreign key with
Adding foreign key constraint in
create table statement.