A DELETE that removes a single row from a parent table occasionally runs for minutes and blocks other sessions. Explain how deleting one row can generate a very large amount of write work, and how you would bound the blast radius.

## Where the work comes from A cascading delete is a graph traversal executed inside your transaction. Deleting parent row *P* means: 1. Find every child row referencing *P* in every table with a cascading FK to it. 2. Delete each of those, which recursively repeats step 1 for their own children. 3. Delete *P*. The row count is the product of fan-outs down the chain. A customer with 10,000 orders, each with 5 lines and 2 shipment records, is 10,000 + 50,000 + 20,000 = 80,000 rows removed by a statement that names one row. Each removed row costs: - **Table modification** — marking the row dead and, in MVCC engines, leaving a dead version that later cleanup must reclaim. - **Index maintenance for every index on that table.** A child table with six indexes pays six index operations per deleted row. This is usually the dominant term. - **Log records** for the row and every index change, which must be written and flushed, then shipped to replicas. - **A row lock**, held until the enclosing transaction commits, because the whole cascade is one atomic statement. - **Trigger execution**, per row, for any row-level triggers on the child tables — including audit triggers that write yet more rows. ## The hidden second multiplier: finding the children To cascade, the engine must locate referencing rows. It does that with an ordinary query on the child's FK columns. If those columns are not indexed, locating children of one parent is a **full scan of the child table**. Cascade one level deeper and you scan the grandchild table for every child row deleted. This is how a delete of one row turns into hours: not the deletes themselves, but nested scans. The tell is a plan showing sequential scans under the cascade, or a delete whose cost grows with total table size rather than with the number of rows actually removed. ## Why it blocks other sessions Everything above happens in one transaction. Consequences: - **Lock hold time.** 80,000 row locks (plus their index entries) are held for the whole duration. Anything touching those rows waits. Some engines escalate to coarser locks under pressure, widening the blocking further. - **Lock memory.** Very large cascades can exhaust the lock table or transaction memory outright, aborting the statement after doing all the work. - **Version/undo growth.** In MVCC engines, dead versions accumulate and the cleanup process has more to do afterwards; long transactions also hold back the cleanup horizon, so *unrelated* tables stop being reclaimable while the delete runs. - **Replication lag.** The log burst applies serially on replicas, so a two-minute cascade on the primary can be a much longer stall on a single-threaded apply path. - **Rollback cost.** If it fails at minute nine, undoing it can take as long as doing it. ## Diagnosing it - Map the FK graph downward from the table being deleted and count the fan-out at each level. Multiply. - Check that every referencing FK column is indexed, at every level, not just the first. - Look at the plan for the delete; nested scans under the cascade point straight at the missing index. - Look for triggers on child tables — audit triggers turn a delete into an insert-heavy workload. ## Bounding the blast radius **Index every FK column.** This is the cheapest and biggest win. It converts nested scans into index lookups, which turns hours back into minutes. **Do not let high-fan-out relationships cascade automatically.** For relations where one parent owns millions of rows, make the parent's delete refuse (restrict) and force an explicit, controlled cleanup path. Automatic cascade is convenient exactly where it is safe — small fan-out — and dangerous exactly where it is not. **Batch it yourself, bottom-up.** Delete the deepest level first in chunks of a few thousand rows with a commit per chunk, working upward, ending with the parent. Each transaction is short, locks are released promptly, log volume is spread, and replicas keep up. The cost is that the data is temporarily half-deleted; make that safe by marking the parent inactive first, so nothing reads the partial state. **Do not delete at all.** Soft delete or archive-and-detach is often the right answer for large hierarchies. The parent gets a status flag; physical removal happens offline, or never. **Make removal a metadata operation.** If the child data is partitioned along the same dimension you retire by (time, tenant), retirement becomes dropping or detaching a partition — near-constant cost, no per-row work, no index maintenance, no log flood. This is by far the strongest structural answer when the deletion pattern is known in advance. **Guard against surprises.** Before an operational delete, count what it will remove and abort if the count exceeds a threshold. A one-line statement that removes 80,000 rows should never be a surprise in production. ## The compressed answer "One delete is really the transitive closure of the FK graph beneath it — every removed row pays index maintenance, logging and a lock held to commit, and if any child's FK column is unindexed each level becomes a nested scan. I'd index the FK columns, refuse to auto-cascade high-fan-out relations, delete bottom-up in committed batches, and where retirement is predictable make it a partition drop instead."