Row vs Column Storage

OLTP databases (Postgres, MySQL) store data ROW by row:

[row1: id=1, name=Alice, age=30]
[row2: id=2, name=Bob,   age=25]
[row3: id=3, name=Carol, age=35]

Reading a single row = read 1 disk block. Inserting a row = append 1 record. Great for transactional workloads.

OLAP / analytics workloads ask different questions: "what's the average age?" "how many users in each region?" — these scan many rows, but only a FEW columns.

Row layout problem: to compute avg(age), you scan ALL columns of all rows (id, name, age), wasting I/O on columns you don't need.

Column store lays out data by column:

id:   [1, 2, 3, ...]
name: [Alice, Bob, Carol, ...]
age:  [30, 25, 35, ...]

Each column = a separate file. Scanning age = read just one file. Massive I/O reduction.

Other benefits:

• Compression: same-type values cluster well. Ages are 0-120 (1 byte each). Strings repeat → dictionary encoding shrinks 100x.
• Vectorization: process arrays of values at once with SIMD.
• Late materialization: filter on cheap columns first, only fetch others for survivors.

Trade-offs:

• Inserts are expensive: each row touches N column files.
• Updates: rewrite a column = rewrite all values (or use update files).
• Single-row read: must read N column files → expensive.

Hence column stores are read-mostly. Insert in BATCH (millions of rows at once); appender writes columnar files.

Real systems: Apache Parquet, ClickHouse, Snowflake, BigQuery, Redshift, DuckDB, Vertica, Amazon Redshift, Google Bigtable variants.