Skip to content

.tip

· one of the small things

Add constraints as NOT VALID, then VALIDATE

Adding a foreign key or a check constraint scans the whole table while holding a lock. Split it into two steps.

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 on new rows. The second checks existing rows under a weaker lock that lets writes through.

postgresqlmigrations