The Physics of Fast OLAP

1. Prologue: The Query at Physical Limits

Consider a deceivingly innocent SQL query executed over ten billion telemetry records:

SELECT
    device_type,
    country_code,
    count(*) AS total_events,
    sum(duration_ms) AS total_duration,
    avg(bytes_transferred) AS avg_payload
FROM telemetry_events
WHERE timestamp >= '2026-01-01 00:00:00'
  AND status_code = 200
GROUP BY device_type, country_code
ORDER BY total_events DESC
LIMIT 10;

To the software engineer typing this statement into an interactive shell, the query is an abstract mathematical declaration. It specifies what relations to filter, aggregate, and project, wholly detached from the underlying thermodynamic reality of silicon gates, copper interconnects, DRAM refresh cycles, and electron drift. The database is treated as an oracle: an unbounded computational black box that ingests relational algebra and spits out a tabular answer.

Yet down on the physical substrate, this query is an act of extreme physical violence. Ten billion rows of telemetry, even moderately dimensioned at 200 bytes per record, constitute two terabytes of raw binary data. If this query is executed on a traditional row-oriented relational engine like PostgreSQL or MySQL running on rotational hard drives or standard cloud block storage, it will thrash the disk read head, choke the operating system page cache, saturate the CPU instruction decoders with millions of dynamic branch mispredictions, burn 120 clock cycles on polymorphic function dispatches for every single row, and take thirty to forty-five minutes to finish—if it does not run out of memory and abort entirely.

Run that exact same query over the exact same ten billion rows on a modern columnar analytical engine—such as ClickHouse, DuckDB, or Apache Pinot—running on a modern multi-core server, and the query completes in 420 milliseconds.

That is not a 10% optimization. It is not even a 2x or 5x speedup. It is a four-orders-of-magnitude leap (4,000x to 10,000x). It is the difference between an offline overnight batch ETL job and an interactive user interface that recalculates global aggregate metrics in real time between keystrokes.

How did analytical data systems bridge this chasm? The popular industry narrative attributes this speedup to vague marketing catchphrases: "the cloud," "in-memory databases," "distributed big data," or "AI query optimizers." This narrative is completely false. In fact, for nearly a decade during the Hadoop MapReduce era, the software industry managed to make analytical queries slower per unit of compute than they were in 1995.

The history of Online Analytical Processing (OLAP) is the story of software engineers repeatedly hitting physical hardware walls—first magnetic disk seek latency, then the CPU-DRAM memory bandwidth gap, then cache line pollution, and finally instruction pipeline stalls—and systematically redesigning data representation, execution algebra, and storage formats to align with the laws of physics.

2. The Genesis: Codd's Cubes & Combinatorial Explosion

The formal distinction between transactional and analytical processing was born in 1993, when Edgar F. Codd—the father of the relational model—published a whitepaper entitled "Providing OLAP (On-Line Analytical Processing) to User-Analysts: An IT Mandate." The paper was famously commissioned by Arbor Software, the creators of Essbase, but its conceptual contribution was profound: it articulated why relational database management systems (RDBMS), engineered for Online Transaction Processing (OLTP), were structurally incapable of serving exploratory business intelligence.

OLTP workloads are characterized by point lookups, short transactions, and localized writes: inserting a single customer order, transferring balances between two accounts, or updating a shipment status. These operations touch a tiny fraction of a table (one or two rows), require strict ACID durability, and are ideally served by B-tree indices that locate a single record on disk in $O(\log N)$ time.

OLAP workloads are the exact mirror opposite:

• Access Patterns: Bulk sequential scans across millions or billions of historical rows.
• Projection Ratio: Queries touch a massive number of rows, but only a tiny subset of columns (typically 3 to 6 out of 100+ attributes).
• Mutations: Appends and bulk updates; almost zero row-level transactional updates.
• Computation: Massive data reduction through aggregation operators: COUNT, SUM, AVG, MIN, MAX, PERCENTILE, and GROUP BY hash partitioning.

The early industry response to this mismatch in the 1990s was MOLAP (Multidimensional OLAP). If scanning raw transactional tables was too slow on 1990s hardware, the solution seemed obvious: pre-compute every conceivable answer before the analyst asks the question.

In a MOLAP system (like Arbor Essbase, Cognos PowerPlay, or Microsoft Analysis Services), data was ingested from transactional tables and pre-aggregated into an $N$-dimensional hypercube. A sale was not a row in an append-only log; it was a numeric measure sitting at the intersection of discrete coordinate axes: $(\text{Time}, \text{Product}, \text{Store}, \text{Customer Segment})$.

Equation 1 reveals why the MOLAP dream shattered against the rocks of computational complexity. In a multidimensional lattice of $D$ dimensions, the number of distinct aggregation groupings (cuboids) scales as $2^D$. If a business tracks sales across just 15 dimensions—such as day, store, SKU, cashier, payment method, promotion code, and region—the lattice contains $2^{15} = 32,768$ distinct cuboids.

Worse still is the cell volume. If each dimension has cardinality $C_i$, the total number of cells in the full hypercube is the product $\prod (C_i + 1)$. A catalog of 50,000 products sold across 500 stores over 1,000 days to 100,000 customers yields:

Two and a half quadrillion cells! In practice, 99.999% of these potential cell combinations never occurred—nobody bought a left-handed titanium snowblower in a Miami store on a Tuesday morning. The hypercube was almost entirely empty space. Storing and materializing these sparse cubes triggered catastrophic storage explosion, requiring hours of nightly batch processing during the "cube build window." If an analyst wanted to slice across a dimension not included in the pre-built cube, or query raw non-aggregated transactions, the system was dead in the water.

The alternative was ROLAP (Relational OLAP), championed by Ralph Kimball and Bill Inmon. Instead of pre-aggregating into hypercubes, ROLAP systems kept the data in relational tables organized into Star Schemas and Snowflake Schemas: a massive central Fact Table containing metrics and foreign keys, surrounded by Dimension Tables containing descriptive attributes.

ROLAP solved the combinatorial explosion: storage scaled linearly with raw records ($O(N)$) rather than exponentially with dimensions ($O(2^D)$). But it exposed a catastrophic bottleneck that would plague the database industry for the next fifteen years: the classic row-store relational engine was fundamentally broken for analytical scanning.

3. The Row-Store Tragedy & The Volcano Trap

To understand why early ROLAP systems were brutally slow, one must examine the physical memory and disk layout of traditional relational database engines (Postgres, Oracle, IBM DB2, MySQL InnoDB). These systems were engineered around the N-ary Storage Model (NSM), commonly known as a row store.

In an NSM engine, all attributes belonging to a single tuple are packed contiguously onto a fixed-size disk page (typically 4 KB, 8 KB, or 16 KB), managed via a slotted-page architecture:

[ Page Header: LSN, Free Space Pointers, Slot Array ]
[ Slot 0: Offset 7200 ] [ Slot 1: Offset 6400 ] ...
... [ Free Space Hole ] ...
[ Record 1: id (8B) | ts (8B) | user_id (8B) | ip (16B) | user_agent (128B) | payload (256B) ]
[ Record 0: id (8B) | ts (8B) | user_id (8B) | ip (16B) | user_agent (128B) | payload (256B) ]

This layout is brilliant for OLTP: when an application requests SELECT * FROM users WHERE id = 42, the engine performs a B-tree search to find the page, reads the page from disk or buffer pool, and retrieves all 500 bytes of the user's record in a single contiguous memory copy.

Now consider what happens when you run an analytical aggregation:

SELECT sum(duration_ms) FROM telemetry_events;

The query requires exactly one column: an 8-byte integer (`duration_ms`). But in a row store with a 500-byte average row width, that 8-byte integer is physically interleaved with 492 bytes of irrelevant columns (`user_agent`, `ip_address`, `request_payload`, `session_token`).

This triggers the four structural catastrophes of row-store OLAP:

Let us inspect the Volcano execution model under a CPU profiler. In Volcano, query execution is a pull-based pipeline where the consumer calls child->next() to receive a single tuple:

// The Classic Volcano Iterator Model (Goetz Graefe, 1994)
class Operator {
public:
    virtual void open() = 0;
    virtual Tuple* next() = 0; // Invoked ONCE PER TUPLE!
    virtual void close() = 0;
};

class FilterOperator : public Operator {
    Operator* child;
    Predicate* pred;
public:
    Tuple* next() override {
        Tuple* t;
        while ((t = child->next()) != nullptr) { // Indirect virtual function call
            if (pred->eval(t)) {                // Indirect virtual function call
                return t;
            }
        }
        return nullptr;
    }
};

If a query processes 100 million rows through a pipeline of five operators (`Scan -> Filter -> Project -> HashAggregate -> Sort`), the system executes 500 million virtual function calls.

In modern CPU architecture, a virtual function call is executed via an indirect branch instruction (`call *%rax`). Because the branch destination depends on dynamic vtable resolution, the CPU's branch target buffer (BTB) struggles to predict the target. When the branch predictor misses, the entire instruction pipeline (15 to 20 stages deep on Intel Skylake or AMD Zen) is flushed. The CPU spends 20 to 30 clock cycles doing absolutely nothing while waiting for the pipeline to refill.

Academic profiling by Peter Boncz and Stefan Manegold in the late 1990s demonstrated that in a classic row store executing an analytical query, less than 5% of CPU cycles are spent performing actual computation (adding numbers, comparing values). The remaining 95% of cycles are burned on virtual call overhead, tuple interpretation, record alignment unpacking, and waiting for DRAM cache misses.

A typical single CPU core running Postgres in 2002 could scan and aggregate approximately 5 to 15 million rows per second in memory. The ceiling was hard, physical, and unyielding.

4. The Columnar Epiphany: DSM & C-Store

The breakthrough came from abandoning the assumption that tuples must be stored together.

The conceptual precursor was the Decomposed Storage Model (DSM), formulated in 1985 by George Copeland and Setrag Khoshafian. In DSM, an $N$-attribute relation is decomposed into $N$ separate binary relations, each storing a surrogate key and a single attribute value: $(\text{surrogate\_id}, \text{attribute})$.

In 1999, researchers at CWI (Centrum Wiskunde & Informatica) in Amsterdam—led by Peter Boncz, Stefan Manegold, and Martin Kersten—built MonetDB. MonetDB recognized that if you sort each column identically, you do not even need to store the surrogate key explicitly: the row ID is simply the array index!

A table with three columns (`id`, `status`, `duration`) is stored in memory as three dense, homogeneous, contiguous arrays:

Columnar Memory Layout:
Column "id":        [ 1001, 1002, 1003, 1004, 1005, ... ]  (int64 array)
Column "status":    [  200,  200,  404,  200,  500, ... ]  (int16 array)
Column "duration":  [   42,   18,  105,   33,  210, ... ]  (int32 array)

In 2005, Michael Stonebraker, Daniel Abadi, Samuel Madden, and their collaborators published the historic paper: "C-Store: A Column-oriented DBMS" (which became the foundation for commercial Vertica).

C-Store did not merely store columns separately on disk. It altered the entire execution philosophy by articulating the four pillars of modern columnar systems:

The consequences for hardware efficiency were staggering. Consider our query: SELECT sum(duration_ms) FROM telemetry_events.

In C-Store, the engine reads only the `duration_ms` column file. Zero bytes of `user_agent` or `ip_address` are transferred across the I/O bus. Every single byte loaded from DRAM into the CPU L1 cache is a useful `duration_ms` value. Cache line utilization jumps from 1.6% to 100%.

Simultaneously, the storage footprint drops by 75% to 90% due to columnar compression. Because a 100 GB table compresses to 15 GB on disk, a 1 GB/s storage controller effectively delivers 6 to 10 GB/s of uncompressed analytical throughput.

5. The Big Data Detour & The Lost Cycles

Just as the database research community proved the overwhelming mathematical superiority of columnar architectures, the commercial enterprise software industry executed one of the most bizarre and costly detours in computing history: the Hadoop Big Data revolution (2006–2015).

In 2004, Jeffrey Dean and Sanjay Ghemawat published Google's seminal "MapReduce: Simplified Data Processing on Large Clusters." Apache Hadoop cloned the framework for the open-source world, pairing it with HDFS (Hadoop Distributed File System).

Hadoop's engineering premise was compelling: storage on enterprise SANs and proprietary shared-nothing MPP appliances (Teradata, Netezza) cost thousands of dollars per gigabyte. Hadoop allowed companies to string together hundreds of cheap, disposable commodity servers and store petabytes of raw unstructured logs.

However, when enterprise architects attempted to use Hadoop for SQL analytics via tools like Apache Hive, the result was a performance atrocity.

Consider how Apache Hive (circa 2010) executed our simple analytical aggregation:

1. Line-by-Line Parsing: HDFS stored data in raw text files (CSV, TSV, or raw JSON). A Java mapper process read each line off disk, converted UTF-8 bytes into Java String objects, and executed regular expressions or string splits to extract fields. This generated billions of short-lived Java heap objects, triggering catastrophic JVM Garbage Collection (GC) pauses.
2. Map-Shuffle-Sort Pipeline: For a GROUP BY device_type, the mapper emitted (device_type, duration_ms) key-value pairs. These pairs were serialized to binary, written to local disk, sorted via external merge sort, and transmitted over an HTTP network shuffle to reducers.
3. Disk Spill at Every Boundary: The reducers read the shuffled network bytes, deserialized them back into Java objects, computed the sum, and wrote the final result back to HDFS—which replicated every output block across three separate nodes over the network!

In 2009, an all-star team of database researchers—including Michael Stonebraker, David DeWitt, Andy Pavlo, and Alexander Rasin—published a devastating empirical benchmark: "A Comparison of Approaches to Large-Scale Data Analysis."

Vertica crushed Hadoop by up to 72x, while consuming a fraction of the hardware cluster. Hadoop was burning 99% of its energy serializing Java strings, writing intermediate scratch files to disk, and shuffling uncompressed bytes across network switches.

The software industry spent the next seven years frantically re-inventing columnar relational database techniques inside the Big Data ecosystem:

• Columnar File Formats (2013): Apache Parquet (co-developed by Twitter and Cloudera, based on Google's Dremel paper) and Apache ORC (Hortonworks) replaced raw CSV/JSON on HDFS, bringing C-Store's columnar layout, dictionary encoding, and RLE to the distributed lake.
• In-Memory Directed Acyclic Graphs (2014): Apache Spark replaced MapReduce's disk-spilling boundaries with in-memory Resilient Distributed Datasets (RDDs).
• Project Tungsten (2015): Spark abandoned Java objects, moving memory management off-heap into raw C-style byte buffers (`sun.misc.Unsafe`) to eliminate GC stalls, and introduced whole-stage code generation.

By 2016, the detour was complete. The industry had finally recognized that distributed coordination cannot compensate for an execution engine that disrespects the underlying hardware.

6. Silicon Physics & The Modern Memory Wall

To understand modern OLAP engines, we must discard high-level software abstractions and examine the raw physical architecture of modern silicon.

A high-performance modern server (such as an AMD EPYC 9654 Genoa with 96 cores or an Intel Xeon Platinum 8480+ with 56 cores) operates under a severe, non-negotiable physical hierarchy of latencies and bandwidths.

Notice the colossal cliff between CPU execution speed and DRAM latency: this is the famous Memory Wall.

At a clock frequency of 3.5 GHz, a single CPU core completes 3.5 clock cycles per nanosecond. With modern superscalar execution (e.g., an 8-wide decode/dispatch engine), a core can retire up to 8 instructions per cycle, or 28 instructions per nanosecond.

When that CPU core experiences a cache miss that must go to DRAM, it waits 70 nanoseconds. During those 70 nanoseconds of dead silence, the core could have executed nearly 2,000 instructions!

If your query engine chases pointers across memory—such as following linked lists in a hash table or navigating child nodes in a B-tree—the execution units sit idle, stalled on memory load buffers.

Now examine the Bandwidth Wall. A state-of-the-art dual-socket server equipped with 16 channels of DDR5-5600 memory has a peak aggregate memory bandwidth of roughly $600\text{ GB/s}$.

If an analytical query scans a table where the required columns total 16 bytes per row (e.g., a 64-bit timestamp and an 8-byte metric), what is the maximum physical scanning speed of that entire machine, assuming zero CPU overhead?

Thirty-seven billion rows per second! That is the absolute physical ceiling imposed by Maxwell's equations and silicon chemistry. No algorithm, no quantum compiler, no clever database index can exceed this rate if it must stream uncompressed data from main memory.

And what if the data is compressed on disk? If a columnar encoder compresses those 16 bytes down to 2 bytes (an 8x compression factor), and the CPU cores can decompress the data at line speed using SIMD instructions, the effective scan rate jumps to:

This is where modern OLAP engines live: fighting to saturate the physical bandwidth of the memory bus, the PCIe bus, and the NVMe controller, while ensuring the CPU never stalls on cache misses.

7. Interactive Laboratory: Physical Speed Limits

The interactive laboratory below simulates the physical execution bounds of an analytical query across different hardware architectures, column configurations, compression codecs, and vectorization widths. Explore how memory bandwidth, bus speed, and SIMD instruction throughput interact to create the ultimate query bottleneck.

8. Vectorization vs. Compilation: The Grand Schism

Once data is stored in columns, a profound engineering dilemma emerges: how should the CPU process the data?

Between 2005 and 2011, two competing paradigms arose from European computer science departments, sparking a fierce academic and industrial rivalry that still shapes every query engine built today.

Camp A: Vectorized Execution (Block-at-a-Time)

Pioneered by Peter Boncz, Marcin Zukowski, and Niels Nes at CWI in their historic 2005 paper "MonetDB/X100: Hyper-Pipelining Query Execution" (which became VectorWise).

MonetDB/X100 retained the elegant pull-based Volcano iterator interface, but fundamentally changed the unit of communication. Instead of next() returning a single tuple, it returns a vector of tuples (typically 1,024 or 2,048 values):

// Vectorized Execution Engine (MonetDB/X100, DuckDB, ClickHouse)
class VectorizedOperator {
public:
    // Returns the number of valid rows emitted in the vector batch
    virtual size_t next(VectorBatch& batch) = 0;
};

// Tight, cache-resident inner execution loop
void filter_greater_than_int32(const int32_t* __restrict src,
                               int32_t threshold,
                               uint16_t* __restrict sel_out,
                               size_t count,
                               size_t& out_count) {
    out_count = 0;
    // Compilers auto-vectorize this into AVX2 / AVX-512 SIMD instructions!
    for (size_t i = 0; i < count; ++i) {
        if (src[i] > threshold) {
            sel_out[out_count++] = (uint16_t)i;
        }
    }
}

The engineering genius of vectorization lies in its mechanical sympathy with CPU cache sizing:

• Amortized Virtual Dispatch: Calling next() once every 2,048 rows reduces virtual function dispatch overhead by 99.95%. The cost of the function call drops to less than 0.01 clock cycles per tuple.
• L1/L2 Cache Residency: A vector of 2,048 32-bit integers occupies exactly $8\text{ KB}$ of RAM. This fits completely inside the ultra-fast $32\text{ KB}$ L1 data cache! Subsequent operators in the pipeline process this hot vector while it resides at the highest level of the memory hierarchy.
• Automatic SIMD Vectorization: Because the inner loops operate on contiguous primitive C arrays without pointer chasing, modern compilers (GCC, Clang) automatically transform the scalar loop into 256-bit AVX2 or 512-bit AVX-512 instructions. A single CPU instruction processes 16 integers simultaneously.

Camp B: Whole-Stage Code Generation (Data-Centric Push)

Pioneered by Thomas Neumann at the Technical University of Munich in his landmark 2011 paper "Efficiently Compiling Efficient Query Plans for Modern Hardware" (the foundation of the HyPer database, later acquired by Tableau).

Neumann leveled a radical critique against vectorization: why materialize intermediate vectors in memory at all? Even if vectors reside in L1 cache, moving data from L1 cache into CPU registers and back out to L1 cache still burns clock cycles and load/store buffer bandwidth.

HyPer inverted the control flow. Instead of consumers pulling vectors from producers, producers push tuples up through the pipeline. An entire query plan is compiled at runtime via LLVM IR into native machine code, fusing all relational operators between pipeline breakers (joins, sorts, aggregations) into a single nested loop:

// Whole-Stage Code Generation (HyPer, Spark Tungsten, SingleStore)
// All operators fused into a single machine-code loop:
uint64_t total_events = 0;
int64_t total_duration = 0;

for (size_t i = 0; i < row_count; ++i) {
    int32_t status = col_status[i];
    if (status == 200) {                          // Filter
        int64_t ts = col_timestamp[i];
        if (ts >= 1767225600) {                   // Filter
            int32_t dur = col_duration[i];
            total_events++;                       // Aggregate (Kept in CPU register!)
            total_duration += dur;                // Aggregate (Kept in CPU register!)
        }
    }
}

In HyPer's compiled code, intermediate tuple values never touch memory or cache. They are held exclusively in hardware CPU registers (`%rax`, `%rbx`, `%r12`). The CPU executes at maximum pipeline speed without a single memory load for intermediate values.

Today, the modern consensus has crystallized: ClickHouse, DuckDB, Meta Velox, and Apache DataFusion are built primarily on vectorized execution engines, incorporating targeted runtime code generation only for complex scalar expression evaluation.

9. Interactive Laboratory: Cache Line Traces

To understand why vectorized columnar engines run circles around row stores, observe how memory is physically transferred across the CPU bus. The simulator below traces how a 64-byte hardware cache line is populated during an aggregation query across both storage models.

10. Dissecting ClickHouse: Naked-Metal Throughput

If there is one system that symbolizes the absolute pursuit of raw analytical execution speed on naked hardware, it is ClickHouse.

Born inside Yandex in 2009 to power Yandex.Metrica (the second largest web analytics platform in the world after Google Analytics), ClickHouse was open-sourced in 2016 under the leadership of creator Alexey Milovidov.

ClickHouse was designed with an unapologetic engineering philosophy: treat the hardware with total mechanical transparency, eliminate every intermediate abstraction layer, and optimize every loop to the bare assembly instructions.

The MergeTree Engine & Sparse Primary Indexing

At the heart of ClickHouse is the MergeTree table engine family.

Unlike transactional databases that build B-trees where every single row has an index entry, ClickHouse uses a sparse primary index:

ClickHouse MergeTree Physical Storage Layout:

[primary.idx] (Sparse Primary Index - Kept in RAM entirely!)
Mark 0: (Date='2026-01-01', UserID=100)      --> points to Row 0
Mark 1: (Date='2026-01-01', UserID=8420)     --> points to Row 8,192
Mark 2: (Date='2026-01-02', UserID=14)       --> points to Row 16,384

[duration.bin] (Compressed Column Data)
[ Granule 0 (Compressed LZ4) ] [ Granule 1 (Compressed LZ4) ] [ Granule 2 ... ]

[duration.mrk2] (Marks File: Byte Offsets in duration.bin)
Mark 0: compressed_offset: 0,       decompressed_offset: 0
Mark 1: compressed_offset: 14210,   decompressed_offset: 0
Mark 2: compressed_offset: 29840,   decompressed_offset: 0

The fundamental unit of physical data in ClickHouse is a granule, consisting of exactly 8,192 rows by default.

The primary index does not record every row: it records only the values at the boundary of each granule. For a table of one billion rows, the primary index contains only $1{,}000{,}000{,}000 / 8{,}192 \approx 122{,}070$ entries. This entire index fits in a few megabytes of RAM!

When a query arrives with WHERE Date = '2026-01-01' AND UserID BETWEEN 5000 AND 9000, ClickHouse performs binary search on the in-memory sparse index in a few microseconds, identifies the exact range of granules to scan, and completely skips the rest.

Relentless Micro-Optimizations

Where ClickHouse separates itself from almost every other database in the world is its fanatical attention to micro-architectural optimizations:

On a modern 64-core server, ClickHouse routinely achieves scan speeds of 30 to 50 GB/s from NVMe storage, and in-memory aggregation throughput exceeding 10 to 20 billion rows per second.

11. Dissecting Druid & Pinot: High-QPS Real-Time

While ClickHouse is often used for ad-hoc analytical queries, a distinct engineering challenge emerged in Silicon Valley around 2011: user-facing, high-concurrency real-time OLAP.

Consider LinkedIn's "Who Viewed My Profile" feature, or programmatic advertising exchanges (Metamarkets):

• Millions of events per second flowing in from Apache Kafka.
• 50,000 queries per second generated by hundreds of thousands of concurrent web and mobile users.
• A non-negotiable service level agreement (SLA): p99 query latency must remain under 50 milliseconds.

If you send 50,000 queries per second to an engine like Snowflake or classic ClickHouse, and each query scans 10 million rows, the database will melt. You cannot brute-force scan billions of rows 50,000 times a second.

This led to the creation of Apache Druid (born at Metamarkets in 2011) and Apache Pinot (born at LinkedIn in 2013, later powering Uber's real-time dashboards).

The Architecture of Sub-50ms Real-Time Ingestion

Druid and Pinot solved this with a hybrid architecture combining real-time streaming ingestion with immutable columnar segments:

Druid / Pinot Dual Ingestion Architecture:

Kafka Topic  -->  [ Real-Time Ingestion Node (Mutable) ]
                  • Ingests raw events into concurrent in-memory buffer
                  • Queries hit memory buffer for 0-latency freshest data
                  • Periodically seals data into an IMMUTABLE SEGMENT

Immutable Segment Generation:
  1. Dictionary-encode all dimension columns
  2. Generate Inverted Bitmap Indexes (Roaring Bitmaps)
  3. Pre-aggregate metrics via Star-Tree Index
  4. Push sealed segment to Deep Storage (S3 / HDFS) & Historical Nodes

The Secret Weapon: Inverted Bitmap Indexes & Roaring Bitmaps

In Druid and Pinot, every dimension column is dictionary-encoded and accompanied by an inverted bitmap index.

Suppose a table contains 10 million events. The column `country` has values `US`, `UK`, `DE`. The engine constructs a bitset for each unique value:

Bitmap for 'US':  [ 1, 0, 1, 1, 0, 0, 1, ... ]  (10 Million bits)
Bitmap for 'iOS': [ 0, 1, 1, 0, 0, 1, 1, ... ]  (10 Million bits)

When a query arrives:

SELECT count(*) FROM events WHERE country = 'US' AND os = 'iOS';

The engine does not scan rows. It retrieves the bitmap for `US` and the bitmap for `iOS`, and executes a bitwise `AND` operation (`vpand` on AVX-512). A 512-bit vector register intersects 512 rows in a single clock cycle!

Using Roaring Bitmaps (invented by Daniel Lemire et al.), these bitsets are compressed into dynamic hybrid containers (run-length arrays for dense regions, sorted 16-bit integer lists for sparse regions). The engine performs set intersections directly on the compressed containers without decompressing them.

Star-Tree Pre-Aggregation: Beating the Combinatorial Explosion

Apache Pinot introduced the Star-Tree Index to solve the classical MOLAP hypercube combinatorial explosion.

Instead of building a full multi-dimensional cube across all $2^D$ cuboids, a Star-Tree builds a tree structure inside each segment, splitting only on high-cardinality dimensions and stopping when a leaf node contains fewer than a configurable threshold of rows (e.g., $10{,}000$ rows). For any query whose group-by dimensions match a path in the Star-Tree, the engine answers the query in sub-millisecond time by reading pre-aggregated leaf values, eliminating the scan phase entirely.

12. Dissecting DuckDB: The In-Process Revolution

By 2019, the database industry had spent twenty years assuming that any analytical database worthy of the name must be a distributed client-server cluster. If you wanted fast OLAP, you had to spin up a ClickHouse cluster, provision a Snowflake warehouse, or launch an AWS Redshift cluster.

Then Hannes Mühleisen and Mark Raasveldt at CWI in Amsterdam asked a disruptive question:

"SQLite is the most widely deployed database engine on the planet because it is embedded directly in-process with zero administration. Why is there no SQLite for analytics?"

The result was DuckDB.

DuckDB operates under a radically different premise: today's developer laptops have 16 CPU cores and 64 GB of unified memory. High-end cloud instances have 128 cores and 512 GB of RAM. The vast majority of real-world analytical queries operate on datasets between 1 GB and 500 GB—a scale that fits entirely within the RAM or NVMe flash of a single commodity workstation!

The Zero-Copy / Zero-Overhead In-Process Architecture

When you run a query against a traditional database over a network socket, an enormous amount of time is wasted on protocol serialization:

Traditional Client-Server Pipeline:
DB Internal Memory -> Serialize to Wire (Postgres/MySQL Wire Protocol) ->
TCP Socket -> Network Stack -> Client TCP Receive -> Deserialize to Python Objects
(Massive memory allocations, byte copies, and CPU deserialization burn!)

In DuckDB, the database runs inside your application's address space as a linked C++ library.

Through its deep integration with Apache Arrow, DuckDB achieves zero-copy data sharing with Python (Pandas, Polars, NumPy) and R:

// DuckDB In-Process Shared Memory Transfer:
// DuckDB exposes its internal columnar vector pointers directly to Python!
// Zero copies. Zero serialization. Memory transfer bandwidth: INFINITE.

A Python data scientist can execute an analytical SQL query over 100 million rows, and DuckDB transfers the resulting columnar table into a Pandas or Polars DataFrame in 0.00 milliseconds simply by passing the underlying virtual memory pointers.

Morsel-Driven Parallelism & The DuckDB Vector Engine

Under the hood, DuckDB is a state-of-the-art vectorized execution engine based on the latest academic research:

DuckDB also revolutionized lakehouse querying: it can read directly from remote Parquet files on AWS S3 using HTTP range requests. By reading only the Parquet metadata footer, DuckDB identifies the exact byte offsets of the required columns and row groups, downloading only the tiny fraction of data needed for the query.

13. How Fast Can You Do OLAP These Days?

We have traversed thirty years of computer systems engineering: from Codd's combinatorial hypercubes to the memory wall, from C-Store's columnar projections to the modern vectorized engines of ClickHouse, Pinot, and DuckDB.

Now we arrive at the concrete question: How fast can you actually do OLAP today?

Let us catalog the verified, real-world performance ceilings across modern hardware in 2026.

1. Single-Core In-Memory Throughput

On an individual CPU core (Intel Raptor Lake, AMD Zen 4/5, or Apple M3/M4) running a modern vectorized engine:

• Simple Filtering & Counting (`count(*) WHERE x > 10`): 2.5 to 5.0 billion rows per second per core using AVX-512 or Neon SIMD bitmask instructions.
• Numeric Aggregation (`sum(x), avg(y)`): 1.5 to 3.0 billion rows per second per core.
• Low-Cardinality Group-By (`GROUP BY status`): 800 million to 1.5 billion rows per second per core using direct array lookup.
• High-Cardinality Group-By (`GROUP BY user_id` with millions of keys): 80 to 200 million rows per second per core, bound by L3 cache line thrashing in the hash table.

2. Single-Socket Multi-Core Server (64 Cores, DDR5 DRAM)

Scaling across all cores on a single 64-core enterprise server with 8-channel DDR5 memory ($250\text{ GB/s}$ sustained memory bandwidth):

• In-Memory Uncompressed Scan: 20 to 40 billion rows per second. At this point, the query is 100% bound by the physical memory bus. Adding more CPU cores yields zero speedup because the memory controller cannot deliver bytes any faster.
• In-Memory Compressed Scan (Bit-packed / Dictionary): 60 to 120 billion rows per second. By decompressing data in L1/L2 cache, the effective bandwidth multiplier allows the cores to process records far faster than raw DRAM bandwidth!

3. NVMe Storage Arrays (Disk-Bound OLAP)

When data cannot fit in RAM and must stream off high-speed enterprise NVMe SSDs:

• Single PCIe Gen 5 NVMe SSD: Sustained read bandwidth of 10 to 14 GB/s. With typical 4x columnar compression, the engine scans effectively 40 to 56 GB/s of data (5 to 10 billion rows per second).
• PCIe Gen 5 Quad NVMe RAID 0 Array: Sustained read bandwidth of 40 to 50 GB/s. Effective scanning throughput reaches 150 to 200 GB/s (15 to 35 billion rows per second) straight off disk!
• Time to aggregate 1 Billion rows from disk: Under 50 milliseconds.

4. Real-Time User-Facing Concurrency (Apache Pinot / ClickHouse)

For high-concurrency production deployments serving live internet traffic:

• Query Concurrency: 30,000 to 100,000+ Queries Per Second (QPS) on a 20-node cluster.
• Latency Profile: p50: 8 ms, p95: 22 ms, p99: 45 ms over a dataset of 50 billion events.
• Real-Time Freshness: Events published to Apache Kafka are queryable in the database within 150 to 300 milliseconds of generation.

5. In-Process Laptop Analytics (DuckDB)

On an ordinary developer laptop (Apple MacBook Pro M3 Max, 16 cores, unified memory):

• Querying a 100-Million-Row Local Parquet File: Full aggregate scan completes in 180 milliseconds.
• Zero-Copy Handoff to Python: Converting a 10-million-row query result into an Apache Arrow table or Polars DataFrame takes less than 1 millisecond.

14. Interactive Laboratory: Multi-Engine Selector

Because modern OLAP engines have specialized to conquer different hardware trade-offs, there is no single "best" database. An engine optimized for 50,000 QPS user-facing dashboard queries (Pinot) makes radically different architectural trade-offs than an in-process library designed for zero-copy Python data science (DuckDB) or a naked-metal batch monster (ClickHouse).

Use the interactive decision matrix below to evaluate the optimal engine for your specific workload requirements.

15. The Frontier: GPUs, CXL & Unbundled Lakehouses

Where is analytical processing heading next? As CPU clock frequency gains remain modest and memory bus scaling slows, the database vanguard is pursuing three physical frontiers:

1. GPU Acceleration: Massive Parallel Bandwidth (HBM3)

Why use GPUs for OLAP? The answer is not primarily the thousands of CUDA tensor cores; it is memory bandwidth.

A high-end server CPU using DDR5 DRAM accesses memory at $250\text{ to }350\text{ GB/s}$. An NVIDIA H100 or B200 GPU equipped with HBM3e (High Bandwidth Memory) accesses memory at an astonishing 3,350 to 8,000 GB/s (3.35 to 8.0 TB/s)!

Projects like NVIDIA cuDF, HeavyDB (OmniSci), and GPU-accelerated DuckDB plugins exploit this massive memory pipeline. A GPU can execute a columnar scan and filter over a billion rows in under 3 milliseconds.

However, GPU OLAP faces its own brutal physical bottleneck: the PCIe bus. Transferring data from host CPU memory to GPU memory across PCIe Gen 5 x16 is limited to $64\text{ GB/s}$. If the dataset does not already reside in GPU HBM memory, the PCIe transfer time completely erases the GPU's execution advantage. GPU OLAP is phenomenal for iterative analytics over fixed in-memory datasets, but challenging for petabyte-scale streaming data.

2. CXL (Compute Express Link): Disaggregated Memory Pools

The emerging CXL (Compute Express Link) standard represents the most profound motherboard-level architecture shift in twenty years.

Historically, DRAM was hard-wired to CPU sockets via point-to-point DDR channels. If a server ran out of memory, queries had to spill to slow NVMe disk or distribute across a network cluster with high-latency TCP shuffles.

CXL runs on top of the physical PCIe 5.0/6.0 electrical interface, providing cache-coherent memory transactions with sub-200ns latency. Servers can attach external CXL memory expansion chassis, providing terabytes of pooled DRAM accessible directly via standard load/store CPU instructions. This enables single-node vectorized engines like DuckDB and ClickHouse to query 10-terabyte datasets entirely in RAM without network shuffles!

3. Kernel-Bypass I/O: io_uring & SPDK

As NVMe storage devices became capable of millions of IOPS and 14 GB/s throughput, the operating system kernel itself became the bottleneck.

Traditional POSIX pread() calls require a user-to-kernel context switch, interrupt handling, and virtual memory page mapping for every I/O request. At 1,000,000 IOPS, context switches alone burn 20% to 30% of all CPU cycles.

Modern engines are transitioning to io_uring (Linux) and SPDK (Storage Performance Development Kit). Using shared-memory ring buffers between user space and kernel space, an engine submits thousands of asynchronous I/O requests with zero system calls. Combined with direct polling of the NVMe completion queue, storage I/O operates at pure wire speed.

4. The Unbundled Lakehouse: Modular Engine Synthesis

The final structural shift is the complete decomposition of the monolithic database:

The Modern Deconstructed Analytical Stack:

┌────────────────────────────────────────────────────────┐
│  Query Engines:  DuckDB  |  ClickHouse  |  Trino  |  DataFusion  │
└────────────────────────────────────────────────────────┘
                           │ (Apache Arrow In-Memory Spec)
┌────────────────────────────────────────────────────────┐
│  Lakehouse Catalogs:  Apache Iceberg  |  Delta Lake    │
└────────────────────────────────────────────────────────┘
                           │ (Parquet / ORC Columnar Format)
┌────────────────────────────────────────────────────────┐
│  Storage Substrate:  AWS S3  |  NVMe Flash  |  Ceph    │
└────────────────────────────────────────────────────────┘

In this new world, storage is standardized on Apache Parquet and open table formats like Apache Iceberg. In-memory data representation is standardized on Apache Arrow.

Engines are no longer proprietary silos that trap your data. Reusable C++ execution libraries like Meta Velox and Rust engines like Apache DataFusion provide hyper-optimized vectorized kernels that power Spark, Presto, and custom lakehouse systems alike.

16. Epilogue: The Victory of Mechanical Sympathy

In 1993, an analyst running an ad-hoc query over a million records waited thirty minutes for a row-store relational engine to churn through magnetic disk platters, while a MOLAP administrator spent ten hours every night pre-computing sparse hypercubes.

Today, an engineer sitting in a coffee shop with a laptop can launch DuckDB inside a Python terminal, point it at a hundred million rows of compressed Parquet data resting on an Amazon S3 bucket three thousand miles away, and receive aggregated results in less than two seconds.

On a dedicated server running ClickHouse, a query over ten billion rows flashes across the screen in 400 milliseconds.

On an Apache Pinot cluster, fifty thousand users simultaneously refresh their dashboards every second, receiving sub-20-millisecond answers from SIMD-evaluated Roaring Bitmaps.

This transformation was not delivered by magical breakthroughs in artificial intelligence or esoteric query languages. It was won in the trenches of computer systems engineering:

• By recognizing that columns—not rows—are the natural representation for analytical aggregation.
• By aligning memory blocks with 64-byte hardware cache lines to eliminate bus pollution.
• By replacing polymorphic Volcano iterators with 2,048-tuple vectorized loops that compile directly into AVX-512 and Neon SIMD instructions.
• By compressing homogeneous column data so densely that the CPU decompressor effectively multiplies the physical bandwidth of the memory bus.
• By dismantling the unnecessary overhead of client-server networking to embrace zero-copy in-process execution.