Why does a column-oriented file layout beat a row-oriented one for a query that reads three of a table's 200 columns?
answer
- think about bytes read, not records
- one column's values stored contiguously
- cost tracks columns touched, not width
- three of two hundred columns read
- whole-record reads become the expensive case
basics
~20 sA column-oriented layout stores each column's values contiguously, so a scan reads only the three columns the query names instead of every byte of every row; a row-major file interleaves all 200 fields, so the whole record comes along.
solid answer
~40 sStorage hands out bytes in blocks, so what matters is how many of the bytes that cross the boundary the query actually wants. In a row-major file the three wanted fields sit a few bytes apart inside a ~1,600-byte record, far below any block size, so fetching them drags in all 200 columns. A column-oriented layout writes each column's values contiguously, so the reader pulls three long runs and never requests the other 197 — about 1.5% of the table instead of all of it. The runs are also homogeneous, so per-column encoding shrinks them further and the filter loop runs over one dense array. The same property is why this layout is a poor fit for reading every field of one record: rebuilding a single row means touching every column's block.
code
pseudocode · 12 lines# row-major scan: each read pulls every field of the record
total = 0
for each record in file.records:
if record.country == "DE":
total = total + record.amount
# column-major scan: only the two named columns leave storage
country = file.read_column("country")
amount = file.read_column("amount")
for i in 0 .. country.length - 1:
if country[i] == "DE":
total = total + amount[i]go deeper
Recall the one-line shape: a column-oriented file keeps all the values of one column together, a row-oriented file keeps all the fields of one record together, and a query that names three columns only needs to read three of them.
Explain the mechanism with granularity: storage serves blocks, so fields eight bytes apart inside a 1,600-byte record are never skipped in practice. Quantify a projection of 3 columns out of 200 as roughly 1.5% of the bytes.
Show the cost model both ways. Say which access patterns collapse under this layout — full-width record reads, per-record updates, one-record appends — and what you would put those on instead.
Frame it as a storage-strategy decision. Argue about which workloads justify a layout per access pattern, what a second copy costs in freshness and reconciliation, and when a single compromise layout is worth its mediocre numbers on both sides.
## The choice a writer has to make A table is a rectangle in the mind — records down, fields across — but storage is one long sequence of bytes, so whoever writes the file must flatten that rectangle in some order. **Row-major** order writes every field of the first record, then every field of the second. **Column-major** order writes every value of one column for a block of records, then every value of the next column. The logical content is identical. Only the byte order differs, and that order decides what a reader is forced to pay for. It decides anything at all because of **granularity**. Storage does not serve individual bytes: a device or a remote object store answers reads in blocks, from a few kilobytes to a few megabytes, and a request that wants eight bytes inside a block pays for the whole block. So the question behind every scan is: *of the bytes that cross the boundary into the reader, what fraction does the query want?* ## Why the three-column query gets cheap Take an event table of one billion records with 200 columns averaging eight bytes per value. A record is about 1,600 bytes and the table about 1.6 TB. 1. **Row-major.** The three wanted fields sit roughly eight bytes apart inside a 1,600-byte record — far below any block size. Every block fetched to obtain them carries the other 197 fields too. Useful fraction: about 1.5%. Bytes transferred: effectively the whole 1.6 TB. 2. **Column-major.** Those three columns are three contiguous runs of roughly 8 GB each, about 24 GB in total, and the other 197 columns are never requested. Bytes transferred: about 1.5% of the table — a reduction of roughly sixty-fold before any encoding is considered. The layout pays off in four further ways: - **Fewer, larger requests.** A column is read as long sequential runs, which prefetching and remote object stores both reward, instead of as a scatter of tiny reads. - **Cheaper bytes.** A column holds one type and one value domain, so per-column encoding and block compression shrink those 24 GB further; interleaved records offer a codec much less local redundancy. - **A tighter inner loop.** The filter and the aggregate run over a dense array of like-typed values with no per-record field navigation. - **No wasted materialisation.** Only the named columns are decoded into values, so decode work scales with the projection as well. A common wrong answer is that a row-major reader could simply seek past the fields it does not want. It cannot do so usefully, and the reason is the same granularity argument: the skips are eight bytes long and the reads are kilobytes long, so the skipped bytes arrive anyway. ## What the layout costs Nothing about this is free, and an interviewer usually wants the other half: - **Reassembling one whole record** means visiting every column's block and stitching the values back together, so a narrow single-record read pays the full width of the table. - **Writing is buffered.** A column block cannot be built from one record, so the writer accumulates many records before it can emit anything. - **In-place updates do not exist.** Changing one field of one record means rewriting the block that holds that column, or recording the change elsewhere and merging later. - **More metadata.** Every column in every chunk needs its own location and description, so a wide schema carries a large footer. ## Where each layout wins | Access pattern | Row-major | Column-major | |---|---|---| | Aggregate over a few columns of many records | Reads the whole record width | Reads only the named columns | | Every field of one record by key | One block, one read | One read per column, then reassembly | | Append one record | Cheap append | Buffer until a chunk fills | | Update one field | Rewrite one record | Rewrite a column block or log the change | | Compression leverage | Mixed types adjacent | One type and domain per block | The general rule is that a row-major file's cost tracks **how many records you touch**, while a column-major file's cost tracks **how many columns you touch**. Analytical queries touch few columns of enormous numbers of records; transactional access touches all columns of very few records. That is the whole trade, and it is why an analytics table and a serving store usually end up in different layouts rather than one clever compromise. ## What an interviewer is listening for A strong answer names granularity rather than reciting "columnar is faster". It quantifies: a projection of 3 columns from 200 reads roughly 1.5% of the bytes. It separates the layout win (fewer bytes requested) from the encoding win (each requested byte compresses better), because those are two effects and only the first is about layout. And it volunteers the losing case, which is the fastest way to show the cost model has actually been internalised.
- Does the same layout help a query that selects every column of a handful of records?No — it hurts. Each of the 200 columns is stored in its own block, so a full-width read of one record issues a read per column and then reassembles the values in order. A row-major file puts that record's fields in one place and answers it with a single block read. Narrow, full-width access is the access pattern this layout is worst at.
- Why does the gap widen as the table gets wider?Row-major cost grows with total record width, because every block fetched carries all fields. Column-major cost grows only with the columns the query names. Add 200 more unused columns and the row-major scan roughly doubles its bytes while the column-major scan reads the same 24 GB plus a slightly larger footer.
saying these in an interview costs you the question
- Says columnar is faster purely because it compresses better
- Claims the layout speeds up every query, including single-record lookups
- Believes a row-major reader can usefully seek past unwanted fields
- Confuses column-oriented storage with an index over a record store
- Assumes the whole file must be decompressed before one column is read