Why does loading a columnar table in sorted order shrink it far more than loading it randomly?

## Encodings are local, so order is everything The encodings that give columnar storage its ratio — run-length, delta, and the dictionary code streams that feed them — all look at **neighbouring values inside one block**. None of them has a global view of the column. That single fact explains the whole phenomenon: the compressed size of a column is a function not just of *which* values it contains but of the *order* they appear in. ## Run-length encoding is the extreme case Run-length encoding replaces a stretch of identical values with the value and a count. Take a `status` column with three distinct values over ten million rows. - **Random order:** the probability that the next row repeats the current value is roughly one in three, so runs average around one or two rows. The encoding must store a value and a count per run, so the output can be as large as the input — or larger. Engines detect this and fall back to another encoding, but the win is gone either way. - **Sorted on `status`:** the column becomes three runs. Ten million values collapse into a handful of pairs. That is a difference of orders of magnitude on the same data, produced purely by ordering. ## Dictionary streams inherit the effect Dictionary encoding itself is order-independent — the dictionary holds the same distinct values either way, and the code width depends only on cardinality. But the *code stream* is just an integer column, and it is encoded in turn. Sorted data gives `0 0 0 0 1 1 1 2 2 2`, which run-length or bit-packs to almost nothing. Shuffled data gives an incompressible integer stream, and the general codec on top finds much less repetition too. So sorting improves dictionary-encoded columns even though the dictionary is unchanged. ## Numeric columns: deltas and the frame of reference A sorted numeric column produces small, non-negative deltas, and small deltas bit-pack into a few bits each. The same values shuffled produce deltas that span the full range of the column, sometimes needing *more* bits than the raw values because they are signed. A per-block minimum plus offsets — the frame-of-reference idea — is likewise narrow only when a block's values are close together, which sortedness guarantees and randomness destroys. ## The correlation cascade Sorting on one column also delivers locality to every column functionally dependent on or correlated with it. Sort on `country` and the `city`, `currency` and `timezone` columns become nearly sorted too, because their values are determined by (or strongly clustered under) the sort column. This is why the benefit of one good sort key often shows up across half the table. ## Choosing the order Two rules of thumb, and one tension between them. - **For ratio:** put the **lowest-cardinality** column first. It forms the longest runs, and each subsequent column still gets locality within each run of the previous one. Order the remaining sort columns by ascending cardinality for the same reason. - **For skipping work:** put the column your queries filter on most in front, so block-level metadata can eliminate blocks. That is a different objective and the two frequently disagree. The resolution is workload-driven: a table whose storage bill dominates and whose queries scan broadly leans toward the ratio ordering; a table with a hot, selective predicate leans toward that predicate. Compound orders can serve both when the selective column is also low-cardinality. ## What it costs Sorting is not free. It costs CPU and memory at load time, and a streaming ingest cannot maintain a global order — data arrives in whatever order it arrives, so the sortedness is only as good as the batch. Systems that reorganise in the background pay that cost later, in a rewrite that reads and rewrites data already stored. And there is **only one physical order**; the second query pattern gets whatever locality it happens to inherit. ## Measuring it Don't argue about it — measure. Load a representative sample twice, once as-is and once sorted, and compare compressed bytes, ideally per column. Per-column figures tell you which columns actually responded and which are incompressible regardless; that is what tells you whether a different sort key would help or whether the column is simply high-entropy. ## Interview framing The crisp answer: *encodings compress locality, sorting manufactures locality*. Everything else — dictionary code streams, delta widths, correlated columns — follows from that sentence.