Columnar Storage

Columnar storage is a way of laying out a table on disk in which the values of each column are stored together, rather than the values of each row. A table of orders stored row-wise places one order’s ID, date, customer, and amount side by side, then the next order’s. Stored column-wise, all the IDs sit together, then all the dates, then all the amounts.

The data is identical either way. What changes is which reads are cheap.

Why the layout matters

A transactional application usually works with whole records, a few at a time: fetch this customer, update that order. Amazon Redshift’s documentation makes the point directly — because such transactions typically read and write all the values of one or a few records, row-wise storage is the better fit for them. One record is one contiguous read.

An analytical query has the opposite shape. It touches a few columns across a very large number of rows: total revenue by region for the quarter needs the date, region, and amount columns and nothing else. In a row-wise layout the engine still has to read past every other field of every row to get them. In a columnar layout it reads only the three columns. The same documentation gives the arithmetic for the simple case: in a table of 100 columns, a query using five of them needs to read about five percent of the data. That assumes the columns take similar space. Columns differ in width and in how well they compress, so the actual saving follows the compressed bytes of the columns read — including those needed only for filters and joins — plus metadata and how the reader fetches data. Five wide columns among ninety-five narrow ones can be most of the bytes.

The second benefit: compression

Keeping one column’s values together also means every block holds a single data type, often with many repeated or similar values. That lets a system choose an encoding per column rather than one scheme for a mix of fields:

• a low-cardinality column such as country encodes well as small integer codes into a dictionary,
• a sorted column collapses into runs of repeated values,
• an increasing column such as a timestamp stores small differences instead of full values.

Compression is not only a storage saving here. Fewer bytes on disk means fewer bytes to read, so it reduces I/O for the same query. How well it works depends on properties a pipeline controls — above all how the data is sorted — which is why load order is a performance decision in columnar systems.

What it costs

The trade is real, and it falls on the workloads that row storage handles well.

• Reassembling rows is work. Returning a whole record means reading one value from each of many separate column locations and stitching them back together. SELECT * removes the saving from skipping unused columns, although compression can still reduce bytes read.
• Small writes are awkward. Inserting or updating one row touches every column’s storage, and encoded, compressed blocks are costly to modify in place. Columnar systems therefore favor bulk appends and rewrites, and typically absorb small changes separately before merging them into the columnar layout — the periodic file compaction that lake tables run is the same idea made explicit. How that is done differs by product.
• Point lookups need help. Finding one row by key is not what the layout optimizes. Analytical systems narrow the search with per-chunk statistics — the basis of predicate pushdown — and some also support indexes for individual keys. DuckDB, for example, provides ART indexes for point and highly selective queries alongside zonemaps. Columnar layout and secondary indexes are compatible; index support, maintenance cost, and optimizer use depend on the engine.

Columnar storage in practice

The idea appears at more than one level, and it helps to keep them apart. Analytical databases and cloud warehouses store their tables column-wise internally; Snowflake, for example, documents storing and compressing each column independently inside its micro-partitions. Open file formats bring the same layout to data lakes: Parquet splits a file into row groups and stores each column of a row group as a separate chunk, described in row group and column chunk. And in-memory formats such as Apache Arrow apply it to data being processed rather than stored.

The execution model matters alongside the layout. An engine can benefit from reading fewer bytes even if it processes rows individually, while vectorized execution can reduce processing overhead by operating on batches. Reading most columns removes much of the projection saving, but column-specific encoding and compression can still reduce I/O. Evaluate those benefits against decoding, row reconstruction, writes, and concurrency for the actual workload rather than treating a wide scan as proof that columnar storage cannot help.

How column layout combines with encoding, data skipping, and the execution model to make analytical queries fast is worked through in Analytical Engines: Where the Speed Actually Comes From.

References

• Amazon Redshift Documentation, Columnar storage
• Apache Parquet Documentation, File Format
• Snowflake Documentation, Micro-partitions and data clustering
• DuckDB: Indexes
• Amazon Redshift: Column compression