Every foreign key in a schema is declared ON DELETE CASCADE, and someone deletes one parent row with millions of descendants. What actually happens inside that transaction, and what are the operational risks?

seniormust knowfreq 52%

answer

Row-by-row deletes, not a metadata operation
Unindexed child FK → table scan per parent row
Locks held to commit → blocking + deadlocks
Undo growth blocks vacuum/purge; rollback costs again
Fix: index FKs, batch leaf-up, drop partitions, soft delete

basics

~20 s

The engine finds and deletes every descendant level by level in one transaction: real row deletes with index maintenance, undo and write-ahead logging, locks held until commit, and triggers firing. Risks are long lock waits and deadlocks, huge rollback/undo growth, replication lag, and a full scan per parent if the child foreign key column is unindexed.

solid answer

~60 s

A cascading delete is not a metadata operation. For each level the engine must locate the children — which is an index lookup only if the child's foreign key columns are indexed, and otherwise a full table scan per parent row — then delete each row individually: maintain every index on that table, write undo and redo records, fire triggers, and recurse into that table's own cascades.

Everything happens in one transaction, so the risks compound:

Row and gap locks on millions of rows are held to commit, blocking other writers and inviting deadlocks, because the cascade acquires locks in index order that no application path matches.
Undo/rollback and log volume balloon; a long transaction blocks vacuum or purge and inflates recovery time.
Row-based replication ships every deleted row, so replicas and change-data-capture consumers lag badly.
The blast radius is invisible at the call site — the true reach lives in the schema graph, including diamond-shaped paths.

Mitigations: index the child foreign key columns, delete in bounded batches from the leaves upward, partition and drop partitions, or soft-delete and purge asynchronously.

A cascading delete is not a metadata operation. For each level the engine must **locate** the children — which is an index lookup only if the child's foreign key columns are indexed, and otherwise a full table scan per parent row — then **delete each row individually**: maintain every index on that table, write undo and redo records, fire triggers, and recurse into that table's own cascades. Everything happens in one transaction, so the risks compound: - Row and gap locks on millions of rows are held to commit, blocking other writers and inviting deadlocks, because the cascade acquires locks in index order that no application path matches. - Undo/rollback and log volume balloon; a long transaction blocks vacuum or purge and inflates recovery time. - Row-based replication ships every deleted row, so replicas and change-data-capture consumers lag badly. - The blast radius is invisible at the call site — the true reach lives in the schema graph, including diamond-shaped paths. Mitigations: index the child foreign key columns, delete in bounded batches from the leaves upward, partition and drop partitions, or soft-delete and purge asynchronously.

code

sql · 8 lines

sql

-- repeat until zero rows affected, one short transaction per batch
DELETE FROM order_line
WHERE order_id IN (SELECT id FROM "order" WHERE customer_id = 42)
LIMIT 5000;

DELETE FROM "order" WHERE customer_id = 42 LIMIT 5000;

DELETE FROM customer WHERE id = 42;

go deeper

for a junior

Recognise that the delete removes rows in child tables too and that this can be slow on large tables.

for a middle

Describe the per-row work — index maintenance, logging, triggers, recursion — and the need for an index on the child foreign key columns.

for a senior

Cover locks held to commit, undo growth blocking vacuum or purge, replication lag, deadlock risk, and concrete mitigations such as leaf-up batching and partition drops.

for a principal

Treat it as a data-lifecycle design question: which entities may be deleted implicitly at all, partitioning for deletion, soft-delete plus asynchronous purge, and the effect on downstream consumers and retention obligations.

What the engine really does

ON DELETE CASCADE is convenient syntax over ordinary work. When the parent row is deleted, for each referencing foreign key the engine:

Finds the children matching the deleted key. If the child's foreign key columns are indexed, this is a range seek. If they are not — and unlike primary keys, foreign key columns are not automatically indexed on every engine — it is a full scan of the child table, repeated for each deleted parent row. This single omission turns a delete of 1,000 parents into 1,000 table scans and is the most common cause of a "mysterious" hour-long DELETE.
Deletes each child row individually. There is no bulk shortcut: each row is marked deleted, every index on that table has its entry removed, undo/rollback information is written so the transaction can be rolled back, and redo/write-ahead log records are written so the change is durable and replayable.
Fires that table's triggers, if any, per row.
Recurses, because the child's own foreign keys may cascade further.

All of it runs inside the caller's transaction. The statement returns only when the entire tree is gone.

Cascading chains and blast radius

Chains compose to arbitrary depth: customer → order → order_line → line_discount. They can also be diamond-shaped, with two paths reaching the same table. Nothing at the call site reveals this. DELETE FROM customer WHERE id = ? looks like a one-row statement and may be a nine-table, ten-million-row operation. Reviewing the reachable set means reading the schema graph, and the graph changes as new tables are added — a table added next quarter can silently join the blast radius of a delete written last year.

The operational failure modes

Lock footprint. Every deleted row is locked until commit; in engines using gap or next-key locking, ranges are locked too. Concurrent writers touching any of those rows block. Because the cascade walks rows in index order, it acquires locks in an order no application path uses, which makes deadlocks likely and hard to reproduce.

Transaction size. Undo/rollback segments grow to hold the before-images. A long-running delete keeps an old read view alive, which blocks vacuum in PostgreSQL or purge in InnoDB, so dead-row bloat accumulates across the whole database, not just the tables being deleted. If the statement is cancelled or fails midway, the rollback can take as long as the delete itself.

Log and replication amplification. Row-based replication and change-data-capture emit an event per deleted row. Millions of rows become millions of events funnelled through a single replication stream, so replicas fall behind, read-replica traffic sees stale data, and downstream consumers back up. The delete does not just cost the primary.

Timeout whiplash. Lock-wait or statement timeouts kill the delete partway, triggering a full rollback, after which someone retries and the cycle repeats — each attempt paying the cost twice.

Making large deletes safe

Index the child foreign key columns. This is the single highest-value fix; without it nothing else matters.
Batch from the leaves upward. Delete children in bounded chunks (a few thousand rows per transaction, in a loop with a key predicate), working up the tree, and delete the parent last. Each transaction is short, locks are released promptly, replication keeps pace, and the operation is resumable.
Partition for lifecycle. If deletion follows time or tenant boundaries, partition on that column and drop partitions. Dropping a partition is a metadata operation that skips row-by-row work entirely — the biggest available win, when the data model allows it.
Soft delete plus asynchronous purge. Mark the parent deleted immediately for user-visible latency, and let a background job remove rows in batches.
Reserve CASCADE for genuinely small child sets — the address rows of one customer, the options of one product — and use RESTRICT for anything whose children are unbounded, so a large deletion must be an explicit, planned operation rather than an accident.

Where cascade still earns its place

None of this argues for deleting children in application code by default. Engine-enforced cascades run on every write path, including migrations and ad-hoc fixes, and cannot be forgotten or raced. The judgement is about bounded versus unbounded child sets, not about whether declarative integrity is a good idea.

## What the engine really does `ON DELETE CASCADE` is convenient syntax over ordinary work. When the parent row is deleted, for each referencing foreign key the engine: 1. **Finds the children** matching the deleted key. If the child's foreign key columns are indexed, this is a range seek. If they are not — and unlike primary keys, foreign key columns are *not* automatically indexed on every engine — it is a full scan of the child table, repeated for each deleted parent row. This single omission turns a delete of 1,000 parents into 1,000 table scans and is the most common cause of a "mysterious" hour-long DELETE. 2. **Deletes each child row individually.** There is no bulk shortcut: each row is marked deleted, every index on that table has its entry removed, undo/rollback information is written so the transaction can be rolled back, and redo/write-ahead log records are written so the change is durable and replayable. 3. **Fires that table's triggers**, if any, per row. 4. **Recurses**, because the child's own foreign keys may cascade further. All of it runs inside the caller's transaction. The statement returns only when the entire tree is gone. ## Cascading chains and blast radius Chains compose to arbitrary depth: customer → order → order_line → line_discount. They can also be diamond-shaped, with two paths reaching the same table. Nothing at the call site reveals this. `DELETE FROM customer WHERE id = ?` looks like a one-row statement and may be a nine-table, ten-million-row operation. Reviewing the reachable set means reading the schema graph, and the graph changes as new tables are added — a table added next quarter can silently join the blast radius of a delete written last year. ## The operational failure modes **Lock footprint.** Every deleted row is locked until commit; in engines using gap or next-key locking, ranges are locked too. Concurrent writers touching any of those rows block. Because the cascade walks rows in index order, it acquires locks in an order no application path uses, which makes deadlocks likely and hard to reproduce. **Transaction size.** Undo/rollback segments grow to hold the before-images. A long-running delete keeps an old read view alive, which blocks vacuum in PostgreSQL or purge in InnoDB, so dead-row bloat accumulates across the whole database, not just the tables being deleted. If the statement is cancelled or fails midway, the rollback can take as long as the delete itself. **Log and replication amplification.** Row-based replication and change-data-capture emit an event per deleted row. Millions of rows become millions of events funnelled through a single replication stream, so replicas fall behind, read-replica traffic sees stale data, and downstream consumers back up. The delete does not just cost the primary. **Timeout whiplash.** Lock-wait or statement timeouts kill the delete partway, triggering a full rollback, after which someone retries and the cycle repeats — each attempt paying the cost twice. ## Making large deletes safe - **Index the child foreign key columns.** This is the single highest-value fix; without it nothing else matters. - **Batch from the leaves upward.** Delete children in bounded chunks (a few thousand rows per transaction, in a loop with a key predicate), working up the tree, and delete the parent last. Each transaction is short, locks are released promptly, replication keeps pace, and the operation is resumable. - **Partition for lifecycle.** If deletion follows time or tenant boundaries, partition on that column and drop partitions. Dropping a partition is a metadata operation that skips row-by-row work entirely — the biggest available win, when the data model allows it. - **Soft delete plus asynchronous purge.** Mark the parent deleted immediately for user-visible latency, and let a background job remove rows in batches. - **Reserve CASCADE for genuinely small child sets** — the address rows of one customer, the options of one product — and use RESTRICT for anything whose children are unbounded, so a large deletion must be an explicit, planned operation rather than an accident. ## Where cascade still earns its place None of this argues for deleting children in application code by default. Engine-enforced cascades run on every write path, including migrations and ad-hoc fixes, and cannot be forgotten or raced. The judgement is about **bounded versus unbounded child sets**, not about whether declarative integrity is a good idea.

Why is an index on the child's foreign key columns so critical for cascading deletes?
To cascade, the engine must find the rows referencing the deleted parent key. With an index that is a range seek; without one it scans the entire child table, once per deleted parent row. Some engines create such an index automatically with the constraint and others do not, so it must be verified rather than assumed.
How would you delete one tenant's data across twelve cascading tables without an outage?
Avoid one giant transaction. Batch from the leaf tables upward, deleting a few thousand rows per transaction in a loop keyed on the tenant column, with a short pause so replication and vacuum keep up, and remove the parent row last. If the schema is partitioned by tenant, dropping partitions replaces the whole exercise with a metadata operation.
Why can a cascading delete deadlock with ordinary application traffic?
The cascade locks child rows in the order the engine walks its index, which is unlikely to match the order application transactions lock rows. Two transactions then hold locks each other needs, in opposite order, and the engine kills one. Batching by the same key order the application uses, and keeping transactions short, reduces the exposure.

saying these in an interview costs you the question

Believing a cascading delete is a cheap metadata operation
Assuming foreign key columns are always automatically indexed
Ignoring that the whole cascade runs in one transaction with locks held to commit
Overlooking replication and change-data-capture amplification
Concluding cascades are always bad rather than distinguishing bounded from unbounded child sets

Every foreign key in a schema is declared ON DELETE CASCADE, and someone deletes one parent row with millions of descendants. What actually happens inside that transaction, and what are the operational risks?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

What the engine really does

Cascading chains and blast radius

The operational failure modes

Making large deletes safe

Where cascade still earns its place

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions7 questions

▾related topics4 topics

What the engine really does

Cascading chains and blast radius

The operational failure modes

Making large deletes safe

Where cascade still earns its place

answer

go deeper

context