The offboarding job was supposed to be the boring kind of deletion: remove one tenant and let the foreign keys clean up behind it. Instead, a single DELETE FROM tenants WHERE id = 412 ran for forty-five minutes, and while it ran, every write touching the events table queued up behind it, p99 latency across the API climbed past two seconds, and the on-call phone lit up. The tenant row was one row. The DELETE statement was one statement. What neither of us had priced in was the foreign key trigger it fired: for every row deleted on the parent side, PostgreSQL checks the referencing tables for orphans, and the events table — ninety million rows, with a tenant_id column and no index on it — paid for that check with a full sequential scan. Inside our transaction. While we held locks.
This is the most quietly expensive schema mistake I see in PostgreSQL reviews, and it is invisible until the day a parent row actually gets deleted or its key updated. Here is why the database lets you build it, how to find every instance of it in your schema, and how to size the blast radius before you trust a cascade.
Why does PostgreSQL only index one side of a foreign key?
Because it has to, and because it cannot guess. The referenced side — the parent — must have a unique index, because the foreign key constraint requires the target column set to be unique, and PostgreSQL enforces that with a real index you can see in pg_indexes. The referencing side — the child column that points at the parent — gets nothing. No index, no warning, not even a log line at constraint creation time. From the database's perspective that is a defensible choice: plenty of child tables are write-mostly, small, or rarely touched by parent deletes, and an index on every foreign key column would be write amplification for no proven benefit. From an operator's perspective it is a trap, because the cost is deferred to the worst possible moment — the first parent deletion against a large child, which usually happens during an incident, an offboarding, or a GDPR purge, never during a quiet Tuesday when you would have noticed the scan in testing.
The mechanics matter for the diagnosis. When you delete a parent row, PostgreSQL fires a referential-integrity trigger that runs a check query against the child: does any row still reference this key? With an index on the child column, that check is a fast index probe. Without one, it is a sequential scan of the child table — once per deleted parent row. Delete ten thousand parents against an unindexed ninety-million-row child and you have not written a cleanup job, you have written a table-scan generator. Updates of the parent's key columns trigger the same check, which is why the canonical advice "never update primary keys" exists; updates that do not touch the key columns do not fire the check at all, because the trigger is column-aware.
What does the lock queue look like while the scan runs?
Longer than you want, in both senses. The trigger query executes inside your transaction, so every lock your DELETE already took is held for the full duration of every child scan. The row locks on the parent are held; the access locks on each child table are held; and if the constraint is ON DELETE CASCADE, the child deletions themselves take row locks on child rows that other sessions may be trying to read for update or delete. Our forty-five minutes was not just forty-five minutes of one slow statement — it was forty-five minutes of transactions piling into lock waits, each new waiter adding to the queue that lock and deadlock diagnosis teaches you to read in pg_locks. The session-level lesson I took away: a slow parent delete does not degrade gracefully, it amplifies, because every second it runs converts more of your write traffic into waiters. A statement_timeout on the offboarding role would have turned our incident into a retried job, and these days every bulk-deletion path gets one.
How do you find every unindexed foreign key in the schema?
You ask the catalog directly. pg_constraint holds every foreign key with its child column list in conkey, pg_index holds every index's column list in indkey, and the check is: for each foreign key, does some index on the child table have the constraint's columns as its leading columns? Order matters — a composite foreign key on (tenant_id, created_at) is served by an index on (tenant_id, created_at, anything) but not by one on (created_at, tenant_id).
SELECT con.conname AS constraint_name,
con.conrelid::regclass AS referencing_table,
con.confrelid::regclass AS referenced_table,
pg_get_constraintdef(con.oid) AS definition
FROM pg_constraint con
WHERE con.contype = 'f'
AND NOT EXISTS (
SELECT 1
FROM pg_index i
WHERE i.indrelid = con.conrelid
AND (i.indkey::int2[])[0:array_length(con.conkey, 1) - 1]
= con.conkey::int2[]
)
ORDER BY pg_total_relation_size(con.conrelid) DESC;Run it ordered by child-table size, because that is the order of pain. Two caveats from running this against real schemas. First, indkey contains zeros for expression columns, so an expression index can make this query conservative — it reports a missing index when a functional one would actually serve the trigger; treat the output as a review list, not an indictment. Second, not every result deserves an index. A child table of two thousand rows scanned twice a year is fine unindexed; a ninety-million-row events table is not. The fix, when the table is big enough to matter, is CREATE INDEX CONCURRENTLY on exactly the foreign key columns — the btree index notes cover why the plain form is a table-lock incident of its own on a hot table. And if the same column already leads some wider composite index, you may need nothing at all: the trigger only needs the columns to be a leftmost prefix.
How do you size a cascade before trusting it in production?
You run the delete inside a transaction you intend to roll back, with EXPLAIN ANALYZE in front of it, and you read the trigger timing lines at the bottom of the plan. This is the single most useful trick in this article:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM tenants WHERE id = 412;
ROLLBACK;The plan output includes one line per referential-integrity trigger that fired, with real elapsed time and call counts — that is how we finally saw that forty-three of our forty-five minutes belonged to a single trigger on events, and not to the seventeen other constraints that fired and finished in milliseconds. ROLLBACK undoes the whole thing, so the rehearsal costs nothing but the scan time itself; on a truly huge child I run it against a restored copy instead, since even a rolled-back seq scan is still a seq scan. The number you are extracting is the cascade's blast radius: how many child rows die with each parent, and how long the lock window lasts. ON DELETE CASCADE is not wrong — it is often exactly the right model — but it converts a one-row delete into an unbounded delete, and the honest question to ask in review is "what is the largest number of child rows this could ever remove, and have we counted them?" ON DELETE SET NULL and SET DEFAULT run the same child-table check, so they need the same index; there is no cascade action that escapes the scan.
What goes in the schema review checklist?
Three lines, all cheap. Every new foreign key ships with its child-side index or a written reason it does not need one. Every cascade ships with a measured blast radius from the rollback trick. And the detection query above runs in CI against the migrated schema, because the failure mode of this bug is that it is added innocently in a feature branch and discovered months later by the offboarding job. Our incident ended with one CREATE INDEX CONCURRENTLY that took eleven minutes to build and reduced the same tenant deletion to ninety seconds — the index was all that was ever missing, and the database had never once suggested it.
Watching constraint costs with MonPG
The symptoms of an unindexed foreign key are all counter-shaped: a parent-table DELETE statement whose mean time in pg_stat_statements is wildly out of proportion to its rows affected, lock waits climbing in pg_stat_activity while it runs, and sequential-scan share creeping up on child tables that should be index-served. MonPG graphs exactly these series — per-statement latency, lock contention, and scan mix per table — as part of its PostgreSQL monitoring, which is how the forty-five-minute delete would have shown up in the first minute: one statement, one trigger, one wall of waiters behind it. Add the detection query to CI and let the dashboard confirm the fix; that combination has kept this class of incident from recurring for us.