Analysts want to run multi-hour reporting queries against a physical replica of your OLTP primary. Explain how long-running readers on a replica interact with replication and with the primary's version cleanup, and how you would design for it.
answer
- replay carries cleanup, hence recovery conflict
- three outcomes: cancel, lag, or bloat the primary
- feedback exports the problem to the primary
- HA replica is not the reporting replica
- alert on lag and on primary horizon age
basics
~20 sA long reader on a replica needs old row versions that the primary's cleanup removes. Either the replica cancels the query when conflicting cleanup arrives, or it delays applying changes and lags, or it makes the primary hold garbage back and bloat. You choose which one you pay for.
solid answer
~60 sOn a physical replica, replay applies the primary's cleanup of old row versions. A reporting query holding a snapshot may still need those versions, which creates a **recovery conflict** with three possible resolutions, and you must pick one deliberately: 1. **Cancel the reader** — the query dies mid-run; hours of work lost, but the primary and lag are unaffected. 2. **Delay replay** — the replica pauses applying changes while the query runs; the query survives but lag grows toward the query duration, which breaks the replica as a failover target. 3. **Standby feedback** — the replica reports its oldest snapshot to the primary so the primary withholds cleanup; the query survives and lag stays low, but the *primary* now bloats exactly as if the long transaction ran there. Because all three have real costs, my design stops treating one replica as both a failover target and an analytics engine: a dedicated reporting replica tuned for option 2 or 3, a separate HA replica tuned for option 1, and genuinely heavy analytics moved to an extracted copy.
go deeper
Know that a replica serves reads and that a very long query there can be cancelled or can cause the replica to fall behind.
Explain the conflict between replayed cleanup and a local snapshot, and name the cancel-versus-delay trade-off.
Add standby feedback and its consequence on the primary, per-role statement timeouts, and the monitoring that makes the chosen trade-off visible.
Lead with the requirement conflict — bounded lag versus long stable snapshots — separate HA from reporting topology, consider logical replication or an extracted analytical copy, and state the staleness contract explicitly.
## The mechanism A physical (binary) replica replays the primary's low-level change stream page by page. That stream includes not only inserts and updates but also the **cleanup** of obsolete row versions — removals decided by the primary's cleanup horizon, which knows nothing about queries running on the replica. Meanwhile the replica serves reads under MVCC with its own snapshots. A report that started an hour ago needs the versions that were current an hour ago. When replay arrives carrying "remove version V, nobody needs it", and a local snapshot still does need V, the two demands are incompatible. This is a **recovery conflict**, and it is the defining operational property of long readers on a replica. The same class of conflict arises from an exclusive lock replayed for a schema change or a table truncation. ## The three resolutions and what each really costs **Cancel the query.** The default in most configurations, usually after a short grace period. Replay proceeds, lag stays near zero, the replica remains a trustworthy failover target — and the analyst's four-hour report dies at minute 200 with a conflict error. Retrying rarely helps, because the same churn will conflict again, so long reports simply become unreliable. **Delay replay.** Give replay a grace window so conflicting cleanup waits. The query finishes, but during the wait the replica is not applying changes, so its lag grows toward the query's duration. The consequences are strategic, not cosmetic: recovery point and time objectives are violated, read-your-writes behaviour on that replica degrades badly, and if the primary fails during the window you lose everything the replica had not applied. A replica that can lag by hours is not a failover target. **Standby feedback.** The replica continuously reports its oldest snapshot to the primary, and the primary holds back cleanup accordingly. The reader survives and replay never stalls, but you have exported the long-transaction problem to the primary: dead versions accumulate there, tables and indexes bloat, and the cleanup horizon is now controlled by whichever analyst forgot to close their session. In the worst case a single reporting session degrades write performance for the entire production workload. If you enable it you must also bound the reader with statement timeouts on the reporting role and alert on the primary's oldest-horizon age, otherwise you have replaced a visible failure with an invisible one. There is a fourth, degenerate option — pause replay entirely for the reporting window and resume afterwards — which is really the delay option under manual control, and it is sometimes the right answer for a nightly batch on a replica that is explicitly not the failover target. ## Designing the system rather than picking a flag The principal-level answer is that no single flag is correct, because one replica is being asked to satisfy two contradictory requirements: **bounded lag** (high availability) and **long stable snapshots** (analytics). Separate them. - **HA replica:** conflict handling set to cancel queries, no feedback to the primary, no ad-hoc user workload. Its job is to stay within seconds of the primary at all times. - **Reporting replica:** delayed replay or feedback enabled, sized for scan-heavy work, with a reporting role carrying its own statement timeout and connection limits. Nobody promises failover from it. - **Extracted analytical copy:** for genuinely heavy or long analysis, extract on a schedule into a separate analytical store. Analysts get a stable snapshot with no coupling to OLTP cleanup at all, and hour-long scans stop being a database concurrency problem. - **Logical replication as an alternative:** it ships row-level changes rather than physical page cleanup, so it does not create this class of conflict on the subscriber, at the cost of different constraints such as schema and DDL handling and apply throughput. ## Guardrails regardless of topology 1. Alert on **replica lag** and, separately, on the **primary's oldest cleanup horizon**; feedback moves the symptom from the first metric to the second, and teams that only watch lag will never see it. 2. Give the reporting role a hard statement timeout, so "someone left a query running" cannot be unbounded. 3. Make the routing explicit in the application: a reporting connection that points at the reporting replica and cannot be used for OLTP paths. 4. Decide and document the contract: what maximum lag the reporting replica may reach, and whether the business accepts reports over data that is minutes or hours stale. 5. Revisit whether the query needs a live database at all. Many multi-hour reports are aggregations that could be pre-computed incrementally, which removes the long snapshot entirely — the cheapest resolution of all. The framing to leave behind: **a long reader on a replica forces a three-way choice between killing the query, lagging the replica, or bloating the primary. Systems that pretend otherwise have simply not noticed which one they picked.**
- Standby feedback is enabled and the primary has started bloating. What do you do?First confirm causation by comparing the primary's cleanup horizon age with the age of the oldest query on the replica; if they track each other, feedback is the cause. Short term, cap the reporting role with a statement timeout so no single query can hold the horizon indefinitely. Longer term, either accept a bounded amount of primary bloat as the deliberate price for reliable reports, or move the heavy readers off the physical replica onto an extracted analytical copy.
- Why does logical replication avoid this specific conflict?Logical replication ships row-level change events which the subscriber applies as ordinary transactions against its own storage, rather than replaying the primary's physical cleanup of specific row versions. The subscriber therefore manages its own versions and horizon locally, so a long reader there conflicts only with local cleanup and never with the publisher's decisions. The trade-offs move elsewhere: apply throughput, schema and DDL handling, and initial synchronization cost.
It is like keeping one archive both as a live mirror of head office and as a reading room. Either you eject readers when head office sends a shredding order, or you stop shredding and fall out of sync, or you tell head office to stop shredding entirely and let their own storeroom overflow.
saying these in an interview costs you the question
- Believing a read replica isolates long queries at no cost anywhere
- Enabling standby feedback without realising it moves bloat onto the primary
- Using the same replica as both the failover target and the analytics engine
- Treating a cancelled query on a replica as a bug rather than a configured conflict resolution
- Monitoring replica lag only, and never the primary's oldest cleanup horizon