require-index-on-fk

What It Detects

Foreign key columns without an index cause slow cascading deletes and joins.

Why It's Dangerous

Without an index on the FK column, PostgreSQL must do a sequential scan on the referencing table for every DELETE on the referenced table. This causes lock escalation and slow cascading deletes.

Bad Example

ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id);
-- No index on orders.user_id!

Good Example

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;

Configuration

Disable this rule:

# .migrationpilotrc.yml
rules:
  MP016: false

Or change its severity:

# .migrationpilotrc.yml
rules:
  MP016:
    severity: warning