Columnar vs Row Storage: Analytical Scan Cost (2D)

Table Geometry

Rows 2.0M

Columns 8

Bytes per column 8 B

Query: SELECT columns

Scan Result

Row-layout reads

—

Column-layout reads

—

Columnar I/O savings

—

bytes read (highlighted)

bytes skipped (dimmed)

How it works

A table has R rows, C columns, each column value b bytes wide. A query's SELECT list picks k of the C columns and (this is a full scan, no WHERE clause) touches every row.

Row-oriented store:
  every row interleaves ALL C columns contiguously,
  so reading any column forces reading the whole row
  bytes = R · C · b   (independent of k)

Column-oriented store:
  each column is its own contiguous run on disk,
  so only the k selected columns are ever opened
  bytes = R · k · b

savings ratio = (R·C·b) / (R·k·b) = C / k

The ratio C/k is exactly why picking fewer columns out of a wide table widens columnar's advantage — a 100-column table with a 2-column SELECT gets a 50× reduction — while selecting nearly every column narrows the advantage toward 1× (parity), because a row-store's one weakness (reading unwanted columns) stops mattering once almost nothing is unwanted.

Rows / Columns / Bytes per column — the actual table geometry; every number below is computed live from these, not scripted.
SELECT columns — toggle each column's checkbox to include/exclude it from the query.
Row-layout reads — always R·C·b: full rows are contiguous, so column pruning is impossible.
Column-layout reads — R·k·b: only the checked columns' bytes are ever touched on disk.

This is exactly why OLAP engines (Parquet/ORC under Spark, Snowflake, BigQuery, Redshift) store data column-major for wide analytical tables, while OLTP databases (Postgres, MySQL row heaps) store row-major for point lookups and full-row writes.