What is Columnar Storage?

How it works

A row store writes (order_id, status, amount) for order 1, then order 2, and so on. That is ideal for OLTP: inserting or fetching one complete order touches one place. A column store writes all order_id values, then all status values, then all amount values, usually in blocks of many thousands of rows.

Analytical queries scan many rows but few columns, such as SELECT status, SUM(amount) FROM orders GROUP BY status. A column store helps them in three ways:

1. Less I/O: only the referenced columns are read.
2. Better compression: a column has one type and often repeating values, so dictionary, run-length and delta encodings shrink it dramatically.
3. Faster execution: engines process a column as a vector (SIMD), and per-block min/max statistics let them skip blocks entirely.

The trade-off is writes. Changing one row touches every column’s block, so column stores prefer bulk loads and rewrite files rather than update in place. That is why OLTP databases stay row-oriented and warehouses and lakehouses are columnar.

Example

The same 200,000 orders as CSV (row-oriented) and as Parquet (columnar). A query on status and amount needs about 36 KB of Parquet data instead of a 4.6 MB file scan (Python 3.11, PyArrow 25).

import os, csv
import pyarrow as pa, pyarrow.parquet as pq

n = 200_000
data = {
    "order_id": list(range(n)),
    "status": [("shipped", "pending", "cancelled")[i % 3] for i in range(n)],
    "amount": [round((i * 7) % 1000 + 0.99, 2) for i in range(n)],
}
with open("orders.csv", "w", newline="") as f:          # row-oriented: one line per order
    w = csv.writer(f); w.writerow(data); w.writerows(zip(*data.values()))
pq.write_table(pa.table(data), "orders_col.parquet")    # column-oriented

print(f"CSV (rows):        {os.path.getsize('orders.csv') / 1e6:.2f} MB")
print(f"Parquet (columns): {os.path.getsize('orders_col.parquet') / 1e6:.2f} MB")

meta = pq.ParquetFile("orders_col.parquet").metadata
for c in range(meta.num_columns):          # compressed bytes per column, summed over row groups
    size = sum(meta.row_group(g).column(c).total_compressed_size for g in range(meta.num_row_groups))
    print(f"  {meta.schema.column(c).name:<9} {size / 1e3:8.1f} KB")

CSV (rows):        4.60 MB
Parquet (columns): 1.11 MB
  order_id    1072.0 KB
  status         3.1 KB
  amount        33.3 KB

Common mistakes

• Using a columnar warehouse for high-rate single-row inserts and updates, which it handles poorly.
• Writing SELECT * in reports, which reads every column and wastes the format’s main advantage.
• Storing many tiny files or micro-batches, so blocks are too small to compress or skip well.
• Assuming a unique, random column (like order_id above) compresses as well as low-cardinality columns.