Columnar Storage and Vectorized Execution

An analytical query often asks a narrow question of a wide data set: scan months of sales, keep two regions and one product family, group by day, and sum revenue. A row-oriented engine stores each complete sale together, which is ideal when a transaction reads or changes one sale. For the analytical query, however, most fields in every row are irrelevant. Reading customer addresses, payment references, and fulfillment notes merely to inspect sold_at, region, product_family, and net_amount wastes I/O, decoding, cache capacity, and CPU instructions.

Columnar systems reorganize that work. Values from one column are stored together inside bounded row groups, making them compressible and independently readable. Metadata can eliminate whole groups before value decoding. Execution then processes batches of values through tight operators instead of constructing one general-purpose row at a time. Late materialization postpones rebuilding selected rows until a query actually needs them.

The thesis is not simply that columns are faster than rows. OLAP becomes efficient when physical layout, metadata, encodings, and execution cooperate to avoid moving or interpreting irrelevant data. A columnar file read through a row-at-a-time abstraction can squander that advantage, while a vectorized engine cannot recover bytes already read from an unsuitable layout.

Start With the OLAP Access Shape #

Online transaction processing and online analytical processing optimize different dominant operations. OLTP commonly performs selective point lookups and short updates while preserving low write latency and strong constraints. OLAP commonly scans many records, projects a few columns, filters, groups, joins, and computes aggregates. Neither label dictates one product, but it predicts useful physical choices.

Consider a sales fact relation:

sales(
  sale_id, sold_at, store_id, region, product_id, product_family,
  customer_id, quantity, gross_amount, discount_amount, net_amount,
  currency, payment_reference, fulfillment_note
)

A dashboard query is:

SELECT CAST(sold_at AS DATE) AS sale_day,
       SUM(net_amount) AS revenue
FROM sales
WHERE sold_at >= TIMESTAMP '2026-04-01 00:00:00'
  AND sold_at <  TIMESTAMP '2026-05-01 00:00:00'
  AND region IN ('east', 'north')
  AND product_family = 'storage'
GROUP BY CAST(sold_at AS DATE)
ORDER BY sale_day;

The query needs four source columns from fourteen. A row layout must step over or read pages containing the other ten because fields for one row are adjacent. A column layout can request only the four relevant column chunks. Its potential read fraction, before metadata and encoding overhead, is closer to

than to the fraction of qualifying rows. That is a workload-dependent expression, not a promised speedup.

Columnar storage is less attractive for fetching a complete record by sale_id, because the engine may need to locate and decode a value from many separate chunks before reconstructing the row. Frequent in-place updates are also awkward: compressed column segments are designed to be read in bulk, not rewritten around one changing value. Analytical systems often absorb new data through batches, immutable files, or append-friendly delta structures and later compact it.

Choose the layout from query shape and mutation pattern. A wide operational table does not become an analytical design merely because it is large, and a columnar replica or warehouse does not remove the need for transactional authority upstream.

Organize Columns Into Row Groups and Pages #

Pure column storage could place every value of one column in a single enormous stream. That would maximize type locality but make selective reading, parallelism, recovery, and metadata unwieldy. Practical formats divide data horizontally into row groups, then store one column chunk per column inside each group. Chunks are often divided again into pages with their own encodings and checksums.

File or segment
  Row group 0: rows 0..999999
    sold_at chunk: pages + statistics
    region chunk: pages + dictionary
    net_amount chunk: pages + statistics
    ...
  Row group 1: rows 1000000..1999999
    sold_at chunk
    region chunk
    net_amount chunk
    ...

Values at position $i$ in every chunk of one row group belong to the same logical row. That positional alignment allows a filter on region to produce a selection vector of row offsets, then use those offsets when reading net_amount. The system does not need to store the row key beside every column value merely to reconnect the chunks.

Row-group size controls several tradeoffs. Large groups amortize headers, improve long sequential reads, and give encoders more values from which to find repetition. Small groups provide finer pruning, more scheduling units, and less wasted decoding when a predicate selects a narrow range. Very small groups create excessive metadata, tiny reads, weak compression, and planning overhead. Very large groups make min/max metadata coarse and increase memory required for writing, decoding, or compacting one unit.

Physical ordering is as important as size. If sales are sorted by sold_at, April values cluster in a limited set of groups and time predicates can prune the rest. If rows are randomly interleaved across years, every group may have a wide timestamp range and time metadata excludes nothing. Sorting by (sold_at, region) may improve common time scans while offering some locality for region; sorting by one high-cardinality identifier may help point access but degrade the dashboard workload.

Files need immutable schema and type metadata, null representation, row counts, per-chunk offsets, and integrity information. Evolution rules must say how readers handle added columns, renamed identifiers, decimal precision changes, and incompatible types. Matching fields only by display name can silently bind old data to new meaning; stable field identifiers are safer when the format supports them.

Encode and Compress Values by Type #

Column chunks expose long runs of one logical type and often one distribution. That makes specialized encodings effective before or alongside general compression.

Dictionary encoding stores each distinct value once and replaces occurrences with compact integer codes. region might map east to 0, north to 1, and so on. Comparisons can operate on codes after resolving the predicate value. It works well for low or moderate cardinality; a nearly unique payment_reference can build a large dictionary with little benefit.

Run-length encoding stores repeated values as (value, run_length). It benefits sorted or clustered columns, such as a region value repeated for many adjacent rows. Random ordering breaks runs. Dictionary codes and run lengths can compose.

Bit packing uses the minimum practical bit width for bounded integers or dictionary codes. Four region codes need two bits per code rather than a general integer width. Frame-of-reference encoding stores a base plus small deltas, useful when values in a block occupy a narrow range.

Delta encoding represents ordered timestamps or identifiers by differences between adjacent values. If intervals are regular, deltas are small and compress well. Delta-of-delta encodes changes between those differences, although irregular arrivals reduce its effectiveness.

Null bitmaps separate validity from values, so null checks can process compact bits and the value stream need not carry a sentinel that collides with legitimate data. Decimal amounts should remain scaled integers or decimal values with declared precision; converting money through binary floating point merely to use a fast vector changes semantics.

General codecs can then compress encoded pages. Faster codecs reduce CPU and latency at the cost of a larger representation; denser codecs reduce storage and network bytes but consume more CPU. The correct choice depends on storage bandwidth, cache behavior, scan concurrency, and whether data is hot or archival. Apply policy per column class or temperature only when readers support it and operations can explain the resulting mix.

Encoding is not free. Writers must build dictionaries and statistics, readers must decode, and malformed metadata must be rejected safely. Extremely small pages may spend more on headers and codec setup than they save. Record encoded and compressed sizes separately so a format change can be attributed to its actual mechanism.

Prune and Push Predicates Before Decoding #

Every row group can carry statistics such as null count and minimum and maximum non-null values. For the sales query, a group with sold_at.max < 2026-04-01 cannot match. A group whose product-family dictionary does not contain storage cannot match either. The reader can skip their data pages entirely.

Suppose an illustrative file has twelve row groups ordered by month, one per month, and only the April group overlaps the requested interval. Pruning reduces twelve candidate groups to one. Inside April, assume four region pages have these dictionaries:

These counts illustrate mechanics, not measured performance. The engine then reads product_family for surviving positions and decodes net_amount only for rows remaining after both dimensions. The process narrows work in stages:

file metadata
  -> row-group candidates
  -> page candidates
  -> timestamp selection
  -> region selection
  -> product-family selection
  -> net-amount values for selected offsets

This is predicate pushdown: move a supported filter into the scan so the storage reader can apply it before producing general rows. Projection pushdown similarly requests only needed columns. A SQL expression can block pushdown if the storage reader lacks equivalent semantics. Wrapping a timestamp in an unsupported function, comparing incompatible types, applying a locale-specific collation, or calling a user-defined function may force the engine to materialize values and filter later.

Statistics must never create false negatives. Truncated string bounds, NaN values, null semantics, time zones, and collations need conservative handling. If metadata cannot prove a group is irrelevant, it must read it. Stale or corrupt statistics should reduce performance or fail validation, not silently remove qualifying rows.

Bloom filters can help negative equality lookups in high-cardinality columns, but they are probabilistic aids, not replacements for min/max ranges or exact dictionaries. A false positive reads extra data; a false negative would violate correctness and is not permitted.

Execute Operators on Vectors #

After scanning, a traditional iterator model may call next() for one row, dispatch through an operator tree, examine dynamically typed values, and repeat. That model is composable, but function calls, branches, object construction, and poor instruction locality become expensive across large scans.

Vectorized execution passes a batch, often hundreds or thousands of values, through each operator. A batch can contain typed arrays, null masks, and a selection vector naming active positions. The filter loop becomes regular:

for each batch:
  decode sold_at values needed by the batch
  selection = positions inside the time interval
  refine selection using region codes
  refine selection using product-family codes
  gather net_amount at selected positions
  update grouped sum vectors

Processing many values per call amortizes dispatch and exposes loops that compilers can optimize. Contiguous typed values improve cache use and can enable SIMD instructions when the operation and representation permit it. Selection vectors avoid copying surviving values after every filter; operators pass compact row positions until materialization is necessary.

Vectorization is not equivalent to SIMD. A vectorized operator can be valuable through batching and type specialization even if it uses scalar instructions. SIMD adds another layer with strict alignment, width, and semantic constraints. Nulls, variable-length strings, decimal overflow, and branchy expressions can limit it.

Batch size has no universal optimum. Tiny batches retain iterator overhead. Huge batches increase working memory, hurt cache locality, delay cancellation, and amplify wasted work when a downstream limit stops early. Engines often adapt batch size or use a format-specific natural unit. Spill-capable joins and aggregations must preserve vector semantics when memory is exhausted rather than failing unpredictably.

Expression compilation is a neighboring approach: generate specialized machine code for a fused pipeline instead of interpreting generic operators. It can remove branches and intermediate materialization, but compilation latency and code-cache pressure matter for short ad hoc queries. Mature engines may combine vectorized scans with compiled expressions rather than choosing one doctrine globally.

Delay Materialization and Preserve Row Identity #

Early materialization reconstructs complete rows soon after the scan. That simplifies downstream operators but decodes and copies columns that later filters may discard. Late materialization carries row positions or compact keys through selective work, fetching output columns only after the candidate set is small.

The sales query never needs complete rows. It can retain selected offsets, decode sold_at into day buckets, decode net_amount, and update aggregates. customer_id, fulfillment_note, and other columns remain untouched. A query that ultimately displays ten sale details can filter and rank using narrow columns, apply LIMIT 10, and only then gather wider display fields for those ten positions.

Late materialization depends on stable identity within a segment. Selection offset 81 must refer to the same logical row in every chunk. Compaction that rewrites files creates a new physical identity, so persisted references should use stable keys or versioned file-and-row identifiers, not unqualified offsets.

Joins complicate the model. A hash join can carry references to build and probe positions instead of concatenating wide rows immediately. That saves copies but may create random gathers later. If most rows survive, repeated gathers can cost more than materializing a compact intermediate once. Optimizers need selectivity, width, and locality estimates to choose the point of materialization.

Variable-length values add indirection through offset and byte buffers. Filtering integer dictionary codes is cheap; retrieving scattered long strings after a join can be cache-unfriendly. Keeping frequently grouped labels dictionary-encoded across operators may help, but dictionaries from different files need reconciliation before equal codes can be treated as equal values.

Accept the Write and Workload Tradeoffs #

Immutable columnar segments favor batch creation. New rows can land in a small row-oriented or lightly encoded delta, then be sorted and compacted into larger columnar files. Queries merge base segments, deltas, and deletion markers. This makes ingestion responsive but introduces read amplification until compaction catches up.

Updates often become a new version plus a delete vector or position marker for the old version. Frequent random updates create scattered deltas, weaken ordering, and force queries to reconcile many fragments. Deletes may remain as tombstones until rewrite. A workload with constant single-row mutation and immediate point reads may belong in row storage, with change data copied into a columnar analytical system.

Compaction is both optimization and correctness-sensitive data movement. It applies deletes, merges small files, re-sorts rows, rebuilds statistics, and chooses new encodings. It consumes read bandwidth, write bandwidth, CPU, and temporary storage while queries continue. Admission control should protect foreground scans and ingestion. A failed compaction must leave either old valid files or a fully published replacement set; a transactional manifest or atomic metadata update controls visibility.

Other recurring failures include too many tiny files, giant row groups that cannot prune, sorting on a key absent from important filters, dictionaries that explode on unique values, statistics computed with incorrect collation, schema evolution that reuses a field identity, and a query layer that reads every column despite format support. “Stored as Parquet” or another columnar format is not evidence that pushdown occurred.

Columnar OLAP also has weak fits: latency-critical key-value access, narrow tables where almost every column is read, workloads dominated by tiny writes, and queries requiring current transactional state before ingestion lag closes. Hybrid engines can maintain row and column representations, but then freshness, write amplification, and representation repair become explicit contracts.

Verify the Whole Scan Pipeline #

Correctness tests should compare columnar results with a simple trusted row implementation over generated data. Include nulls, empty and all-null pages, minimum and maximum values, negative decimals, dictionary changes, duplicate sort keys, Unicode strings, NaN where legal, timestamp boundaries, schema additions, deletes, and updates. Run every predicate both with pushdown enabled and disabled; results must match exactly.

Format tests corrupt checksums, page lengths, encodings, statistics, and dictionaries. Readers must reject malformed input without out-of-bounds allocation or silent truncation. Compatibility fixtures should be written and read across supported software versions. A successful round trip with one version does not prove evolution compatibility.

Performance verification should account for work rather than publish context-free speedups. For stable query fixtures, record:

• Files, row groups, and pages considered versus pruned.
• Compressed bytes read from storage and bytes returned by the scan.
• Columns requested and columns actually decoded.
• Rows examined, selected, and materialized.
• Decode time, filter time, exchange time, and aggregate time.
• Vector batch counts, average active positions, spills, and peak memory.
• Compaction backlog, small-file count, and ingest-to-query freshness.

Explain plans should expose pushed predicates, projection, row-group pruning, and vectorized operators. Alerts should focus on regressions such as pruning ratios collapsing after a data-order change, dictionary size rising unexpectedly, scan bytes growing for the same query class, or compaction debt producing too many fragments.

Benchmark with representative width, cardinality, ordering, selectivity, codec mix, object-store latency, concurrency, and warm caches as appropriate. Test broad scans and highly selective scans; a layout optimized for one can regress the other. Label synthetic results as synthetic and retain the fixture generator so conclusions can be reproduced.

Columnar analytical speed is cumulative avoidance. Projection avoids unrelated columns. Metadata avoids unrelated groups and pages. Encoding reduces representation size and sometimes permits comparisons without full decoding. Vectorization amortizes execution overhead. Late materialization avoids rebuilding rows that will not survive. None is a universal shortcut, but together they align the physical work with the narrow question an OLAP query asks. The operational goal is to keep that alignment visible as data distribution, schema, and workload change.