A relational engine can lay a table's data out on disk row by row or column by column. Describe both physical layouts and explain which workloads each favors, and why.
answer
- Row = whole record contiguous (NSM)
- Column = whole attribute contiguous (DSM)
- I/O ~ fraction of columns touched
- Point write touches every column file
- Compression + vectorization + zone maps
basics
~20 sA row store keeps all of a row's columns together, so reading or writing one whole row touches one place. A column store keeps each column's values contiguous, so scanning a few columns over many rows reads far less data. Rows suit point reads and writes; columns suit wide scans and aggregates.
solid answer
~50 s**Row store:** the storage unit is the row - every column value of a row sits adjacent inside a page. Fetching or modifying a whole row is one page access, which is exactly what OLTP does: find by key, read or write the entire record. **Column store:** each column is stored as its own contiguous run of values, and rows are reassembled by ordinal position. A query touching 3 of 60 columns reads only those 3 columns, so I/O falls roughly with the fraction of columns referenced. Each block holds one data type with similar values, so compression is far better, cutting I/O again, and tight per-column loops enable vectorized execution. The cost: writing one row in a column store touches every column's storage, and rebuilding a wide row means N separate reads stitched by position. So OLTP - point lookups, single-row writes, high concurrency, whole-row access - favors rows; analytics - scan millions of rows, few columns, aggregate - favors columns.
code
text · 7 linesrows: (1,42,'PAID',19.99) (2,7,'NEW',5.00) (3,42,'PAID',31.50)
column id: 1, 2, 3
column cust: 42, 7, 42
column status: 'PAID','NEW','PAID'
column amount: 19.99, 5.00, 31.50
row N = the Nth entry of every columngo deeper
Be able to state the two layouts and give one workload each: point lookups by key for rows, big aggregate scans for columns.
Explain the I/O argument quantitatively - bytes read scale with columns touched - and name the secondary wins: compression and batch execution.
Discuss the write path and row-reconstruction cost, and where the workload boundary actually sits for a given system rather than reciting the OLTP/OLAP slogan.
Frame it as a data-placement decision across a system: which copy of the data serves which workload, the freshness and operational cost of keeping both, and when a single hybrid engine beats two systems.
## Same logical table, two physical layouts SQL hides physical layout: a table is a set of rows with named columns. Underneath, the engine chooses the order in which values land on disk pages. Take `orders(id, customer_id, status, ..., amount)` with 60 columns and 100M rows. **Row-oriented (n-ary storage model, NSM):** a page holds a set of complete rows. On the page you see `(1, 42, 'PAID', ..., 19.99)(2, 7, 'NEW', ..., 5.00)...`. All of row 1 is contiguous. **Column-oriented (decomposition storage model, DSM):** each column becomes its own file or block sequence. All `id` values are contiguous, all `amount` values are contiguous. Row 1 is the first entry of every column; the row identity is the *position* (ordinal), not a stored pointer. ## Why layout decides performance Disk and memory are read in blocks, and the CPU reads cache lines. You pay for whole blocks, not for the bytes you wanted. - `SELECT * FROM orders WHERE id = 12345` - one row, all columns. A row store finds the page via an index and gets the whole row in one read. A column store must do 60 positional reads, one per column, and reassemble. - `SELECT status, sum(amount) FROM orders GROUP BY status` - two columns, 100M rows. A row store reads every page of the table, dragging 58 unwanted columns through I/O and cache to get at two. A column store reads only the `status` and `amount` sequences: roughly 2/60 of the bytes before compression. That asymmetry is the whole story. Row layout optimizes *access to a whole row*; column layout optimizes *access to a whole column*. ## The secondary effects that widen the gap **Compression.** A column block contains one type with low local entropy - repeated statuses, slowly-changing timestamps, a small set of currencies. Dictionary, run-length, and delta encoding routinely give 5-20x on real columns, so scans read even fewer bytes. A row block interleaves types and compresses poorly. **Execution efficiency.** A dense array of same-typed values lets the engine process values in batches in tight loops, using SIMD instructions and avoiding per-row interpretation overhead. **Skipping.** Columnar formats keep per-block min/max (zone maps) so blocks that cannot match a filter are skipped without decoding. ## The costs of columnar - **Point writes are expensive.** Inserting one row appends to 60 separate structures; updating one field may require rewriting or invalidating a compressed block. Engines mitigate with a row-oriented write buffer (delta store) merged into compressed column segments in the background - which adds read-time merge work and latency before data is fully compressed. - **Row reconstruction (tuple materialization)** costs N reads plus stitching, so `SELECT *` on a wide table is the worst case. - **Indexing and concurrency** are built around single-row access in OLTP engines; columnar engines usually favor large batch commits over many tiny transactions. ## The practical rule High-concurrency, small, key-based reads and writes over whole records: row store. Large scans over a minority of columns with aggregation: column store. Real systems often run both - a row-store OLTP database plus a columnar analytics copy, or one engine keeping both representations of the same data.
- If a column store is so much faster for scans, why is the primary transactional database of most applications still a row store?OLTP traffic is dominated by short transactions that read or write entire single rows found by key, and that is precisely the access pattern row layout optimizes. A column store pays N structure accesses per row and struggles with high rates of tiny writes because compressed blocks must be rewritten or buffered. Row stores also carry mature single-row locking, MVCC and index machinery tuned for concurrency.
- How does a column store know which values belong to the same row if it stores no row pointers?By position. Every column is stored in the same logical row order, so ordinal k in each column belongs to row k. Reassembly is a positional lookup across the needed columns. This is why deletes are usually done with a tombstone bitmap rather than by physically removing a value, since removing an entry would shift every subsequent position.
A row store is a filing cabinet of complete forms - pull one folder, you have everything about one customer. A column store is a spreadsheet split into separate single-column lists - to average one column you read one list, but to see one customer you must fetch the same line number from every list.
saying these in an interview costs you the question
- Saying a column store is simply faster than a row store, without naming the access pattern that decides it
- Believing the choice is about SQL or the data model rather than physical layout - both serve the same relational tables
- Claiming a column store stores no rows at all, missing that rows are reconstructed by ordinal position
- Assuming columnar wins on SELECT * of a wide table, which is its worst case
- Confusing a column store with a key-value or wide-column NoSQL store