30-Second Glance
- Row stores (InnoDB) fetch whole rows — optimal for OLTP point reads/writes; column stores fetch only queried columns — optimal for OLAP scans over billions of rows.
- Columns compress 5–20× (RLE, dictionary, bit-packing on sorted low-cardinality data) and feed vectorized/SIMD execution (1024-row batches).
- Sort key / clustering order decides everything: range-skipping (zone maps) prunes 90–99% of I/O on filtered columns, starves unfiltered ones.
- Writes go through delta stores / merge-trees (batched, immutable parts); single-row updates are the known weakness — pair with a row-store primary.
Row vs Column: The I/O Argument
An orders table with 50 columns where the query needs 3 (SUM(amount) GROUP BY region): a row store reads all 50 columns' bytes off disk to use 3 (16× waste); a column store reads exactly the 3 column files. At 1B rows × 500 bytes/row = 500 GB scanned row-wise vs ~3 columns × 8 bytes × 1B = 24 GB column-wise (before compression) — the difference between a 10-minute and a 10-second query. This single ratio decides OLAP engine choice more than any other factor.
The mirror image: SELECT * WHERE id = ? touches one row — the row store does 1 page read; the column store must seek into 50 separate column files and stitch the row back (50 seeks). OLTP belongs on rows.
Compression: Why Columns Win
Values within one column look alike (same type, often sorted runs, low cardinality), so lightweight codecs beat generic gzip:
| Codec | Idea | Wins when | Typical ratio contribution |
|---|---|---|---|
| Run-length (RLE) | AAAA → (A,4) |
Sorted / clustered low-cardinality (region, status, dates) | 10–100× on sorted runs |
| Dictionary | Map distinct values → small ints | Low cardinality (gender, country, category) | 3–10× |
| Bit-packing / FOR | Store deltas from min in few bits | Narrow numeric ranges (ages, small ints) | 2–4× |
| Delta + Gorilla-style XOR | Deltas / XOR of successive floats | Timestamps, metrics (see Stream Processing time-series) | 3–8× on series |
| LZ4/ZSTD on top | General entropy squeeze per block | Cold blocks where CPU is free | Extra 1.5–3× |
End-to-end 5–20× vs raw row storage is normal (Parquet on S3, ClickHouse MergeTree, Redshift). Bonus: predicates can evaluate directly on encoded data (dictionary compare without decode), and zone maps / min-max indexes per block let the engine skip blocks whose range excludes the filter — sort key choice makes or breaks this (prune 99% on the sort column, ~0% on uncorrelated columns).
Execution and Writes
Vectorized execution processes 512–4096 values per batch with tight SIMD loops instead of per-row virtual calls — 3–10× CPU speedups on scans/aggregations (MonetDB/X100 heritage; now DuckDB, ClickHouse, Snowflake, BigQuery). Late materialization keeps row IDs through filters and fetches other columns only for survivors.
Writes are the weakness: in-place single-row updates fight immutability + compression. Production designs batch them:
- Delta + merge (SQL Server columnstore deltastore, SAP HANA L1-delta): small writes land in a row-wise delta, merged into compressed columns asynchronously.
- MergeTree parts (ClickHouse): inserts create new immutable sorted parts; background merges compact them — LSM philosophy applied to columns (see LSM Trees and SSTables).
- Micro-partitions (Snowflake): 50–500 MB immutable Parquet-ish files with rich metadata; DML rewrites whole files — fine at warehouse cadence, hopeless at OLTP rates.
Rule: column stores ingest in batches of ≥10K–1M rows (or per-second micro-batches); trickle single-row writes go to a row store first (CDC/Kafka → warehouse).
Concrete Numbers
| Quantity | Typical value | Why it matters |
|---|---|---|
| Scan selectivity payoff | Reading 3/50 cols ≈ 6% of row-store I/O | The core OLAP win |
| Compression | 5–20× end-to-end | 500 GB rows → 25–100 GB columns; S3/scan cost falls proportionally |
| Vector batch | 1024–4096 rows | SIMD + cache-resident working set |
| Zone-map pruning | 90–99% blocks skipped on sort-key filters | Sort key = most-filtered column, almost always time |
| Part/file size | 50–500 MB (Snowflake), 1–10 GB parts (ClickHouse) | Too small = metadata/RPC storm; too large = rewrite amplification |
| Ingest batch floor | ≥10K rows / file-part | Below this, merge/metadata overhead dominates |
Trade-offs
| Approach | Pros | Cons | Use when |
|---|---|---|---|
| Columnar (Parquet/ClickHouse/Snowflake/BigQuery) | 10–100× scan speed; huge compression; cheap S3 scans | Single-row reads/writes terrible; sort-key dependent; DML rewrites files | Analytics, dashboards, feature pipelines, lakehouse |
| Row store (InnoDB/Postgres) | Point reads/writes, transactions, secondary indexes | Full-row I/O kills wide scans; weak compression | OLTP primary; feed warehouse via CDC |
| Hybrid HTAP (delta+column, TiDB/TiFlash, AlloyDB columnar) | One system, both shapes | Replication lag between stores; ops complexity; cost | Fresh-analytics-on-OLTP without a pipeline |
Standard HLD answer: row-store RDBMS for serving + CDC (Debezium/Kafka) → columnar warehouse/lakehouse (Parquet on S3 + query engine) for analytics. Never serve 1B-row aggregations from the OLTP primary.
Interview One-Liners
- Q: Row vs column in one line each? A: Row: one row = contiguous bytes, best for point reads/writes. Column: one column = contiguous bytes, best for scans over few columns of many rows.
- Q: Why does columnar compress so much better? A: Same-type, often sorted/r low-cardinality runs let RLE/dictionary/bit-packing fire; row pages mix types and defeat all three.
- Q: What is the sort key and why does everyone ask about it? A: The physical clustering order enabling zone-map pruning + RLE runs — filters on it skip 90–99% of I/O; other columns get little pruning.
- Q: How do column stores execute queries faster beyond I/O? A: Vectorized SIMD batches (1024+ values per tight loop) + encoded-data predicates + late materialization.
- Q: Why are single-row writes bad in column stores? A: They fight immutable compressed parts: each write would decode/rewrite megabytes — so engines buffer into deltas/parts and merge asynchronously.
- Q: Standard OLTP + analytics topology? A: Row-store primary for serving, CDC stream to a columnar warehouse/lakehouse for analytics — HTAP only when sub-minute freshness justifies the complexity.