How do you decide that a join or aggregation is expensive enough to justify permanently storing redundant data in a transactional schema?

## Start from evidence, not intuition The first question is whether the query matters. Rank by *total* time — calls multiplied by mean duration — rather than by worst single call. A 900 ms report run four times a day is not the problem; a 12 ms query run nine thousand times a minute may be. Then read the actual plan: it frequently shows the real cost is a sequential scan from a missing index, a filter applied after the join, or a predicate that defeats an index. Denormalizing on top of a fixable plan permanently bakes in a redundancy you never needed. ## Exhaust the cheaper corrections in order **Indexes.** An index on the join column turns a hash or merge join over a large table into a bounded lookup. A covering index — one that includes the columns the query returns — can answer it without touching the table at all, which is often the same win a duplicated column would give, with the engine maintaining consistency for you. Indexes cost write throughput and space, but never correctness. **Query shape.** Filtering earlier, avoiding a repeated correlated aggregate, or fetching a page of parents and then their children in one keyed query often removes more time than the schema change would. **Caching.** A result cache with a short expiry is redundancy with a built-in correction mechanism: it is wrong for at most its expiry window and then fixes itself. A denormalized column is wrong until someone notices. If the read tolerates seconds of staleness, a cache is strictly the safer instrument. ## What makes the case for redundancy strong **Cost that grows with unbounded data.** The clearest case is an aggregate over child rows whose count is uncontrolled: counting comments, summing ledger entries, computing a running balance. No index makes counting a million rows cheap, so the work is proportional to data you cannot bound, and a stored value converts it to constant time. Contrast a join to a lookup table of two hundred rows — that is a few microseconds after the first read, and duplicating its columns buys nothing while costing consistency. **A read-to-write ratio strongly favouring reads.** Redundancy moves work from read time to write time. If a value is read ten thousand times per write, that is an excellent trade. If it is read twice per write, you have made the system slower and less correct simultaneously. **A query on a path where latency is contractual.** A checkout step or a first-paint API call justifies more than an internal admin screen. **Sorting or filtering, not just projection.** Needing to order a large result by a value that lives in another table is much harder to fix with indexes than merely displaying that value — the engine cannot use an index on a column it must join to first. Sorting and pagination by a foreign attribute is one of the most legitimate reasons to copy a column. ## What makes the case weak The join is to a small table; the query is not actually hot; the plan is fixable; the duplicated value changes frequently, so you are paying constant synchronisation cost for it; the write path already contends on the parent row you would be updating; nobody can say who repairs drift. Also weak: "joins are slow" as a general belief. On indexed keys with reasonable row counts, relational engines join extremely well — the pathological cases are wide fan-outs, many-table joins that defeat the planner's estimates, and aggregation over unbounded children. ## Ship it with a contract If all four conditions hold, the change is not just a column. It is: the mechanism that maintains it (trigger, same-transaction write, or asynchronous job), the staleness the mechanism implies, the reconciliation query that detects drift, and the measurement that justified it recorded next to the schema. That last item is what lets a future engineer remove the redundancy when the workload changes — without it, every denormalization is permanent by default because nobody can prove it is still needed. ## Verify afterwards Measure the same query after the change, and also measure the write path you just made heavier. It is common to move a read from 40 ms to 2 ms while adding contention that costs more than it saved under peak concurrency. A denormalization that was never re-measured is a belief, not a result.