Why it matters
A query that sums one measure across a billion rows touches a tiny fraction of the table's columns. In row storage the engine still reads whole records from disk, so most of the I/O is wasted. Columnar layout keeps each column's values adjacent, which lets the reader skip irrelevant columns, compress runs of similar values and evaluate predicates against per-block statistics before decompressing anything.
Mental model
A row store is a stack of completed forms; a column store is a set of trays, one per question, each holding that answer for every form. The metaphor breaks because forms are rarely independent: a column store still has to reassemble whole rows for a point lookup, and that reassembly is the cost it hides.
How it works
- 1Decide the access pattern first: full-record lookups by key belong in a row store, wide scans and aggregations in a columnar one.
- 2Choose the container: Parquet for open analytics interchange, ORC where the Hive ecosystem expects it, Arrow for in-memory exchange between processes.
- 3Write in large batches so each file holds a useful row group and column chunks are big enough to compress well.
- 4Order rows by the columns your filters use most, because sort order determines which row groups a predicate can skip.
- 5Compact small files on a schedule, since each file adds metadata and open cost to every query.
- 6Verify with the query plan that the engine is actually pruning row groups rather than reading everything.
Step 1 of 6
Decide the access pattern first: full-record lookups by key belong in a row store, wide scans and aggregations in a columnar one.
Figure. The mechanism in this page's own steps. The figure adds sequence, not new claims.
What to notice. A step can be read alone, but the order is the mechanism. The control lets you stop on any one.
Text version
The page's steps: 1. Decide the access pattern first: full-record lookups by key belong in a row store, wide s… 2. Choose the container: Parquet for open analytics interchange, ORC where the Hive ecosyste… 3. Write in large batches so each file holds a useful row group and column chunks are big en… 4. Order rows by the columns your filters use most, because sort order determines which row … 5. Compact small files on a schedule, since each file adds metadata and open cost to every q… 6. Verify with the query plan that the engine is actually pruning row groups rather than rea…
- Decide the access pattern first: full-record lookups by key belong in a row store, wide scans and aggregations in a columnar one.
- Choose the container: Parquet for open analytics interchange, ORC where the Hive ecosystem expects it, Arrow for in-memory exchange between processes.
- Write in large batches so each file holds a useful row group and column chunks are big enough to compress well.
- Order rows by the columns your filters use most, because sort order determines which row groups a predicate can skip.
- Compact small files on a schedule, since each file adds metadata and open cost to every query.
- Verify with the query plan that the engine is actually pruning row groups rather than reading everything.
Trade-offs
| Choice | Good for | Cost | Fails when |
|---|---|---|---|
| Parquet | Open interchange across engines | Row-group granularity limits skipping | Rows arrive in tiny files that never get compacted |
| ORC | Hive and Presto style stacks | Weaker support outside that ecosystem | You need portable files for external consumers |
| Wide table, many columns | Flexible ad hoc queries | Scan cost grows with column count | Most queries touch a handful of fields |
| Narrow nested model | Cheap scans of common fields | Joins at read time | The warehouse cannot push down through the nesting |
Good for
Open interchange across engines
Cost
Row-group granularity limits skipping
Fails when
Rows arrive in tiny files that never get compacted
- Rows arrive in tiny files that never get compacted
- You need portable files for external consumers
- Most queries touch a handful of fields
- The warehouse cannot push down through the nesting
Figure. Each choice with what it buys, what it costs, and the case where it loses.
What to notice. The fourth column is the decision: if the losing case describes your situation, the choice is wrong.
Text version
Four trade-offs: Parquet is good for Open interchange across engines, costs Row-group granularity limits skipping, and fails when Rows arrive in tiny files that never get compacted. ORC is good for Hive and Presto style stacks, costs Weaker support outside that ecosystem, and fails when You need portable files for external consumers. Wide table, many columns is good for Flexible ad hoc queries, costs Scan cost grows with column count, and fails when Most queries touch a handful of fields. Narrow nested model is good for Cheap scans of common fields, costs Joins at read time, and fails when The warehouse cannot push down through the nesting
Failure modes
- Small file accumulation. Query planning time grows and scans open thousands of files. Debug: count files per partition and check average file size against your target.
- Schema drift across files. Some files have extra or renamed columns and readers disagree on nulls. Debug: read the footer metadata of a sample and diff column types by partition.
- Compression defeating pruning. A single dictionary-encoded block spans many distinct keys and statistics cannot exclude it. Debug: compare rows scanned against rows returned in the query plan.
In production
Monitor file count and average file size per partition as first-class metrics. Compact on write or on a schedule, and keep row-group size stable so predicate pruning stays effective.
Common misconceptions
People think: Columnar storage is always faster.
Actually: It wins on wide scans and loses on single-row lookups and frequent updates, where reassembly and rewrite cost dominate.
People think: Compression is just a smaller file.
Actually: Compression also enables pruning and vectorised decoding, which is where most of the query speedup comes from.
Interview angle
Explain the layout, then connect it to a query: which columns are read, which row groups are skipped, why compression helps. Mention the small-file problem and where row storage still wins. A weak answer says columnar is faster without naming the access pattern that makes it true.
Sources
- Apache Parquet documentation — row groups, column chunks and statistics.
- Apache Arrow documentation — the in-memory columnar representation.
- Apache ORC specification — an alternative columnar container and its encodings.