Indexing Foreign Keys

~10 min · indexes, foreign-keys

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The most common missing index

PostgreSQL creates indexes automatically for primary keys and unique constraints. It does not create them for foreign keys. This catches teams over and over: queries on the FK column are slow, and worse, every parent DELETE has to scan the entire child table looking for references.

Always do this

For every ... REFERENCES parent(id), add the matching index. It's so consistent that I add the index in the same migration as the FK declaration — never separately.

How to find unindexed FKs

One catalog query reveals every foreign key in the database that lacks an index on the referencing column. Run it on every database you operate.

Code

Always pair FK + index·sql

CREATE TABLE orders (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    placed_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

Find FKs missing indexes·sql

SELECT
    c.conrelid::regclass AS table_name,
    string_agg(a.attname, ', ') AS columns
FROM   pg_constraint c
JOIN   pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE  c.contype = 'f'
GROUP  BY c.oid, c.conrelid
HAVING NOT EXISTS (
    SELECT 1
    FROM   pg_index i
    WHERE  i.indrelid = c.conrelid
      AND  (c.conkey::int[]) <@ (i.indkey::int[])
);
-- Returns every FK whose referencing columns aren't covered by any index.

External links

Index Your Foreign Keys

Exercise

Run the catalog query above on a real database. For every FK without an index, decide whether to add one. Most should get an index; rare exceptions (lookup tables that never have rows deleted) can stay.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousGIN Indexes for JSONB and Full-Text Next →Covering Indexes (INCLUDE)

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.