The problem: a wide table and a narrow question#
TL;DRthe 30-second version
- Store the table one column at a time instead of one row at a time, so a query reads only the columns it selects.
- Split the rows into groups, and keep the smallest and largest value of each column in each group. A query skips any group whose range cannot match its filter.
- Encode each column on its own. A column of repeats collapses under run-length or dictionary encoding.
- The result: an analytical scan reads a tiny fraction of the table. The cost is slow single-row writes and updates.
- This is the layout behind Parquet, ORC, and columnar warehouses like ClickHouse, Snowflake, and BigQuery.
Picture an orders table: a customer id, a country, a status, a timestamp, an amount, a quantity, and thirty more attributes. On disk a row store writes the whole first row, then the whole second row, and so on. That is exactly what you want for an application that loads one order by its id and shows every field. One row lives in one place, so one read brings it back.
Now change the question from 'show me this order' to 'sum the amount for orders above 100 dollars'. This is an analytical query. It touches almost every row but only two of the columns. The row layout works against you here. To read the amount of each row you must step over the country, the status, the timestamp, and the thirty other fields sitting between one amount and the next. You pay to move data past the disk head that the query never looks at. Over a billion wide rows that waste is the whole cost of the query.
The fix starts with one idea. If a query reads columns, store the data by column. Then the amounts sit together in one continuous run, the countries sit together in another, and reading one column means reading one tight stripe with nothing irrelevant in between.
PredictA row store and a column store hold the same 40-column table. A query reads 2 columns from every row. Roughly how much of the raw table does each layout have to read off disk?
Hint: Can a row store read one field of a row without touching the rest of that row?
The row store reads all 40 columns, because rows are stored whole and it cannot fetch two fields without stepping over the other 38. The column store reads only the 2 stripes it needs, so it reads about 2/40, roughly 5%, of the table. That single change, before any encoding or skipping, is the bulk of the win.
How it works: stripes, row groups, zone maps, and encoding#
A columnar file is built from four ideas stacked together. Each one is simple. Together they turn a full-table scan into a read of a small corner of the file.
First, column striping. The engine takes the table and stores each column as its own contiguous run of values, called a column chunk. All the amounts in one place, all the countries in another. A query that selects two columns reads two chunks and never sees the rest. This alone is called column projection, and for a wide table it is the largest single saving.
Second, row groups. The file does not store one column top to bottom for the entire billion rows. It cuts the table into horizontal batches, a fixed number of rows each, and stores every column chunk-by-chunk within a batch. Parquet calls a batch a row group; ORC calls it a stripe. A row group of, say, a hundred thousand rows keeps a column's chunk small enough to read and decode as a unit, and it gives the next idea something to summarize.
Third, zone maps. For each column in each row group, the file stores two tiny numbers: the smallest value in that chunk and the largest. This min and max pair is a zone map, sometimes called a page or stripe statistic. It costs a few bytes per group and it is kept in the file's metadata, so a reader loads all the zone maps up front without touching any data.
Now the payoff. When a query carries a filter like amount above 100, the reader checks each row group's zone map before reading it. If a group's amount ranges from 15 to 40, no value in it can be above 100, so the reader skips the entire group without reading a byte of its data. Pushing the filter down to the storage layer this way is called predicate pushdown, and skipping a group on its zone map is the skip that makes columnar formats fast on selective queries. A group is only read when its range overlaps the filter, and even then only the projected columns are decoded.
Fourth, per-column encoding. Because a column holds one kind of value, its bytes are far more compressible than a mixed row. Two encodings do most of the work. Run-length encoding stores a stretch of equal values once with a count, so a status column that reads OK, OK, OK, OK, ERR becomes OK times four then ERR times one. Dictionary encoding lists each distinct value once in a small dictionary and replaces the column with short integer codes that point into it, which crushes a low-cardinality column like country down to one byte per row plus a handful of dictionary entries. The decoder reverses these on read, and a filter can often run directly against the codes.
- Transpose the table so each column is a contiguous stripe.
- Cut the rows into row groups; store each column's chunk within a group.
- Record a min/max zone map for each column in each group, in the file metadata.
- Encode each column chunk on its own with run-length, dictionary, or bit-packing.
- To read: load zone maps, drop the groups the predicate cannot match, then decode only the projected columns of the survivors.
What it costs: the bytes a scan actually reads#
The useful cost model for columnar storage is not big-O of rows. It is how many bytes a scan reads, because that is what disk and network time track. Two factors set it, and they multiply.
The first factor is projection. A query reads the columns it selects plus any column its filter names, and nothing else. If a table has C columns and the query touches k of them, projection alone reads about k over C of the data. Select two of forty columns and you are already down to one twentieth before anything else happens.
The second factor is pruning. Of the row groups, a query reads only those whose zone map overlaps its predicate. How many that is depends entirely on how the data is laid out. If the filter column is sorted or naturally clustered, most groups have a tight range and the predicate misses them, so the scan skips the large majority. If the column is in random order, almost every group's range is wide and spans the predicate, so pruning saves little. This is why analytical tables are physically sorted or partitioned on the columns people filter by. Clustering is what turns a zone map from decoration into a skip.
| Move | What it saves | What it depends on |
|---|---|---|
| Column projection | reads k of C columns | how many columns the query names |
| Zone-map pruning | skips non-matching row groups | how well the data is clustered on the filter column |
| Per-column encoding | shrinks the bytes that are read | the column's repetition and cardinality |
Encoding is the third factor, and it compounds on top: the columns a scan does read are themselves smaller because a single column compresses well. In the simulator a query for amount above 100 over sixteen rows reads 96 bytes where a full row scan reads 384, a saving of about three quarters, from pruning two of four groups and projecting two of four columns. Real files apply encoding on top of that, and real tables have far more columns and far more groups, so the ratios in production are steeper still.
The trade-off: fast scans, slow single-row work
Columnar storage is a bet that reads are analytical and writes are bulk. When the bet holds it wins by a wide margin. When it does not, it loses just as clearly.
- Point lookups suffer. Fetching one whole row means touching every column chunk and reassembling the row from pieces scattered across the file. A row store answers that in one read; a column store pays for its layout here.
- Writes are not incremental. Appending one row would mean extending every column chunk and updating every zone map. Columnar files are written in large batches instead, which is why they suit append-mostly analytical loads and not transactional traffic.
- Updates and deletes are awkward. Files are typically immutable, so a change is handled by writing new files and merging later, or by keeping a separate delete list, rather than editing a value in place.
- Pruning is only as good as the clustering. On data that is not sorted or partitioned on the queried column, zone maps rule out almost nothing and you fall back to projection alone.
The clean way to hold it in mind: a column store is optimized for scanning many rows of few columns, and a row store is optimized for reading or writing one row of many columns. Most systems run both, one for transactions and one for analytics, and copy data between them.
Variants: Parquet, ORC, and the encodings
The two dominant open formats share the model and differ in the vocabulary and the defaults.
- Apache Parquet organizes a file into row groups, then column chunks, then pages. Statistics and dictionaries live per page and per chunk, and a footer at the end of the file holds the schema and the offsets. It grew out of the Dremel design and is the default in the Spark and Arrow world.
- Apache ORC organizes a file into stripes, each with an index, the column data, and a footer. It keeps a lightweight row index with min/max every ten thousand rows and can attach an optional bloom filter per column for equality predicates. It came from the Hive world.
- Encodings stack. Dictionary encoding is usually tried first; if a column has too many distinct values the encoder falls back to plain. Run-length and bit-packing then squeeze the codes, and a general compressor like Snappy or Zstd is applied on top of the encoded bytes.
- Late materialization is the execution-side partner: run filters against the encoded codes and the zone maps first, decide which rows survive, and only then decode the projected columns for those rows.
Row store vs column store
| Dimension | Row store | Column store |
|---|---|---|
| Layout | one row stored whole | one column stored whole |
| Best query | read or write a single row | scan many rows of a few columns |
| Projection | must read whole rows | reads only selected columns |
| Skipping | needs a secondary index | zone maps skip row groups |
| Compression | mixed types per row, weaker | one type per column, strong |
| Writes | cheap single-row insert | batch rewrite, weak point lookups |
| Fits | OLTP, application traffic | OLAP, analytics and reporting |
The comparison is not a winner and a loser. It is two layouts tuned for opposite access patterns. A transactional database keeps rows together so an order loads in one read. An analytics engine keeps columns together so a report scans one field across a billion rows without dragging the other thirty-nine along.
Where it shows up
- Data lakes store tables as Parquet or ORC files in object storage, queried by Spark, Trino, Presto, and DuckDB. The engines rely on projection and predicate pushdown to read only the needed columns and row groups from remote files.
- Cloud warehouses are columnar under the hood. Snowflake stores micro-partitions with per-column min/max metadata, Amazon Redshift is a column store with zone maps, and Google BigQuery's Capacitor format is columnar.
- ClickHouse stores each column in its own files and uses a sparse primary index of marks, which is a zone-map-style skip index over granules of rows, to jump to the ranges a query needs.
- Apache Arrow is the in-memory columnar standard that lets these engines pass column batches between each other without reserializing row by row.
Common pitfalls
My columnar query is slow even though it filters heavily. Why?
Almost always the data is not clustered on the filter column, so every row group's range spans the predicate and nothing gets pruned. Sort or partition the table on the columns you filter by, so each group holds a tight range that the predicate can miss.
Does SELECT * still benefit from columnar storage?
Barely. Projection is half the advantage, and SELECT * projects every column, so you throw that half away and read the whole table. Column stores reward asking for only the columns you need.
Why are tiny row groups a problem?
Each row group carries fixed overhead: its own metadata, statistics, and dictionary. Make them too small and that overhead dominates, and each column chunk is too little data to compress or read efficiently. Formats default to large groups, tens to hundreds of megabytes, for this reason.
Can I update a single value in a Parquet file?
Not in place. The file is immutable. You rewrite the affected file or record the change in a separate delete or update file that a table format like Iceberg or Delta merges at read time.
In an interview
If you are asked to design analytics over a large event or orders table, reach for a columnar layout and be ready to say why in one breath: store by column so a query reads only the fields it projects, group the rows and keep a min/max per group so the query skips groups its filter cannot match, and encode each column on its own so the little it does read is small.
- Name the two independent savings: projection reads fewer columns, pruning reads fewer row groups. They multiply.
- Tie pruning to clustering. Zone maps only help when the data is sorted or partitioned on the filter column; say you would order the table on the common predicate.
- Contrast with a row store honestly: columnar loses on single-row reads and on incremental writes, so it sits beside a transactional store, not in place of it.
- Drop the real names: Parquet and ORC for files, Arrow for in-memory, and note that warehouses like Snowflake, Redshift, and BigQuery are columnar with the same min/max skipping.
QuizA columnar table is filtered on a column stored in random order, and the query selects 3 of its 30 columns. Which saving still applies?
- Both projection and zone-map pruning apply fully
- Projection applies; pruning barely helps because the column is not clustered
- Pruning applies; projection does not matter
- Neither applies without an external index
Show answer
Projection applies; pruning barely helps because the column is not clustered — Projection is independent of ordering, so reading 3 of 30 columns still cuts the data to about a tenth. Pruning depends on clustering: with the filter column in random order, nearly every row group's min/max spans the predicate, so few groups can be skipped.
References
- Apache Parquet — file format documentation — Row groups, column chunks, pages, and footer metadata.
- Apache ORC — format specification — Stripes, row indexes with min/max, and optional bloom filters.
- C-Store: A Column-oriented DBMS (Stonebraker et al., VLDB 2005) — The paper that framed the modern column store.
- Dremel: Interactive Analysis of Web-Scale Datasets (Google, 2010) — The columnar-storage design Parquet drew from.
- ClickHouse — sparse primary indexes — Zone-map-style skipping over granules of rows.
Ready to try it?
The simulator is a real, deterministic implementation — pick a scenario and step through it, scrubbing the timeline forward and backward through every change.