Column-Oriented Storage and Compression

Why analytics databases store each column on its own, how that makes data small enough to scan fast, and what it costs for writes and single-row reads.

Overview

An analytics query usually touches a few columns of a very wide table, but across millions or billions of rows. A normal row store keeps every row together on disk, so it has to read all the columns to get the few it needs. A column store keeps the values of each column together instead. The query reads only the columns it names, and each column compresses very well because its values look alike.

Why analytics hurts a row store

Two kinds of work ask very different things of storage. Transaction work (OLTP, online transaction processing) reads and writes a few whole rows by key. Analytics work (OLAP, online analytical processing) scans many rows but only a few columns, and aggregates them.

Typical query — fetch or update one order by id — total sales by region for last year
Rows touched — a few — millions to billions
Columns touched — all of the row — 2 to 5 of maybe 100
Best layout — row-oriented — column-oriented
Examples — PostgreSQL, MySQL — Parquet files, Vertica, DuckDB

In a row store, reading 3 columns of a 100-column table still pulls all 100 columns off disk, because they sit side by side in the same pages. About 97 percent of the bytes read are thrown away.

Store each column on its own

The idea is simple: write all values of column 1 together, then all values of column 2, and so on. Every column keeps the rows in the same order, so the fifth value in each column belongs to the fifth row. To rebuild a row, take the fifth value from each column you need.

Row-oriented — 2026-01-03, US, shoes, 40, then 2026-01-03, US, hats, 15, then 2026-01-04, DE, shoes, 55
Column-oriented — all dates, then all countries (US, US, DE), then all products, then all amounts (40, 15, 55)

How a Parquet file is laid out

Apache Parquet is the most common open columnar file format in data lakes. A file is split into row groups (horizontal slices of rows). Inside each row group, each column's values sit together in a column chunk, and a chunk is divided into pages, the unit that is encoded and compressed. The footer at the end holds the file metadata: where every column chunk starts and statistics about it. The footer is written last so a writer can stream the file in one pass, and a reader reads the footer first to find the chunks it needs.

ORC is a similar columnar format from the Hive world. AWS documents both for Amazon Athena and notes that they compress by column, let a query skip blocks using min and max statistics, and can be split across many readers.

Compression: values that look alike shrink

A column holds one type of value, and often only a few distinct values (countries, status codes, product categories). That makes it easy to compress.

Dictionary — Store each distinct value once; the column stores small integer codes — Few distinct values (low cardinality)
Run-length (RLE) — Store a run of equal values as (value, count) — Sorted columns with long runs
Bit-packing — Store small integers in only as many bits as the largest needs — Dictionary codes, flags
Delta — Store the difference from the previous value — Timestamps, increasing ids
Bitmap — One bit per row for each distinct value — Filters that combine conditions

Parquet defines dictionary, a hybrid of run-length and bit-packing, and several delta encodings, and it falls back to plain encoding when a dictionary grows too large. On top of the encoding, each page can be compressed with a general codec such as Snappy or Zstandard.

A small example of dictionary encoding followed by run-length encoding on a sorted column:

Sixteen strings became a three-entry dictionary and three pairs of numbers. With billions of rows, runs are far longer and the saving is far larger.

Bitmaps turn filters into bit operations. For a status column, keep one bitmap per value, with bit i set when row i has that value. The filter status is FAILED and country is US becomes a bitwise AND of two bitmaps, which a CPU does on 64 rows per instruction.

Sort order: skip data you do not need

If rows are stored sorted by a column that queries often filter on (usually a date), two things improve. Runs get long, so run-length encoding shrinks the column a lot. And the min and max of each chunk become narrow, so a query can skip whole chunks whose range cannot match. This is what Parquet footer statistics and Athena's predicate pushdown use.

1 — Jan 1 — Jan 31 — skipped
2 — Feb 1 — Feb 28 — skipped
3 — Mar 1 — Mar 31 — read
4 — Apr 1 — Apr 30 — skipped

The first sort column matters most; the second only helps within runs of the first. The C-Store research system went further: it stores the same columns several times, each copy (a projection) sorted a different way, so different queries can pick the best copy. Vertica, the commercial system built from C-Store, kept this idea.

The cost: writes

Sorted, compressed columns are hard to update in place. Inserting one row in the middle would mean rewriting every column file. Column stores therefore split writes from reads, much like an LSM tree. C-Store names the parts a Writeable Store and a Read-optimized Store, joined by a tuple mover.

For plain Parquet files there is no write store at all: a file is written once. Pipelines batch rows and write whole new files. Table formats built on Parquet handle updates by writing new files and tracking which ones are current.

Vectorized execution: fast in the CPU too

Once the data is in memory, a column layout also helps the CPU. The classic engine processes one row at a time and pays interpretation overhead for every row. A vectorized engine processes a batch (a vector) of values from one column in a tight loop. The MonetDB/X100 research engine introduced this model, and DuckDB describes its engine the same way.

Unit of work — one row — a batch of values from one column
Overhead per value — a function call or more — spread over the batch
CPU cache use — poor, whole rows — good, dense arrays
SIMD friendly — no — yes

SIMD (single instruction, multiple data) instructions work on a wide register. A 256-bit AVX2 register holds 4 64-bit floats or 8 32-bit integers, so one add instruction handles 4 or 8 values. Apache Arrow defines a standard in-memory columnar layout built for this kind of processing and for sharing data between systems without copying.

What it saves: a worked example

Athena charges by bytes scanned: $5 per TB, with a 10 MB minimum per query. AWS's own pricing example uses a 3 TB text table that compresses 3 to 1 in Parquet and a query that needs one of its four equal-sized columns.

Uncompressed text, all columns — 3 TB — $15.00
Parquet, compressed, one column of four — 0.25 TB — $1.25

That is 12 times less data read and paid for: 3 times from compression and 4 times from reading one column.

What you will be able to do

Explain why a column layout reads less data for analytics and more work for single-row lookups.
Name the main column encodings and say which kind of column each suits.
Describe how sort order and min/max statistics let a query skip data.
Explain how column stores take writes without rewriting files for every row.
Estimate the scan and cost savings of moving from CSV or JSON to Parquet.

Key concepts

Column-oriented storage: Stores all values of one column together, in the same row order for every column, so a query reads only the columns it uses.
Row group and column chunk: Parquet splits a file into row groups; inside each, the values of one column form a column chunk, divided into pages.
Dictionary encoding: Replaces each value with a small integer code that points into a list of distinct values. Strong on columns with few distinct values.
Run-length encoding: Stores a run of equal values as one value and a count. Strong on sorted columns.
Bitmap: One bit per row for each distinct value, so combined filters become fast bitwise AND and OR operations.
Min/max statistics: Per-chunk minimum and maximum values that let a reader skip chunks that cannot match a filter (predicate pushdown).
Projection (C-Store): A stored copy of some columns sorted in its own order; several projections let different queries use the best sort.
Vectorized execution: Running each operator over a batch of column values at once instead of one row at a time, which cuts overhead and allows SIMD.

When to use it

Choosing a file format for a data lake or an analytics pipeline (Parquet or ORC rather than CSV or JSON).
Explaining in a design interview why the reporting side of a system uses a warehouse or columnar engine instead of the main OLTP database.
Choosing a sort or partition key for a large analytics table so queries can skip data.
Estimating scan cost on a pay-per-byte engine such as Athena.

Interview questions

Why is Parquet preferred over CSV for data lake storage?
Walk me through how a column store compresses a country column with a billion rows.
How does a column store handle inserts if its files are sorted and compressed?
When would you not use a column store?
Why does sort order matter so much in a column store?