How to prevent locking writes to your PostgreSQL table when adding a foreign key

Wed Jun 17 2026

When you add a new foreign key constraint to a PostgreSQL table, the PostgreSQL engine locks the table for updates and writes while each value in the column is validated against the constraint.

This is how you might ordinarily create a new column and add a foreign key constraint to it:

ALTER TABLE foo ADD COLUMN bar_id INTEGER;

ALTER TABLE foo ADD FOREIGN KEY(bar_id) REFERENCES bar (id);

Thanks to this Github issue (opens new window) and this StackExchange (opens new window) post, I learned that if you use the REFERENCES keyword to create the column and the foreign key in one step, the database engine skips validation on all the existing rows, because it knows that all the values are NULL at that point.

Here is how you would create the column and foreign key constraint in one command:

ALTER TABLE foo ADD COLUMN bar_id INTEGER REFERENCES bar (id);

However, because Alembic doesn't yet support (opens new window) adding ON DELETE clause when creating a foreign key in this way, we still had to create the column and constraint in two steps. However, we still wanted to try and bypass the potentially long lock on the table due to the engine's validation of the column.

This is where PostgreSQL docs (opens new window) came in handy:

The main purpose of the NOT VALID constraint option is to reduce the impact of adding a constraint on concurrent updates. With NOT VALID, the ADD CONSTRAINT command does not scan the table and can be committed immediately. After that, a VALIDATE CONSTRAINT command can be issued to verify that existing rows satisfy the constraint. The validation step does not need to lock out concurrent updates, since it knows that other transactions will be enforcing the constraint for rows that they insert or update; only pre-existing rows need to be checked. Hence, validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered.

In other words, we could split up the creation of the constraint and its validation into two steps by using the NOT VALID clause. Moreover, the validation step would not lock writes this time, because the constraint will already be in place for any new rows.

Our SQL commands would end up looking something like the following:

ALTER TABLE foo ADD COLUMN bar_id INTEGER;

ALTER TABLE foo ADD FOREIGN KEY(bar_id) REFERENCES bar (id) NOT VALID;

ALTER TABLE foo VALIDATE CONSTRAINT fk_foo_bar_id_bar;