Row-oriented versus columnar storage
storage layout for OLTP versus OLAP.
row stores keep whole rows together, ideal for point reads and writes; columnar stores keep each column contiguous, enabling reading only needed columns and strong compression, ideal for scans and…
What's really being asked
The interviewer checks that you map storage orientation to workload and understand why columnar compression and selective reads dominate analytics.
The full answer
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.
The mistakes people make
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.
What usually comes next
What is dictionary versus run-length encoding; what are row groups; why does columnar enable vectorized execution; why is columnar bad for updates.
A 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.
Interview question
Why is columnar storage dramatically more efficient for an aggregate over two columns of a wide table?
- a.It caches the entire table in memory before aggregating
- b.It converts the query to a point lookup
- c.It reads only the referenced columns and their highly compressible homogeneous data, cutting I/OCorrect
- d.It indexes every column automatically, so no scan is needed
Why? this is the answer
Columnar layout lets the engine read just the needed columns and exploit strong same-type compression, slashing I/O versus a row store that drags whole rows through memory. It does not auto-index every column or turn scans into point lookups.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
- #databases
- #columnar-storage
- #olap
- #compression
- #parquet
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on databases — each one lists the topics its interview covers.
See open roles