CSE Roadmap

Common tier · about 2 hours · 5 parts

Part 4 of 5 · 2 min read

Why columns compress so much better

Similar values grouped together compress far more effectively than mixed values.

Why columns compress so much better

Think of a drawer sorted entirely by color. A thousand identical blue items can be described as "a thousand blue items" in one short note. Scatter those same items randomly among many other colors, and no such shortcut works.

In plain words: column-oriented storage groups highly similar values together, since one column's values tend to repeat or follow patterns far more than a mixed row of unrelated columns does. This similarity lets compression algorithms achieve dramatically better ratios than compressing a row-oriented file, where each row mixes unrelated types and values together.

A worked example. A status column holding only a handful of distinct values, such as "placed," "shipped," "delivered," compresses extremely well when stored together, since the same few values repeat constantly.

The key lines. No new SQL; compression ratio is a storage-engine characteristic, not something a query controls.

Columnar compression

Columnar compression takes advantage of column-oriented storage grouping similar values together, achieving far better compression ratios than compressing mixed-type row data.

Example: a status column with only a few distinct repeating values compresses dramatically better stored column-wise than mixed in with unrelated row data.

Remember this

Row-oriented storage favors transactional workloads: reading and writing whole records at a time. Column-oriented storage favors analytical workloads: aggregating a few columns across huge numbers of rows. Level 12 covers analytical storage engines that lean heavily on this columnar advantage.

Quick notes

  • Row-oriented storage keeps one row's values physically adjacent, efficient for whole-record access.
  • Column-oriented storage keeps one column's values physically adjacent across rows, efficient for aggregating a few columns across many rows.
  • Column-oriented storage compresses far better, since similar values are grouped together.
  • Row-oriented storage fits transactional workloads; column-oriented storage fits analytical workloads.
  • The same SQL query can run on either layout, but performs very differently depending on which one is used.