For a mass data change, how do you decide between a set-based statement through the layer and the object path, and who then owns the skipped invariants?

## The decision is not "is it faster" It almost always is. The interesting question is what the speed costs and whether the organisation can carry it. ## Step 1 — is the statement path even available? A set-based write requires the new value to be derivable from data the database already has: a constant, an expression over the row, or a value from a joined table. If the new value depends on application logic — a rules engine, a call to another service, anything not expressible in the query language — you cannot express it in one statement. The honest options are then the object path, or staging the computed values in a table and applying them in one statement afterwards, which is the statement path with an extra step. ## Step 2 — inventory what is skipped, per invariant Do not reason about "guarantees" in the abstract; enumerate them against this change. | Skipped behaviour | The question to ask | If it matters | |---|---|---| | Cascade to children | Do dependent rows need to change too? | Declare the referential action in the schema, or issue explicit child statements in dependency order | | Lifecycle hooks | Does a hook write audit rows, stamp fields, or maintain a derived column? | Reproduce it set-based over the same predicate | | Events published on change | Does a downstream consumer — search index, cache, another service — rely on per-row notifications? | Emit an equivalent signal, and agree with consumers whether one summary event suffices | | Per-row version check | Could a concurrent writer be mid-change on these rows? | Narrow the predicate, or take the object path for the contended subset | | In-memory and cached copies | Who holds copies of these rows right now? | Order the bulk statement before loading, evict what it touched, and invalidate caches deliberately | Most changes have one or two live entries and several that genuinely do not apply. Writing the table out is what turns a vague unease into a decision. ## Step 3 — weigh the operational footprint The object path spreads the work over many small transactions; a single statement over millions of rows concentrates it. That concentration has its own costs on a live system — a long write transaction, a large volume of change to replicate, and contention with concurrent traffic on the same rows. The mitigations belong to the statement's own design rather than to the layer, but a decision made without acknowledging them is incomplete. Conversely, the object path's cost is not just time: it is millions of small writes, each with its own overhead, spread over a window in which the system is in a partially-updated state that concurrent readers can observe. Neither path is inherently safer for a live system; they fail differently. ## Step 4 — accept ownership explicitly Once you choose the statement path, the skipped behaviour becomes yours. Three practices keep that honest: 1. **Co-locate.** Put the bulk operation in the same module as the object path for the same entity. A change to the neighbourhood is then visible to whoever extends either one. 2. **Test the invariant, not the callback.** Assert that no orphaned children remain, that the audit trail covers every affected row, that the search index converges. A test asserting a hook fired passes on the object path and never exercises the bulk one, so it cannot detect divergence. 3. **Document the loss where it was chosen.** "This path deliberately does not publish a per-row event; consumers receive one batch signal instead" is a sentence that survives staff turnover. An undocumented omission reads as a bug to the next person, who will either re-add the cost or work around it. ## Step 5 — the standing question, not the one-off The strategic version of this decision is whether the codebase should have two write paths for the same rows at all. A team that reaches for bulk statements freely accumulates a shadow set of write paths that no hook, no cascade and no event applies to, and eventually nobody can say what actually happens when a row changes. A team that forbids them entirely writes loops that cannot finish inside a maintenance window. The workable middle is to make it a small, visible category: bulk operations are allowed, they live in named places, each carries an explicit note of what it skips and how that is compensated, and adding one is a decision someone reviews rather than a local optimisation. The point is not the ceremony — it is that "how a row changes in this system" stays answerable.