A team wants to run a large reporting query with reads of uncommitted data so it stops waiting behind write transactions. How do you evaluate that request, and what would you propose instead?

## Start with the consumer, not the level The request is framed as a performance problem, but the decision is a correctness one. So the first question is never 'how much faster is it?' — it is **what happens to this number afterwards?** A workable rule: - **Acceptable**: a human glances at an approximate progress count, a queue-depth gauge, a rough row estimate during a migration. Nobody stores it, nobody compares it to a limit, nothing downstream consumes it. - **Not acceptable**: anything written back to the database, compared against a threshold that triggers an action, reconciled against another system, invoiced, exported, or presented as authoritative to a user or auditor. The reason for the hard line is that the error from an uncommitted read is unbounded rather than proportional. A rolled-back writer means the value corresponds to no committed state at all; and because a transaction's writes become visible before commit, the reader can also observe a partially applied multi-row change — a debit without its credit — so a total can be wrong by the size of an entire in-flight transaction, not by a small drift. There is a second, less-known hazard specific to lock-free scans on engines that move rows during page splits or reorganizations: a scan holding no locks can traverse a structure that is being modified underneath it, and end up **missing committed rows or returning them twice**. That is worth naming, because it defeats the usual defence of 'the count is only a bit off — it will be roughly right'. ## Then check whether the premise even holds Often the request is inherited folklore. On a multi-version engine, readers do not block behind writers in the first place: they read older committed versions rather than waiting. If someone is adding an uncommitted-read hint there, it is doing nothing, and the actual wait is somewhere else — a lock taken explicitly, a schema-change operation, a resource bottleneck, or plain query cost. Measure the wait before changing semantics. ## The alternatives, in the order I would propose them 1. **Fix the query.** A reporting query that scans far more than it needs holds resources longer and conflicts with more writers. Indexing, narrowing the range, or pre-aggregating often removes the problem entirely. 2. **Read-only, snapshot-consistent transaction.** Declaring the transaction read-only and running it against a consistent snapshot gives a report that is internally consistent — every table agreeing with every other — which is *better* than the status quo, not worse. This is usually the direct answer to 'we want to stop blocking'. 3. **A read replica or an analytics copy.** Moves the load off the transactional path completely. Costs replication lag, which is honest, bounded staleness of committed data — a categorically safer error than uncommitted data. 4. **Incremental pre-aggregation.** Maintain the total as writes happen (a summary table, materialized aggregate, or event-driven rollup) so the report reads one small, already-consistent row set. 5. **Shorten the write transactions.** If reports are blocking behind writers on a lock-based engine, the writers holding locks for a long time are the real defect. No user waits, no external calls, no batch loops inside a transaction. 6. **Bound the report's blast radius.** Timeouts, a lower resource priority, and a schedule outside peak write windows. ## How I would close the conversation I would agree to uncommitted reads only with three things written down: the specific query, a statement that its output is display-only and never persisted or thresholded, and a comment at the call site explaining why. Anything else, and I would rather spend the same effort on a replica or a pre-aggregate, because those fix the class of problem instead of one query. The underlying principle worth saying out loud in an interview: **staleness and incorrectness are not the same risk**. Replica lag and cached aggregates give you data that was true at some point; uncommitted reads give you data that may never have been true. Almost every reporting requirement tolerates the former and none should tolerate the latter without an explicit, narrow justification.