Columnar vs Row Storage: Analytical Scan Cost

Storage Layout

Query

Columns selected (SELECT) 3 / 8

Filter selectivity (WHERE) 25%

Table size (rows) 2.0M

Scan Result

Bytes scanned

—

Blocks touched

—

Est. scan time

—

Other layout would cost

—

read from disk

row matched filter, column pruned

skipped (predicate pushdown)

How it works

A table with C columns is chunked into P row-group blocks. An analytical query selects k of the C columns and a WHERE clause matches a fraction s of the blocks (zone-map / min-max predicate pushdown skips the rest — the same technique both engines use to avoid reading blocks that provably can't match).

Row-oriented store:
  a block interleaves every column for its rows,
  so touching ANY column forces reading ALL of them
  bytes = P·s · C · blockColBytes

Column-oriented store:
  each column is a separate contiguous run,
  so unselected columns are never opened
  bytes = P·s · k · blockColBytes / compressionRatio

Column stores compress far better too: values inside one column share a type and often repeat, so dictionary encoding (map each distinct value to a small integer code) plus run-length encoding (store "value, repeat-count" instead of every row) can shrink a column 3-5×. Row-oriented pages mix types and rarely compress as well.

Row-oriented / Column-oriented — switch the physical layout being scanned.
Columns selected — how many of the 8 columns the query's SELECT list needs.
Filter selectivity — the fraction of blocks whose min/max range overlaps the WHERE clause and must be opened.
Table size — total rows, used only to scale the byte estimate (the 3D grid always shows the same 8×20 block structure).
Compression — applies a realistic per-layout ratio: strong for columnar (~4.2×), weak for row-oriented (~1.3×, mixed types).

This is exactly why OLAP engines (Parquet/ORC files under Spark, Snowflake, BigQuery, Redshift) store data column-major, while OLTP databases (Postgres, MySQL row heaps) store row-major — the workloads read opposite shapes of a table.

8 columns × 20 row-group blocks. X = column, Z = block. Brightness = bytes actually read for this query.

⚠ Couldn't load the 3D engineThree.js failed to load from the CDN. Check your connection and reload.