Row-oriented versus columnar storage

WHAT THIS TESTS The interviewer checks that you map storage orientation to workload and understand why columnar compression and selective reads dominate analytics.

A GOOD ANSWER COVERS A row-oriented engine stores each row's columns together on a page, so reading or updating a whole record touches one contiguous region, which suits transactional point lookups, inserts, and updates. Compression is modest because adjacent values are heterogeneous types. A column-oriented format stores all values of one column contiguously, splitting a table into per-column segments, often within row groups in Parquet or ORC. Two advantages drive analytical efficiency. First, projection pushdown: an aggregate over two columns of a hundred-column table reads only those two columns from disk, avoiding the wasted I/O a row store pays to drag entire rows through memory. Second, compression: a column holds values of one type with low entropy and often repetition, so run-length encoding, dictionary encoding, delta encoding, and bit-packing achieve far higher ratios than mixed rows, further cutting I/O, and many engines operate directly on compressed, encoded columns with vectorized, cache-friendly batch processing. The cost is that assembling or modifying a full row means stitching across many column segments, making columnar poor for OLTP-style point writes.

COMMON WRONG ANSWERS Saying columnar is simply faster overall, ignoring its weakness at point writes and single-row reads. Forgetting projection pushdown as a separate win from compression. Believing row stores cannot compress at all. Confusing the formats with the query engine.

LIKELY FOLLOW-UPS What is dictionary versus run-length encoding; what are row groups; why does columnar enable vectorized execution; why is columnar bad for updates.

ONE CONCRETE EXAMPLE SELECT AVG(price) FROM sales over a billion-row, eighty-column table: a columnar format reads only the price column, already dictionary and delta compressed, scanning a few gigabytes. A row store must read every page containing those rows, pulling all eighty columns through I/O and memory to extract one, moving an order of magnitude more data.