Data Engineering
OLTP vs OLAP: multiple-choice review
Crux Multiple-choice synthesis across the OLTP vs OLAP unit — row vs column layout, column pruning, compression, vectorization, the replica-analytics outage, and when to split stores.
Your altitude — climbing toward senior
ZeroJuniorMiddleSenior
You are at senior altitude — in orbitSix questions that cut across the whole unit. Each one mirrors a decision you make in a real architecture review — not a definition to recite, but a tradeoff to weigh when the dashboard and the checkout path share a database.
Goal
Confirm you can connect physical layout, the column-store multipliers, the replica-analytics failure mode, and the store-split decision — the synthesis the overview built toward.
Quiz
Completed
A query runs SUM(total) over 40M orders rows that have 50 columns each. Why does a column store crush a row store on exactly this query?
Heads-up A full-table aggregate needs most or all rows, so there is nothing selective to seek — the planner does a sequential scan regardless. The win is reading fewer columns, not an index.
Heads-up Neither engine is defined by caching everything. The structural win is the on-disk layout — one column contiguous — which enables pruning, compression, and vectorized scans even from disk.
Heads-up Column stores are slower at single-row writes, not faster — a write touches every column file. The advantage is on column scans, an entirely different axis.
Quiz
Completed
Service A runs point lookups by id at 5k req/s; Service B runs a column aggregate over the same data. Why can't one storage layout serve both well?
Heads-up There is no free switch. Layout is physical: a row store keeps rows contiguous and a column store keeps columns contiguous. HTAP systems try to bridge this but pay a real cost; one layout cannot be optimal for both shapes.
Heads-up It is not a speed knob. The mismatch is structural — a row store reads every byte of every row for an aggregate, and more CPU does not change that it is reading 50 columns to use 1.
Heads-up Bigger tables are not the distinction. The distinction is access pattern: point lookups and small writes vs full-table scans over a few columns — opposite optimization targets.
Quiz
Completed
On 100M-row ClickBench, ClickHouse stores the data in roughly 9 GiB where Postgres needs about 100 GiB. Why does columnar compress so much better, and why does that widen the scan-throughput gap rather than just saving disk?
Heads-up The size difference is from columnar compression of same-type values, not from dropping indexes. The throughput win comes from pruning plus reading far fewer compressed bytes, not from index maintenance.
Heads-up For scan-bound analytical queries, I/O is the bottleneck. Fewer bytes on disk means fewer bytes to read, so compression directly multiplies scan throughput — it is not just a storage saving.
Heads-up Page compression on a row store mixes int, string, timestamp, and float per page and reaches only ~1.5–3×. A column packs one type together, which is why it reaches 5–10× — the layouts are not equivalent.
Quiz
Completed
A growth dashboard runs a 90-day revenue aggregate against the prod Postgres read replica. Checkout p99 jumps from 40ms to 2s and replica lag climbs to 90s. What root cause does a senior name first?
Heads-up An index does not help a full-table 90-day aggregate — there is nothing selective to seek. And the symptom is OLTP slowdown from cache eviction and contention, not a slow dashboard query.
Heads-up The dashboard only reads, and writes go to the primary, not the replica. The damage is buffer-cache eviction and resource contention on the replica plus replication lag — not blocked writes.
Heads-up Pool size is not the mechanism. Even a single analytical scan poisons the buffer cache and competes for I/O on a row store sized for transactional working sets.
Quiz
Completed
Product wants the 90-day revenue dashboard. Of these four options, which is the architecture a senior ships, and why?
Heads-up Indexes fix selectivity, not scan volume. A 90-day, all-countries aggregate needs most rows, so the planner ignores the index and scans anyway — you have added write cost for no read benefit.
Heads-up The hourly recompute still runs the destructive full scan on the replica — checkout degrades once an hour and the cache is stale between runs. It treats a storage-layout problem as a presentation problem.
Heads-up Off-peak narrows the blast radius but does not fix it: the scan still evicts the buffer cache and drives replica lag during the window, and 'last 90 days' dashboards are rarely truly off-peak. The fix is a separate store, not a schedule.
Quiz
Completed
A team proposes an HTAP engine to serve both transactions and analytics on one system, arguing it removes the need to split stores. What is the senior framing?
Heads-up No single physical layout is optimal for both point writes and column scans. HTAP systems keep both a row and a column representation internally and pay to maintain them — they manage the tension, not abolish it.
Heads-up HTAP has real uses where data latency must be near-zero and operating two systems is too costly. It is a tradeoff, not a mistake — but it does not erase the contention that motivates the split.
Heads-up Even with HTAP you often still feed downstream warehouses; and the point of CDC in the two-store design is isolation. HTAP changing where data lives does not by itself eliminate the contention the split was built to avoid.
Recap
The through-line of the unit is one decision: access pattern sets layout. Point lookups and small writes want a row store; full-table scans over a few columns want a column store, where pruning (read ~6% of the bytes), 5–10× compression, and vectorized batch execution stack into a 10×–1000× gap no index can close. The classic senior failure is analytics on the prod OLTP replica, where one scan evicts the hot buffer cache, contends for I/O, and drives replica lag. The fix is architectural — a separate columnar store fed by CDC or ETL — accepting a little data latency for isolated storage, caches, and failure domains. HTAP can manage the contention but never abolishes it.