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

• :validate - explicit false disables this check.
• :rules - optional rule keyword list or non-empty list of rule keyword lists. Rules use only shared scope keys.

Run globally with defaults:

Bylaw.Db.Adapters.Postgres.Checks.MissingForeignKeyIndexes

Run only for matching rule scopes:

{Bylaw.Db.Adapters.Postgres.Checks.MissingForeignKeyIndexes,
 rules: [where: [schemas: ["public"]]]}

{Bylaw.Db.Adapters.Postgres.Checks.MissingForeignKeyIndexes,
 rules: [where: [schemas: ["public"]], except: [[tables: ["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.