Cookbook recipe

How foreign keys are enforced (and the index you forgot)

Applies to PostgreSQL 13–17 Last reviewed May 2026 Grounded in source

What This Is

A production experience capsule based on real incident patterns.

Why It Matters

Builds production judgment under pressure.

Next Step

Run the decision point → · Enter full simulator →

Estimated investigation4 min

Scenario

Deletes on a parent table are mysteriously slow. The child table's foreign key has no index, so every parent delete scans the child. Diagnose it Find FK columns missing an index on the child side: --…

Investigation Path

Deletes on a parent table are mysteriously slow. The child table’s foreign key has no index, so every parent delete scans the child.

Diagnose it

Find FK columns missing an index on the child side:

-- check that child(fk_col) has an index
EXPLAIN DELETE FROM parent WHERE id = 1;  -- watch for seq scan on child

Why it happens

A foreign key is enforced by checking the referenced/referencing rows. PostgreSQL auto-indexes the parent’s referenced key (it must be unique) but NOT the child’s referencing column — so cascade/restrict checks scan the child unless you add that index yourself.

This is a Pro lesson

Get every Learning Pathway and cookbook recipe — grounded in PostgreSQL source code, with diagnostics, fixes, and prevention for each topic.

Continue this lesson to learn:

  • How to fix it
  • Prevent it next time
  • Related & next steps
  • All 36 Learning Pathway lessons
  • 170+ cookbook recipes
  • Source-grounded diagnostics & fixes
Unlock everything — $24.99/month $199/year — save 35%

Secure checkout Cancel anytime Source-grounded

Career Impact

This scenario builds production judgment and operational confidence under pressure.

Open Career Dashboard →

Keep going

Related & next steps

More like this

Password Authentication Failed shared_buffers Too Small Partition a growing table with declarative partitioning pg_stat_activity Gaps: Queries Not Visible CREATE INDEX CONCURRENTLY: Building Indexes Online

Concepts on this page

Foreign key Index
Browse the cookbook Step-by-step recipes Follow a pathway Learn it in order Open the glossary Every concept, plainly
Was this helpful?

← All cookbook recipes