Columnar Storage

Overview

Columnar formats such as Parquet and ORC, and engines such as ClickHouse, Redshift and BigQuery, store values column by column. A query summing revenue by region reads two columns instead of fifty.

How it works

Same table, two layouts.

Row layout:    [id1,US,9.5,...] [id2,EU,4.0,...] [id3,US,7.2,...]
Column layout: id:     [id1,id2,id3]
               region: [US,EU,US]    -> dictionary + RLE
               amount: [9.5,4.0,7.2]

SELECT region, SUM(amount) FROM sales GROUP BY region;
-- reads only region and amount blocks

Each block stores min and max values, so a filter on a date range skips blocks entirely.

Encoding choices are chosen per column. A low-cardinality country column compresses to a dictionary of ids further squeezed by run-length encoding, a monotonic timestamp column compresses with delta encoding, and a sorted column benefits most because equal values sit together. Engines process data in vectors of a thousand or so values, keeping loops tight and CPU caches warm. Many warehouses also use table partitioning by date and clustering by a secondary key, so most queries read only a small part of the files.

Pitfalls

• Streaming one row at a time directly into column files, producing thousands of tiny files.
• Sorting data on a column nobody filters by, so block statistics are useless.
• Running OLTP-style updates against a column store.
• Selecting every column with SELECT star, which removes the main advantage.

Key takeaways

• Read only the columns you need; that is the whole point.
• Sort and partition data to match the common filters.
• Load in batches and compact small files.
• Keep OLTP and analytics in separate engines, linked by CDC.