OLTP vs OLAP Workload Lab (Interactive)
Sweep row counts, table width, and compression to compare row-page I/O against vectorized column scans. Run the same SQL against a row-oriented store and a columnar engine to see bytes read, latency, and wasted cache-line traffic diverge by query shape.
OLTP Row Store vs OLAP Columnar Scan
Compare physical I/O of the same SQL against row pages and compressed column chunks.
Volcano iterator reads every byte of every row: full pages of addresses, JSON payloads and tax fields are loaded then discarded.
Projection pruning reads only referenced column chunks; RLE / dictionary / delta encoding feeds AVX-512 SIMD batches of 2,048 values per operator call.
How It Works Under the Hood
Row stores like PostgreSQL lay every column of a tuple contiguously on an 8KB page, so point lookups finish in one I/O but aggregations must materialize the whole table through a tuple-at-a-time Volcano iterator. Columnar engines like ClickHouse store each column separately, letting projection pruning read only referenced columns at RLE and dictionary compression ratios of 5x-15x, then process them with SIMD batches of thousands of values. The simulator computes both physical byte volumes and CPU costs so you can see exactly where each paradigm wins.
Core Architectural Principles
- Point lookups read one B+Tree leaf page in the row store; columnar point queries scan the whole key column.
- Projection pruning limits an OLAP scan to only the columns named in SELECT and WHERE.
- Volcano per-tuple virtual-call overhead versus vectorized batch loops drives the CPU time gap.
When designing reporting or dashboards, explicitly segregate the OLTP path from the OLAP path via CDC into a columnar warehouse, and justify it with physics: row pages waste over 90% of loaded bytes on analytical scans, while column chunks saturate cache lines with homogeneous values. Quote compression ratios and SIMD batching to show you understand why the gap is 100x, not 2x.
Sub-millisecond ACID point mutations favor row stores; 100x faster billion-row aggregations favor columnar stores that cannot cheaply update single rows.