Why does a mapper sometimes delete every row behind a mapped collection and reinsert them instead of one targeted statement?

seniorshould knowfreq 50%

answer

the layer writes what it can compare
replacing the instance discards the snapshot
fallback is remove-all then reinsert
cost scales with the collection, not the change
mutate in place: add and remove

basics

~10 s

Because it can no longer compute a difference. Assigning a new collection to the mapped field discards the snapshot the layer diffed against, leaving a wholesale delete-and-reinsert as the only safe write.

solid answer

~50 s

A tracking layer writes a collection either by diffing it against the snapshot taken at load — one insert per added element, one delete per removed one — or by rewriting it wholesale. The rewrite is the fallback when the diff is impossible: most often the code assigned a fresh collection to the mapped field, so the instance the layer was watching is gone. Unstable element identity between load and flush causes the same thing, as can a stored shape with no addressable row. The cost is statements proportional to the collection rather than to the change, a lock taken on every row of the link, and triggers or change-capture streams firing for rows nothing actually touched. The fix is to mutate the loaded collection — add and remove — instead of replacing it; for very large link sets, write the rows with direct statements instead.

A tracking layer writes a collection either by **diffing** it against the snapshot taken at load — one insert per added element, one delete per removed one — or by **rewriting** it wholesale. The rewrite is the fallback when the diff is impossible: most often the code assigned a fresh collection to the mapped field, so the instance the layer was watching is gone. Unstable element identity between load and flush causes the same thing, as can a stored shape with no addressable row. The cost is statements proportional to the collection rather than to the change, a lock taken on every row of the link, and triggers or change-capture streams firing for rows nothing actually touched. The fix is to mutate the loaded collection — add and remove — instead of replacing it; for very large link sets, write the rows with direct statements instead.

code

sql · 7 lines

sql

-- collection instance replaced: the layer rewrites the whole set
DELETE FROM order_tag WHERE order_id = 42;
INSERT INTO order_tag (order_id, tag_id) VALUES (42, 7);
INSERT INTO order_tag (order_id, tag_id) VALUES (42, 9);

-- collection mutated in place: the layer writes only the difference
INSERT INTO order_tag (order_id, tag_id) VALUES (42, 9);

go deeper

for a junior

Take away one habit: after loading an object, add to and remove from the collection it gave you. Do not assign a new collection to that field, even when the new one holds exactly the right contents.

for a middle

Explain the mechanism — the layer holds a snapshot bound to the collection instance it handed out, and replacing that instance leaves it nothing to compare, so it falls back to removing and rewriting the set.

for a senior

Read the statement log and diagnose it: identify the replacement in the update path, quantify the extra statements and the widened lock footprint, and note which triggers or change streams fired for rows that never changed.

for a principal

Set the boundary for the object path. Decide at what link-set size collections stop being mapped at all and become explicit statements, so the write cost of a save is bounded by the change rather than by the largest row in the data.

When a mapped collection changes, a layer with change tracking has two ways to turn that into statements. It can diff — compare what the collection holds now against what it recorded at load time, and emit an insert per added element and a delete per removed one. Or it can rewrite — delete every stored row behind the collection and insert the current contents back. The rewrite is correct, and it is what the log shows as DELETE FROM link WHERE parent_id = ? followed by a burst of inserts, most of them putting back rows that were already there.

What makes the diff possible

The diff needs two things: the original snapshot of the collection, and a way to recognise the same element in both versions.

The layer takes its snapshot when it loads the collection, and it holds it against the collection instance it handed to the code. It watches that instance for adds and removes.
Element recognition comes from the element's identity — for a child row, its key; for a junction link, the pair of keys in the row.

What breaks it

The collection instance was replaced. Assigning a brand-new collection to the mapped field throws away the instance the layer was watching. It now holds a snapshot for an object nobody is using and a fresh collection with no history, and the only correct thing it can do without re-reading is remove everything it knew about and write what is there now.
Elements have no stable identity while the code holds them. If equality and hashing over an element change between load and flush, the diff sees removals and additions where nothing moved.
The stored shape has no addressable row. An unordered link table with duplicates allowed, or a positional collection whose index is itself stored, can force a rewrite because a single row cannot be targeted or because every position after a removal shifts.

	Collection mutated in place	Collection instance replaced
What the layer has	its snapshot plus the instance it is watching	a snapshot for an abandoned instance
Statements for adding one element	one insert	delete all rows, then insert all elements
Cost with 500 elements	1 statement	roughly 501 statements
Rows the log touches	one	every row of the link

Why the rewrite hurts

Statement volume. Every save costs statements proportional to the whole collection, not to the change, and this scales with the largest collection in the data rather than with the workload.
Lock footprint. Deleting every row of the link takes locks on all of them for the rest of the transaction, so two units of work touching overlapping sets now conflict — and because the deletes and inserts arrive in the layer's order, this is a classic source of deadlocks between concurrent savers.
Side effects fire. Triggers, audit rows and change-capture streams see a delete and an insert for rows that never changed, so downstream consumers get churn that did not happen in the domain.
It happens on saves that changed nothing. If the field is reassigned on every update — a common shape when an update method builds a fresh collection from a request payload — the rewrite runs even when the contents are identical.

Keeping the targeted path

Mutate what the layer loaded. Add and remove elements on the existing collection; never assign a new one to the mapped field after load.
Compute the difference in the code when the input arrives as a whole set: work out which elements are new and which are gone, then apply those two sets to the loaded collection.
Expose add and remove methods and keep the field's setter out of the public surface, so a well-meaning update path cannot reassign it.
Keep element identity stable for the lifetime of a unit of work, so the diff recognises unchanged elements.
Leave the mapped collection out of the write path entirely for very large link sets, and write the link rows with direct statements — a single insert-select or a delete with a narrow predicate beats any object-level path at that size.

What an interviewer is listening for

The candidate should read the log correctly — this is not a bug, it is the layer's fallback when it cannot compute a difference — and name the replacement of the collection instance as the usual trigger. The strong answer goes on to the cost profile: statements proportional to the collection, the widened lock footprint, and the spurious downstream events. The best answers add the honest boundary: layers vary in how aggressively they fall back, and once a link set is large enough, the object-level path is the wrong tool regardless of how well the diff works.

When a mapped collection changes, a layer with change tracking has two ways to turn that into statements. It can **diff** — compare what the collection holds now against what it recorded at load time, and emit an insert per added element and a delete per removed one. Or it can **rewrite** — delete every stored row behind the collection and insert the current contents back. The rewrite is correct, and it is what the log shows as `DELETE FROM link WHERE parent_id = ?` followed by a burst of inserts, most of them putting back rows that were already there. ## What makes the diff possible The diff needs two things: the **original snapshot** of the collection, and a way to recognise the same element in both versions. - The layer takes its snapshot when it loads the collection, and it holds it against the *collection instance* it handed to the code. It watches that instance for adds and removes. - Element recognition comes from the element's identity — for a child row, its key; for a junction link, the pair of keys in the row. ## What breaks it - **The collection instance was replaced.** Assigning a brand-new collection to the mapped field throws away the instance the layer was watching. It now holds a snapshot for an object nobody is using and a fresh collection with no history, and the only correct thing it can do without re-reading is remove everything it knew about and write what is there now. - **Elements have no stable identity while the code holds them.** If equality and hashing over an element change between load and flush, the diff sees removals and additions where nothing moved. - **The stored shape has no addressable row.** An unordered link table with duplicates allowed, or a positional collection whose index is itself stored, can force a rewrite because a single row cannot be targeted or because every position after a removal shifts. | | Collection mutated in place | Collection instance replaced | |---|---|---| | What the layer has | its snapshot plus the instance it is watching | a snapshot for an abandoned instance | | Statements for adding one element | one insert | delete all rows, then insert all elements | | Cost with 500 elements | 1 statement | roughly 501 statements | | Rows the log touches | one | every row of the link | ## Why the rewrite hurts - **Statement volume.** Every save costs statements proportional to the whole collection, not to the change, and this scales with the largest collection in the data rather than with the workload. - **Lock footprint.** Deleting every row of the link takes locks on all of them for the rest of the transaction, so two units of work touching overlapping sets now conflict — and because the deletes and inserts arrive in the layer's order, this is a classic source of deadlocks between concurrent savers. - **Side effects fire.** Triggers, audit rows and change-capture streams see a delete and an insert for rows that never changed, so downstream consumers get churn that did not happen in the domain. - **It happens on saves that changed nothing.** If the field is reassigned on every update — a common shape when an update method builds a fresh collection from a request payload — the rewrite runs even when the contents are identical. ## Keeping the targeted path 1. **Mutate what the layer loaded.** Add and remove elements on the existing collection; never assign a new one to the mapped field after load. 2. **Compute the difference in the code** when the input arrives as a whole set: work out which elements are new and which are gone, then apply those two sets to the loaded collection. 3. **Expose add and remove methods** and keep the field's setter out of the public surface, so a well-meaning update path cannot reassign it. 4. **Keep element identity stable** for the lifetime of a unit of work, so the diff recognises unchanged elements. 5. **Leave the mapped collection out of the write path entirely** for very large link sets, and write the link rows with direct statements — a single insert-select or a delete with a narrow predicate beats any object-level path at that size. ## What an interviewer is listening for The candidate should read the log correctly — this is not a bug, it is the layer's fallback when it cannot compute a difference — and name the replacement of the collection instance as the usual trigger. The strong answer goes on to the cost profile: statements proportional to the collection, the widened lock footprint, and the spurious downstream events. The best answers add the honest boundary: layers vary in how aggressively they fall back, and once a link set is large enough, the object-level path is the wrong tool regardless of how well the diff works.

An update method receives the whole desired set from a request payload. How do you avoid the rewrite?
Compute the difference in your own code: work out which elements are new and which are missing relative to the loaded collection, then apply those two sets with add and remove calls on the instance the layer handed you. Never assign the incoming set to the mapped field.
Why does the rewrite raise the chance of deadlocks?
It takes locks on every row of the link for the rest of the transaction rather than on the one row that changed, and the statements go out in the layer's order. Two units of work touching overlapping sets now conflict on rows neither of them meant to change.
When is the mapped collection simply the wrong tool?
When the link set is large or churns constantly. At that size the object path costs a load of the whole collection plus statements proportional to it, while a narrow delete and an insert-select do the same work in two statements and take far smaller locks.

Handing the shop a freshly written shopping list instead of ticking items off the old one. They cannot tell what changed, so they clear the basket and refill it from scratch.

saying these in an interview costs you the question

Assumes a collection save always writes only what changed
Reassigns a fresh collection to the mapped field on every update
Reads the delete-then-reinsert burst as a bug in the layer
Ignores unstable element equality when the diff misbehaves
Expects statement count to track the change rather than the collection size

Why does a mapper sometimes delete every row behind a mapped collection and reinsert them instead of one targeted statement?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

What makes the diff possible

What breaks it

Why the rewrite hurts

Keeping the targeted path

What an interviewer is listening for

▾common follow-ups3 questions

▾analogy~10 s

▾red flags5 flags

What makes the diff possible

What breaks it

Why the rewrite hurts

Keeping the targeted path

What an interviewer is listening for

answer

go deeper