OLTP vs OLAP: the workload split that defines data engineering

The growth team ships a dashboard: “revenue by country, last 90 days.” It points at the prod Postgres read replica because “it’s just a read.” At 9am the query fans out a SUM over 40M orders rows, evicts the hot B-tree pages every checkout needs out of shared_buffers, and pins I/O for 30 seconds. Checkout p99 jumps from 40ms to 2s, replica lag climbs to 90s, and on-call gets paged for “database down.” Nothing was down. One analytical scan poisoned a transactional system. Adding an index would not have helped — there was no WHERE to narrow; the query needed every row.

Two workloads, two opposite shapes

OLTP — Online Transaction Processing — is the app database: INSERT an order, UPDATE a balance, fetch one user by id. The access pattern is point lookups and small writes against a few rows, thousands of times per second, each in single-digit milliseconds. OLAP — Online Analytical Processing — is the question machine: average order value by region by month, retention cohorts, “which 1% of users drive 40% of revenue.” It scans millions of rows but touches only a handful of the table’s columns, and a query running for seconds is fine.

These are not two settings on one knob. They pull the physical design in opposite directions. OLTP optimizes for write latency and single-row retrieval; OLAP optimizes for scan throughput over a few columns across the whole table. A schema, an index, a storage format that is excellent at one is structurally bad at the other. That tension — not any specific tool — is what the rest of data engineering is built to manage.

Row-store vs column-store: it’s about what sits next to what

A row store (Postgres, MySQL, most OLTP engines) keeps a row’s columns contiguous on disk: [id, user, country, total, created_at] for row 1, then all of row 2, and so on. That is perfect for OLTP — fetching or writing one whole order touches one page. But ask for SUM(total) over 40M rows and the engine must read every page of the table, dragging id, user, country, created_at through memory just to reach total. You pay I/O for 50 columns to use 1.

A column store (ClickHouse, Redshift, BigQuery, Snowflake) flips the layout: all total values sit together, all country values together. Now SUM(total) reads only the total column — column pruning. A query touching 3 of 50 columns reads roughly 6% of the bytes a row store would. That single property is most of the gap.

Why columnar compresses 5–10× and runs vectorized

Storing one column together means storing values of one type and often low cardinality next to each other — and that compresses far better than a row’s grab-bag of an int, a string, a timestamp, a float. A country column is a few hundred distinct strings repeated millions of times: dictionary-encode it to small ints, then run-length-encode the runs, then LZ4/ZSTD on top. Row stores typically reach 1.5–3×; column stores hit 5–10× on analytical data and 30×+ on low-cardinality columns. On the 100M-row ClickBench, ClickHouse stores the dataset in ~9 GiB where Postgres needs ~100 GiB. Less data on disk is less I/O, and I/O is the bottleneck for scans — so the compression gap widens the throughput gap to 50–100×.

The second multiplier is vectorized execution. A row engine processes a query tuple-by-tuple: per row, a function call, a type check, a branch. A column engine processes a column in batches of thousands of contiguous, same-type values, so the CPU runs tight loops over arrays, SIMD-friendly, with predictable branches and warm caches. Same arithmetic, an order of magnitude less per-value overhead. Pruning, compression, and vectorization stack: columnar beats row stores by 10×–1000× on analytical queries — and “add an index” cannot close that, because a full-table aggregate has nothing to seek.

The failure mode: analytics on the prod OLTP replica

The Hook is the senior failure mode in full. A read replica is still a row store with a buffer cache sized for transactional working sets. Point it at a long analytical scan and three things go wrong at once. First, buffer-cache eviction: the scan pulls cold pages through shared_buffers and evicts the hot OLTP pages, so transactional queries that were memory-hits start hitting disk and slow 10–100×. Second, lock and resource contention: sustained CPU, memory for hash joins, and I/O bandwidth are zero-sum — the analytical query starves the transactional threads sharing the engine. Third, replica lag: a long query holding resources delays apply of the WAL stream, so the replica falls behind the primary, and anything reading it serves stale data.

The fix is architectural, not a tuning flag: give analytics its own home. Stand up a separate OLAP store (a columnar warehouse — Redshift/BigQuery/Snowflake — or ClickHouse) and feed it from OLTP continuously via CDC (change data capture, e.g. Debezium streaming the WAL into Kafka) or batch ETL/ELT. The two systems now have independent storage, independent caches, independent failure domains. The dashboard hits the warehouse; checkout never feels it. The cost you take on is a pipeline to own and a few seconds-to-minutes of data latency — almost always the right trade.