Unindexed Foreign Keys.

🐛The symptom

DELETE FROM customers WHERE id = 91422;

One row. Primary key. Indexed. Staging: 3ms. Production: 41 seconds — and while it runs, order inserts pile up behind it.

The plan looks innocent:

Delete on customers (actual time=0.041..0.042 rows=0)
  -> Index Scan using customers_pkey (actual time=0.019..0.021 rows=1)
Trigger for constraint orders_customer_id_fkey: time=40912.338 calls=1

🔍Read the last line again

The scan took 0.02ms. The 40.9 seconds is in a trigger. Postgres enforces foreign keys with internal triggers: before releasing the parent row it must prove no child still points at it —

SELECT 1 FROM orders WHERE customer_id = 91422 FOR KEY SHARE;

No index on orders.customer_id → sequential scan over 80M rows. Per parent row deleted.

💡The concept

Declaring a foreign key indexes nothing on the child side. Postgres requires a unique index on the parent key and gives you zero on the referencing column. (MySQL/InnoDB is the odd one out — it auto-creates one. Postgres, Oracle and SQL Server do not.)

So every FK you declare is a latent full scan on three paths:

• DELETE of a parent row
• UPDATE of the parent's key column
• the join you write every day: WHERE customer_id = ?

🔧The fix

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

41s → 0.3ms.

Find every one of them:

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

⚠️What practitioners get wrong

1. "My composite index covers it." Only if the FK column is the LEFTMOST column. An index on (status, customer_id) does nothing for the FK check.
2. It's the locks, not just the clock. That check takes FOR KEY SHARE row locks on matching children, so one slow parent delete blocks concurrent updates to those rows — the damage spreads past the one query.
3. ON DELETE CASCADE makes it worse, not better. It still has to FIND the children, then delete them, firing their own FK checks one level down.
4. Don't blanket-index every FK either. A high-write child pointing at a parent you never delete (country_id → a static lookup table) pays index-maintenance cost on every insert for a check that never scans. Index the FKs whose parents get deleted or re-keyed, plus the ones you join on — in practice, most of them.

🎯Your exercise

Run that catalog query against your main database. For each row it returns, answer one question: "does anything ever delete from the parent table?" If yes, you've found a 40-second query waiting for a bad day. Pick the worst one and time it: EXPLAIN (ANALYZE) a delete inside a transaction you ROLLBACK, and read the Trigger line — not the scan line.