A transactional database is being slowed by long analytical scans from a BI tool. Discuss the architectural options for serving both transactional and analytical workloads, including hybrid designs that maintain row and column copies of the same data, and the tradeoffs you would weigh.
answer
- OLTP vs OLAP fight over cache, I/O and snapshots
- Ladder: tune -> replica -> columnar copy -> HTAP engine
- HTAP = row write path + column representation, optimizer chooses
- Freshness vs isolation vs operational surface
- Long analytical snapshots block old-version cleanup
basics
~20 sOptions: keep one row store and accept the scan cost; offload analytics to a replica; copy data into a columnar analytical system; or use a hybrid engine that keeps a columnar representation alongside the row data, updated from the transaction stream. Trade freshness, storage and operational cost against isolation of the two workloads.
solid answer
~1 minThe two workloads want opposite things: OLTP wants short, indexed, whole-row access with tight latency; analytics wants long scans over few columns and will happily evict the entire buffer cache and saturate I/O doing it. So the real question is how strongly to isolate them. Escalating options: 1. **Tune in place** - indexes, pre-aggregates, resource limits. Cheapest, but a big scan still competes for cache, I/O and CPU. 2. **Read replica** - physical isolation of resources, same row layout. Scans stop hurting OLTP but stay slow; you gain replication lag and a second host. 3. **Separate columnar system** fed by CDC or batch ETL. Best analytical performance and full isolation, at the cost of a pipeline, schema drift, and minutes-to-hours staleness. 4. **Hybrid / HTAP engine** - one system keeping both a row representation for writes and a columnar representation (in memory or on disk) maintained from the write path, with the optimizer choosing per query. Near-real-time analytics with one copy of truth, paid for in memory, background maintenance CPU, and vendor lock-in. I would decide on required freshness, analytical latency, and how much operational surface the team can carry - and I would keep transactional correctness on the row side regardless.
go deeper
Recognize that scans and transactions compete for the same memory and disk, and that moving reporting to a separate copy is the usual remedy.
Lay out the ladder - tuning, replica, columnar copy - and name what each one costs in freshness and infrastructure.
Add the second-order effects: buffer-cache eviction, long snapshots blocking version cleanup, replication lag, and how a hybrid engine maintains its column side.
Drive the decision from freshness, latency targets, blast radius, operational surface and cost of ownership, and state a default plus the trigger that would change it.
## Why one engine struggles to do both OLTP and OLAP conflict at every level of the storage stack: - **Access pattern:** point access to whole rows versus sequential scans over a few columns. - **Layout:** rows win the first, columns win the second. - **Memory:** OLTP depends on a hot working set staying cached; a 500 GB scan can evict it, and the OLTP p99 collapses even though the analytical query itself looks fine. - **Concurrency:** thousands of millisecond transactions versus a handful of multi-minute queries holding snapshots. Long snapshots also delay cleanup of old row versions, bloating the transactional tables. So the design axis is **isolation**: how far apart to put the two workloads, and what you pay in freshness and complexity to move along it. ## The option ladder **1. Same instance, tuned.** Add covering indexes, materialize summary tables, cap the BI tool's concurrency, apply per-query resource governance. Cheap and immediate. Fails once the analytical set genuinely requires scanning large fractions of the table, because you cannot stop a scan from consuming shared cache and bandwidth. **2. Replica offload.** Point BI at a read replica. Resource isolation is real: the primary keeps its cache and I/O. But the replica is still a row store, so scans remain slow; you inherit replication lag, and long-running readers on a replica can conflict with applying the change stream. **3. Dedicated columnar analytics store.** Move analytical data into a columnar system via change data capture or batch loads. This is the biggest performance win - columnar compression and vectorized scans on a system built for it - plus complete isolation. Costs: a pipeline to build and monitor, a second copy with schema drift and semantics questions, and staleness measured in minutes or hours. This is the standard shape when analytics is a separate product surface. **4. Hybrid (HTAP) engine.** One system maintains both representations of the same tables: a row-organized store handling transactions, plus a columnar representation - an in-memory column store, a columnstore index, or columnar segments on disk - kept current from the same write path or the transaction log. The optimizer picks the representation per query. Writes go to the row side and are propagated to the column side either synchronously or with small lag; recently-written rows may be served from a row-shaped delta area and merged at read time. HTAP buys near-real-time analytics without a pipeline and without a second source of truth. The price is real: substantial extra memory or storage for the second representation, continuous background CPU for compaction and merging, more complex recovery, and unpredictable behaviour if the column side falls behind. Coverage is often partial - you choose which tables or columns get a columnar copy. ## What I would actually weigh - **Freshness requirement.** "Yesterday's data" permits ETL; "the dashboard must reflect the order placed 10 seconds ago" pushes toward HTAP or a replica. - **Analytical latency and shape.** Seconds over billions of rows demands columnar; a few thousand rows through good indexes does not need any of this. - **Blast radius.** Anything sharing an instance with OLTP can hurt customer-facing latency. Isolation has value even when performance would be adequate. - **Operational cost.** A pipeline is code, alerts, backfills and on-call. A hybrid engine is fewer moving parts but deeper coupling to one vendor's behaviour. - **Correctness and semantics.** One copy of truth is worth a lot; two copies invite "the numbers disagree" investigations. - **Cost.** Doubling representations doubles storage or memory; a separate cluster doubles infrastructure but can be sized and scaled independently. ## A defensible default Start by confirming that the scans are genuinely scan-shaped rather than a missing index. Offload to a replica immediately for isolation, because it is cheap and reversible. If analytical latency remains unacceptable, adopt a columnar target - via a hybrid feature of the existing engine when freshness must be near real-time and the workload fits, or a dedicated columnar system when analytics has its own scale, modelling and consumers. Keep the transactional row store as the system of record either way.
- In a hybrid engine, how are rows written a second ago made visible to the columnar side without destroying write throughput?Writes land in the row store and in a small, uncompressed row-shaped delta buffer; a background process periodically converts delta rows into compressed column segments and marks superseded rows in a deletion bitmap. Queries against the columnar representation read compressed segments plus the delta, minus the tombstones, so they see current data. The compaction cadence is the tuning knob: too slow and the delta grows until scans slow down, too aggressive and it steals CPU and I/O from transactions.
- Why can a long analytical query on a transactional database cause table bloat as well as slow queries?A long-running query holds a read snapshot, and under MVCC the engine cannot reclaim row versions that might still be visible to that snapshot. Cleanup stalls for the query's whole duration, so dead versions accumulate in tables and indexes, which in turn slows down the transactional workload and enlarges storage even after the query ends.
saying these in an interview costs you the question
- Jumping straight to a separate analytics system without first checking whether the query is scan-shaped or just missing an index
- Assuming a read replica makes analytical queries fast - it isolates resources but keeps the row layout
- Treating HTAP as free real-time analytics, ignoring the memory, compaction CPU and partial-coverage costs
- Ignoring cache pollution and MVCC cleanup stalls, and arguing only about CPU
- Proposing two copies of the data with no answer for reconciliation when the numbers disagree