Columnar Execution
What an engine actually does with SELECT avg(spend) FROM users WHERE country = 'DE' once the data is stored by column: chunks opened, blocks skipped, batches decoded, a predicate evaluated into a mask, and one running total.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
Once the data is stored by column, what does the engine do with it that a row-at-a-time engine structurally cannot?
Every analytical query, and — more practically — every engineer who has to explain why one query costs a hundred times what a superficially similar one costs. The explanation is always some combination of columns opened, blocks skipped and rows materialised, and this lesson is those three mechanisms (Scan Cost).
The unit of execution is a batch of values from one column — commonly a thousand or so, sized to stay inside the CPU cache while an operator works on it. Not a row, not a table: a vector. Every property in this lesson comes from that choice of unit (Vectorized Execution).
Assume the engine reads the rows that match the filter and averages a field of each — the same mental model as a row-at-a-time loop, just running somewhere faster. It produces the right answer, so nothing corrects it, and it makes every real cost question unanswerable.
It cannot explain why adding one unused column to a SELECT list changes the cost, because in the row model reading a row is reading a row (Projection Pushdown).
- It cannot explain why adding one unused column to a
SELECTlist changes the cost, because in the row model reading a row is reading a row (Projection Pushdown). - It cannot explain why the same query is fast on a table sorted by
countryand slow on the identical data in random order, since no row-level reasoning involves block statistics (Clustering and Sort Order). - It cannot explain why filtering on a high-cardinality string column behaves differently from filtering on a low-cardinality one, which is a dictionary-encoding effect invisible at row level (Dictionary, Run-Length, Delta and Bit Packing).
- It leads people to optimise the SQL — rewriting joins, adding hints — when the cost is decided by which chunks were opened, a decision made before any SQL-level rewriting matters (Query Optimizers).
- It makes
SELECT *in a BI tool look harmless, because in the row model it costs nothing extra. In a columnar engine it is the most expensive thing the tool can emit (Data Platform Anti-Patterns).
What is actually happening
- The reader resolves the query to a set of column chunks. Columns no expression names are never opened — this is not an optimisation the planner may choose to apply, it is what the reader was going to do anyway (Projection Pushdown).
- Before decoding anything, the reader consults per-block metadata: min, max and null count per column chunk per row group. If the predicate value falls outside a chunk's range, the whole row group is skipped without a byte being decoded. Statistics can prove absence and never presence (Predicate Pushdown, The Parquet Read Path).
- Surviving chunks are decoded into batches — a vector of values from one column, sized to stay resident in cache while an operator processes it. The per-row interpreter overhead that dominates a row-at-a-time engine is now amortised across a thousand values (Vectorized Execution).
- The predicate is evaluated over a whole batch into a mask or a selection vector — a list of the positions that survived — instead of into a branch per row. There is no data-dependent branch left in the inner loop, so there is nothing for the branch predictor to guess wrong (Misprediction: What a Wrong Guess Costs, Branchless Code: A Trade, Not an Upgrade).
- With dictionary encoding, comparing a string column to a constant becomes: look the constant up in the dictionary once, then compare small integer codes. The strings themselves are never touched, and the comparison fits in registers (Dictionary, Run-Length, Delta and Bit Packing).
- With late materialisation, the payload column is gathered only at the positions the mask kept. A highly selective filter therefore avoids decoding most of the column it is aggregating, which is a saving that has no analogue in a row engine (How a Query Executes: Planner and Executor).
What the query opens, and what it never touches
Two columns are named in the query below. In a columnar file that resolves to two column chunks per surviving row group, and the other chunks are simply never opened. There is no planner decision involved and no hint to give — the reader was constructed from the projection list.
On top of that, a second and completely independent skip is available. Each row group carries min/max statistics per column, so before decoding anything the reader can ask whether DE could possibly appear in this chunk's range. If the answer is no, the entire row group is eliminated. If the answer is yes, the answer is still only "possibly" — statistics prove absence, never presence.
Read the layout table by its why column and notice which skips depend on the data's ordering and which do not. Projection is unconditional: it works identically on sorted and random data. Statistics-based skipping is entirely conditional on the data being clustered on the predicate column, which is why sort order is a design decision and not housekeeping.
- part-0000.parquet — row group 0 (country: min AT, max DE)one row group of the example table · 1 file · read
- part-0000.parquet — row group 1 (country: min FR, max US)one row group of the example table · 1 file · skipped
- part-0001.parquet — row group 0 (country: min AT, max IT)one row group of the example table · 1 file · read
- part-0001.parquet — row group 1 (country: min US, max US)one row group of the example table · 1 file · skipped
- columns `id` and `age`, in every row group of every fileall of them · 0 files · skipped
Two independent mechanisms are firing. Projection skips columns the query does not name and always works; statistics skip row groups whose values cannot match and work only in proportion to how sorted the data is. Neither involves an index, and both are decided before any decoding begins.
1SELECT avg(spend) AS avg_spend2FROM users3WHERE country = 'DE';Two column names appear. Everything the physical layer does below is a consequence of which columns are named and which values the predicate can exclude — neither of which is affected by how the SQL is phrased.
Batches, not rows
Inside a surviving row group, the difference between a row engine and a columnar one becomes an execution-model difference rather than an I/O one. A classic row-at-a-time engine calls a function per row, dispatches per expression per row, and takes a data-dependent branch per row. At a billion rows that overhead is the query.
A vectorized engine processes a batch of values from one column at a time — commonly around a thousand, chosen so the batch and its intermediates stay inside the CPU's fast caches while an operator runs over it. The predicate is evaluated for the whole batch into a mask before the payload column is touched at all, so the inner loops contain arithmetic and no control flow.
The sketch after the code makes the dictionary and the mask concrete. Notice that the string DE is compared a single time, against the dictionary, and everything after that is small-integer work. Notice too that only four of the eight spend values are ever needed — that is late materialisation, and it is a saving with no row-store equivalent, because in a row store those values were already in the page you had to read.
1# Row-at-a-time: one call per row, one branch per row,2# one dispatch per expression per row.3total, n = 0.0, 04for row in scan_rows("users"): # row carries every column5 if row.country == "DE": # data-dependent branch, per row6 total += row.spend7 n += 18 9# Vectorized: one call per batch of ~1024 values from one column.10# The predicate becomes data, not control flow.11DE = dictionary_code("country", "DE") # look the string up ONCE12total, n = 0.0, 013for country_codes, spend in scan_batches("users", ["country", "spend"]):14 mask = [c == DE for c in country_codes] # small-int compares15 total += sum(s for s, m in zip(spend, mask) if m) # gather + reduce16 n += sum(mask)The Python is illustrative structure, not an API — real engines write these loops in a compiled language over typed buffers. What transfers is the shape: the predicate is evaluated over a whole batch before any payload value is touched, the result is data rather than a branch, and per-row interpreter overhead is paid once per thousand values instead of once per value.
country chunk, row group 0 spend chunk, row group 0
dictionary : 0=AT 1=CH 2=DE 3=FR values : [120.00, 80.00, 400.00, 15.00,
codes : [ 2, 3, 2, 0, 2, 1, 3, 2 ] 38.50, 210.00, 9.99, 77.00]
predicate country = 'DE'
step 1 look 'DE' up in the dictionary ONCE -> code 2
step 2 compare eight small integers, not eight strings
mask [ 1, 0, 1, 0, 1, 0, 0, 1 ]
selection [ 0, 2, 4, 7 ] <- surviving positions
aggregate avg(spend) over the selection
step 3 gather spend[0], spend[2], spend[4], spend[7]
step 4 the other four spend values are never decoded, because
late materialisation reads the payload only where the
mask survived
and at no point were the id or age chunks opened at allWhere the hardware shows up
Every technique above has a counterpart one layer down, in the machine. That is not a coincidence or an analogy: columnar storage is the same idea as a structure-of-arrays layout, scaled from cache lines up to files, and it works for the same reason (Array of Structs, or Struct of Arrays?).
A cache line is the smallest unit that moves between memory and the CPU. When a loop walks a column, every byte of every line it pulls in is a value that loop is about to use, and the hardware prefetcher can predict the next address trivially because the access is sequential. When a loop walks rows, each line arrives mostly full of fields the loop will ignore, and the prefetcher's job is harder (Memory Moves in Lines, Not Variables, Prefetching: The Hardware Guesses What You Will Read Next, Spatial Locality).
The right column of the table is the important one and it is the part usually left out. Each technique has a condition, and when the condition fails the technique quietly stops paying while still costing its overhead. That asymmetry is why measured results vary so much between datasets, and why quoting anyone's benchmark number as a property of "columnar" is a mistake (Benchmark Fallacies: Confident Numbers That Are Wrong).
| Columnar technique | What the hardware actually does | When it stops helping |
|---|---|---|
| One column chunk read end to end | Sequential access the prefetcher predicts easily, and every cache line fully occupied by values the loop will use | When the engine materialises rows early and starts chasing per-row structures again |
| Values of one type packed contiguously | One SIMD register holds many values, so a single instruction compares or adds all of them at once | Variable-length values, nullable columns needing a separate validity check, and any branch inside the loop |
| Dictionary encoding | String comparison becomes small-integer comparison, so the compare fits in registers and the string bytes stay in the dictionary | High-cardinality columns, where the dictionary approaches the size of the data and the indirection costs more than it saves |
| Predicate evaluated into a mask | The inner loop has no data-dependent branch, so there is nothing for the branch predictor to guess wrong about | Extremely selective predicates, where branching out early would have been cheaper than evaluating every row |
| Batches of about a thousand values | One operator's working set stays inside the fast caches while it runs, so intermediates do not round-trip to main memory | Batches large enough to spill out of cache — the code still works and the performance argument stops |
| Late materialisation | Only the positions the mask kept are gathered from the payload column | Unselective queries, where a scattered gather costs more than reading the column straight through |
| Large row groups | Per-file and per-block metadata is amortised over many values, and encodings have more data to find structure in | Row groups so large that statistics can no longer eliminate anything within a file |
How to build it
Most important first.
- Name only the columns you need, everywhere, including in the views and BI models that generate SQL on someone's behalf. This is the single largest lever and it is a discipline rather than a technology (Projection Pushdown).
- Sort or cluster on the column the common predicate uses, so block statistics can actually eliminate row groups. Statistics on unsorted data are metadata that never fires (Clustering and Sort Order).
- Push predicates to the source rather than filtering after loading — the whole benefit of statistics is lost if the engine reads everything and filters in memory (Source Pushdown).
- Prefer stable, low-cardinality encodings for the columns you filter on, and accept that high-cardinality identifier columns will neither compress nor skip well (Dictionary, Run-Length, Delta and Bit Packing).
- Keep row groups large enough to encode and skip well, and small enough that skipping is granular. A file made of one enormous row group cannot skip anything within itself (Parquet Internals, File Size and the Small-Files Problem).
- Measure with bytes scanned rather than with wall-clock time when comparing layouts, because wall-clock on a shared cluster mostly measures who else was running (Benchmark Fallacies: Confident Numbers That Are Wrong).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- Projection is guaranteed by the format: an unnamed column's chunk is not read. This is the one guarantee in the lesson that does not depend on the data.
- Statistics-based skipping guarantees only a sound *exclusion* test. A skipped block definitely contains no match; a read block may contain none, and on unsorted data most read blocks contain none.
- Vectorized execution guarantees identical results to row-at-a-time execution, with one honest exception: floating-point aggregates can differ in the last bits because the summation order changes. That is a real, reproducible difference and it surprises people during reconciliation (Why 0.1 + 0.2 Is Not 0.2 + 0.1's Problem).
- Nothing here guarantees a speedup. Every mechanism has a precondition — narrow projection, sortedness, low cardinality, selectivity — and a workload that meets none of them gets the overhead without the benefit.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The check that belongs here is an execution-plan assertion for the queries you care about: confirm that projection eliminated the columns you expected and that pruning eliminated the partitions you expected (Reading EXPLAIN ANALYZE).
- It misses correctness entirely — a query can prune perfectly and return a wrong number because the filter encodes a wrong business rule (Two Dashboards, Two Numbers).
- It also misses drift: a plan verified once stays verified only until the data's ordering, cardinality or file layout changes, none of which produces an alert (File Compaction).
- These mechanisms all require encoded, statistically-summarised chunks, and producing those requires batching writes. The most recent data is therefore usually held in a less optimised form until it is compacted in (File Compaction).
- That produces a characteristic shape most analysts notice without being able to name: queries over old data are fast and queries over the last hour are comparatively slow, on the same table.
- Nothing here removes latency from the pipeline. Columnar execution makes a scan cheaper; it does not make yesterday's partition arrive sooner (Cost vs Freshness).
- Adding a column costs nothing at read time for queries that do not name it, which is the strongest argument for wide analytical tables and against premature normalisation of them (Analytical Data Modeling).
- Changing a column's type usually forces re-encoding of every chunk, because the encoding is chosen per type. This is why type changes are breaking in analytical stores while additions are not (Breaking Schema Changes).
- Statistics are written at file-write time and describe the data as it was then. Rewriting or compacting a table regenerates them; editing rows underneath a format that does not track it invalidates them silently (Open Table Formats).
- Nothing here is a data-loss risk: execution is a read path, and a bad plan costs time and money rather than correctness (Scan Cost).
- Recovering from a layout that never skips means rewriting the table sorted differently — bounded, expensive, and safe if published atomically into a new location (Atomic Publish).
- Recovering from a floating-point discrepancy found during reconciliation means agreeing on a rounding rule rather than "fixing" the engine, because both summation orders are correct (Reconciliation).
What can go wrong
SELECT *reaching the engine from a BI tool or a view, so no column is ever skipped (Projection Pushdown).- Statistics that never eliminate a block because the data is randomly ordered on the predicate column (Clustering and Sort Order).
- A filter expressed in a way the engine cannot push down — a function applied to the column, an implicit cast — so the predicate is evaluated after the read instead of before it (Source Pushdown).
- Thousands of tiny files, so per-file metadata and open cost dominate and no amount of vectorization inside each one helps (File Size and the Small-Files Problem).
- The mitigation failing: a table sorted for one query pattern, which is by construction unsorted for every other pattern, so a second team's queries get slower when the first team's get faster.
- "Columnar is fast because of compression." Compression reduces the bytes for columns you *do* read. The larger effect is usually not reading a column at all, which is a layout property rather than a compression one (Row vs Column Storage).
- "Vectorization means SIMD." SIMD is one of the benefits and not the main one. Most of the gain is amortised interpreter overhead and branch-free inner loops; the vector width is a bonus on top (SIMD: One Instruction, Many Elements).
- "The engine will push my filter down." It will if it can recognise the predicate. Wrap the column in a function or force an implicit cast and it silently cannot, and the query reads everything (Predicate Pushdown).
- "Statistics mean the engine only reads matching blocks." They mean it can skip blocks that definitely do not match. On unsorted data almost every block's range covers the predicate, so almost nothing is skipped and the metadata was written for nothing (Clustering and Sort Order).
Operating it
- Bytes scanned per query, and the ratio of bytes scanned to bytes in the table (Scan Cost).
- Row groups or blocks skipped versus read for the common predicate — the number that tells you whether the sort order matches the workload (Partition Pruning).
- Columns referenced per query from the query log, so a table's width can be justified or challenged with evidence (Metadata: Technical, Operational and Business).
- File count and size distribution, because the small-file failure mode defeats every mechanism in this lesson at once (File Size and the Small-Files Problem).
- At 10x, projection and pruning carry the query and the execution model is barely relevant — you win by reading less, not by decoding faster.
- At 100x, both matter and the work is distributed, so the batch becomes the unit of parallelism as well as of decoding: many workers each processing vectors of a partition (Distributed Data Processing, Task Parallelism vs Data Parallelism).
- At every scale, the constant factor from vectorization is worth roughly the same proportion, which is why a single-node columnar engine can beat a cluster on datasets that fit — the cluster's coordination overhead is not free and the vector loop is the same loop (DuckDB Concepts).
- Bytes decoded is the dominant term, and it is set by columns projected and blocks skipped — both decided before execution begins (What Actually Drives Data Platform Cost).
- CPU spent decoding and evaluating is second, and it is what vectorization and dictionary comparison reduce. It becomes the dominant term only once the I/O has already been minimised (Busy Is Not the Same as Working).
- Per-file overhead is the term everyone forgets: listing, opening and reading the footer of thousands of small files can cost more than reading the data in them (File Size and the Small-Files Problem).
- Vectorized execution buys amortised overhead and cache-friendly loops, and costs a materially more complex engine: every operator must handle batches, masks, null bitmaps and dictionaries rather than a tuple.
- Late materialisation buys avoided decoding on selective queries and costs a random gather on unselective ones, where reading the column straight through would have been cheaper.
- Dictionary encoding buys cheap comparisons and small chunks on low-cardinality columns, and costs an indirection plus a dictionary that approaches the size of the data on high-cardinality ones.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALProjection, block-level skipping, batched decoding and branch-free predicate evaluation are the four mechanisms every columnar engine implements. What varies is naming, batch size and how aggressively each engine materialises.
- ENGINE-SPECIFICSome engines compile a query into machine code per operator pipeline, others interpret pre-compiled vectorized operators over batches. Both amortise per-row overhead and they trade differently: compilation costs startup latency and wins on long queries, interpretation starts instantly and wins on short ones.
- FORMAT-SPECIFICWhether statistics exist at page level as well as row-group level, and whether dictionaries are per column chunk or per file, differ between Parquet and ORC. That changes how granular skipping can be, though not the mechanism that makes skipping possible.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Computer Architecture owns everything under this lesson's third section: the memory hierarchy, cache line size and replacement, prefetching, SIMD registers and branch prediction. Columnar execution is those mechanisms exploited deliberately at the scale of a query rather than a loop.
- — Distributed Systems owns what happens when the scan is spread across machines — how a partial failure mid-scan is retried without double-counting into the running aggregate, and why a straggler decides the query's latency regardless of how well vectorized each worker is.