Row vs Column Storage: Projection, Compression, CPU, Updates, and Analytical Workloads

Learning outcomes

1. The AtlasMart decision: reduce work without changing meaning

AtlasMart’s finance dashboard usually needs order_date, product, and amount, while the paid order-line fact also carries customer, channel, quantity, cost, profit, status, and operational keys. A row-oriented layout keeps the fields of one logical row physically close; a column-oriented layout keeps values from the same column close, usually in chunks. A projection is the subset of columns a query asks to read. An analytical scan is work that evaluates many fact rows, often while projecting only a few columns.

The business question is unchanged: “What was paid GMV for this date/product slice?” Chapter 18 changes only the physical representation and execution work. That separation is essential: a faster answer with different filters, grain, or restatement semantics is not an optimization—it is a different calculation.

2. Row and column layouts optimize different locality

3. Projection changes physical work, not the metric

In the end-to-end harness, the compressed row CSV is 1,344,039 bytes. The clustered didactic column store is 808,424 bytes across all column chunks and metadata. Those whole-representation sizes are descriptive, not sufficient to predict a query. For the selective date+P100 query, the row CSV must be parsed end-to-end, while the column harness reads only three needed columns from one qualifying row group: order_date, product_code, and amount_cents.

4. Controlled failure: “columnar is always faster”

That statement collapses workload, cache, update pattern, engine, encoding, and data distribution into one slogan. A point lookup returning a complete row, a high-churn OLTP update, or a tiny hot dataset can erase the advantages demonstrated by a wide analytical scan. Even in this chapter’s own harness, a full SUM(amount) cannot skip logical rows; it only benefits from projecting one physical column.

The repair is to define a representative query corpus first: projection width, filter selectivity, grouping keys, update rate, concurrency, freshness, and history behavior. Then compare correct implementations under the same semantics.

5. Hands-on mechanism probe: projection bytes

import csv, gzip, tempfilefrom pathlib import Pathrows = [    ("2026-09-18", "P100", 10000, 6000, "web"),    ("2026-09-18", "P200",  2500, 1500, "web"),] * 5000with tempfile.TemporaryDirectory() as td:    root = Path(td)    with gzip.open(root / "rows.csv.gz", "wt", newline="") as f:        w = csv.writer(f); w.writerow(["date","product","amount","cost","channel"]); w.writerows(rows)    with gzip.open(root / "amount.txt.gz", "wt") as f:        f.write("\n".join(str(r[2]) for r in rows))    row_bytes = (root / "rows.csv.gz").stat().st_size    projected_bytes = (root / "amount.txt.gz").stat().st_size    assert sum(r[2] for r in rows) == 62500000    print("same logical sum:", 62500000)    print("row representation bytes:", row_bytes)    print("projected amount-column bytes:", projected_bytes)# Exact byte counts vary by Python/zlib build; the equality must not.

The exact gzip byte counts are implementation-dependent. The invariant is the measure equality; the physical observation is that a representation capable of opening only the amount column can avoid reading unrelated fields for this narrow aggregate.

6. What this evidence does and does not prove

• Proves: the fixture preserves the same metric across layouts; narrow projections can reduce bytes opened; layout can create compression opportunities.
• Does not prove: Parquet/ORC behavior, cloud scan billing, SIMD use, production latency, or a universal row-vs-column crossover point.
• Dataset dependent: compression ratio and row-group elimination.
• Engine dependent: pushdown, vectorization, late materialization, cache design, and update strategy.
• Security dependent: column-level access controls may constrain what can be projected or materialized, but the local lab has no real PII.

7. Production judgment and bridge

Choose physical locality from workload evidence, not from the label “warehouse.” Preserve replayability and reconciliation: a new format must be reproducible from governed raw/integration data, and rollback must restore the prior representation without changing facts. Measure both read savings and write/maintenance cost. Lesson 2 opens the column chunks themselves and asks why some value distributions compress or encode well while others do not.

Knowledge check

Authoritative references

• Apache Parquet — ConceptsOfficial terminology for row groups, column chunks, pages, and the units at which I/O and encoding occur.
• Apache Parquet — File FormatOfficial layout showing column chunks organized inside row groups and metadata used to locate relevant chunks.
• Apache Parquet — EncodingsOfficial definitions of plain, dictionary, run-length/bit-packed, and delta encodings. Parquet is a reference, not a prerequisite for the mandatory lab.
• DuckDB — Execution FormatCurrent official example of a vectorized analytical engine. This course uses the page only as a non-prerequisite reference for the execution concept.
• SQLite — EXPLAIN QUERY PLANOfficial documentation for the local row-store plan evidence. The plan text is diagnostic output, not a stable application API.
• Python — gzipStandard-library compression used by the dependency-free local storage harness.
• Python — structStandard-library binary packing used for fixed-width didactic column chunks.
• Python — sqlite3Standard-library SQLite interface used for the local row-oriented table and query-plan evidence.
• Kimball Group — Dimensional Modeling TechniquesBackground for keeping dimensional grain and metric semantics stable while changing physical storage and access paths.