Validates that Postgres foreign keys have supporting indexes.

Examples

Before, the foreign key exists but the referencing column has no index:

CREATE TABLE accounts (
  id uuid PRIMARY KEY
);

CREATE TABLE orders (
  id uuid PRIMARY KEY,
  account_id uuid NOT NULL REFERENCES accounts(id)
);

Deletes or primary-key updates on accounts can become slow because Postgres must scan orders to enforce the foreign key.

After, add an index whose leading columns are the foreign key columns:

CREATE INDEX orders_account_id_index ON orders (account_id);

Postgres can enforce the relationship with an index lookup instead of a table scan.

Notes

The supporting index does not have to be unique, and it may include extra trailing columns such as (account_id, inserted_at). Partial indexes such as WHERE deleted_at IS NULL do not count as support for the foreign key.

Options

By default the check inspects all non-system schemas in a Postgres target. Use schemas: [...] or tables: [...] for simple filtering:

{Bylaw.Db.Adapters.Postgres.Checks.MissingForeignKeyIndexes,
 schemas: ["public"],
 tables: ["orders", "line_items"]}

Use rules: [...] when the scope needs matchers or exclusions:

{Bylaw.Db.Adapters.Postgres.Checks.MissingForeignKeyIndexes,
 rules: [
   [
     only: [schema: "public"],
     except: [[table: "audit_events"]]
   ]
 ]}

A foreign key passes when the referencing table has a valid, non-partial index whose leading columns contain the foreign key columns.

Usage

Add this module to the checks passed to Bylaw.Db.Adapters.Postgres.validate/2. See the README usage section for the full ExUnit setup.