Skip to content

Columnar vs Row Storage#

Problem statement (interviewer prompt)

Two workloads on the same data: an OLTP system fetches single rows by primary key 10,000x/sec; an analytics dashboard scans tens of millions of rows and aggregates one column. Why does no single storage format optimize both?

Row stores keep all columns of a record together; column stores keep each column as a separate vector. The choice drives I/O, compression, and query patterns.

flowchart TB
  subgraph RowStore[Row store - Postgres, MySQL]
    R1[id=1, name=A, total=100, status=PAID]
    R2[id=2, name=B, total=200, status=NEW]
    R3[id=3, name=C, total=150, status=PAID]
  end
  subgraph ColStore[Column store - ClickHouse, Parquet, Snowflake]
    C1[id: 1, 2, 3]
    C2[name: A, B, C]
    C3[total: 100, 200, 150]
    C4[status: PAID, NEW, PAID]
  end

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class R1,R2,R3,C1,C2,C3,C4 datastore;

Row stores fetch a full record in one I/O; column stores scan only the columns the query needs, compress hard, and vectorise.

The physical layout of bytes determines what's cheap and what's expensive. Row stores were the OLTP default for decades; columnar formats redefined what's possible for analytics.

Physical layout#

flowchart TB
  subgraph Row[Row layout - heap]
    P1[Page 1<br/>row 1: id,name,total,status<br/>row 2: id,name,total,status<br/>row 3: id,name,total,status]
  end
  subgraph Col[Columnar layout]
    Bid[id block]
    Bn[name block]
    Bt[total block]
    Bs[status block]
  end

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class P1,Bid,Bn,Bt,Bs datastore;

Why columnar wins on analytics#

A query like SELECT AVG(total) FROM orders WHERE created_at > '2024-01-01':

  • Row store reads every column of every matching row from disk.
  • Column store reads only the created_at and total columns - often 5-10x less I/O.

Plus:

  • Compression: a column is uniform in type; codecs like dictionary, RLE, delta, and bit-packing get 10-100x compression on real workloads.
  • Vectorised execution: CPU operates on arrays of values per instruction (SIMD).
  • Late materialisation: filter on one column, then fetch only the rows that survive.

Why row stores still rule OLTP#

  • A single-row fetch in a row store reads one page; in a column store, one page per column.
  • INSERTs append one record; column stores must touch every column block.
  • UPDATEs of one column trigger full-record updates in columnar (most are append-only).

Encoding tricks#

Columnar formats stack multiple codecs:

Codec Idea Best on
RLE Encode runs of equal values Sorted or low-cardinality columns
Dictionary Replace strings with int ids Repeated categorical values
Bit-packing Use only needed bits per integer Small-range integers
Delta Store differences from previous Time series, monotonic ids
General compression zstd, snappy, lz4 over a block Universal final pass

A timestamp column compressed with delta + bit-pack + zstd often shrinks 50:1.

Modern columnar systems#

System Where used
ClickHouse OLAP server, embedded analytics
Snowflake / BigQuery Cloud warehouses
Redshift / Synapse Cloud warehouses
DuckDB Embedded OLAP
Vertica / SingleStore On-prem OLAP

File formats (lakehouse era)#

Format Used by
Parquet Spark, Trino, Athena, BigQuery external
ORC Hive
Arrow In-memory analytics, IPC
Avro Schema-evolution-friendly row format

Parquet is the default columnar format for lakehouse storage; combined with Delta/Iceberg/Hudi (see data-lake-warehouse).

Hybrid - HTAP#

"Hybrid Transactional / Analytical Processing" tries to serve both:

  • TiDB / SingleStore / CockroachDB: dual row + column storage; query optimizer picks.
  • MemSQL / SingleStore: in-memory row + columnar.
  • Snowflake hybrid tables: row-optimized OLTP layer.
  • Postgres + Citus columnar: column extension for one table family.

True HTAP is hard: the workloads stress the same engine in opposite directions.

When to choose which#

Workload Pick
Single-row lookup, write-heavy Row store
Multi-row scan, aggregations Columnar
Both, on same DB HTAP or ELT to a columnar warehouse
Cheap object storage, ad-hoc analytics Parquet + Trino/Spark

Where the layout decision sits#

flowchart TB
  CR((Columnar /<br/>row storage))
  SE[Storage engines LSM / B-Tree<br/>row-store family]
  LAKE[Data lake / warehouse<br/>where Parquet lives]
  LK[Lambda / Kappa<br/>columnar is analytics tier]
  IDX[Indexing strategies<br/>zone maps, min/max]
  SE -. row alt .- CR
  CR --> LAKE
  LK --> CR
  CR --> IDX

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class CR service;
    class SE,LAKE,LK,IDX datastore;

Glossary & fundamentals#

Tag Concept What it is Page
HLD Storage engines LSM B-Tree the row-store engine families storage-engines-lsm-btree
HLD Data Lake Warehouse Parquet's home data-lake-warehouse
HLD Lambda Kappa architecture columnar is the analytics tier lambda-kappa-architecture
HLD Indexing strategies indexes inside columnar (zone maps, min/max) indexing-strategies

Quick reference#

Pick#

Workload Layout
Single-row OLTP Row
Wide-scan analytics Columnar
Both, one engine HTAP or ELT to OLAP

Why columnar wins on scans#

  • Reads only the columns the query needs
  • Compression (RLE, dictionary, delta, bit-pack, zstd)
  • SIMD vectorised execution
  • Late materialisation

Why row wins on OLTP#

  • 1 row = 1 page read
  • INSERT = append a record
  • UPDATE doesn't touch all columns

Modern columnar#

  • ClickHouse, DuckDB, Snowflake, BigQuery, Redshift, Vertica

Lakehouse formats#

  • Parquet (default)
  • ORC (Hive)
  • Arrow (in-memory)

HTAP#

  • TiDB, SingleStore, CockroachDB
  • Snowflake hybrid tables
  • Citus columnar in PG

Refs#

  • Abadi & Madden - Column-Stores vs Row-Stores
  • Parquet spec
  • ClickHouse architecture docs

FAQ#

What is the difference between columnar and row storage?#

Row stores keep all columns of a record together, ideal for OLTP point lookups. Column stores keep each column as a contiguous vector, ideal for OLAP scans and aggregations on a few columns.

Why are columnar databases faster for analytics?#

They read only the columns the query needs, skipping the rest. Contiguous values of the same type compress well and let CPUs use SIMD. This often reduces I/O by 10 to 100 times for scans.

When should I use a row store?#

Use a row store like Postgres or MySQL for transactional workloads where you read and write whole rows, need ACID, secondary indexes, and per-row updates. OLTP is the natural fit.

Parquet vs CSV, which is better?#

Parquet is columnar with built-in compression and predicate pushdown, typically 10x smaller and 100x faster for analytics than CSV. CSV is only useful for ad-hoc exchange with non-data tools.

Can one database be both row and column?#

Yes, hybrid systems like SQL Server columnstore indexes, Postgres with citus columnar, and SingleStore mix both formats per table. They are slower than pure columnar but flexible across workloads.

Further reading#