The most important decision in a storage engine is the physical order of a table's cells. This page explains row and column layouts from first principles, why analytics engines are columnar, how compression and data skipping work, and where columnar storage hurts.
A table is a two-dimensional thing, but disks, memory and files are one-dimensional sequences of bytes. Every storage engine must choose an order in which to lay out the cells. Row-oriented storage writes all fields of record 1, then all fields of record 2, and so on. Column-oriented (columnar) storage writes every value of column 1, then every value of column 2. Nothing about the data changes; only the physical order does, and that single decision determines which queries are fast.
Row stores include PostgreSQL, MySQL/InnoDB, SQL Server's default rowstore, and row file formats such as CSV and Avro. Column stores include Snowflake, BigQuery, Redshift, ClickHouse, DuckDB, Vertica, and the file formats Parquet and ORC. Many systems now offer both: SQL Server columnstore indexes, Oracle's in-memory column store, and hybrid formats that group rows into blocks and store columns within each block (PAX layout, which is what Parquet row groups and ORC stripes are).
The workload decides. OLTP (online transaction processing) workloads read and write individual records: fetch customer 42, insert an order, update a balance. Having the whole row in one place makes those operations a single small I/O. OLAP (analytics) workloads scan millions of rows but touch a handful of columns: total revenue by country for last quarter. A row store must read every byte of every row to answer that; a column store reads only the columns named in the query. For wide tables with hundreds of columns that difference is often an order of magnitude or more in bytes read, before compression adds a further gain.
If a query references k of m columns, a columnar scan reads roughly k/m of the data. This is the biggest single win and needs no indexes.
A column holds values of one type with similar distributions, and often long runs of equal values. That enables lightweight encodings that are cheap to decode: run-length encoding (value, count), dictionary encoding (replace strings with small integer codes), delta encoding (store differences between sorted timestamps or ids), and bit-packing (use only as many bits as the largest value needs). A general-purpose codec then compresses further. Row layouts interleave types, so they compress less well.
Columnar files store statistics (min, max, null count, sometimes Bloom filters) per block of rows. The engine hands the query's filter to the reader, which skips blocks whose statistics prove no row can match. Sorting or clustering by a filter column makes ranges narrow and skipping effective.
Processing one column at a time lets engines operate on arrays of values in tight loops that use CPU caches and SIMD instructions well, instead of interpreting one row at a time. Apache Arrow standardises this columnar layout in memory.
A columnar engine can evaluate the filter on the country column alone, produce a list (or bitmap) of matching row positions, and only then fetch amount values at those positions. Rows are stitched back together as late as possible, often never fully, which avoids decoding values that are filtered out anyway.
Pure column files would force a reader to jump across the whole file to rebuild rows. Practical formats therefore cut the table into horizontal chunks of many thousands or millions of rows (Parquet row groups, ORC stripes) and store each column contiguously within a chunk. A chunk can be processed independently, which also makes it the natural unit of parallelism for Spark tasks and of skipping for statistics.
Inserting one row into a column store touches every column file, and updating a value in a compressed run is awkward. Column stores therefore buffer writes (in a row-oriented delta store or write-ahead log) and merge them into columnar blocks in batches, and table formats such as Delta Lake and Iceberg rewrite files rather than editing them in place. Reconstructing a full record requires reading from every column, so SELECT * for one key is slower than in a row store.
The script below writes the same synthetic table as CSV (row-oriented text) and Parquet (columnar), then measures file size and the time to aggregate one column. Exact numbers depend on your machine; the direction of the result does not.
import time, numpy as np, pandas as pd, os
n = 2_000_000
rng = np.random.default_rng(1)
df = pd.DataFrame({
"order_id": np.arange(n),
"country": rng.choice(["US", "DE", "JP", "BR", "IN"], n),
"amount": rng.gamma(2.0, 50.0, n).round(2),
**{f"attr_{i}": rng.integers(0, 1000, n) for i in range(20)}, # make it wide
})
df.to_csv("orders.csv", index=False)
df.to_parquet("orders.parquet", compression="zstd") # needs pyarrow
print("csv MB ", round(os.path.getsize("orders.csv") / 1e6, 1))
print("parquet MB", round(os.path.getsize("orders.parquet") / 1e6, 1))
t = time.perf_counter()
pd.read_csv("orders.csv")["amount"].sum() # must parse every row fully
print("csv scan ", round(time.perf_counter() - t, 2), "s")
t = time.perf_counter()
pd.read_parquet("orders.parquet", columns=["amount"])["amount"].sum()
print("parquet scan ", round(time.perf_counter() - t, 2), "s")The same comparison in SQL with DuckDB, which can query both files directly:
-- reads one column, skips row groups using statistics
SELECT country, SUM(amount) FROM 'orders.parquet' WHERE country = 'JP' GROUP BY country;
-- must tokenize every line of text
SELECT country, SUM(amount) FROM read_csv_auto('orders.csv') WHERE country = 'JP' GROUP BY country;| Property | Row-oriented | Column-oriented |
|---|---|---|
| Best workload | OLTP: point reads, inserts, updates | OLAP: scans and aggregates over few columns |
| Read one full record | One seek, contiguous | One read per column |
| Scan one column | Reads all columns anyway | Reads only that column |
| Compression | Moderate (mixed types) | High (RLE, dictionary, delta) |
| Single-row write | Cheap, in place | Expensive; batched or buffered |
| Examples | PostgreSQL, MySQL, Avro, CSV | Parquet, ORC, Snowflake, BigQuery, ClickHouse, DuckDB |
A quick decision rule: if most queries name a key and want the whole record back, or most traffic is small writes, choose a row store. If most queries aggregate or filter over many rows and a few columns, and data arrives in batches, choose a columnar format. Data platforms commonly use both: a row-oriented operational database feeding, through change data capture or batch export, a columnar warehouse or lakehouse.
Hybrid and HTAP (hybrid transactional/analytical processing) systems keep a row store for fresh writes and a column store for analytics, synchronising between them.
SELECT * on a column store. It throws away projection, the main benefit.