Row vs Columnar Storage: Why It Matters

The choice between row vs columnar storage is the single physical decision that decides whether an analytics query over a billion-row table finishes in half a second or grinds for ten minutes against the exact same data — and it is a decision most engineers inherit without ever being told it was made. The table is identical either way: the same rows, the same columns, the same values. What changes is the order the bytes sit in on disk — whether the engine keeps every field of one record together, or keeps every value of one column together — and that byte-ordering choice cascades into how much data a scan must read, how well the data compresses, and how fast the CPU can chew through it. Get the layout right and a wide aggregation touches a tiny slice of the file; get it wrong and every query drags the whole table off disk to answer a question about two columns.

This guide opens that black box in layers. It starts with the raw on-disk difference between row-major and column-major, then builds up the reasons columnar layouts dominate analytics: column pruning so a scan reads only the fields it references, per-column encoding and compression that shrink same-domain values by an order of magnitude, and vectorized execution that processes a whole batch of one column at a time instead of one record at a time. From there it turns the argument around — the workloads where a row store is still the correct answer (point lookups, single-row writes, transactional mutation) — and finishes on the file formats and hybrids that most real systems actually run: Parquet, ORC, the PAX layout underneath both, and the HTAP engines that keep a row copy and a columnar copy side by side. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works. Examples are PostgreSQL and Python, but the mental model carries to every analytical engine.

When you want hands-on reps immediately after reading, drill the database practice library →, sharpen the scan-cost intuition on the query optimization practice library →, and rehearse the pipeline mechanics on the data-analysis practice library →.

---

On this page

• The physical layout difference — row-major vs column-major
• Why columnar wins analytics — compression + column pruning
• Encoding & vectorized execution
• Where row stores still win — OLTP, point lookups
• Formats & hybrids — Parquet / ORC / PAX / HTAP
• Cheat sheet — row vs columnar storage recipes
• Frequently asked questions
• Practice on PipeCode

---

1. The physical layout difference — row-major vs column-major

Row-major packs whole tuples together; column-major packs whole columns together — and that one byte-ordering choice sets your entire performance envelope

The one-sentence invariant: row vs columnar storage is a choice about the physical order of bytes on disk — a row store writes every field of record 1, then every field of record 2 (tuples are contiguous), while a columnar store writes every value of column A, then every value of column B (columns are contiguous) — and because a scan can only read contiguous bytes efficiently, the layout decides how much of the file a query must touch, how compressible the data is, and how expensive it is to fetch or mutate a single row. The logical table is unchanged; only the serialization order differs, and everything else in this article — pruning, encoding, vectorization, file formats — is a consequence that falls out of that one decision.

The two physical layouts.

• Row-major (row store). The unit of storage is the tuple. On disk you see [id₁, name₁, price₁, region₁][id₂, name₂, price₂, region₂]… — all four columns of row 1 sit adjacent, then all four columns of row 2. This is how PostgreSQL heap pages, MySQL InnoDB, and every classic OLTP engine store data. Reading one whole row is one contiguous read.
• Column-major (columnar store). The unit of storage is the column. On disk you see [id₁, id₂, id₃, …][name₁, name₂, name₃, …][price₁, price₂, …][region₁, region₂, …] — every id together, then every name. This is how Parquet, ORC, ClickHouse, DuckDB, Redshift, BigQuery, and Snowflake store data. Reading one whole column is one contiguous read; reading one whole row means gathering one value from each column region.

The axes that matter.

• Scan width — how many columns the query touches. Analytics queries are narrow: SELECT region, SUM(price) FROM sales GROUP BY region references 2 of maybe 80 columns. Columnar reads exactly those 2 column runs; row-major must read every byte of every row because the 2 wanted fields are interleaved with 78 it doesn't want.
• Compressibility — how alike adjacent bytes are. A column holds one type and one domain (region is a handful of country codes; price is a tight numeric range). Values that are alike sit adjacent, so they compress hard. Row-major interleaves an integer id, a text name, a decimal price, and a short code — adjacent bytes are unlike, so general compression struggles.
• Write cost — mutating one record. Row-major writes one tuple to one place: one INSERT touches one page. Column-major must append to every column region (and update every per-column encoding), so a single-row write fans out across all columns — cheap to scan, expensive to poke.
• Point-lookup cost — fetching one whole row by key. Row-major returns the entire tuple in one seek. Column-major needs N gathers (one per column) plus reassembly to rebuild the same row — the mirror image of the scan advantage.

The 2026 reality.

• Analytics is columnar, full stop. Every warehouse and lakehouse engine — Snowflake, BigQuery, Redshift, Databricks/Delta, ClickHouse, DuckDB — stores analytical data column-major, on-disk as Parquet/ORC or a proprietary columnar format. If the workload is "scan a lot of rows, touch a few columns, aggregate," the storage is columnar.
• Transactions are row-major. Postgres, MySQL, SQL Server, Oracle, and every operational database default to row storage because their workload is "fetch/insert/update whole rows by key." The row store is not legacy — it is the correct tool for mutation-heavy point access.
• Real systems run both. The modern stack is a row store (OLTP) feeding a columnar store (OLAP) via CDC or ELT, or a single HTAP engine keeping both representations. Nobody picks one layout for the whole company; they pick a layout per workload.

What interviewers listen for.

• Do you say "columnar reads only the columns the query touches" as the headline benefit? — required answer.
• Do you connect layout to compression ("same-type values adjacent compress better"), not just I/O volume? — senior signal.
• Do you name the mirror-image cost — columnar is expensive for point lookups and single-row writes? — senior signal.
• Do you describe the difference as byte order on disk, not as "a different kind of database"? — required answer.
• Do you land on "row for OLTP, columnar for OLAP, and most stacks run both" rather than declaring a universal winner? — senior signal.

Worked example — one table, two byte layouts

Detailed explanation. The fastest way to internalise the difference is to serialize one tiny table both ways and look at the byte order. Take a four-column sales table with three rows and write out what actually lands on disk under each layout. This is the artifact every storage discussion should start from — once you can draw the two byte strips, every downstream property (pruning, compression, lookup cost) becomes obvious.

• The table. sales(id INT, region TEXT, price NUMERIC, ts TIMESTAMPTZ) — four columns, three rows.
• Row-major serialization. Row 1's four fields, then row 2's four fields, then row 3's.
• Column-major serialization. All three ids, then all three regions, then all three prices, then all three timestamps.

Question. Write out the on-disk byte order for the same three rows under row-major and column-major, and mark which regions the query SELECT region, SUM(price) FROM sales GROUP BY region must read.

Input.

id	region	price	ts
1	EU	19.90	2026-09-05T10:00
2	US	4.50	2026-09-05T10:01
3	EU	19.90	2026-09-05T10:02

Code.

ROW-MAJOR (tuples contiguous) — one record at a time:
  [ 1 | EU | 19.90 | 10:00 ]  [ 2 | US | 4.50 | 10:01 ]  [ 3 | EU | 19.90 | 10:02 ]
   \_____ row 1 _____/          \_____ row 2 _____/         \_____ row 3 _____/

  Query SELECT region, SUM(price): the region and price bytes are
  interleaved with id and ts inside every tuple, so the scan must
  read ALL 12 fields to reach the 6 it wants.

COLUMN-MAJOR (columns contiguous) — one column at a time:
  id:     [ 1 , 2 , 3 ]
  region: [ EU , US , EU ]      <-- query reads this run
  price:  [ 19.90 , 4.50 , 19.90 ]  <-- and this run
  ts:     [ 10:00 , 10:01 , 10:02 ]

  Query SELECT region, SUM(price): read ONLY the region run and the
  price run (6 fields). The id and ts runs are never touched.

Step-by-step explanation.

1. Under row-major, the four fields of row 1 are physically adjacent, then the four fields of row 2, and so on. To answer a query about region and price, the engine still has to stream every tuple past the CPU because the wanted fields are wedged between id and ts in each record — you cannot read "just the region column" without skipping over id and ts on every single row, which defeats sequential I/O.
2. Under column-major, all three region values are one contiguous run and all three price values are another contiguous run. The scan reads those two runs and nothing else — the id run and the ts run are never brought off disk. This is column pruning and it is only possible because columns are contiguous.
3. Notice the region run is EU, US, EU — two identical values close together. Under column-major that repetition is adjacent and trivially compressible; under row-major the two EU strings are separated by a whole tuple's worth of unrelated bytes, so a compressor can't exploit the repetition nearly as well.
4. The three-row toy scales linearly: at a billion rows, row-major reads ~4 billion fields to answer this query; column-major reads ~2 billion (region + price) — and after encoding, far fewer bytes than that. The ratio is the performance gap.
5. The exact same bytes, reordered, produce a scan-optimised file or a lookup-optimised file. That is the whole thesis: layout is a serialization order, and serialization order is destiny for the query engine.

Output.

Layout	Bytes the query must read	Repetition adjacent?
Row-major	all 4 columns × all rows	no (values interleaved)
Column-major	region run + price run only	yes (region: EU, US, EU)

Rule of thumb. Before arguing about engines, draw the two byte strips for one table. If the workload reads few columns over many rows, the column-major strip wins on sheer bytes-read; if it reads whole rows by key, the row-major strip wins. The layout, not the brand, sets the envelope.

Worked example — the "how many bytes does this query read" estimate

Detailed explanation. Interviewers love a back-of-envelope I/O estimate because it forces you to reason about layout instead of hand-waving "columnar is faster." Take a realistic wide table and estimate the bytes scanned by a narrow aggregation under each layout. The estimate ignores compression for now (section 2 adds that multiplier) so the pruning effect is isolated.

• The table. events — 100 columns, 1 billion rows, ~200 bytes/row uncompressed (2 bytes/column average).
• The query. SELECT country, COUNT(*) FROM events WHERE event_type = 'purchase' GROUP BY country — references 2 columns (country, event_type).
• The metric. Uncompressed bytes read off disk.

Question. Estimate the raw bytes scanned by the query under row-major and column-major, and state the ratio.

Input.

Parameter	Value
Rows	1,000,000,000
Columns	100
Bytes per column (avg)	2
Row width	200 bytes
Columns referenced	2 (country, event_type)

Code.

ROWS          = 1_000_000_000
N_COLUMNS     = 100
BYTES_PER_COL = 2
ROW_WIDTH     = N_COLUMNS * BYTES_PER_COL      # 200 bytes
COLS_TOUCHED  = 2                               # country, event_type

# Row-major: a scan must read every column of every row, because the
# two wanted columns are interleaved inside each 200-byte tuple.
row_major_bytes = ROWS * ROW_WIDTH

# Column-major: read only the two referenced column runs.
col_major_bytes = ROWS * COLS_TOUCHED * BYTES_PER_COL

print(f"row-major:    {row_major_bytes/1e9:6.1f} GB")
print(f"column-major: {col_major_bytes/1e9:6.1f} GB")
print(f"ratio:        {row_major_bytes/col_major_bytes:.0f}x less I/O columnar")
# row-major:     200.0 GB
# column-major:    4.0 GB
# ratio:            50x less I/O columnar

Step-by-step explanation.

1. The row width is 100 columns × 2 bytes = 200 bytes, so the full table is 1e9 × 200 = 200 GB. A row-major scan of this query must read all 200 GB, because the two wanted columns cannot be isolated from the other 98 interleaved in each tuple.
2. A column-major scan reads only the country and event_type runs: 1e9 rows × 2 columns × 2 bytes = 4 GB. The other 98 columns are never fetched — the file offsets for those column chunks are simply skipped.
3. The ratio is 200 GB / 4 GB = 50×. The pruning factor is exactly N_columns / cols_touched = 100 / 2 = 50 in this uniform-width model. The wider the table and the narrower the query, the larger the win.
4. This estimate deliberately ignores compression. Section 2 shows columnar typically compresses another 4–10×, so the real bytes-read gap is often 200–500× for a wide-table narrow query — but even the pruning-only estimate is decisive.
5. The estimate also explains why row stores add secondary indexes and covering indexes to fake column pruning: an index-only scan on (event_type, country) lets a row store read a narrow structure instead of the heap. That is the row store borrowing the columnar trick — at the cost of maintaining an extra copy per index.

Output.

Layout	Bytes read	Formula
Row-major	200 GB	rows × all columns × width
Column-major	4 GB	rows × 2 columns × width
Pruning ratio	50×	n_columns / cols_touched

Rule of thumb. For a narrow query over a wide table, columnar I/O ≈ row-major I/O × (columns_touched / total_columns). Memorise the ratio form — it lets you estimate the win for any table in your head, and it makes the case for columnar without needing a benchmark.

Worked example — the layout decision axes table

Detailed explanation. The most reusable interview artifact is a compact axes table: given a workload, which layout wins on each axis. Build it once and you can defend any storage choice by walking the axes out loud. The axes are scan width, compression, write pattern, and point-access pattern — the same four that recur through every section of this article.

• Scan-heavy, narrow (analytics). Wide fact table, few columns per query, no single-row mutation.
• Mutation-heavy, whole-row (transactions). Point reads and writes of complete records by primary key.
• Mixed (HTAP). Both patterns on the same data; resolved by keeping two copies or a hybrid format.

Question. Fill in which layout wins each axis, then map three workloads to a recommended layout.

Input.

Axis	Row-major favoured when…	Column-major favoured when…
Columns per query	many / SELECT *	few of many
Rows per query	few (by key)	many (scan)
Write pattern	single-row INSERT/UPDATE	bulk append / batch load
Compression need	low	high (same-domain columns)

Code.

def pick_layout(cols_touched, total_cols, rows_scanned, single_row_writes):
    """Return 'row' or 'columnar' for a workload profile."""
    narrow  = cols_touched / total_cols < 0.30      # touches < 30% of columns
    scan    = rows_scanned > 100_000                # reads many rows
    mutating = single_row_writes                    # frequent point writes

    if mutating and not scan:
        return "row"          # OLTP: point access + mutation
    if narrow and scan:
        return "columnar"     # OLAP: wide-table narrow scan
    return "row (or hybrid)"  # ambiguous -> default row, or keep both

print(pick_layout(2,  100, 1_000_000_000, False))  # -> columnar
print(pick_layout(80, 80,  1,             True))   # -> row
print(pick_layout(5,  40,  500,           True))   # -> row (or hybrid)

Step-by-step explanation.

1. Workload 1 (analytics) touches 2 of 100 columns and scans a billion rows with no single-row writes — narrow and scan is true, so the function returns columnar. This is the canonical warehouse fact-table profile.
2. Workload 2 (transaction) reads/writes the whole 80-column row by key, one row at a time, with frequent mutation — mutating and not scan is true, so it returns row. This is the canonical OLTP profile: the row store's whole-tuple locality is exactly what a point read/write needs.
3. Workload 3 is genuinely mixed — a narrow-ish read but frequent point writes and only 500 rows scanned. Neither branch fires cleanly, so it defaults to row (or hybrid). Ambiguous workloads default to the row store because point writes are the axis columnar is worst at; if the scan side grows, you add a columnar replica.
4. The decision function is deliberately crude — three thresholds — because the real decision is crude. You are not tuning; you are picking a physical model that matches the dominant access pattern. Get the dominant pattern right and the layout follows.
5. The narrow threshold (touching < 30% of columns) is the practical line where pruning starts to dominate. If a query genuinely reads most columns of every row (SELECT * reporting), columnar loses much of its edge and the choice comes down to compression and write pattern alone.

Output.

Workload	cols/total	rows scanned	point writes	Layout
Analytics fact table	2/100	1e9	no	columnar
OLTP orders	80/80	1	yes	row
Mixed lookup+scan	5/40	500	yes	row / hybrid

Rule of thumb. Decide on the dominant access pattern, not the occasional one. Scan-heavy and narrow → columnar; mutation-heavy and whole-row → row; genuinely both → keep two copies. The layout is a consequence of the pattern, and the pattern is a fact about the workload, not a preference.

Data engineering interview question on storage layout selection

A senior interviewer often opens with: "You have a 90-column, 5-billion-row pageviews table. The product team runs funnel and cohort queries that each touch 3–6 columns and scan the whole table; the ops team occasionally looks up a single session by id. You're on Postgres today and every dashboard query takes minutes. Walk me through why the current layout is the problem, what layout you'd move the analytics to, and how you'd keep the single-session lookup fast."

Solution Using a columnar analytics copy fed from the row-store source

-- 1. The source stays row-major in Postgres — it is the system of record
--    and the single-session lookup wants whole-tuple locality.
--    A covering index makes the point lookup a single-structure read:
CREATE INDEX idx_pageviews_session ON public.pageviews (session_id)
  INCLUDE (user_id, url, ts);           -- lookup by session_id stays fast

-- 2. Export the analytics-shaped copy as columnar Parquet, partitioned by day.
--    (Run from the warehouse/ELT layer, not the OLTP primary.)
COPY (
    SELECT event_date, country, event_type, user_id, url, ts
    FROM   public.pageviews
    WHERE  event_date = DATE '2026-09-05'
) TO PROGRAM 'write_parquet --partition event_date'  -- illustrative sink
  WITH (FORMAT csv);

# 3. The analytics engine (DuckDB / Spark / Trino) reads the columnar copy.
#    The funnel query touches 3 columns of 90 -> column pruning does the work.
import duckdb

con = duckdb.connect()
con.execute("""
    SELECT country,
           COUNT(*)                                    AS views,
           COUNT(*) FILTER (WHERE event_type='signup') AS signups
    FROM   read_parquet('s3://lake/pageviews/event_date=2026-09-05/*.parquet')
    WHERE  event_type IN ('view','signup')
    GROUP  BY country
    ORDER  BY views DESC
""")
# Reads only the country + event_type column chunks; the other 87
# columns are never fetched from S3.

Step-by-step trace.

Step	Before (row-major only)	After (columnar analytics copy)
Funnel scan	reads all 90 columns × 5e9 rows	reads 3 column chunks only
Bytes off disk	~full table (900 GB)	pruned + compressed (~8 GB)
Single-session lookup	index scan on heap	still index scan on heap (unchanged)
Compression	poor (mixed-type rows)	strong (per-column encoding)
Freshness	live	minutes behind (ELT/CDC)

1. The row-store source is left in place: it is the system of record and the ops team's single-session lookup wants the whole tuple returned in one seek, which is exactly what a row-major covering index delivers. Do not move OLTP off row storage to fix an analytics problem.
2. The analytics workload is copied into a columnar Parquet dataset partitioned by event_date. The copy is scan-shaped: it exists to be aggregated, never point-mutated, so column-major locality and per-column compression are pure wins.
3. The funnel query references country and event_type — 2 or 3 of 90 columns. Column pruning reads only those column chunks; the other ~87 columns' bytes never leave S3. That is the 50–100× I/O reduction that turns minutes into sub-second.
4. Freshness is the trade: the columnar copy lags the source by the ELT/CDC interval (minutes). Analytics tolerates minutes; the single-session ops lookup, which needs live data, is answered from the live row store — so each workload is served by the layout that fits it.
5. The result is the standard modern shape: row store for point access and writes, columnar copy for scans, one directional pipeline between them. Neither layout is "better" — each owns the access pattern it is built for.

Output:

Metric	Row-major only	+ Columnar copy
Funnel query time	minutes	sub-second
Bytes scanned per funnel query	~900 GB	~8 GB
Single-session lookup	fast (index)	fast (unchanged)
Storage overhead	1 copy	1 copy + compressed columnar copy
Analytics freshness	live	minutes (ELT lag)

Why this works — concept by concept:

• Column pruning — because the columnar copy stores each column contiguously, the scan reads only the referenced column chunks and skips the rest by file offset. Touching 3 of 90 columns reads ~3% of the width before compression even helps.
• Per-column compression — a columnar copy groups same-type, same-domain values adjacently, so encoding + Zstd shrink them far below the mixed-type row representation, cutting bytes-off-disk again on top of pruning.
• Whole-tuple locality preserved — the single-session lookup stays on the row store, where a covering index returns the full record in one seek. The row layout is retained precisely because the lookup needs it.
• Directional pipeline — CDC/ELT moves data one way (row → columnar). The columnar side is append-mostly, so it never pays the single-row-write penalty that would cripple it.
• Cost — one extra compressed copy of the analytics columns (often < 20% of the raw table after compression) and an ELT lag of minutes. In exchange, funnel scans drop from O(full table) to O(referenced columns) — a 50–100× I/O reduction. The point lookup stays O(log n) on the row store's index. You pay storage to buy back scan latency.

Database
Topic — database
Database storage-layout and internals problems

Practice →

Data analysis Topic — data-analysis Data-analysis problems on scan-shaped pipelines

Practice →

---

2. Why columnar wins analytics — compression + column pruning

Contiguous columns let the engine read only what a query references and compress what it does read — two multiplicative wins that stack

The mental model in one line: columnar storage beats row storage on analytics because two effects multiply — column pruning means the scan reads only the columns the query names (skipping the rest by file offset), and per-column compression means the columns it does read are far smaller on disk (same-type, same-domain values sit adjacent and encode tightly) — and on top of both, block-level statistics let the engine skip whole ranges of rows whose values can't match the predicate. Pruning cuts the width of the read, compression cuts the depth, and statistics cut the height — three orthogonal reductions that a row store can only partly imitate with indexes.

Column pruning — the width reduction.

• What it is. Also called projection pushdown: the engine parses which columns the query references and fetches only those column chunks from storage. SELECT a, b FROM t reads the a and b chunks; c…z are never read.
• Why only columnar can do it cheaply. Pruning requires columns to be contiguous. In a row store the wanted columns are interleaved with every other column in each tuple, so "reading only column a" means seeking past the rest on every row — random-access death. Columnar reads a clean contiguous run.
• The magnitude. Bytes read scale with columns_touched / total_columns. A 3-column query over a 100-column table reads ~3% of the width before compression. This is usually the single biggest factor in the columnar win.
• The anti-pattern. SELECT * defeats pruning entirely — it references every column, so columnar has to read the whole width and loses its main advantage. Analytics queries should name their columns.

Compression — the depth reduction.

• Same domain compresses hard. A country column is a few hundred distinct strings repeated billions of times; a status column is 4 values; a price column is a tight numeric range. Adjacent same-domain values are highly redundant, so encoding + a general compressor shrink them 5–20×.
• Row-major can't match it. In a row the compressor sees int, text, decimal, timestamp, int, text, … — the byte stream flips type every few bytes, so redundancy is low and compression ratios are modest (often 2–3×).
• Type-aware, not just byte-aware. Columnar compressors know the column's type and apply the right encoding first (section 3): run-length for repetition, dictionary for low-cardinality strings, delta for sorted or timestamp columns. Only then does a byte compressor run on top.
• The compound effect. Pruning × compression multiply: read 3% of the width, and that 3% is itself 10× smaller. A 100-column table's 3-column query can read 0.3% of the raw table's bytes.

Block statistics — the height reduction.

• Zone maps / min-max stats. Columnar files store per-block statistics (min, max, null count, sometimes a bloom filter) for each column chunk. Before reading a block, the engine checks: can any row in this block satisfy WHERE ts >= '2026-09-01'? If the block's max(ts) is August, skip it entirely.
• Predicate pushdown. The filter is pushed down to the storage layer so blocks are eliminated before decompression, not after. This is why a well-clustered columnar table can answer a selective query by reading a handful of blocks out of thousands.
• Clustering matters. Statistics only prune well if the data is sorted/clustered on the filter column. Randomly ordered data has wide min-max ranges per block (every block spans the whole domain), so nothing gets skipped. Sorting on the common filter column is the enabling step.

What interviewers listen for.

• Do you separate pruning (width) from compression (depth) as distinct, multiplicative wins? — senior signal.
• Do you note that SELECT * kills pruning? — required answer.
• Do you connect compression ratio to same-domain adjacency, not magic? — senior signal.
• Do you mention min-max/zone-map block skipping and that it needs clustering to work? — senior signal.

Worked example — measuring the compound pruning × compression win

Detailed explanation. Put numbers on the two effects for a realistic table and show how they multiply. Take a wide events table, a 3-column aggregation, and typical per-column compression ratios, and compute the bytes actually read off disk versus a row-store full scan.

• The table. 100 columns, 1e9 rows, 200 bytes/row raw = 200 GB.
• The query. references 3 columns (country, event_type, price).
• Compression. columnar per-column average 10×; row-major whole-row average 3×.

Question. Compute bytes read off disk for the query under (a) row-major compressed and (b) columnar compressed, and state the total speedup factor.

Input.

Parameter	Value
Raw table	200 GB
Columns total / touched	100 / 3
Columnar compression	10× per column
Row-major compression	3× whole row

Code.

RAW_GB          = 200
TOTAL_COLS      = 100
COLS_TOUCHED    = 3
COL_COMPRESS    = 10     # columnar: same-domain values encode tightly
ROW_COMPRESS    = 3      # row-major: mixed-type bytes compress modestly

# Row-major: must read every column (no pruning), then divide by its
# modest whole-row compression ratio.
row_gb = RAW_GB / ROW_COMPRESS

# Columnar: prune to the 3 touched columns (width), then apply the
# stronger per-column compression (depth). The two effects multiply.
pruned_raw = RAW_GB * (COLS_TOUCHED / TOTAL_COLS)   # width reduction
col_gb     = pruned_raw / COL_COMPRESS              # depth reduction

print(f"row-major read:  {row_gb:6.2f} GB")
print(f"columnar read:   {col_gb:6.3f} GB")
print(f"speedup:         {row_gb/col_gb:6.0f}x less I/O")
# row-major read:   66.67 GB
# columnar read:     0.600 GB
# speedup:             111x less I/O

Step-by-step explanation.

1. Row-major cannot prune, so it reads the full 200 GB and gets only its modest 3× whole-row compression: 200 / 3 = 66.7 GB off disk. The mixed-type byte stream is the ceiling on its compression.
2. Columnar prunes first: 3/100 of the width is 6 GB of raw column data for the three touched columns. Then per-column compression at 10× brings that to 0.6 GB. Pruning and compression are independent multipliers.
3. The combined factor is 66.7 / 0.6 ≈ 111× less I/O. Decompose it: pruning contributes ~33× (100/3 relative to reading all columns) and the compression-ratio difference contributes the rest. Neither effect alone explains the gap — they stack.
4. The 10× per-column ratio is not optimistic for real columns: low-cardinality strings dictionary-encode to a byte or two, sorted timestamps delta-encode to tiny deltas, repeated codes run-length-encode to almost nothing. Section 3 shows the encodings.
5. This is why "just add an index in Postgres" only partly closes the gap: a covering index fakes pruning (read a narrow structure) but does not get columnar's per-column compression, and it costs a full extra copy per index. Columnar gets both for free from the layout.

Output.

Layout	Pruned?	Compression	Bytes read
Row-major	no	3× whole-row	66.7 GB
Columnar	yes (3/100)	10× per-column	0.6 GB
Speedup	—	—	~111×

Rule of thumb. Estimate the columnar win as pruning_factor × compression_ratio_gain, not one or the other. A wide-table narrow query commonly lands at 50–200× less I/O than a row-store scan — big enough that it changes what queries are even feasible.

Worked example — column pruning in a real query plan

Detailed explanation. Show that pruning is a plan-level behaviour you can observe, not a theoretical claim. Run the same aggregation against a Parquet dataset in DuckDB with SELECT * versus an explicit column list, and read off the bytes/columns scanned. This is the demo that makes pruning concrete in an interview.

• The dataset. events.parquet, 100 columns, partitioned.
• Query A. names 2 columns.
• Query B. SELECT * then aggregates — references every column.

Question. Show the pruning difference between naming columns and SELECT *, and explain why B is slow.

Input.

Query	Columns referenced	Expected scan
A: SELECT country, COUNT(*)	1 (country)	1 column chunk
B: SELECT * … GROUP BY country	100 (all)	full width

Code.

-- Query A — explicit columns: the planner prunes to the 'country' chunk
EXPLAIN ANALYZE
SELECT country, COUNT(*) AS n
FROM   read_parquet('events.parquet')
GROUP  BY country;
--  Parquet scan:  columns = [country]        <- pruned
--  bytes read  :  ~0.4 GB

-- Query B — SELECT * defeats pruning: every column chunk is read
EXPLAIN ANALYZE
SELECT country, COUNT(*) AS n
FROM (SELECT * FROM read_parquet('events.parquet')) t
GROUP  BY country;
--  Parquet scan:  columns = [all 100]        <- NOT pruned
--  bytes read  :  ~40 GB

Step-by-step explanation.

1. In Query A the planner sees that only country is referenced (the COUNT(*) needs no column values), so it pushes a projection of [country] into the Parquet scan. The reader opens only the country column chunk in each row group and skips the other 99 chunks' byte ranges.
2. In Query B the inner SELECT * materialises all 100 columns before the outer aggregation, so the planner cannot prune — it must read every column chunk even though the final result uses one. The SELECT * is a projection barrier.
3. The observable difference is ~100× bytes read (0.4 GB vs 40 GB) for an identical result. Pruning is not a micro-optimisation here; it is the difference between a fast query and a slow one.
4. This is why analytics style guides ban SELECT * in production queries and views: on columnar storage, naming columns is a first-order performance decision, not a style preference. A view defined as SELECT * propagates the anti-pattern to every query built on it.
5. The same logic applies to WHERE on unreferenced columns: filtering on a column pulls that column's chunk into the scan even if it isn't in the projection, so a filter has its own read cost — but statistics (next example) can eliminate most of it.

Output.

Query	Columns scanned	Bytes read	Same result?
A (named columns)	1	~0.4 GB	yes
B (SELECT *)	100	~40 GB	yes
Ratio	100×	100×	—

Rule of thumb. Name the columns you need — never SELECT * on columnar storage, and never define a view as SELECT *. Pruning is real and plan-visible; a wildcard projection throws away the layout's biggest advantage.

Worked example — min-max block skipping needs clustering

Detailed explanation. Block statistics only help if the data is ordered so that each block covers a narrow value range. Compare a selective time-range query against a Parquet dataset written in random order versus sorted by timestamp, and show how many row groups get skipped. This is the step teams forget, then wonder why "columnar isn't fast."

• The dataset. 1e9 rows, 1000 row groups of 1M rows each, filter WHERE ts BETWEEN '2026-09-05' AND '2026-09-06' (~1 day of 365).
• Unsorted. rows in random ts order → every row group spans the whole year.
• Sorted. rows sorted by ts → each row group covers a narrow ts range.

Question. Compute how many row groups the engine must read in each case using min-max stats.

Input.

Layout	Row groups	Per-group ts range	Groups matching 1-day filter
Unsorted	1000	full year (min=Jan, max=Dec)	all 1000
Sorted by ts	1000	~0.365 day each	~3

Code.

ROW_GROUPS      = 1000
DAYS_IN_DATA    = 365
FILTER_DAYS     = 1

# Unsorted: each group's [min_ts, max_ts] spans the whole year, so the
# filter's range overlaps EVERY group -> no group can be skipped.
unsorted_groups_read = ROW_GROUPS

# Sorted by ts: each group covers DAYS_IN_DATA / ROW_GROUPS days.
# The 1-day filter overlaps only the groups whose range intersects it.
days_per_group = DAYS_IN_DATA / ROW_GROUPS          # 0.365 days/group
sorted_groups_read = max(1, round(FILTER_DAYS / days_per_group)) + 1

print(f"unsorted: read {unsorted_groups_read} / {ROW_GROUPS} groups")
print(f"sorted:   read {sorted_groups_read} / {ROW_GROUPS} groups")
print(f"skip win: {unsorted_groups_read / sorted_groups_read:.0f}x fewer groups")
# unsorted: read 1000 / 1000 groups
# sorted:   read 4 / 1000 groups
# skip win: 250x fewer groups

Step-by-step explanation.

1. Min-max block skipping works by comparing the query predicate against each row group's stored [min, max] for the filter column. If the ranges cannot overlap, the whole group is skipped without decompression.
2. In the unsorted dataset, rows land in random timestamp order, so every 1M-row group contains dates from across the entire year — each group's [min_ts, max_ts] is roughly [Jan, Dec]. The 1-day filter overlaps all 1000 groups, so nothing is skipped and pruning by statistics fails.
3. In the sorted dataset, each group covers a contiguous ~0.365-day slice. The 1-day filter overlaps only ~3–4 groups; the other ~996 are eliminated by their min-max ranges before any I/O. That is a 250× reduction in groups read.
4. The lesson: columnar's statistics are only as good as the clustering. Writing data sorted (or partitioned) on the common filter column is what activates block skipping. Many "columnar is slow" complaints are really "data written in ingest order, not query order."
5. Partitioning (event_date=… directories) is coarse clustering at the file level; sorting within files is fine clustering at the row-group level. Real deployments use both — partition prune to the day, then min-max skip to the hour.

Output.

Layout	Groups read	Groups skipped	Effect
Unsorted	1000	0	stats useless
Sorted by ts	~4	~996	250× fewer groups

Rule of thumb. Statistics prune only what clustering exposes. Sort or partition columnar data on the columns you filter on most; otherwise every row group spans the whole domain and min-max skipping buys you nothing.

SQL interview question on columnar scan performance

A senior interviewer might ask: "Your team migrated a 120-column, 3-billion-row clickstream table from Postgres to Parquet on S3, queried via Trino. Analysts complain that SELECT * FROM clicks WHERE day = '2026-09-05' is still slow, but SELECT user_id, url FROM clicks WHERE day = '2026-09-05' is fast. The data is written in ingestion order. Explain both observations and give the two changes that make the whole workload fast."

Solution Using explicit projection, partition + sort clustering, and predicate pushdown

-- Observation 1: SELECT * defeats column pruning -> reads all 120 chunks.
-- Fix A: name columns so the scan prunes to what each query needs.
SELECT user_id, url, referrer
FROM   clicks
WHERE  day = DATE '2026-09-05';          -- reads 3 column chunks, not 120

-- Observation 2: written in ingest order -> row-group min-max on `day`
-- spans many days, so partition/stat pruning is weak.
-- Fix B: partition by day and sort within partition by the hot filter col.
CREATE TABLE clicks_opt
WITH (
    format          = 'PARQUET',
    partitioned_by  = ARRAY['day'],
    sorted_by       = ARRAY['ts']        -- clusters row groups by time
) AS
SELECT * FROM clicks;

-- After the rewrite, a selective query prunes on all three axes:
EXPLAIN
SELECT user_id, COUNT(*)
FROM   clicks_opt
WHERE  day = DATE '2026-09-05'           -- partition prune: 1 of 365 dirs
  AND  ts >= TIMESTAMP '2026-09-05 09:00' -- row-group min-max skip within day
GROUP  BY user_id;                        -- projection prune: 2 columns read

Step-by-step trace.

Axis	Before	After
Projection	SELECT * reads 120 chunks	named columns read 2–3 chunks
Partition	one big dataset, no dirs	day= partitions, prune to 1/365
Row-group stats	ingest order → wide min-max	sorted by ts → narrow min-max
Predicate pushdown	filter applied after read	filter eliminates groups pre-read
Compression	already columnar	unchanged (still per-column)

1. SELECT * is the first problem: it references all 120 columns, so Trino cannot prune and reads every column chunk. Naming user_id, url, referrer prunes the scan to those chunks — the fast query the analysts already noticed was fast because it named columns.
2. The second problem is clustering. Written in ingestion order, each row group's [min_day, max_day] spans many days, so filtering day = '2026-09-05' cannot skip groups by statistics — every group might contain that day. Partitioning by day turns the filter into a directory prune (read 1 of 365 partitions).
3. Sorting within each partition by ts narrows each row group's time range, so a sub-day filter (ts >= 09:00) skips most groups inside the chosen partition via min-max stats. Partition pruning is coarse; row-group skipping is fine; together they read a sliver of the table.
4. Predicate pushdown ties it together: the day and ts filters are evaluated against partition paths and row-group stats before decompression, so eliminated blocks cost zero I/O. The scan only decompresses the row groups that can actually contain matching rows.
5. Compression was never the issue — the data was already columnar and per-column compressed. The two fixes (name columns, cluster the data) are about pruning width and height; compression was already reducing depth. All three axes then compound.

Output:

Query shape	Chunks read	Partitions read	Row groups read
SELECT *, ingest order	120	all	all
Named cols, partitioned + sorted	2–3	1 / 365	few per partition
Net effect	width ↓	height ↓ (coarse)	height ↓ (fine)

Why this works — concept by concept:

• Projection pushdown (column pruning) — naming columns lets the scan read only those column chunks. SELECT * references everything and forces the full width; it is the single most common cause of "columnar is still slow."
• Partition pruning — partitioning by day maps the common filter to directory paths, so the engine lists and reads one partition instead of scanning all 365. Coarse but decisive block elimination.
• Row-group min-max skipping — sorting within a partition narrows each row group's value range, so sub-partition filters skip most groups by their stored min/max before any decompression. Clustering is what makes statistics useful.
• Predicate pushdown — filters are pushed to the storage layer and applied to partition paths and row-group stats first, so eliminated data is never read or decompressed. Pushdown turns statistics into I/O savings.
• Cost — a one-time rewrite (CTAS to a partitioned, sorted copy) plus the discipline of naming columns. In return, a selective query drops from O(all columns × all row groups) to O(few columns × few row groups) — often two to three orders of magnitude less I/O. The only ongoing cost is maintaining the sort/partition on ingest.

Optimization
Topic — optimization
Query-optimization problems on pruning and scan cost

Practice →

Database Topic — database Database problems on compression and layout

Practice →

---

3. Encoding & vectorized execution

Lightweight per-column encodings shrink the data before any byte compressor runs, and columnar batches let the CPU scan it branch-light

The mental model in one line: columnar storage compresses well not by magic but by encoding each column with a scheme matched to its type and distribution — run-length for long repeats, dictionary for low-cardinality strings, delta and frame-of-reference for sorted or clustered numerics, bit-packing for small integer ranges — after which a general compressor (Snappy/Zstd) squeezes the already-compact codes further; and because the column is stored as one dense array, the engine runs vectorized execution, applying an operation to a whole batch of values at once (SIMD-friendly, branch-light) instead of interpreting one row at a time. Encoding shrinks the bytes; vectorization shrinks the CPU cycles per row. Both are only possible because a column is a homogeneous contiguous array.

The lightweight encodings — matched to the column.

• Run-length encoding (RLE). Stores (value, run_length) pairs instead of repeated values. A status column that is paid, paid, paid, … for a million rows becomes (paid, 1_000_000). Ideal for sorted or low-cardinality columns with long runs. Turns repetition into near-zero bytes.
• Dictionary encoding. Builds a dictionary of distinct values and stores small integer codes instead. A country column of 200 distinct strings becomes a 200-entry dictionary plus a stream of 1-byte codes. Strings collapse to integers; the integers then bit-pack or RLE further.
• Delta encoding. Stores differences between consecutive values. A monotonically increasing id or ts column becomes a stream of tiny deltas (often 1) instead of full 8-byte values. Ideal for sorted keys and timestamps.
• Frame-of-reference + bit-packing. Subtract a per-block base value, then store the residuals in the minimum number of bits. A column of ages (0–120) needs 7 bits, not 32. Packs small-range integers to a fraction of their nominal width.

The compression stack — encoding then compressor.

• Two layers, not one. Columnar files apply a type-aware encoding first (RLE/dict/delta/bit-pack), then optionally a byte-level compressor (Snappy, Zstd, gzip) on top of the encoded stream. The encoding exploits column semantics; the compressor mops up residual byte redundancy.
• Why order matters. Dictionary-encoding a string column to 1-byte codes gives the byte compressor a small, regular stream to work with — far more compressible than raw variable-length strings. Encoding makes the compressor's job easy.
• Codec trade-offs. Snappy is fast to decompress, moderate ratio — good for hot, frequently-scanned data. Zstd gives a better ratio at a tunable CPU cost — good for colder data or when storage/network dominates. The choice is a decode-speed vs size trade.
• Ratios are per-column. A high-cardinality free-text column may only reach 2–3×; a low-cardinality code column may reach 50×+. The table's overall ratio is the weighted blend — which is why schema design (keep low-cardinality dimensions as codes) affects storage cost.

Vectorized execution — the CPU-cycle reduction.

• Row-at-a-time (Volcano) vs batch. A classic row engine calls next() per row and interprets each operator per value — heavy per-row overhead, unpredictable branches. A vectorized engine passes a batch (e.g. 1024 values of one column) through each operator, running a tight loop over a dense array.
• SIMD and branch prediction. A dense column batch of the same type lets the CPU use SIMD instructions (one instruction, many values) and keeps branches predictable, so the pipeline stays full. This is often a 10–100× per-row CPU reduction versus interpreting tuples.
• Operating on encoded data. Good engines evaluate predicates directly on dictionary codes or RLE runs without fully decoding — e.g. WHERE country = 'US' becomes "find the code for US, compare integers," and an RLE run of non-matching values is skipped in one step. Late decoding keeps the hot loop cheap.
• Late materialization. The engine carries lightweight column vectors and row-position bitmaps through filters and joins, only assembling full rows (materializing) at the very end for the surviving rows. Less data flows through each operator.

What interviewers listen for.

• Do you name specific encodings (RLE, dictionary, delta, bit-packing) and when each applies? — senior signal.
• Do you separate encoding (type-aware) from the byte compressor (Snappy/Zstd) as two stacked layers? — senior signal.
• Do you explain vectorized/batch execution and why a dense column enables SIMD? — required answer.
• Do you mention operating on encoded data / late materialization as the reason the hot loop stays cheap? — senior signal.

Worked example — dictionary + RLE on a low-cardinality column

Detailed explanation. Show the actual byte reduction from encoding a realistic low-cardinality column. Take a 1M-row status column with 4 distinct values in long runs and encode it dictionary-then-RLE, computing the size at each stage. This is the demo that turns "columnar compresses better" into concrete numbers.

• The column. status — 1,000,000 rows, values in {new, paid, shipped, cancelled}, mostly in long runs (data sorted by status).
• Raw. average 7 bytes/value as text = ~7 MB.
• Dictionary. 4-entry dict + 1M 1-byte codes.
• RLE on codes. long runs collapse to a few (code, length) pairs.

Question. Compute the encoded size at each stage and the total ratio versus raw text.

Input.

Stage	Representation	Size
Raw text	1M × ~7 bytes	~7,000,000 B
Dictionary codes	1M × 1 byte + tiny dict	~1,000,020 B
RLE over codes	~200 runs × 5 bytes	~1,000 B

Code.

ROWS       = 1_000_000
RAW_BYTES  = 7          # avg bytes per text value
DISTINCT   = 4          # new, paid, shipped, cancelled
RUNS       = 200        # long runs because data is sorted by status

raw       = ROWS * RAW_BYTES                       # text
dict_dict = DISTINCT * (RAW_BYTES + 1)             # dictionary entries
dict_codes= ROWS * 1                               # 1-byte code per row
dict_total= dict_dict + dict_codes                 # dictionary-encoded
rle_total = dict_dict + RUNS * (1 + 4)             # (code:1B, length:4B) per run

print(f"raw text:        {raw:>10,} B")
print(f"dictionary:      {dict_total:>10,} B  ({raw/dict_total:5.1f}x)")
print(f"dictionary+RLE:  {rle_total:>10,} B  ({raw/rle_total:7.0f}x)")
# raw text:         7,000,000 B
# dictionary:       1,000,032 B  (  7.0x)
# dictionary+RLE:       1,032 B  (  6783x)

Step-by-step explanation.

1. Raw, the column is 1M variable-length strings averaging 7 bytes — about 7 MB, and a byte compressor alone would get maybe 3–4× on the repeated strings.
2. Dictionary encoding replaces each string with a 1-byte code (4 distinct values fit in 2 bits, rounded to a byte) plus a tiny 4-entry dictionary. That is already 7 MB → 1 MB, a clean 7× from encoding alone — and crucially the codes are integers, which encode further.
3. Because the data is sorted by status, the code stream is long runs of identical bytes. RLE collapses each run to a (code, length) pair; ~200 runs × 5 bytes ≈ 1 KB plus the dictionary. The column is now ~1 KB — a 6,000×+ reduction versus raw text.
4. The two encodings compose: dictionary turns strings into small integers, RLE turns runs of integers into pairs. This composition is why columnar low-cardinality columns are nearly free to store, and why keeping dimensions low-cardinality is a schema-design lever.
5. A row store cannot approach this: the status values are separated by whole tuples, so neither the dictionary adjacency nor the run adjacency exists in the byte stream. The compression is a consequence of columnar layout plus sorting, not of a better compressor.

Output.

Stage	Size	Ratio vs raw
Raw text	~7 MB	1×
Dictionary	~1 MB	7×
Dictionary + RLE	~1 KB	~6,800×

Rule of thumb. Low-cardinality columns are almost free under dictionary + RLE, especially when sorted. Model dimension columns as low-cardinality codes and sort on the biggest one — you buy enormous compression and better block skipping at once.

Worked example — vectorized filter vs row-at-a-time

Detailed explanation. Contrast how a row engine and a vectorized columnar engine evaluate the same filter, to show why the columnar CPU cost per row is far lower. Take WHERE amount > 100 over 1M rows and sketch the inner loop of each. The point is the per-row interpretation overhead, not the comparison itself.

• Row engine. Iterator calls next() per tuple, extracts amount from the tuple, interprets the > operator, branches per row.
• Vectorized engine. Loads a batch of 1024 amount values as a dense array, runs a tight SIMD comparison producing a selection bitmap, no per-row interpretation.

Question. Sketch both inner loops and explain the per-row overhead difference.

Input.

Engine	Unit of work	Per-value overhead
Row-at-a-time	one tuple	tuple decode + operator dispatch + branch
Vectorized	1024-value batch	one tight loop, SIMD, branch-light

Code.

# Row-at-a-time (Volcano) — per-row interpretation overhead
def row_filter(rows):
    out = []
    for tup in rows:                    # one call per row
        amount = tup["amount"]          # decode field from tuple
        if amount > 100:                # operator interpreted per row
            out.append(tup)             # branch per row
    return out

# Vectorized — operate on a dense column batch at once
import numpy as np
def vec_filter(amount_col: np.ndarray):
    # one array comparison over the whole batch -> selection mask
    mask = amount_col > 100             # SIMD over 1024 values, no per-row branch
    return mask                         # positions carried forward (late materialize)

# The vectorized path touches only the `amount` column (pruned) and
# produces a bitmap; full rows are assembled later for survivors only.

Step-by-step explanation.

1. The row engine pays a fixed overhead per row: a virtual next() call, decoding the amount field out of a packed tuple, dispatching the > operator through an interpreter, and a data-dependent branch. At 1M rows that overhead dominates the actual comparison work.
2. The vectorized engine loads 1024 amount values as a contiguous typed array and runs one comparison over the batch. Modern CPUs execute this with SIMD (comparing 8–16 values per instruction) and no per-row branch — the result is a compact selection mask.
3. Because the column is dense and homogeneous, the CPU's prefetcher and pipeline stay full — no pointer chasing into tuples, no type checks per value. This is where the 10–100× per-row CPU reduction comes from; it is a property of the layout, not just the code.
4. The mask (late materialization) is carried forward: subsequent operators work on positions, and only surviving rows are assembled into full tuples at the end. Less data flows through each stage, and the expensive "rebuild a row" step runs on the few rows that pass, not all of them.
5. Pruning and vectorization combine: the filter reads only the amount column chunk (width reduction) and processes it in batches (CPU reduction). A row engine reads whole tuples and interprets per row — losing on both axes at once.

Output.

Aspect	Row-at-a-time	Vectorized
Calls per 1M rows	~1M next()	~1000 batches
Per-value branch	yes	no (masked)
SIMD usable	no (scalar tuples)	yes (dense array)
Columns touched	whole tuple	pruned column

Rule of thumb. Vectorized execution is the CPU-side twin of column pruning: dense typed batches let the processor use SIMD and stay branch-light, cutting cycles per row by one to two orders of magnitude. It only works because the column is a contiguous homogeneous array.

Worked example — choosing a codec: Snappy vs Zstd

Detailed explanation. The encoding is type-driven, but the byte compressor on top is a deliberate choice with a decode-speed vs size trade-off. Compare Snappy and Zstd for a hot dashboard table versus a cold archive table and pick each. This is a common real decision that interviewers use to check whether you understand the two-layer stack.

• Hot table. scanned constantly by dashboards; decode speed matters most.
• Cold table. rarely read, stored long-term; size (storage + network) matters most.
• Both. already encoded per-column; the codec runs on the encoded stream.

Question. Pick a codec for each table and justify with the decode-speed vs ratio trade-off.

Input.

Table	Access	Priority	Codec
dashboard_facts	scanned every minute	decode speed	Snappy
audit_archive	read a few times/year	storage size	Zstd (high level)

Code.

# Illustrative codec trade-offs on an already-encoded column stream
CODECS = {
    "snappy":     {"ratio": 3.0, "decode_MBps": 2000},  # fast, modest ratio
    "zstd-3":     {"ratio": 4.2, "decode_MBps": 1200},  # balanced default
    "zstd-19":    {"ratio": 5.5, "decode_MBps":  900},  # small, slower to make
}

def pick_codec(reads_per_day, cold_storage_gb):
    if reads_per_day > 100:            # hot -> favour decode speed
        return "snappy"
    if cold_storage_gb > 1000:         # cold + large -> favour ratio
        return "zstd-19"
    return "zstd-3"                    # sensible default

print(pick_codec(reads_per_day=1440, cold_storage_gb=50))   # -> snappy
print(pick_codec(reads_per_day=2,    cold_storage_gb=8000)) # -> zstd-19

Step-by-step explanation.

1. Both codecs run after per-column encoding, on the already-compact code stream. The codec choice does not change pruning or vectorization — it trades decompression CPU against on-disk/on-wire size.
2. The dashboard table is scanned every minute, so decompression CPU is on the hot path. Snappy decompresses at ~2 GB/s per core with a modest ~3× ratio — the right pick when reads are frequent and storage is cheap relative to query latency.
3. The audit archive is read a couple of times a year but stored for compliance, so size dominates. Zstd at a high level reaches a better ratio (5×+) at slower decode — acceptable because the read cost is amortised over years of cheap storage.
4. Zstd level 3 is the balanced default many engines use out of the box: close to Snappy's decode speed with a better ratio. Reach for Snappy only when decode throughput is provably the bottleneck, and for high-level Zstd only when storage/network is.
5. The interview signal is understanding that codec is a second-layer decision on top of encoding, and that the right answer depends on the read/write ratio and storage economics — not a single universal "best codec."

Output.

Table	Codec	Ratio	Decode	Why
dashboard_facts	Snappy	~3×	~2 GB/s	hot; decode speed wins
audit_archive	Zstd-19	~5.5×	~0.9 GB/s	cold; size wins
general default	Zstd-3	~4×	~1.2 GB/s	balanced

Rule of thumb. Encoding is chosen by the column's type/distribution; the byte codec is chosen by the read/write economics. Snappy for hot, frequently-scanned data; high-level Zstd for cold, storage-dominated data; Zstd-3 as the balanced default.

Data engineering interview question on encoding and vectorization

A senior interviewer might ask: "A colleague says 'columnar is faster because it compresses better.' Sharpen that: explain the two-layer compression stack, why a dictionary-encoded low-cardinality column is nearly free, how the engine still scans it fast at query time, and what schema and codec choices you'd make for a 2 TB events table that's scanned hundreds of times a day."

Solution Using dictionary + RLE encoding, Snappy for hot data, and vectorized predicate pushdown on codes

# 1. Schema for compressibility: low-cardinality dimensions as codes,
#    sorted on the hottest filter column so runs form and stats narrow.
import pyarrow as pa
import pyarrow.parquet as pq

schema = pa.schema([
    ("event_date", pa.date32()),                 # partition key
    ("country",    pa.dictionary(pa.int16(), pa.string())),  # dict-encoded
    ("event_type", pa.dictionary(pa.int8(),  pa.string())),  # dict-encoded
    ("user_id",    pa.int64()),
    ("amount",     pa.float32()),
])

# 2. Write partitioned + sorted, Snappy codec (hot table), tuned row groups.
pq.write_to_dataset(
    table.sort_by([("event_type", "ascending"), ("user_id", "ascending")]),
    root_path   = "s3://lake/events",
    partition_cols = ["event_date"],
    compression = "snappy",              # hot: fast decode, scanned 100s/day
    row_group_size = 1_000_000,          # ~1M rows/group for good stats + batches
)

-- 3. Query time: predicate evaluated on dictionary codes, vectorized,
--    reading only the two referenced column chunks.
SELECT country, COUNT(*) AS n
FROM   read_parquet('s3://lake/events/event_date=2026-09-05/*.parquet')
WHERE  event_type = 'purchase'           -- compared as an int code, RLE-skipped
GROUP  BY country;                        -- country chunk only; SIMD aggregation

Step-by-step trace.

Layer	Choice	Effect
Schema	low-cardinality dims as dictionary	strings → small int codes
Sort	by event_type, then user_id	long runs → RLE; narrow min-max
Encoding	dict + RLE + bit-pack (automatic)	dimension columns near-free
Codec	Snappy	fast decode for a hot, scanned table
Row group	1M rows	good stats granularity + full batches
Query	predicate on codes, pruned	2 chunks read, vectorized, groups skipped

1. The schema declares country and event_type as dictionary types, so at write time they become a small dictionary plus a stream of integer codes. Low-cardinality dimensions collapse from variable-length strings to 1–2 byte codes before any codec runs.
2. Sorting by event_type then user_id creates long runs in the code streams (RLE collapses them) and narrows each row group's min-max ranges (so statistics can skip groups). One sort buys both compression and block skipping.
3. The two-layer stack runs automatically: type-aware encoding (dictionary + RLE + bit-packing) first, then Snappy on the encoded bytes. Snappy is chosen because the table is scanned hundreds of times a day — decode speed is on the hot path, and its modest ratio is fine given the encoding already did the heavy lifting.
4. Row groups of ~1M rows balance two needs: fine enough that min-max stats prune usefully, coarse enough that vectorized batches stay full and per-group metadata overhead stays small.
5. At query time the engine prunes to the country and event_type chunks, evaluates event_type = 'purchase' directly on the integer codes (skipping non-matching RLE runs in one step), and aggregates country with SIMD over dense batches. Pruning, encoding, and vectorization all fire on the same scan.

Output:

Metric	Result
Dimension column size	near-free (dict + RLE)
Bytes read per query	2 chunks of N, Snappy-compressed
Predicate evaluation	on int codes, RLE-skipped, vectorized
Decode throughput	~2 GB/s (Snappy)
Block skipping	active (sorted → narrow stats)

Why this works — concept by concept:

• Dictionary encoding — maps a low-cardinality column's distinct strings to small integer codes plus a tiny dictionary, collapsing variable-length text to fixed 1–2 byte codes and enabling integer-speed predicate evaluation.
• RLE + sorting — sorting creates long runs so run-length encoding collapses repeated codes to (code, length) pairs, and simultaneously narrows row-group min-max ranges so statistics skip non-matching groups. One sort, two wins.
• Two-layer stack (encoding then codec) — type-aware encoding exploits column semantics; Snappy/Zstd mop up residual byte redundancy. Snappy here because the table is hot and decode speed dominates.
• Vectorized execution on encoded data — the engine compares integer codes over dense batches with SIMD, skipping whole RLE runs and materializing full rows only for survivors. CPU per row drops one to two orders of magnitude.
• Cost — a schema that models dimensions as codes, a sort on ingest, and a codec choice. In return, dimension columns cost almost nothing to store, scans read O(referenced chunks) of compressed bytes, and the hot loop runs at SIMD speed. The write side pays the sort cost once per batch; the read side is paid back hundreds of times a day.

Optimization
Topic — optimization
Optimization problems on encoding and vectorized scans

Practice →

Data analysis Topic — data-analysis Data-analysis problems on columnar encoding

Practice →

---

4. Where row stores still win — OLTP, point lookups

One seek returns a whole tuple in a row store; a columnar store pays N gathers to rebuild the same row — so mutation and point access stay row-major

The mental model in one line: the same layout property that makes columnar great at scans makes it bad at whole-row access — a row store keeps every field of a record contiguous, so a point lookup or a single-row write touches one place, while a columnar store scatters a record's fields across N separate column regions, so reassembling one row needs N gathers and mutating one row must append to (or rewrite) every column — which is why OLTP, point lookups, and mutation-heavy workloads stay on row storage even in 2026. This is the mirror image of sections 2–3: the wins there are exactly the costs here.

Point lookups — the whole-row seek.

• Row store: one seek. SELECT * FROM orders WHERE id = 42 on a row store follows the primary-key index to one heap page and returns the entire tuple in a single read. All fields are adjacent, so there is nothing to gather.
• Columnar: N gathers. The same lookup in a columnar store must read one value from each column region at the matching row position, then stitch them into a tuple. For a wide table that is dozens of small random reads plus reassembly — the opposite of what columnar is optimised for.
• The reporting exception. Columnar can answer "give me all columns for this one row" but it is slow relative to a row store; if your workload is dominated by single-row full-tuple fetches, that is a row-store signal.

Single-row writes — the fan-out cost.

• Row store: append one tuple. An INSERT writes one contiguous record to one page; an UPDATE rewrites one tuple (or MVCC-appends one new version). The write touches one location.
• Columnar: touch every column. A single-row insert must append a value to every column region and update every per-column encoding/stats. Columnar formats are usually immutable and write-once (Parquet/ORC files are not updated in place), so single-row mutation means rewriting files or buffering into delta files.
• Batches, not rows. Columnar loves bulk appends (write a million rows at once, amortising the per-column overhead) but hates trickle single-row writes. If the workload is high-frequency small mutations, columnar is the wrong tool.
• Updates and deletes. Because columnar files are immutable, updates/deletes are handled by merge-on-read (delete vectors + new files) or copy-on-write (rewrite the affected files). Both are expensive per single-row change — fine for batch corrections, painful for OLTP.

OLTP vs OLAP — the storage-format angle.

• OLTP = mutation-heavy, whole-row, point access. Transactions read and write complete records by key, with strict latency and concurrency needs. Row storage's whole-tuple locality and in-place update model fit exactly. This is the storage-format reason OLTP is row-major — not a legacy accident.
• OLAP = scan-heavy, narrow, append-mostly. Analytics scans many rows, touches few columns, and rarely mutates a single existing row. Columnar's pruning + compression + vectorization fit exactly.
• The failure mode. Running analytics on the OLTP row store drags whole tuples off disk for narrow queries (slow); running OLTP on a columnar store pays N-gather lookups and file rewrites per mutation (slow and complex). Each layout fails at the other's job.

Other row-store strengths.

• Row-level locking and MVCC. Row stores are built for concurrent transactions — per-row locks, snapshot isolation, quick rollback. Columnar formats have no comparable fine-grained concurrent-mutation story.
• Covering indexes fake pruning. A row store can approximate column pruning with a covering/secondary index that stores just the hot columns — at the cost of a full extra copy per index and write amplification. It is pruning bought with storage and write cost, not layout.
• Small results, low latency. For queries that return a handful of whole rows with sub-millisecond latency, the row store's single-seek model beats the columnar reassembly path.

What interviewers listen for.

• Do you frame columnar's lookup/write cost as the mirror image of its scan win? — senior signal.
• Do you say columnar files are immutable, so single-row updates mean merge-on-read or copy-on-write? — senior signal.
• Do you tie OLTP → row, OLAP → columnar to the storage-format reason, not just "that's how it's done"? — required answer.
• Do you note row stores fake pruning with covering indexes at a storage/write cost? — senior signal.

Worked example — point-lookup cost, row vs columnar

Detailed explanation. Quantify the whole-row lookup cost under each layout to show why OLTP stays row-major. Take SELECT * FROM orders WHERE id = 42 on a 60-column table and count the reads each layout needs. The count of random reads is the story.

• The table. orders — 60 columns, indexed on id.
• Row store. PK index → one heap page → whole tuple.
• Columnar. locate row position, then gather that position from 60 column regions.

Question. Count the random reads and describe the reassembly for a single-row full-tuple fetch under each layout.

Input.

Layout	Index step	Value reads	Reassembly
Row store	1 PK index probe	1 (whole tuple)	none
Columnar	locate row position	60 (one per column)	stitch 60 values

Code.

N_COLUMNS = 60

# Row store: index probe -> one page -> full tuple returned as-is.
row_store_reads      = 1        # the heap page holding the whole tuple
row_store_reassembly = 0

# Columnar: find the row position, then fetch that position out of
# every column region and reassemble the tuple.
columnar_reads       = N_COLUMNS      # one gather per column
columnar_reassembly  = N_COLUMNS      # stitch 60 values into a row

print(f"row store:  {row_store_reads} read,  {row_store_reassembly} gathers")
print(f"columnar:   {columnar_reads} reads, {columnar_reassembly} gathers")
# row store:  1 read,  0 gathers
# columnar:   60 reads, 60 gathers

Step-by-step explanation.

1. On the row store, the primary-key index points at the single heap page (or a couple, for a large tuple) that holds the whole record. One seek returns all 60 fields because they are contiguous — there is nothing to assemble.
2. On the columnar store, the 60 fields of row 42 live in 60 different column regions. Even after locating the row position, the engine must read that position out of each region — up to 60 small, scattered reads — then stitch the values back into a tuple.
3. The columnar path is doing "random access to a scan-optimised structure," which is exactly what it is worst at. Column chunks are sized and encoded for sequential batch reads; pulling one value out of each is high per-value overhead.
4. This is why a workload dominated by single-row full-tuple fetches (an app fetching an order to render a page) belongs on a row store. The columnar copy is for aggregations, not for serving individual records.
5. The gap widens with table width: at 200 columns, the columnar lookup is ~200 gathers versus the row store's one seek. Wide tables make columnar point lookups worse, not better — the inverse of the scan advantage where width helps columnar.

Output.

Layout	Random reads	Reassembly cost	Fit
Row store	1	none	OLTP point lookup
Columnar	~60	stitch 60 values	poor for point lookup

Rule of thumb. A whole-row fetch is one seek on a row store and N gathers on a columnar store, where N is the column count. If your dominant access is "give me this one record," the row layout wins by construction — and wider tables make the columnar penalty worse.

Worked example — single-row update in an immutable columnar file

Detailed explanation. Show why a single-row UPDATE is cheap row-major and expensive columnar, by walking what each layout physically does. Take UPDATE orders SET status = 'shipped' WHERE id = 42 and contrast in-place row update with columnar's immutable-file reality.

• Row store. MVCC-update one tuple in place (new version, same page family).
• Columnar (immutable). cannot edit the file in place; must copy-on-write the affected file or record a merge-on-read delete + insert.

Question. Describe the physical work each layout does for one single-column update of one row.

Input.

Layout	Mechanism	Physical work
Row store	MVCC in-place	write 1 new tuple version
Columnar copy-on-write	rewrite file	rewrite the whole data file containing row 42
Columnar merge-on-read	delete vector + delta	mark row 42 deleted + append new row

Code.

# Row store: one tuple version written; index updated if needed.
def update_row_store():
    return {"tuples_written": 1, "files_rewritten": 0}

# Columnar copy-on-write: the file holding row 42 is rewritten in full.
def update_columnar_cow(rows_in_file=1_000_000):
    return {"tuples_written": rows_in_file, "files_rewritten": 1}

# Columnar merge-on-read: cheaper write, cost deferred to read time.
def update_columnar_mor():
    return {"delete_vector_marks": 1, "delta_rows_appended": 1,
            "read_time_merge": "reader reconciles base + deletes + deltas"}

print(update_row_store())      # {'tuples_written': 1, 'files_rewritten': 0}
print(update_columnar_cow())   # {'tuples_written': 1000000, 'files_rewritten': 1}
print(update_columnar_mor())   # cheap write, merge cost on every read

Step-by-step explanation.

1. The row store updates one tuple: MVCC writes a new version of row 42 (invalidating the old), touching one page family. The cost is O(1) per updated row — exactly what OLTP needs at high concurrency.
2. Copy-on-write columnar cannot edit an immutable Parquet/ORC file, so it rewrites the entire data file that contains row 42 — potentially a million rows rewritten to change one field. Correct, but absurdly expensive per single-row change.
3. Merge-on-read columnar (Delta/Iceberg/Hudi style) makes the write cheap — mark row 42 in a delete vector and append the new version to a delta file — but defers the cost to every read, which must reconcile base files, delete vectors, and deltas. Frequent small updates pile up deltas and slow all subsequent scans until compaction.
4. Either columnar strategy is fine for batch corrections (update a day's partition once) but wrong for trickle OLTP mutation (thousands of single-row updates per second). The immutability that makes columnar files cheap to scan and cache is what makes them expensive to poke.
5. This is the concrete storage-format reason transactional systems stay row-major: in-place, O(1)-per-row mutation with fine-grained locking has no cheap columnar equivalent. Columnar buys scan speed by giving up cheap mutation.

Output.

Strategy	Write cost (1 row)	Read cost	Fit
Row store MVCC	1 tuple version	unchanged	OLTP
Columnar CoW	rewrite whole file	fast scans	batch updates
Columnar MoR	1 mark + 1 append	merge on every read	occasional updates

Rule of thumb. Columnar files are immutable: a single-row update is either a full-file rewrite (copy-on-write) or a deferred merge cost (merge-on-read). Both are fine for batch corrections and wrong for high-frequency OLTP mutation — keep trickle writes on a row store.

Worked example — the hybrid-workload trap

Detailed explanation. Teams often try to serve one layout to both OLTP and OLAP and get the worst of both. Walk through a concrete "one table, two access patterns" scenario and show why splitting the layouts wins. This is the design judgment interviewers probe with "can't we just use one system?"

• The workload. An orders table serving (a) an app that fetches/updates single orders by id (OLTP) and (b) analysts running daily revenue-by-region scans (OLAP).
• Columnar-only. analytics fast, but the app's point lookups and single-row updates are slow.
• Row-only. app fast, but the analytics scans drag whole tuples off disk.

Question. Recommend a layout strategy and justify it against both access patterns.

Input.

Access pattern	Frequency	Best layout
Point fetch/update by id	thousands/sec	row
Revenue-by-region scan	dozens/day	columnar

Code.

def serve(workload):
    if workload == "point_rw":
        return "row store (system of record) — O(1) lookups + in-place updates"
    if workload == "wide_scan":
        return "columnar copy (analytics) — pruning + compression + vectorized"
    return "split: row store OLTP -> CDC/ELT -> columnar OLAP copy"

print(serve("point_rw"))   # row store
print(serve("wide_scan"))  # columnar copy
print(serve("both"))       # split the layouts

Step-by-step explanation.

1. A columnar-only design makes the analytics scans fast but cripples the app: every single-order fetch is an N-gather reassembly and every status update is a file rewrite or a deferred merge. The app's latency and write rate collapse.
2. A row-only design keeps the app fast but forces analytics to full-scan whole tuples for narrow queries — the exact anti-pattern from section 1, where a 3-column query reads all 60 columns of every row.
3. The resolution is to split by access pattern: the row store is the system of record for OLTP point access and mutation; a columnar copy, fed by CDC or ELT, serves the analytics scans. Each layout does the job it is built for.
4. The cost is one extra (compressed) copy of the data and a replication lag of minutes on the analytics side — which analytics tolerates. The alternative, forcing one layout to do both, degrades whichever workload doesn't match the layout.
5. HTAP engines (next section) automate this split inside one system, keeping a row representation for transactions and a columnar representation for scans — the same idea, packaged as a product instead of a pipeline.

Output.

Strategy	OLTP latency	OLAP scan	Extra cost
Columnar only	poor (N gathers, rewrites)	fast	—
Row only	fast	poor (full-tuple scans)	—
Split (row → columnar)	fast	fast	one compressed copy + ELT lag

Rule of thumb. Don't force one layout to serve both patterns — you get the worst of each. Keep OLTP on the row store, feed a columnar copy for analytics, and accept one extra copy plus minutes of lag. When you want it in one system, that's what HTAP is for.

Systems interview question on choosing a storage layout

A senior interviewer might ask: "A startup runs its whole product on one Postgres instance. It serves live single-order lookups and updates for the app, and the analytics team runs growing full-table aggregations that now take minutes and are starting to affect app latency. They ask whether they should 'switch to a columnar database.' Walk me through the storage-format trade-offs and what you'd actually recommend."

Solution Using a row-store system of record plus a columnar analytics replica

-- 1. Keep Postgres (row store) as the system of record for OLTP.
--    Point lookups and single-row updates stay O(1) and in-place:
SELECT * FROM orders WHERE id = 42;                 -- one seek, whole tuple
UPDATE orders SET status = 'shipped' WHERE id = 42; -- MVCC in-place, cheap

-- 2. Stop running heavy scans on the OLTP primary — they read whole
--    tuples and compete with the app for buffer cache and I/O.

# 3. Replicate to a columnar analytics store (CDC or scheduled ELT).
#    The columnar copy is append-mostly and scan-shaped.
#    Analytics runs there, pruned + compressed + vectorized:
import duckdb
duckdb.sql("""
    SELECT region, SUM(amount) AS revenue
    FROM   read_parquet('s3://lake/orders/event_date=2026-09-05/*.parquet')
    GROUP  BY region
""")   # reads region + amount chunks only; never touches the OLTP primary

Step-by-step trace.

Concern	Row store (Postgres)	Columnar replica
Single-order lookup	1 seek, whole tuple	(not used for this)
Single-order update	MVCC in-place, O(1)	(not used for this)
Revenue-by-region scan	full-tuple scan (slow)	pruned + compressed (fast)
App latency impact	protected (no heavy scans)	isolated compute
Freshness	live	minutes (CDC/ELT lag)

1. Postgres stays the system of record because the app's dominant pattern is single-row fetch and update — the row store's one-seek lookup and in-place MVCC update are exactly right, and moving OLTP to columnar would turn every lookup into an N-gather and every update into a file rewrite.
2. The mistake causing the pain is running growing full-table aggregations on the OLTP primary: those scans read whole tuples for narrow queries and evict the app's hot pages from cache, coupling analytics load to app latency.
3. The fix is a columnar analytics replica fed by CDC or scheduled ELT. Analytics moves off the primary entirely, so heavy scans no longer compete with the app for I/O and cache. Compute is isolated.
4. On the columnar side the revenue query prunes to region and amount, reads compressed chunks, and vectorizes the aggregation — the minutes-long scan becomes sub-second, and it runs on separate infrastructure.
5. "Switch to a columnar database" was the wrong framing: the answer is not to replace the row store but to add a columnar copy so each layout serves the workload it fits. The recommendation is a split, not a migration.

Output:

Metric	Before (single Postgres)	After (split layouts)
Order lookup	fast	fast (unchanged)
Order update	fast	fast (unchanged)
Revenue scan	minutes, on primary	sub-second, on replica
App latency during analytics	degraded	protected
Extra cost	none	one compressed copy + ELT lag

Why this works — concept by concept:

• Whole-tuple locality for OLTP — the row store keeps every field of an order contiguous, so a lookup is one seek and an update is one in-place tuple version. Point access and mutation are what row-major is built for.
• Compute isolation — moving scans off the OLTP primary stops analytics from evicting the app's hot pages and competing for I/O. The app's latency is decoupled from analytics load.
• Columnar for scans — the replica prunes to referenced columns, reads compressed chunks, and vectorizes — turning a minutes-long full-tuple scan into a sub-second columnar scan.
• Directional, append-mostly replication — CDC/ELT moves data one way, so the columnar copy is append-mostly and never pays the single-row-write penalty that would cripple it.
• Cost — one extra compressed copy (often < 20% of the raw table after compression) and a few minutes of lag. In exchange, OLTP stays O(1) per row and OLAP drops to O(referenced columns) per scan, and the two workloads stop fighting over one machine. Replacing the row store would have made OLTP worse; adding a columnar copy makes both better.

Database
Topic — database
Database problems on OLTP row storage and point access

Practice →

Optimization Topic — optimization Optimization problems on read/write trade-offs

Practice →

---

5. Formats & hybrids — Parquet / ORC / PAX / HTAP

Real columnar files are PAX — row groups on the outside, columns on the inside — so they get partition pruning, column pruning, and local row reassembly at once

The mental model in one line: production columnar formats (Parquet, ORC) are not purely column-major on disk — they use the PAX layout: the file is split into horizontal row groups (Parquet) or stripes (ORC), and within each group the data is stored column-by-column as column chunks and pages, each with min/max statistics — so a query prunes at three granularities (partition directory → row group → column chunk), reassembles a row from column chunks that are physically near each other, and skips blocks by statistics; and when a single engine must serve both OLTP and OLAP, HTAP systems keep a row representation and a columnar representation of the same data side by side. PAX is the pragmatic middle: mostly the columnar wins, with enough row-group locality to make reassembly and parallelism sane.

Parquet anatomy.

• File → row groups → column chunks → pages. A Parquet file is a sequence of row groups (typically 128 MB or ~1M rows). Each row group holds one column chunk per column; each chunk is split into pages (the unit of encoding/compression, ~1 MB). The footer stores the schema and per-row-group, per-column statistics.
• Statistics in the footer. Min, max, null count, and (optionally) bloom filters per column chunk let a reader skip whole row groups before reading them. Predicate pushdown reads the footer first, eliminates row groups, then reads only surviving chunks.
• Encoding per page. Each page is dictionary/RLE/delta/bit-pack encoded then compressed (Snappy/Zstd). The reader can often evaluate predicates on encoded pages without full decode.
• Row-group size is a tuning knob. Bigger row groups → better compression and fewer metadata reads but coarser statistics (less skipping) and more memory per scan; smaller → finer skipping but more overhead. ~128 MB / ~1M rows is the common balance.

ORC anatomy.

• File → stripes → streams. ORC splits into stripes (~64–256 MB); within a stripe each column is stored as data + length + present streams. A file footer plus per-stripe indexes carry statistics.
• Row indexes. ORC keeps fine-grained row-group indexes inside a stripe (every 10k rows by default), so it can skip sub-stripe ranges — often finer-grained skipping than default Parquet.
• Built-in lightweight indexes. Min/max per column per stripe and per index-stride, plus optional bloom filters, drive predicate pushdown. Conceptually the same PAX idea as Parquet with different granularity defaults.
• Ecosystem. ORC is the historical Hive-native format; Parquet is the broader cross-engine default (Spark, Arrow, DuckDB, Trino). Both are PAX columnar; the choice is usually ecosystem, not fundamentals.

PAX — why row groups exist at all.

• The problem with pure column-major. If a column's values for the whole file are one giant contiguous run, reassembling any row means reaching across the entire file, and parallelism/splitting is awkward. Pure column-major is impractical at file scale.
• The PAX fix. Partition Attributes Across (PAX): break rows into horizontal groups, then store columnar within each group. A row's fields all live in the same row group, so reassembly is local; groups are independently splittable for parallel scans; and each group carries its own statistics for skipping.
• Three pruning granularities. Partition directory (e.g. event_date=…) → row group / stripe (min-max stats) → column chunk (projection). A selective, narrow query prunes on all three and reads a sliver of the file.
• The unifying insight. Almost every modern columnar format is PAX. "Row vs columnar" at the file level is really "row-major vs PAX-columnar," and PAX is engineered to keep the columnar scan wins while making reassembly and parallelism practical.

HTAP — both representations, one system.

• The goal. Serve OLTP and OLAP from one system without a separate ELT pipeline, by maintaining both a row representation (for transactions) and a columnar representation (for analytics) of the same data.
• How. A row store handles writes and point access; a columnar replica (in-memory column store, columnar index, or delta-synced columnar copy) is kept up to date for scans. Examples in spirit: SQL Server columnstore indexes, Oracle In-Memory, SingleStore, TiDB/TiFlash.
• The trade. HTAP removes the ELT lag and the two-system operational burden, but pays with more storage (two representations) and engineering complexity keeping them consistent. It is the "split the layouts" idea from section 4, internalised.
• When to reach for it. When freshness requirements make a minutes-lagged ELT copy unacceptable and the team wants one system. Otherwise a row store + columnar copy pipeline is simpler and cheaper.

What interviewers listen for.

• Do you describe Parquet as row groups → column chunks → pages with footer statistics, not "just columnar"? — senior signal.
• Do you name PAX and explain why row groups exist (local reassembly + splittable + per-group stats)? — senior signal.
• Do you know row-group size is a tuning knob trading compression/metadata vs skipping granularity? — senior signal.
• Do you frame HTAP as keeping both representations to avoid the ELT split, at a storage/complexity cost? — required answer.

Worked example — Parquet row-group anatomy and footer pruning

Detailed explanation. Make the PAX structure concrete by reading a Parquet file's metadata and showing how footer statistics drive row-group skipping. Take a partitioned Parquet dataset and a selective filter, and trace which row groups survive. This is the demo that turns "Parquet is columnar" into "Parquet is PAX with footer stats."

• The file. one partition's Parquet, 1000 row groups of ~1M rows, sorted by ts.
• The footer. per-row-group min/max for each column.
• The filter. WHERE ts BETWEEN '09:00' AND '10:00'.

Question. Show how the reader uses footer statistics to read only the matching row groups' referenced column chunks.

Input.

Structure	Contents
File footer	schema + per-row-group stats (min/max/nulls)
Row group i	column chunks for all columns, each paged
Filter	ts in [09:00, 10:00]

Code.

import pyarrow.parquet as pq

pf = pq.ParquetFile("s3://lake/events/event_date=2026-09-05/part-000.parquet")

# 1. Read the footer metadata only (cheap) — no column data yet.
meta = pf.metadata
print(meta.num_row_groups, "row groups")

# 2. Use per-row-group min/max on `ts` to select survivors.
ts_col = meta.schema.names.index("ts")
lo, hi = "2026-09-05 09:00", "2026-09-05 10:00"

survivors = []
for rg in range(meta.num_row_groups):
    stats = meta.row_group(rg).column(ts_col).statistics
    if stats and not (str(stats.max) < lo or str(stats.min) > hi):
        survivors.append(rg)      # ranges overlap -> must read this group

print(f"survivors: {len(survivors)} of {meta.num_row_groups} row groups")

# 3. Read ONLY survivor row groups, ONLY the referenced columns.
table = pf.read_row_groups(survivors, columns=["country", "ts"])

Step-by-step explanation.

1. The reader first fetches only the file footer — schema plus per-row-group statistics — which is tiny relative to the data. No column data is read yet. This is the cheap metadata pass PAX enables.
2. For each row group it checks the stored [min_ts, max_ts] against the filter [09:00, 10:00]. If the ranges cannot overlap, the group is skipped entirely; because the file is sorted by ts, only a handful of groups overlap a one-hour window.
3. Only the surviving row groups are read, and within them only the country and ts column chunks (projection pushdown) — the other columns' chunks are skipped by file offset. Partition already narrowed to one day; row-group stats narrow to one hour; projection narrows to two columns.
4. This is all three PAX pruning granularities firing on one query: partition directory (the event_date=… path), row group (footer min/max), and column chunk (projection). The read is a sliver of the file.
5. If the file were not sorted by ts, every row group's min/max would span the whole day and no group could be skipped — the same clustering lesson as section 2, now visible in the row-group metadata. PAX gives you the mechanism; clustering gives you the benefit.

Output.

Pruning stage	Granularity	Result
Partition path	directory	1 of 365 days
Footer min/max on ts	row group	~few of 1000 groups
Projection	column chunk	2 of N columns
Net	—	sliver of the file read

Rule of thumb. Parquet is PAX: read the footer first, skip row groups by min/max, then read only referenced column chunks of survivors. Sort/cluster on your filter column so the footer stats are narrow — otherwise the skipping mechanism has nothing to skip.

Worked example — tuning row-group size

Detailed explanation. Row-group size is the main Parquet knob and it trades competing effects. Walk through what grows and shrinks as you change it, and pick a size for two workloads. This is the tuning question that separates people who've operated columnar storage from those who've only read about it.

• Large row groups. better compression, fewer footer/metadata reads, more memory per scan, coarser skipping.
• Small row groups. finer skipping, less memory, but more metadata overhead and weaker compression.
• The workloads. highly selective point-ish analytics vs full-partition aggregations.

Question. Choose a row-group size for a selective-filter workload and for a full-scan aggregation workload, and justify.

Input.

Knob direction	Compression	Skipping granularity	Metadata overhead	Memory/scan
Larger groups	better	coarser	lower	higher
Smaller groups	weaker	finer	higher	lower

Code.

def pick_row_group_rows(selectivity, avg_row_bytes=200, mem_budget_mb=512):
    """Rough row-group sizing by workload selectivity."""
    if selectivity < 0.01:            # very selective -> want fine skipping
        rows = 256_000                # smaller groups, narrower min-max
    else:                             # scan-heavy -> want compression + fewer reads
        rows = 1_000_000              # larger groups
    est_mb = rows * avg_row_bytes / 1e6
    return {"rows_per_group": rows, "approx_group_MB": round(est_mb)}

print(pick_row_group_rows(selectivity=0.002))  # selective analytics
print(pick_row_group_rows(selectivity=0.8))    # full-partition aggregation
# {'rows_per_group': 256000, 'approx_group_MB': 51}
# {'rows_per_group': 1000000, 'approx_group_MB': 200}

Step-by-step explanation.

1. A highly selective workload (touches < 1% of rows) benefits from smaller row groups: narrower per-group min/max ranges mean the reader skips more groups, so less data is decompressed. The cost — more metadata and weaker compression — is worth it when skipping is the dominant win.
2. A full-partition aggregation reads most rows anyway, so skipping doesn't help; larger row groups win by compressing better and cutting the number of footer/metadata round-trips (important on object storage where each read has latency).
3. There is a memory trade: a scan buffers roughly one row group per column per thread, so very large groups raise peak memory and can hurt parallelism. The ~128 MB / ~1M-row default balances these for typical mixed workloads.
4. The knob interacts with clustering: fine row groups only skip well if the data is sorted so each small group covers a narrow value range. Small groups on unsorted data just add overhead without improving skipping.
5. The interview signal is naming the competing effects — compression and metadata cost pull toward larger groups, skipping granularity and memory pull toward smaller — and choosing by which effect the workload is dominated by, rather than quoting a single "correct" size.

Output.

Workload	Row-group size	Why
Selective analytics	~256K rows (~50 MB)	finer skipping dominates
Full-partition aggregation	~1M rows (~200 MB)	compression + fewer reads
Mixed default	~1M rows / 128 MB	balances all effects

Rule of thumb. Row-group size trades compression and metadata cost (favouring larger) against skipping granularity and memory (favouring smaller). Size down for very selective workloads on well-sorted data; size up for scan-heavy aggregations; keep the ~128 MB default when unsure.

Worked example — PAX reassembly and the HTAP alternative

Detailed explanation. Show why PAX makes row reassembly practical, and contrast the pipeline-split from section 4 with an HTAP engine that keeps both representations internally. Take a query that needs a few whole rows out of a scan and trace reassembly, then frame HTAP. This is the synthesis example tying the article together.

• PAX reassembly. a row's fields all live in one row group, so stitching a surviving row reads nearby chunks, not the whole file.
• Pipeline split. row store → ELT → columnar copy (minutes lag, two systems).
• HTAP. one system keeps row + columnar representations synced (no ELT lag, more storage/complexity).

Question. Trace how PAX reassembles surviving rows locally, then state when HTAP beats the pipeline split.

Input.

Approach	Freshness	Systems	Storage
Pipeline split	minutes lag	2 (row + columnar)	2 copies
HTAP	near-live	1	2 representations in one

Code.

# PAX reassembly: after row-group + projection pruning, the survivors'
# fields are all within the SAME row group -> local stitch, not a
# whole-file scatter/gather.
def reassemble(row_group, surviving_positions, columns):
    rows = []
    for pos in surviving_positions:
        # each column chunk lives in this same row group (local reads)
        rows.append({c: row_group[c][pos] for c in columns})
    return rows

# HTAP framing: one engine, two representations kept in sync.
def choose(freshness_needed_seconds, ops_budget):
    if freshness_needed_seconds < 5 and ops_budget == "single-system":
        return "HTAP (row + columnar in one engine)"
    return "pipeline split (row store -> ELT -> columnar copy)"

print(choose(2, "single-system"))   # HTAP
print(choose(300, "two-systems"))   # pipeline split

Step-by-step explanation.

1. After pruning to surviving row groups and referenced columns, reassembling a row is local: the row's fields all live in the same row group's column chunks, so the engine gathers them from physically nearby data rather than scattering across the whole file. This is exactly why PAX exists — pure column-major would make reassembly a file-wide scatter/gather.
2. Row groups are also independently splittable, so a scan parallelises across groups and each worker reassembles its own survivors locally. PAX gives both scan parallelism and sane reassembly — the practical engineering that makes columnar files usable.
3. The section-4 pipeline split (row store → ELT → columnar copy) works but carries minutes of lag and two systems to operate. When the business can tolerate that, it is the simplest, cheapest design.
4. HTAP collapses the split into one engine that maintains a row representation for transactions and a columnar representation for scans, kept in sync internally. It removes the ELT lag and the two-system burden, at the cost of storing both representations and the complexity of keeping them consistent.
5. Reach for HTAP when freshness needs are tight (analytics must see near-live transactional data) and the team wants a single system; otherwise the row-store-plus-columnar-copy pipeline is easier to reason about and operate. Both are the same underlying idea — serve each access pattern with the layout that fits — packaged differently.

Output.

Approach	Best when	Cost
PAX file + pipeline split	minutes-fresh analytics is fine	ELT lag, 2 systems
HTAP	near-live analytics, one system	2 representations, complexity

Rule of thumb. PAX (row groups outside, columns inside) is what makes real columnar files skippable, splittable, and reassemblable. HTAP internalises the section-4 split when freshness demands near-live analytics in one system — otherwise a row store plus a columnar copy is the simpler shape.

Data engineering interview question on columnar file formats

A senior interviewer might ask: "You're standardising the company lakehouse on Parquet. Explain the file layout you'd rely on for fast queries, how you'd write the files so predicate and projection pushdown actually work, how you'd size row groups, and when you'd instead recommend an HTAP engine over the lake-plus-warehouse split."

Solution Using PAX-aware Parquet writes (partition + sort + row-group sizing) with pushdown, and an HTAP decision rule

# 1. Write Parquet as PAX-optimised: partition on the coarse filter,
#    sort within partition on the hot filter column, size row groups.
import pyarrow.parquet as pq

pq.write_to_dataset(
    table.sort_by([("ts", "ascending")]),   # narrow row-group min/max on ts
    root_path      = "s3://lake/events",
    partition_cols = ["event_date"],         # partition pruning granularity
    row_group_size = 1_000_000,              # ~128 MB groups: balance
    compression    = "zstd",                 # good ratio for lake storage
    write_statistics = True,                  # footer min/max drive skipping
)

-- 2. Query written to exploit all three pruning granularities:
SELECT country, COUNT(*) AS n
FROM   read_parquet('s3://lake/events/event_date=2026-09-05/*.parquet')
WHERE  ts >= TIMESTAMP '2026-09-05 09:00'   -- row-group min/max skip
  AND  event_type = 'purchase'              -- more skipping + code predicate
GROUP  BY country;                           -- projection: read country/ts/event_type

# 3. HTAP decision rule: split pipeline unless freshness demands near-live.
def storage_strategy(analytics_freshness_seconds):
    if analytics_freshness_seconds < 5:
        return "HTAP engine (row + columnar in one; no ELT lag)"
    return "lakehouse split (row store OLTP -> Parquet lake -> query engine)"

print(storage_strategy(300))  # -> lakehouse split (minutes-fresh is fine)
print(storage_strategy(2))    # -> HTAP engine (near-live required)

Step-by-step trace.

Layer	Choice	Effect
Partition	event_date	directory-level pruning (1/365)
Sort	by ts	narrow row-group min/max → skipping
Row group	~1M rows / ~128 MB	balance compression, stats, memory
Statistics	write_statistics=true	footer min/max enable pushdown
Codec	Zstd	strong ratio for lake storage
Query	filters + named columns	partition + row-group + column pruning
HTAP rule	freshness threshold	split unless near-live needed

1. Writing partitioned by event_date gives the coarse pruning granularity — a query for one day lists one directory instead of scanning 365. This is the cheapest, most decisive block elimination and it is a write-time decision.
2. Sorting within each partition by ts narrows every row group's [min_ts, max_ts], so sub-day filters skip most groups via footer statistics. Without the sort, PAX still stores stats but they span the whole partition and skip nothing — clustering is what activates the mechanism.
3. Row groups of ~1M rows (~128 MB) balance the competing effects: big enough for good Zstd ratios and few metadata round-trips on object storage, small enough for useful min/max granularity and bounded scan memory.
4. write_statistics=true ensures the footer carries per-row-group min/max (and optionally bloom filters), which is what predicate pushdown reads first to eliminate row groups before any column data is fetched.
5. The query then fires all three pruning granularities — partition path, row-group stats, and projection to country/ts/event_type — reading a sliver of the lake. Finally, the HTAP rule decides architecture: if minutes-fresh analytics is acceptable, the lakehouse split is simpler; if analytics must see near-live data in one system, an HTAP engine that keeps both representations is worth its extra storage and complexity.

Output:

Metric	Result
Partition pruning	1 of 365 directories
Row-group skipping	few of ~1000 groups (sorted)
Projection	3 of N columns
Codec ratio	strong (Zstd)
Architecture	split (minutes-fresh) or HTAP (near-live)

Why this works — concept by concept:

• PAX layout — row groups on the outside, column chunks inside, statistics per group. This is what makes a Parquet file simultaneously prunable (by stats), splittable (by row group), and reassemblable (fields local to a group).
• Partition + sort clustering — partitioning maps the coarse filter to directories; sorting narrows row-group min/max so footer statistics can skip groups. Pruning only works as well as the clustering exposes.
• Row-group sizing — the central knob trading compression and metadata cost (larger) against skipping granularity and memory (smaller); ~128 MB balances them for mixed workloads.
• Predicate + projection pushdown — filters hit partition paths and footer stats before decompression, and only referenced column chunks of surviving groups are read. Three granularities of elimination compound.
• HTAP vs split — both serve each access pattern with its fitting layout; HTAP keeps both representations in one engine for near-live freshness at a storage/complexity cost, while the lakehouse split accepts ELT lag for operational simplicity. Cost — write-time partitioning/sorting/sizing plus (for HTAP) a second representation; the payoff is queries that read O(surviving groups × referenced columns) instead of O(whole file), a two-to-three-order-of-magnitude I/O reduction on selective, narrow queries.

Data analysis
Topic — data-analysis
Data-analysis problems on Parquet and file formats

Practice →

Database
Topic — database
Database problems on PAX layout and HTAP

Practice →

---

Cheat sheet — row vs columnar storage recipes

• The one-line model. Row-major stores whole tuples contiguously (great for point access + mutation); column-major stores whole columns contiguously (great for scans). Same data, different byte order — and byte order is destiny for the query engine. Analytics is columnar, transactions are row-major, most stacks run both.
• Estimate the columnar scan win. speedup ≈ (total_columns / columns_touched) × (columnar_compression / row_compression). A 3-column query on a 100-column table commonly lands at 50–200× less I/O. Pruning cuts width, compression cuts depth, statistics cut height — three orthogonal multipliers.
• Column pruning (projection pushdown). Read only referenced column chunks. Bytes scale with columns_touched / total_columns. Never SELECT * on columnar storage or in a view — it references every column and throws away the layout's biggest advantage.
• Block skipping needs clustering. Min/max (zone-map) statistics skip whole row groups only if the data is sorted/partitioned on the filter column. Random ingest order → every group spans the domain → nothing skips. Partition on the coarse filter, sort within partition on the hot filter column.
• Encoding is a two-layer stack. Type-aware encoding first — RLE (long runs), dictionary (low-cardinality strings → codes), delta (sorted/timestamps), frame-of-reference + bit-packing (small integer ranges) — then a byte codec (Snappy/Zstd) on top. Encoding exploits column semantics; the codec mops up residual redundancy.
• Codec choice = read/write economics. Snappy (fast decode, ~3×) for hot, frequently-scanned data; high-level Zstd (~5×+, slower) for cold, storage-dominated data; Zstd-3 as the balanced default. The codec is orthogonal to pruning and vectorization.
• Vectorized execution. Dense homogeneous column batches let the CPU use SIMD and stay branch-light, cutting cycles per row 10–100× versus row-at-a-time interpretation. Good engines evaluate predicates on encoded codes and late-materialize full rows only for survivors.
• Where row stores win. Point lookup = 1 seek (row) vs N gathers (columnar). Single-row write = 1 in-place tuple (row) vs whole-file rewrite / merge-on-read (columnar, since files are immutable). OLTP + trickle mutation + whole-row access → row store, full stop.
• Columnar files are immutable. Updates/deletes are copy-on-write (rewrite affected files) or merge-on-read (delete vectors + delta files reconciled at read time, compacted later). Fine for batch corrections; wrong for high-frequency single-row mutation.
• Parquet = PAX. File → row groups (~128 MB / ~1M rows) → column chunks → pages, with footer min/max stats. ORC = stripes → column streams + fine row indexes. Both prune at three granularities: partition directory → row group/stripe → column chunk. Row-group size trades compression/metadata (larger) vs skipping/memory (smaller).
• HTAP vs pipeline split. Split = row store → CDC/ELT → columnar copy (simple, minutes lag, two systems). HTAP = one engine keeping row + columnar representations synced (near-live, more storage + complexity). Choose HTAP only when freshness makes ELT lag unacceptable.
• Migration shape. Don't "switch to a columnar database" for a mixed workload — add a columnar analytics copy beside the row-store system of record. Keep OLTP row-major, feed columnar for scans, name your columns, sort on your filter column, and let pruning + compression + vectorization compound.

Frequently asked questions

What is the difference between row and columnar storage in one sentence?

Row storage (row-major) writes every field of a record contiguously, so one record's columns sit adjacent on disk; columnar storage (column-major) writes every value of a column contiguously, so one column's values across all records sit adjacent. The logical table is identical — only the byte order on disk differs. That byte order decides how much of the file a query must read (columnar reads only the columns referenced), how well the data compresses (same-domain values sit adjacent in columnar), and how expensive it is to fetch or mutate a single whole row (cheap in row-major, expensive in columnar). Every downstream property — column pruning, encoding, vectorization, file-format design — follows from that one serialization choice.

Why does columnar storage compress better than row storage?

Because a column holds one type and one domain, so adjacent values are highly redundant and encode tightly. A country column is a few hundred strings repeated billions of times (dictionary-encodes to 1-byte codes), a sorted status column is long runs (run-length-encodes to near nothing), a ts column is monotonic (delta-encodes to tiny deltas), and small-range integers bit-pack to a fraction of their nominal width. A general compressor (Snappy/Zstd) then squeezes the already-compact encoded stream further — a two-layer stack. Row storage interleaves an int, a string, a decimal, and a timestamp within each tuple, so the byte stream flips type every few bytes and a compressor finds little redundancy, typically reaching only 2–3× versus columnar's 5–20×+ on low-cardinality columns.

What is column pruning (projection pushdown)?

Column pruning is the engine reading only the column chunks a query references and skipping the rest by file offset. SELECT country, SUM(price) reads the country and price chunks; the other columns are never fetched. Bytes read scale with columns_touched / total_columns, so a 3-column query over a 100-column table reads ~3% of the width before compression even helps — usually the single biggest factor in the columnar scan win. It is only cheap on columnar storage because columns are contiguous; a row store would have to seek past the unwanted columns on every single row. The critical anti-pattern is SELECT * (or a view defined as SELECT *), which references every column and defeats pruning entirely.

When should I still use a row store?

Whenever the dominant access pattern is whole-row and mutation-heavy: OLTP transactions, point lookups by primary key, and high-frequency single-row inserts/updates. A row store returns an entire record in one seek (all fields are contiguous) and updates a tuple in place with fine-grained locking and MVCC — exactly what an application fetching and editing individual records needs. A columnar store would turn each whole-row lookup into N gathers (one per column) and each single-row update into a whole-file rewrite or a deferred merge-on-read cost, because columnar files are immutable and optimised for sequential batch scans. Keep OLTP row-major; add a columnar copy for the analytics scans rather than forcing one layout to do both jobs.

Is Parquet row storage or columnar? What is a row group?

Parquet is columnar, but not purely — it uses the PAX layout. The file is split into horizontal row groups (typically ~128 MB or ~1M rows), and within each row group the data is stored column-by-column as column chunks divided into pages, with per-row-group min/max statistics in the file footer. A row group is that horizontal slice: it keeps a record's fields local (so reassembly reaches nearby data, not the whole file), makes the file splittable for parallel scans, and carries its own statistics so a reader can skip entire groups whose min/max can't match a predicate. That PAX design is why Parquet supports three pruning granularities at once — partition directory, row group, and column chunk — and ORC does the same with stripes and finer row indexes.

OLTP vs OLAP — which storage format for which, and why?

OLTP (online transaction processing) is mutation-heavy, whole-row, point access under strict latency and concurrency — so it uses row storage, whose contiguous tuples give one-seek lookups and cheap in-place updates. OLAP (online analytical processing) is scan-heavy, narrow (few columns of many), and append-mostly — so it uses columnar storage, whose contiguous columns give column pruning, strong per-column compression, and vectorized execution. Running analytics on the OLTP row store drags whole tuples off disk for narrow queries; running transactions on a columnar store pays N-gather lookups and file rewrites per mutation — each layout fails at the other's job. The standard resolution is to keep OLTP on a row store and feed a columnar copy (via CDC/ELT) for OLAP, or use an HTAP engine that maintains both representations when near-live analytics is required.

Practice on PipeCode

• Drill the database practice library → for the storage-layout, OLTP-vs-OLAP, indexing, and point-access problems that test whether you understand row-major internals.
• Sharpen the scan-cost intuition on the query optimization practice library → for column pruning, predicate pushdown, min-max skipping, and read/write trade-off problems.
• Rehearse the pipeline mechanics on the data-analysis practice library → for Parquet/ORC formats, encoding, partitioning, and columnar ELT patterns.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the row-vs-columnar decision matrix against real graded inputs.

Lock in row-vs-columnar muscle memory

Docs explain the two layouts. PipeCode drills explain the decision — when column pruning turns a full scan into a sliver, when encoding makes a dimension column nearly free, when vectorization wins the CPU, and when a row store's one-seek lookup still beats columnar. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the storage-format trade-offs data engineers actually face.

Practice database problems →
Practice optimization problems →