Columnar Storage

Definition

Columnar storage keeps each column of a table together instead of keeping each row together. A query that needs only a few columns, such as an average over one of fifty, can skip the rest, and similar values stored side by side compress well.

In plain terms

Row storage is natural for applications: fetch or update one customer and you read one contiguous record. Analysis asks different questions: the average of one field over millions of rows. With rows, every query drags all the other fields through memory; with columns it touches only the ones it names.

The trade-off is that reading or changing a single whole record is slower, so columnar formats such as Parquet are used for analytical data that is written in batches and read many times.

Example

A table of 1,000,000 rows and 50 numeric columns of 8 bytes each. A query averages one column.

The column layout reads 2% as much. Compression usually widens the gap, because values in one column are alike.

Common misunderstandings

• “Columnar is always faster.” It wins for scans over a few columns; looking up or updating single records is usually slower.
• “Parquet is a database.” It is a file format; a query engine is still needed to read it.
• “It changes the data.” The same table is stored in a different physical order; the values are identical.