PostgreSQL doesn't index your foreign keys

PostgreSQL creates an index automatically for PRIMARY KEY and UNIQUE. For a foreign key, it doesn't. This surprises people because MySQL/InnoDB indexes FKs on its own, and anyone coming from there assumes Postgres does the same.

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    total NUMERIC(12,2) NOT NULL
);

In this schema, orders.customer_id has no index. What that costs:

• Joins and lookups by customer (WHERE customer_id = ?) turn into a sequential scan over the orders table. At 10k rows nobody notices; at 10 million the endpoint that lists a customer's orders melts.
• DELETE and UPDATE on the parent table get expensive: for every row deleted in customers, Postgres has to check whether a child points at it. Without an index, that's a full scan of orders per check. It's the classic "delete that locks up the table".

The fix

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

In production, use CREATE INDEX CONCURRENTLY so you don't hold a write lock while it builds.

How to find the unindexed FKs in your database

SELECT c.conrelid::regclass AS table, a.attname AS column
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND a.attnum = ANY (i.indkey::int2[])
  );

Not every FK needs an index: if the column never shows up in a join, a filter or a cascading delete, the index just costs you on writes. But that's the rare case; when in doubt, index it.

Schemint flags unindexed FKs straight from your CREATE TABLE, alongside the JPA Entity, the Flyway migration and the ER diagram.