Row vs Column Storage

• Storage devices, page caches and CPU caches all move fixed-size blocks. Nothing anywhere in the stack can fetch four bytes in isolation — the smallest unit that moves is a sector, a page or a cache line, and whatever else is inside that unit comes along for free and for nothing (Memory Moves in Lines, Not Variables, The Memory Hierarchy).
• A row-oriented layout puts one record's columns adjacent. That makes "give me this whole record" one block fetch, and makes "give me this one attribute of every record" a fetch of every block with most of each one discarded (How Is Database Data Physically Stored?, Slotted Pages).
• A column-oriented layout puts one attribute's values adjacent across many records. That inverts both statements exactly: one attribute of everything is contiguous, and one whole record is scattered across as many places as it has columns (Physical Layouts Compared: Heap + Secondary Index vs Clustered Index).
• Column adjacency also changes what the *data* looks like to a compressor. Values in a column share a type, a domain and usually a great deal of local similarity, so run-length and dictionary encodings apply; a row mixes an integer, a two-character code, a small integer and a decimal, and there is far less structure to exploit (Why Analytical Data Compresses, Dictionary, Run-Length, Delta and Bit Packing).
• Real analytical formats are hybrids rather than pure columns. Parquet and ORC split a file into row groups first, then store each column's chunk inside the row group, so a horizontal slice of rows stays local to one place in the file while remaining column-addressable inside it. That hybrid is what makes both partition pruning and projection work on the same file (Parquet Internals).
• The same argument scales down to the CPU. A row store fills a cache line with one record's mixed fields; a column store fills it entirely with values the loop is about to use, which is the same reasoning as choosing a structure of arrays over an array of structures (Array of Structs, or Struct of Arrays?, Spatial Locality).

ROW-ORIENTED   one page holds whole records, back to back

  page 42
  +-------------------------+-------------------------+-------------------------+
  | 1 | DE | 34 | 120.00    | 2 | FR | 51 |  80.00    | 3 | DE | 29 | 400.00    |
  +-------------------------+-------------------------+-------------------------+
    ^------ one record, contiguous ------^

  SELECT avg(spend) FROM users
     -> read every page of the table
     -> id, country and age travel from storage into memory and through
        the CPU cache whether the query wanted them or not
     -> roughly three quarters of the bytes moved are discarded on arrival

  SELECT * FROM users WHERE id = 3
     -> one index descent, one page read, done.  ROW STORAGE WINS THIS.


COLUMN-ORIENTED   one chunk per column, records split apart

  id       chunk  ->  [ 1   , 2   , 3   , 4   , 5   , 6   , 7   , 8    ]
  country  chunk  ->  [ DE  , FR  , DE  , IT  , DE  , FR  , ES  , DE   ]
  age      chunk  ->  [ 34  , 51  , 29  , 44  , 38  , 61  , 27  , 45   ]
  spend    chunk  ->  [120.00,80.00,400.00,15.00,38.50,210.00,9.99,77.00]

  SELECT avg(spend) FROM users
     -> open the spend chunk, read it end to end
     -> the id, country and age chunks are never opened, so their bytes
        never leave storage at all.  COLUMN STORAGE WINS THIS.

  SELECT * FROM users WHERE id = 3
     -> four separate chunk reads plus reassembly of position 3
     -> and finding position 3 means scanning the id chunk, because
        there is no index descent here to find it with.

• Choose the layout from the access pattern, not from the product category. Scan-shaped, narrow-projection, append-mostly work wants columns; key-shaped, whole-record, update-heavy work wants rows (OLTP vs OLAP).
• Once columnar, stop writing SELECT *. It is the one construct that defeats the layout's primary advantage, and BI tools emit it constantly unless you stop them (Projection Pushdown).
• Sort or cluster the data on the column the common predicate uses, because per-block statistics can only skip blocks whose ranges exclude the value — and on randomly ordered data no block's range excludes anything (Clustering and Sort Order).
• Size the blocks deliberately. Larger row groups amortise metadata and encode better, and they also raise the floor on the smallest possible read. It is a trade with a middle, not a parameter to maximise (File Size and the Small-Files Problem).
• Batch the writes. Columnar layouts assume write-once-read-many; if the workload genuinely needs row-level mutation, use a table format that implements it explicitly rather than emulating it with rewrites (Open Table Formats, Upserts and Merges).

• A layout guarantees nothing about correctness. Both layouts return identical results for identical queries; this whole lesson is about cost, not about answers.
• Columnar guarantees that a column the query does not name is not read. That is a physical property of the format, not an optimisation the planner might or might not apply (Projection Pushdown).
• Block statistics guarantee only that a block *can* be skipped when its range excludes the predicate value. They can prove absence and never presence, so a block that is read may still match nothing (Predicate Pushdown).
• Nothing guarantees compression helps. Encoding gains depend entirely on the data's cardinality and ordering, and a high-cardinality identifier column may barely shrink at all (Dictionary, Run-Length, Delta and Bit Packing).