Chapter 18 · Columnar Storage, Compression, Encoding, Vectorized Execution, and Analytical Scan Economics
Vectorized Execution, Batch Processing, SIMD Concepts, and Why Analytical Engines Differ from OLTP DBMSs
Explain vectorized execution as operators processing batches of typed values, distinguish it from storage format and hardware SIMD, and use a local batching proxy only to demonstrate reduced interpreter overhead—not to claim a CPU instruction path.
Learning outcomes
Explain the central warehouse mechanism in “Vectorized Execution, Batch Processing, SIMD Concepts, and Why Analytical Engines Differ from OLTP DBMSs” and connect it to AtlasMart’s declared grain and governed metrics.
Keep logical correctness, history, and control totals unchanged while evaluating the lesson’s physical or operational choice.
Run or interpret the deterministic local evidence and distinguish what it proves from engine-, cache-, scale-, or cloud-dependent behavior.
Diagnose the controlled failure, repair it safely, and state the production checks required before adopting the pattern.
The Chapter 15–17 canonical current-state controls remain 9 paid lines, 7 orders, 11 units, 740 USD GMV, 450 USD cost, and 290 USD gross profit. Chapter 18 expands those nine seed lines deterministically to 216,000 benchmark rows only to make storage mechanisms observable. Benchmark rows are never reported as new production facts.
Runtime: mandatory path uses Python 3 standard library; generation evidence below used Python 3.13.5 and SQLite 3.46.1. Storage: local filesystem only. Source: synthetic AtlasMart sales facts. Time: dates are UTC business dates; no time-zone conversion is hidden in the benchmark. Grain: one current paid order line. Security: synthetic identifiers only; no secrets or PII. History: the benchmark is a deterministic physical scale expansion, not a new business history. Cache: OS cache is not flushed; timing probes use two warm-ups and seven measured runs. Cost: no paid service is required.
1. Storage layout and execution model are separate layers
A columnar file can be read by a non-vectorized executor, and a vectorized engine can process data sourced from row-oriented structures. Vectorized execution means relational operators consume/produce batches (vectors) of typed values rather than invoking the full operator logic once per tuple. Batch processing amortizes interpretation, function dispatch, and metadata checks across many values. SIMD is a hardware technique where one instruction operates on multiple data lanes. A vectorized engine may exploit SIMD, but “vectorized” does not by itself prove which CPU instructions ran.
2. Why analytical operators benefit from homogeneous batches
Consider SUM(amount_cents). A row-at-a-time path
repeatedly locates the amount field, checks row metadata, and
calls aggregation logic. A batch path can receive a contiguous
amount vector plus a validity mask and aggregate many values in
a tight loop. Homogeneous types improve cache locality and make
compiler/hand-written optimizations easier. Selection vectors
can also represent qualifying row positions without copying
entire rows.
DuckDB’s current execution documentation is an authoritative non-prerequisite example: operators work on fixed-size vectors/DataChunks. This course does not require DuckDB and does not copy its vector size as a universal tuning recommendation.
3. OLTP and analytical engines optimize different dominant work
| Workload pressure | Typical OLTP priority | Typical analytical priority |
|---|---|---|
| Access shape | small point/range reads, complete records | large scans, selective projections, aggregates |
| Mutation | frequent fine-grained insert/update/delete | batch append/rewrite/merge common |
| CPU | low latency per transaction | high throughput over many values |
| Concurrency | many short transactions | fewer heavier scans plus BI concurrency |
| Physical organization | row/page/index locality often useful | column chunks, compression, skipping, vector execution often useful |
These are tendencies, not rigid categories. Modern engines mix techniques.
4. Controlled failure: “Python sum proved SIMD”
The local probe compares a Python loop with the built-in
sum over the same 216,000 integer values. In the
generation environment the medians were
4.209 ms and 1.514 ms after
warm-up. That only shows less Python-level dispatch in the
built-in path. It does not reveal whether the
interpreter/runtime used SIMD, which cache levels hit, or how a
database engine schedules operators.
Use the probe to understand batching/amortization. Use engine profiles, CPU performance counters, disassembly, or vendor documentation if you need to establish actual SIMD behavior.
5. Hands-on batching proxy
import statistics, timevalues = [10_000, 2_500, 19_000, 10_000, 5_000, 10_000, 7_500, 5_000, 5_000] * 24_000def row_loop(): total = 0 for value in values: total += value return totaldef batch_proxy(): return sum(values)def measure(fn): for _ in range(2): fn() # warm-up; OS cache not flushed samples = [] for _ in range(7): t = time.perf_counter(); result = fn(); samples.append((time.perf_counter()-t)*1000) return result, statistics.median(samples)r1, t1 = measure(row_loop)r2, t2 = measure(batch_proxy)assert r1 == r2 == 1_776_000_000print("same result:", r1)print("row-loop median ms:", round(t1, 3))print("built-in-sum median ms:", round(t2, 3))print("This is a Python batching proxy, NOT evidence of SIMD instructions.")Your timings will differ. The required assertions are result equality and disclosure of warm-up/cache conditions. Do not publish the ratio as “vectorized engines are X times faster.”
6. Execution boundaries: nulls, branches, and data shape
Vector processing still pays for null handling, variable-width strings, branch-heavy expressions, decompression, hash-table probes, and materialization. Highly selective predicates can shrink later vectors; poor selectivity can move large batches through many operators. Dictionary/constant/sequence vector representations can sometimes preserve encoded forms longer, but that is engine-specific behavior.
7. Observability, performance, and cost
For production evidence, record operator-level rows in/out, vector/batch counts if available, CPU time, wall time, decompression time, bytes read, spills, memory peaks, and cache state. In cloud systems, translate those mechanics into the actual billing model rather than assuming lower CPU automatically means lower cost. Benchmark at representative concurrency because a design that minimizes single-query CPU may still create poor workload isolation.
8. Production judgment and bridge
Vectorization is useful when it increases throughput for the actual analytical operators without breaking semantics or memory limits. It is not a reason to redesign facts or denormalize blindly. Lesson 5 combines storage bytes and CPU evidence into one decision record, then verifies that the optimized layout still reproduces the same AtlasMart totals and can be rolled back/rebuilt.
Knowledge check
Check your understanding
- What is the difference between vectorized execution and SIMD?
- Why can typed batches reduce per-value overhead?
- Why is the Python timing probe not a database benchmark?
- Name two factors that can reduce vectorized efficiency.
- What evidence would you collect to investigate actual CPU behavior?
Review the answers
1. Vectorization is a query-execution model over batches; SIMD is a CPU instruction technique.
2. Dispatch/type/null/operator setup can be amortized across many values.
3. It uses Python data structures/runtime and does not reproduce an analytical engine’s storage, operators, scheduler, or code generation.
4. Null-heavy/branch-heavy expressions, variable-width strings, spills, poor cache locality, or expensive decompression.
5. Engine profiles plus CPU counters/disassembly or documented execution details, with reproducible workload context.
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.