Columnar Versus Row Storage

Concepts covered: Columnar vs Row Formats

Every storage format decides which bytes sit next to each other on disk. That choice sets how many bytes a query must read and how well they compress. It also sets how fast a CPU can work through them. Take a page_views table with 40 columns. They include country, session_id, page_url and user_agent, plus a JSON blob in properties. Its rows average 290 bytes, and country is a 2-letter code.

Same rows, 2 layouts

A row layout writes each row's values together, then the next row's. A columnar layout writes each column's values together, then the next column's. Here are 4 rows of page_views, with 3 of its 40 columns, in the order each layout puts them on disk.

SELECT
  country,
  count(*) AS views
FROM page_views
GROUP BY country

For this query the row layout leaves the engine no shortcut. The 2 bytes of country sit inside every 290-byte row, and storage returns data in blocks of kilobytes or megabytes, so the engine reads every row in full to extract a single field. In the columnar layout the country values form one contiguous run: the engine reads that run and skips the other 39 columns.

0.7%
145x

Why columns compress better

Neighbouring values in a column share a type and often repeat. Millions of country values hold around 200 distinct strings, while a single row puts a timestamp beside a URL beside a boolean. Columnar writers turn that sameness into savings with encodings, applied per column before any general-purpose codec runs.

3 encodings a columnar writer picks per column

Dictionary encoding. Each distinct page_type is stored once, and every value becomes a small integer code. With 18 page types, a code needs 5 bits instead of a 10 to 20 byte string.
Run-length encoding. A repeated value is stored once with its count. If a file is sorted by country, 3 million consecutive US values become a single pair: US, 3,000,000.
Delta encoding. Each timestamp is stored as its difference from the previous one. Sorted event times sit milliseconds apart, and small differences need far fewer bits than full timestamps.

Sort order decides how much run-length encoding saves. Written in arrival order, country goes US, DE, US, BR, so each run is only 1 or 2 values long. Sorted by country, each country collapses into a single long run.

Vectorized execution

The third gain happens inside the CPU. A row-at-a-time engine calls its filter and aggregation code once per row, decoding each row to find the field it wants. A vectorized engine takes a batch of one column's values, typically 1,000 or more, and runs a single tight loop over the batch. The batch sits contiguously in memory and stays in the CPU cache, and modern CPUs can apply a single instruction to several values at once. A columnar file already stores data in that shape, so the engine decodes a column straight into batches.

Where rows still win

The operational database from the beginner lessons stores rows on purpose. Its work is a single record at a time: insert order 8841, fetch it with all its fields, update its status. A row layout serves each of those with a single contiguous read or write. In a columnar file, assembling a single row touches 40 separate column regions, and adding a single row means appending to all 40, so columnar files are written in large batches and rarely updated in place.

Row layout fits

Fetching or updating a record by its key
Thousands of small inserts per second
Reading most columns of a few rows

Columnar layout fits

Aggregating millions of rows
Reading a few of many columns
Writing in large batches, then reading many times

Inside a columnar file

A columnar file cuts its rows into row groups, commonly roughly 128MB each; some formats call them stripes. Within a row group each column has its own chunk, so a reader can fetch country for a row group without touching page_url. The footer at the end of the file records the minimum and maximum value of each column in each row group, along with how many nulls it holds. The pushdown section puts those statistics to work, letting the engine skip row groups without reading them.

Part of the file	What it holds
Row group 1	About 128MB of rows, one chunk per column
Row group 2	The next 128MB of rows, one chunk per column
Footer	Schema, plus min, max and null count per column per row group

page_views is stored as columnar files with 40 columns. Which query gains the least from the columnar layout?

Count views per country across the whole table
Fetch all 40 columns for the 12 views in 1 session
Average load_ms per page_type over a year
Count distinct session_id per day for a quarter

About this lesson

This section is part of the Storage Layers: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.