Columnar vs Row Storage: Why OLAP Queries Get 100× Faster

The same data, two layouts

Consider a table of pageviews with thirty columns and ten million rows. A row store writes each record contiguously:

[id=1, ts=…, path=/home, country=IN, browser=Chrome, …30 fields]
[id=2, ts=…, path=/blog, country=US, browser=Safari, …30 fields]

A column store writes each column contiguously:

id:      [1, 2, 3, 4, …]
ts:      [1719…, 1719…, …]
country: [IN, US, US, IN, …]
browser: [Chrome, Safari, Safari, …]

Identical information. The layout is the entire difference.

Why layout decides the winner

Now run SELECT country, count(*) FROM pageviews GROUP BY country.

The row store must read every row to reach the country field in each — thirty columns' worth of bytes to use one. If a row is 300 bytes and only 8 of them matter, you have read 37× more data than the query needs.

The column store reads the country column and nothing else. Same answer, a fraction of the I/O. That is the bulk of the speedup, and it comes from nothing cleverer than not reading bytes you did not ask for.

Compression compounds it

Sorted, homogeneous data compresses far better than mixed rows. A country column is a handful of distinct values repeated millions of times:

• Dictionary encoding — store ['IN','US','GB'] once, then indexes into it.
• Run-length encoding — IN × 40,000 instead of forty thousand copies.
• Bit packing — a column with 200 distinct values needs 8 bits per value, not 64.

A country column that is 80 MB raw is often under 2 MB encoded. That is 2 MB moving from disk to memory instead of 300 MB of full rows — and it frequently means the working set fits in cache.

Vectorized execution

The third multiplier is how the data is processed. Row engines typically evaluate one row at a time through the operator tree: a function call per row, per operator, with branch mispredictions throughout.

Columnar engines process vectors — batches of a few thousand values from one column — in tight loops over contiguous, uniformly typed memory. That is exactly what CPUs and SIMD instructions are built for. One function call amortized over 2,048 values instead of 2,048 calls.

Layout, compression, and vectorization stack. Together they explain order-of-magnitude differences, not a 20% edge.

Where row stores still win

Columnar is not universally better. It loses badly at:

• Single-row lookups. SELECT * FROM users WHERE id = 42 in a column store means touching every column's storage separately to reassemble one row. A row store fetches one contiguous block.
• Frequent single-row updates. Changing one field means rewriting a compressed block. Column stores generally prefer append-only or batch rewrite.
• Transactional workloads. Row-level locking, foreign keys, high-concurrency writes — this is what OLTP engines are built for.

This is the whole reason "just use one database" is an unsatisfying answer for analytics.

The practical consequence

Serving a dashboard from a transactional row store means paying for full-row reads on every aggregation. Serving your application from a column store means paying for column reassembly on every lookup.

InsightsTrack keeps them separate: PostgreSQL takes the writes — single-row, transactional, durable — and a sync feeds DuckDB, which serves every read. The architecture deep-dive covers the full design. Each engine runs the workload its layout was designed for.

Frequently asked questions

What makes columnar storage faster for analytics?

It reads less. Aggregating one column in a row store still pulls every column of every matching row off disk; a columnar layout reads just that column. Add per-column compression and vectorized execution and the gap compounds.

Is columnar storage always the better choice?

No. Fetching or updating a single complete record — the shape of most transactional work — means touching every column, which a row store does in one contiguous read and a columnar store does as many separate ones.

Why does columnar data compress so much better?

Because a column holds one type with repeated values, which run-length and dictionary encoding exploit directly. A row mixes types and values, leaving far less structure for a compressor to work with.

What is vectorized execution?

Processing values in batches through tight loops rather than one row at a time through an interpreter. It keeps data in CPU cache and lets the compiler emit SIMD instructions, which is why the speedup exceeds what reduced I/O alone would explain.

How do I know my workload is hitting the wrong layout?

The symptom is a query that scans a lot to return a little — a SUM or COUNT over months of rows returning a handful of numbers, slow in proportion to table size rather than result size.

Recognizing the symptom

If your dashboard queries scan large fractions of a table, touch a handful of columns out of many, aggregate rather than fetch, and get slower as data grows despite indexes — that is a storage-layout problem. More indexes will not fix it. Indexes help you find rows; the problem is that you are reading whole rows to use two fields.