Do foreign key columns need an index in Postgres?
Foreign key without an index on the referencing columns
Note: this works and blocks nothing, but a performance regression is likely.
What happens
Postgres indexes the referenced side of a foreign key (it must be unique) but never the referencing side. Without that index, every UPDATE or DELETE on the parent table sequential-scans the child table to enforce the constraint, fine in dev, a scan-per-row regression in production.
Why it is dangerous on a populated table
Nothing blocks, but the cost scales with the table and shows up as a slower query plan or a longer maintenance window rather than an outage.
Fires on
ALTER TABLE orders ADD CONSTRAINT fk FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;The safe pattern
Index the referencing column(s): built CONCURRENTLY in its own migration so the build never blocks writes. With the index in place, parent-side UPDATE/DELETE enforce the constraint via index lookups instead of sequential scans of the child table.
-- In its own migration file, run outside a transaction:
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);Fixtures
The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.
Fires (1)
ALTER TABLE orders ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;Stays silent (1)
ALTER TABLE orders ADD CONSTRAINT orders_user_fk FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);How to check locally
This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:
npx bolvrk check migration.sql --remote