Use foreign keys, and index the referencing column

A foreign key stops orphan rows that point to deleted parents. It is cheap insurance, but it has one gap people often miss.

Postgres automatically indexes the referenced side (it must be a primary key or unique), but not the referencing column on the child table.

Without an index on the child column, deleting a parent or joining children scans the whole child table. On a large table, a single delete from orders can turn into a sequential scan of order_items.

Add an index on every foreign key column you join or delete through. To find missing ones:

1. Compare pg_constraint foreign keys to pg_index, or
2. Watch for sequential scans on child tables in EXPLAIN.

CREATE TABLE order_items (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id  bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  sku       text NOT NULL
);
CREATE INDEX order_items_order_id_idx ON order_items (order_id);