Adding a foreign key to a big table without locking both of them
Foreign keys are the constraint people add late. The table already has a few million rows in it, someone notices that order_items.order_id can point at nothing, and the fix is one line. ALTER TABLE order_items ADD CONSTRAINT order_items_order_id_fkey FOREIGN KEY ( order_id ) REFERENCES orders ( id ); This statement does two things. It records the constraint, which is instant, and it validates it,…
Foreign keys can be added to large tables without locking them entirely. When a foreign key is added, it instantly records the constraint and validates it, scanning every row of the referenced table. This process holds a SHARE ROW EXCLUSIVE lock on both tables, preventing writes. However, Postgres allows the recording of the constraint and the validation of existing rows to be separated.
By first adding the constraint without validation (ALTER TABLE ... ADD CONSTRAINT NOT VALID), and then separately validating the existing rows under a lock that still permits writes (ALTER TABLE ... VALIDATE CONSTRAINT), the impact on the database is minimized. This approach holds the lock for a very short duration, as there is no data to scan.
The validated constraint remains in place as NOT VALID, protecting new writes and allowing for the fixing and re-validation of bad rows at a later time. Postgres automatically creates an index for primary keys and unique constraints, but not for foreign key referencing columns. This can lead to performance issues, as writes to the parent table require scanning the child table to ensure no orphaned references.
Adding a concurrent index (CREATE INDEX CONCURRENTLY) on the foreign key columns addresses this. The "not valid then validate" pattern is not exclusive to foreign keys; it can also be applied to CHECK and NOT NULL constraints. By first recording the constraint as NOT VALID and then separately validating it, Postgres can perform the scan under a weaker lock (ACCESS EXCLUSIVE), reducing the impact on the database.
This approach is recommended for any constraint validation that requires a scan, as it optimizes performance by minimizing the lock time necessary for the operation.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
