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, which means scanning every row of order_items and looking each order_id up in orders. While it does that it holds a SHARE ROW EXCLUSIVE lock on both tables. Reads continue. Every write to either table waits. On a busy orders table a validation that takes ninety seconds is ninety seconds where nobody can place an order.
Split the two things
Postgres lets you separate recording the constraint from validating it.
-- 1. record it; new and updated rows are checked from now on, existing rows are not yet
ALTER TABLE order_items
ADD CONSTRAINT order_items_order_id_fkey
FOREIGN KEY (order_id) REFERENCES orders (id) NOT VALID;
-- 2. validate the existing rows, under a lock that lets writes through
ALTER TABLE order_items VALIDATE CONSTRAINT order_items_order_id_fkey;
Step one still takes a brief lock on both tables, but it holds it for milliseconds because there is nothing to scan. Step two does the scan under SHARE UPDATE EXCLUSIVE, which blocks other schema changes and not much else: inserts, updates and deletes all proceed. The end state is identical to the one-line version. The only difference is who waited. BV008 is the rule for the one-line version.
If validation finds a violating row, it fails, and the constraint stays in place as NOT VALID. That is a feature: new writes are already protected, and you can fix the bad rows at leisure and validate again.
The index nobody adds
Postgres creates an index for a primary key and for a unique constraint. It does not create one for the referencing side of a foreign key. So after the statements above, orders.id is indexed and order_items.order_id is not, and every DELETE FROM orders or update to orders.id has to scan order_items to check that nothing still points at the row. On a large child table a single parent delete becomes a sequential scan, and a batch of them becomes a lock queue.
CREATE INDEX CONCURRENTLY order_items_order_id_idx
ON order_items (order_id);
BV017 flags a foreign key whose referencing columns have no index. It is one of the few rules where the fix is a statement you add rather than a statement you change, and it is also one where the checker benefits from a live schema: with a read-only connection it can see whether the index already exists rather than guessing from the file.
The same trick, twice more
The NOT VALID then VALIDATE split is not special to foreign keys. A CHECK constraint takes it in exactly the same form. And a NOT NULL constraint, which has no NOT VALID form of its own, can borrow it: add CHECK (col IS NOT NULL) NOT VALID, validate that, and from Postgres 12 onward SET NOT NULL will see the validated check and skip its own full-table scan under ACCESS EXCLUSIVE. That scan is BV011, and it is the reason the three statements are worth the extra lines.
The principle underneath all three: any constraint whose validation needs a scan should be recorded first and validated seperately, so the scan runs under the weakest lock that is correct. Postgres gives you the syntax. It just does not make it the default.
Originally published at bolvrk.com. Bolvrk is a deterministic checker for database migrations and committed credentials: free CLI, GitHub App and Action.
