cubic
Try cubic for free

Index Foreign Keys and New Query Filters in PostgreSQL

Flags PostgreSQL foreign keys without an index on the referencing columns, and new queries that filter or sort large tables on unindexed columns.

Catch this in your code

What it flags

Flaggedmigrations/20260920_invoice_line_items.sql
CREATE TABLE invoice_line_items (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  invoice_id bigint NOT NULL REFERENCES invoices (id) ON DELETE CASCADE, -- not indexed
  product_id bigint NOT NULL REFERENCES products (id),
  description text NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  unit_price_cents bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
Passesmigrations/20260920_invoice_line_items.sql
CREATE TABLE invoice_line_items (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  invoice_id bigint NOT NULL REFERENCES invoices (id) ON DELETE CASCADE,
  product_id bigint NOT NULL REFERENCES products (id),
  description text NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  unit_price_cents bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX invoice_line_items_invoice_id_idx
  ON invoice_line_items (invoice_id);

CREATE INDEX invoice_line_items_product_id_idx
  ON invoice_line_items (product_id);

Why it matters

PostgreSQL requires a unique index on the referenced columns but does not create one on the referencing columns. Without it, every delete of a parent row, or update of its key, scans the child table to enforce the constraint, and a bulk delete or cascade repeats that scan for each parent row. New filters and sorts fail the same way: fast on a development table with a few hundred rows, and a sequential scan on every request at production size.

Agent instructions

cubic checks every pull request against these instructions. Paste them into AGENTS.md or Cursor rules and your AI coding tool follows them while it writes code.

AGENTS.md

Add this section to AGENTS.md at the repository root. Codex, Cursor, and other AI coding tools read it.

## Index Foreign Keys and New Query Filters in PostgreSQL

Applies to `**/*.sql`, `**/migrations/**`, `**/migrate/**`.

Review PostgreSQL schema changes and SQL queries for foreign keys and new query patterns that lack a usable index.

Flag changes that:
- Add a foreign key whose referencing columns are not the leading columns of any index on that table. PostgreSQL does not index the referencing side automatically, so each parent delete or key update, including an `ON DELETE CASCADE`, scans the child table.
- Drop or replace the only index whose leading columns cover a foreign key.
- Add a query, view, or function that filters, joins, or sorts on columns of a large table with no index whose leading columns match, such as `WHERE status = $1 ORDER BY created_at DESC LIMIT 50`.
- Add an index the new query cannot use: columns in the wrong order for the filter and sort, an index on `email` for a filter on `lower(email)`, or a partial index whose predicate the query's `WHERE` clause does not imply.
- Search a large table with `LIKE` or `ILIKE` and a leading wildcard, which a B-tree index cannot serve; a `pg_trgm` GIN or GiST index can.

Do not flag:
- Foreign keys covered by the leading columns of an existing index, including a composite primary key such as `PRIMARY KEY (order_id, product_id)` for a key on `order_id`.
- Child tables that stay small, such as lookup or configuration tables.
- One-off maintenance or analytics queries that do not run on a request path.

Checks files matching **/*.sql, **/migrations/**, **/migrate/**.

More PostgreSQL agents

See all PostgreSQL code review standards

Run this agent on every pull request

cubic reviews every pull request against the agents you turn on and comments on the lines that break them.

Catch this in your code