R005 · default CAUTION

Missing Foreign Key Index

Postgres does not auto-create an index on the child side of a foreign key. Without one, JOINs and DELETEs on the parent are sequential scans.

Why this is a problem

This is the #1 silent performance issue in Postgres apps. Missing FK index → DELETE on parent triggers seq scan on child to enforce referential integrity. On a 240M-row child, every parent DELETE is a 47-second query.

Don't do this

-- FK without backing index — slow joins, slow parent deletes
ALTER TABLE orders
  ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

Do this instead

-- Add the index FIRST (concurrently to avoid lock)
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders (user_id);

-- Then add the FK
ALTER TABLE orders
  ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

References

• Postgres FK without index — common cause of slow DELETE →