Columnar Storage & Encoding

An encoding rewrites one column's values in a type-aware way — dictionary codes, run lengths, deltas — and can often be queried directly. A codec such as ZSTD then compresses those encoded bytes opaquely, and the block must be decompressed before anything can read it.

Columnar engines write immutable files, and the smallest unit a write can produce is a whole file. One row per insert means millions of tiny files, terrible compression, metadata bigger than data, and a background merge queue that can never catch up.

A zone map is small per-block metadata holding each column's minimum and maximum value in that block. Before reading a block, the engine tests the query's filter against those bounds and skips every block that cannot possibly contain a matching row.

Sorting puts equal values next to each other, so run-length encoding sees long runs and delta encoding sees small differences instead of noise. The same data can compress several times better sorted, and correlated columns downstream of the sort key gain too.

The engine never edits a file. It finds the files containing affected rows, writes new files holding the surviving and modified rows, and atomically swaps the file set in the table metadata. Old files stay until retention expires, then are dropped.

Because user ids are scattered across the whole timeline, every block's user_id min/max spans nearly the entire id domain, so no block can be proved empty. Block skipping only works on columns correlated with the table's physical order.

Because one column's values sit adjacent in memory at identical fixed width and type, a single SIMD instruction can compare eight or sixteen of them per cycle, with no per-value type dispatch, pointer chasing or branching.

Late materialization evaluates predicates on the filter columns first, producing a list of surviving row positions, and only then fetches the projected columns at those positions. It wins when the filter is selective and the projected columns are wide.

A row-wise UDF is a black box invoked once per value, so it collapses batch-at-a-time execution back to row-at-a-time: no SIMD, no branch-free evaluation, often a cross-language boundary per call, and the optimizer can neither push it to storage nor reorder it confidently.

Row groups bound the memory a writer and reader need, give the engine an independently readable unit it can hand to a parallel worker, and give each column chunk its own metadata so whole chunks can be skipped.

By ordinal position. Within one row group every column chunk holds the same rows in the same order, so the k-th value of each chunk belongs to the k-th row. No row identifier is stored beside the values; the shared ordering is the join.

Large files amortise per-file overhead — metadata, catalog entries, storage round trips, task scheduling — and give encodings enough rows to compress well. Files that are too large hurt too: less parallelism, coarser skipping, and every mutation rewrites more bytes.

Composite sorting is lexicographic: country is ordered only within rows sharing the same event_date. Blocks generally span many dates, so each one contains countries from across the whole list and its country min/max covers nearly everything.

Batches are sized so that the vectors an operator touches stay resident in L1 or L2 cache between operators. Too small and per-call overhead dominates; too large and intermediates spill to slower memory, so each operator re-reads its input from RAM.

Grouping on small fixed-width dictionary codes replaces string hashing and byte-by-byte comparison with integer operations on values already in cache. When the code range is small the engine can even index an array directly instead of hashing, and decode group labels once at the end.

The dictionary grows toward the size of the column itself and each code widens to roughly log2 of the cardinality, so the saving collapses. Most engines detect this and fall back to plain storage, leaving only the general codec to compress the column.

Choose ZSTD when scans are bound by bytes moved — remote object storage, network, or a storage bill — and LZ4 when scans are already CPU-bound on locally cached data. ZSTD buys a better ratio and charges compression CPU, mostly at write time.

It evaluates the predicate once per dictionary entry rather than once per row, turning the filter into a set of integer codes, then scans and groups on those codes and decodes only the surviving result values. Run-length encoding lets it aggregate whole runs at once.

The table accumulated hundreds of thousands of tiny files. Planning must enumerate them all, each scan task pays a storage round trip for a few kilobytes of data, and compression is poor — so the query becomes metadata and latency bound rather than throughput bound.

Clustering depth is the average number of blocks whose key ranges overlap at a given point of the key domain. Depth near 1 means disjoint ranges and near-perfect skipping; depth that climbs over time means new or rewritten blocks now span wide ranges and every query reads more.

Start from the freshness the dashboards actually need, size ingest batches to that interval, and let compaction absorb whatever fragmentation remains. Then bound write amplification by choosing how many merge passes data goes through and how large the final files get.

Rank the access patterns by bytes scanned times query frequency, give the physical order to the biggest, then fund the others separately: a partition axis, block-level skipping structures, a second ordered copy, or a pre-aggregate. Decide from measured traffic, not from opinion.

Weight it by how much of your workload is actually CPU-bound after pruning. Efficiency buys lower cost per query, better tail latency and more concurrency per node, but scale-out fixes throughput and cannot fix single-query latency or a bad data layout.

Delta stores each value as the difference from its predecessor, frame-of-reference stores one per-block minimum plus offsets from it, and bit-packing then writes those small numbers in just enough bits. Composed, a block of rising 64-bit ids can drop to a few bits per row.

A delete vector is a small side file marking which row positions in a data file are logically deleted. Readers filter them out on the fly. It turns an expensive file rewrite into a tiny write, at the cost of extra work on every read until compaction.

Min/max bounds only prove a value falls outside a block's range. A bloom filter answers possible membership, so it can eliminate blocks whose range covers the value but that do not actually contain it — rescuing equality lookups on columns scattered across the table.

A selection vector is a small list of the positions in a batch that survived a filter. Carrying it lets later operators skip non-matching rows without physically copying every projected column, which would cost more than the filter itself.

Width is nearly free only while every consumer projects narrowly. It stops being free once tools issue star-selects, once per-column metadata and short chunks add up, and on the write side, where every column is encoded on every load.