A foreign key guarantees no orphans
Every non-null customer_id in orders exists in customers, after every statement, for every writer. A NULL escapes the check, so add NOT NULL when the link is mandatory.
PostgreSQL indexes foreign keys?
Bad: It indexes both sides
Only the parent key gets one.
Good: Index the child yourself
InnoDB creates one. In PostgreSQL, each parent delete otherwise scans the child table.
On delete, pick the action from the relationship
Parts of the parent cascade. Independent records restrict. Optional links set NULL.
A merchant leaves, and the delete cascades to 2M orders and 6M lines.
One statement
8M rows in one transaction: ≈ 800 MB of WAL and 8M row locks. Restrict at the top; delete children in batches.
Inserting a child locks its parent row
PostgreSQL takes KEY SHARE, so ordinary parent updates go ahead. InnoDB takes a shared lock, so every update to that parent waits.
40
transactions locking one merchant row at once
2,000 orders a second, each in a 20 ms transaction. In InnoDB, updating that merchant can time out waiting. Keep busy fields off hot parents.
Why large MySQL shops drop foreign keys
- Online schema tools: gh-ost doesn’t support them
- Sharding: keys work only inside one database
- Services: payments.order_id is just an ID
- Hot parents: a lookup and lock per write
Remember
Give every in-database relationship a foreign key, index its child side, and choose its delete action deliberately.