CSE Roadmap

Common tier · about 2 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Name the layout (15 minutes)

State whether storing all of one order's column values physically together is row-oriented or column-oriented.

Done when: row-oriented is correctly named.

Hint

Row-oriented, since the whole record's values are grouped together.

Easy 2: Predict the efficient query (15 minutes)

State which layout, row-oriented or column-oriented, is more efficient for SELECT AVG(price) FROM books; across a million rows.

Done when: column-oriented is correctly chosen.

Hint

Column-oriented, since only the price column needs to be read across all rows.

Easy 3: Why columns compress better (15 minutes)

Explain, in one sentence, why column-oriented storage compresses better than row-oriented storage.

Done when: the explanation correctly ties better compression to similar values being grouped together.

Hint

Grouping similar, repeating values together lets compression exploit those patterns far more effectively than mixed row data.

Medium

Medium 1: Choose the layout for a workload (25 minutes)

A system constantly reads and writes whole customer records, rarely aggregating across many rows. Decide which storage layout fits better.

Done when: row-oriented is correctly chosen, citing whole-record access as the dominant pattern.

Hint

Row-oriented, since whole-record reads and writes dominate the described workload.

Medium 2: Choose the layout for the opposite workload (25 minutes)

A reporting system aggregates revenue across millions of order rows but rarely fetches a single complete order. Decide which storage layout fits better.

Done when: column-oriented is correctly chosen, citing the aggregate-heavy, few-columns-at-a-time access pattern.

Hint

Column-oriented, since aggregating across a few columns over many rows dominates the described workload.

Medium 3: Predict the cost of a whole-row read in columnar storage (25 minutes)

Explain why fetching one complete row becomes relatively more expensive in column-oriented storage compared to row-oriented storage.

Done when: the explanation correctly notes the row's values are now scattered across separate column groupings, requiring several separate reads to reassemble.

Hint

Reassembling one row means gathering its value from each separate column grouping individually, rather than reading one contiguous chunk as row-oriented storage would.

Hard

Hard 1: Why most production databases default to row-oriented (30 minutes)

Explain why typical transactional application databases, handling constant small reads and writes of whole records, default to row-oriented storage rather than column-oriented.

Done when: the explanation correctly ties the dominant whole-record access pattern of transactional applications to row-oriented storage's strength.

Hint

Transactional applications constantly read and write whole records (one customer, one order at a time), which row-oriented storage handles efficiently by keeping each record's data together.

Hard 2: Running both layouts side by side (35 minutes)

Describe a realistic architecture where a company keeps its main application database row-oriented, but also maintains a separate column-oriented copy of the same data for reporting.

Done when: a realistic dual-storage architecture is described, with each layout's role correctly matched to its respective workload.

Hint

A common architecture runs the live application on a row-oriented database, periodically copying data into a column-oriented data warehouse or analytics engine used specifically for reporting queries.

Hard 3: A query that performs poorly on the wrong layout (40 minutes)

A report running SELECT SUM(price * quantity) FROM order_items JOIN books USING (book_id); is unexpectedly slow, and the team discovers the underlying storage is row-oriented. Explain why this layout mismatch causes the slowdown.

Done when: the explanation correctly notes that row-oriented storage must still read full rows, including unneeded columns, to extract the few needed values, wasting I/O that column-oriented storage would have avoided entirely.

Hint

Row-oriented storage must read each full row from disk to extract just the price and quantity values needed, wasting I/O on every other unrelated column in that row, which column-oriented storage would never have touched in the first place.