Index the foreign key you forgot
PostgreSQL indexes the parent key of a foreign key but never the child column. Without it, deleting or updating a parent scans the whole child table, a delete's FK trigger drops from 22 ms to 0.2 ms once the index exists.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
A FOREIGN KEY creates an index on the referenced (parent) column automatically, but not on the referencing (child) column. Every parent DELETE or key UPDATE then has to scan the child table to enforce the constraint.
Meridian's parcels reference couriers. Deleting one courier forces PostgreSQL to check parcels for children, and with no index on parcels.courier_id, that check is a full scan hidden inside a trigger.
Simple terms
Setting up a foreign key makes PostgreSQL index the parent side automatically, but not the child column that points back at it. So every time you delete or re-key a parent row, the database has to scan the entire child table to make sure nothing still references it, and that scan is hidden inside a constraint trigger, so it never shows up in your query. Put an index on the child column and that check turns into an instant lookup.
Full runbook for this incident
- The full identify checklist, the exact signals that tell you it's this incident
- Every diagnostic query; lab output is attached only to the steps we actually captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
Card required. Cancel before day 7 and you are not charged.
More in this category
Other Indexing runbooks
Neighbouring incidents that share the same diagnostic surface.
Connected
How this connects to the rest of the library
A live view of this page's real cross-references, what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.
Need the full procedure?
Pro runbooks finish the incident path
Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.