Columnar storage

What is columnar storage?

Columnar storage keeps the values of one column together on disk. All the order_date values sit in one run, all the amount values in the next, and so on. Row storage does the opposite: it keeps every field of a single record side by side, so one order's date, amount, customer, and status are written together before the next order begins.

The layout is the whole story. None of the data changes, only where each value physically lands. That one decision is what separates a database built for transactions from one built for analytics.

Columnar storage is the format under Parquet and Apache ORC files, under a SQL Server columnstore index, and inside analytical engines in general. Row storage is the format under a normal operational database, the kind that runs a webshop or an accounting system.

A query that reads 3 columns out of 200

Picture a wide events table: 200 columns, 50 million rows. A report asks for revenue by region for last quarter. It needs three fields, region, amount, and order_date, and nothing else.

On row storage the engine still has to walk through whole rows, because each row's 200 fields are interleaved on disk. To reach the three it cares about, it reads past the other 197. On columnar storage the engine opens only the three column runs it needs and ignores the rest of the table. Reading 3 columns out of 200 is roughly one and a half percent of the width, so the query touches a small fraction of the bytes instead of every one.

That is why the same query can be an order of magnitude faster on a column store. Microsoft reports up to ten times the query performance for a SQL Server columnstore index over row-oriented storage on data-warehouse workloads, and the mechanism is exactly this: fewer bytes read from disk. Formats like Parquet and ORC push it further by keeping the smallest and largest value per block, so the engine can skip whole blocks whose range cannot match the filter. That skip is called predicate pushdown.

Why columnar data compresses so well

Values in one column come from the same domain. A country column is a short set of codes repeated millions of times, a status column cycles through a handful of states, a timestamp column climbs slowly. Similar values sitting next to each other are what compression is best at.

Two encodings do most of the work. Dictionary encoding replaces each distinct value with a small integer that points into a lookup table, so a long text category becomes a one-byte code. Run-length encoding then collapses a stretch of the same value into one value plus a count, so a run of 40,000 identical country codes is stored once. Parquet applies both, then compresses the result with a general codec on top. Microsoft quotes up to ten times the data compression for a columnstore over the raw size, for the same reason: a column of lookalike values is far more predictable than a row of mixed types.

Row storage cannot get near this. A single row interleaves a date, a decimal, a few text fields, and a boolean, and the compressor sees a jumble with little to hold onto.

Why it is a poor fit for transactions

The layout that speeds up scans works against single-row writes. To insert one order into a column store, its fields have to be scattered across every column, one value appended to each of 200 separate runs. To read that one order back in full, the engine reassembles it from 200 places. A row store writes and returns the whole record in one spot, which is what a checkout or a stock update needs.

Updates are worse. Columnar files such as Parquet are immutable, so changing one value means rewriting the file that holds it. A SQL Server columnstore does not edit in place either: it marks the old row deleted and inserts the new version into a side structure. Heavy small writes also leave a trail of tiny files that a column engine has to merge later, the job known as table compaction. For the fast single-row lookups that transactions live on, a row store with a database index is the right tool.

Row stores for transactions, column stores for analytics

This is the split the field settled on. Row storage is for transactional systems, the OLTP side, where you write and read one record at a time and want it fast and safe. Columnar storage is for analytical systems, the OLAP side, where you scan many rows but few columns and care about totals over history.

Most architectures run both. The operational database stays on row storage as the system of record. A copy lands in a data warehouse or a lakehouse on columnar storage, where Parquet files and in-memory engines like VertiPaq behind Power BI answer the reporting questions. The source system keeps the business running, and the columnar copy is what makes it fast to analyse.