Under the Hood: Vectorized OLAP Engines vs. Distributed LSM Tables
Engineers often confuse Wide-Column stores (Apache Cassandra, ScyllaDB, Google Cloud Bigtable) with true Columnar databases (ClickHouse, Snowflake, DuckDB, AWS Redshift, Apache Parquet). Despite both referencing 'columns', their underlying storage engines serve diametrically opposed workloads:
- Wide-Column (Cassandra / ScyllaDB): An OLTP engine designed for massive horizontal distributed writes. Under the hood, data is organized around a
Partition Keyand aClustering Key. Rows are stored in an LSM-tree sorted by clustering key. It is effectively a two-dimensional key-value store:Map<PartitionKey, SortedMap<ClusteringKey, Columns>>. Within a single SSTable a row's columns are stored together, but one logical row can be spread across several SSTables (and the memtable) until compaction merges them. - Columnar (ClickHouse / Parquet): An OLAP engine designed for aggregate analytical queries across billions of rows (e.g.,
SELECT avg(latency), sum(bytes) FROM logs WHERE status = 500). On disk, values for a single column across millions of rows are packed sequentially into compressed data blocks.
Disk Layout: Row-Oriented vs. Pure Columnar
flowchart TD
subgraph RowLayout ["Row / Wide-Column Storage (Cassandra / Postgres)"]
R1["Row 1: [ID: 1 | Timestamp: 10:00 | Status: 200 | Latency: 42ms | Payload: ...]"]
R2["Row 2: [ID: 2 | Timestamp: 10:01 | Status: 500 | Latency: 980ms | Payload: ...]"]
R3["Row 3: [ID: 3 | Timestamp: 10:02 | Status: 200 | Latency: 35ms | Payload: ...]"]
end
subgraph ColumnarLayout ["Pure Columnar Storage (ClickHouse / Parquet)"]
C_ID["Col ID: 1, 2, 3 - bit-packed"]
C_Time["Col Timestamp: 10:00, 10:01, 10:02 - delta-encoded"]
C_Status["Col Status: 200, 500, 200 - dictionary compressed"]
C_Latency["Col Latency: 42, 980, 35 - SIMD vectorized scan"]
end
Why Columnar Storage Wins for Analytics: Vectorization & Compression
When calculating an average across 100 million logs, a row-oriented database must read all row data (including user agents, payloads, request URLs) from disk into RAM, discarding 95% of the bytes read. A columnar engine only reads the latency column file from disk.
Furthermore, because identical data types sit adjacent on disk, columnar engines often achieve around 10:1 compression (highly workload-dependent) using specialized codecs:
- Run-Length Encoding (RLE): Sequences of identical values (e.g.,
[200, 200, 200, 200]) compress to[200, count=4]. - Delta Encoding: Timestamps (
1600000000, 1600000001, 1600000003) store only the small deltas (+1, +2), which compress down to single bytes. - SIMD Vectorization: Modern CPUs can execute mathematical operations on 512-bit registers (AVX-512) processing 16 32-bit integers per instruction (actual per-cycle throughput depends on the microarchitecture).