System Design · Storage Internals

Columnar Database Concepts

A dashboard asks for yesterday’s revenue. The fact table is 80 columns wide and 40 billion rows. A would drag names, JSON payloads, and unused flags off disk for every event. A opens the amount and ts pages, skips row groups whose max timestamp is too old, and aggregates in CPU cache.

This note is the analytical layout: encodings, zone maps, vectorized scans, and why OLTP updates hate this shape. It is not a . Those cluster operational columns under a partition key; they do not make SUM(amount) cheap across the whole table.

One analytic query

I/O follows SELECT list, not table width.

Min/max stats drop groups that cannot match.

Filters run on integer codes, not fat strings.

Batches, not per-row interpreters.

What actually happens

On disk, a columnar file is a sequence of . Inside a group, each column is a stack of pages with a chosen encoding. A footer (or catalog) lists column offsets, encodings, and . The reader never walks the file linearly like a CSV.

Execution is plus : keep columns separate, apply filters on the cheapest encodings, then stitch rows only for the tiny remainder that must leave the engine. That is why 1% selectivity on a clustered timestamp column can turn a terabyte table into a few hundred megabytes of I/O.

Writes are the other half of the story. Appending a new row group is cheap. Updating one cell in the middle of a compressed page is not. Lakehouse table formats and warehouse MERGE paths therefore treat mutations as new files, delete vectors, or rewrite of a partition—not as InnoDB-style in-page updates. Confusing those costs is how people propose “just store OLTP in Parquet.”

Row pages versus column pages

Diagram text: the exact Mermaid definition below preserves the nodes, labels, and connections. Enable JavaScript to view the rendered diagram.

Read the diagram definition

flowchart LR
    subgraph Row["Row page"]
        R1["r1: id,name,amount,json"]
        R2["r2: id,name,amount,json"]
    end
    subgraph Col["Columnar row group"]
        C1["id page"]
        C2["name page"]
        C3["amount page"]
        C4["json page"]
    end
    Q["SUM(amount)"] --> Row
    Q --> Col
    Row --> Waste["Read every field"]
    Col --> Win["Read amount page only"]

CPU and disks transfer blocks, not cells. If a 8 KB page holds one fat row, a scan of one integer still pays for the JSON blob sitting next to it. If the same 8 KB holds two thousand amounts, the scan saturates sequential bandwidth and SIMD. The crossover is not ideological: it is “what fraction of columns does this query touch, and how many rows?”

plaintext

Table: 40e9 rows, 80 columns, avg row 800 B  →  32 TB logical
Query: SELECT SUM(amount) WHERE ds = '2026-10-09'
  amount is 8 B, ds is 4 B encoded; 2 of 80 columns

Row-oriented scan (no covering index):
  must read ~32 TB (or the whole clustered index)
  plus CPU to skip 78 unused fields per row

Columnar, ds clustered by partition:
  read ds + amount for one day
  if 1/365 of rows in that partition ≈ 88 GB uncompressed
  with 4× encoding on low-cardinality ds + 2× on amounts
  → tens of GB, often much less with zone-map skips

Point GET of one order by id:
  row store: 1–2 pages
  columnar: open footer, jump to id page, binary search,
            then gather other columns by row index
  → many more random reads unless you keep a row-store replica

Scan path inside one file

Diagram text: the exact Mermaid definition below preserves the nodes, labels, and connections. Enable JavaScript to view the rendered diagram.

Read the diagram definition

flowchart TD
    Q["SQL: SUM(amount) WHERE ts >= T"] --> Plan[Planner]
    Plan --> Footer[Read file footer + stats]
    Footer --> Prune["Column prune: open amount, ts only"]
    Prune --> ZM["Zone maps skip cold row groups"]
    ZM --> Dec["Decode / dictionary pages"]
    Dec --> Pred["Predicate on ts vectors"]
    Pred --> Agg["Aggregate amount vectors"]
    Agg --> Out[Return scalar]

is mechanical: the footer is a map from column name to byte range. If the planner does not name a column, that range is never requested from object storage or disk. This is also why SELECT * in a warehouse is an operational incident, not a style preference.

After pruning, and Bloom-like filters on dictionary ids skip groups. Remaining pages decode into vectors. A predicate becomes a bitmask; the aggregate walks only the surviving lanes. That bitmask is the physical form of .

plaintext

Parquet-style reader (simplified):
  footer = read_tail(file)
  needed = {'ts', 'amount'}
  for group in footer.row_groups:
    if group.stats['ts'].max < T: continue      # zone map
    ts_page = decode(group.column['ts'])        # vector
    mask = ts_page >= T
    if none(mask): continue
    amount_page = decode(group.column['amount'])
    acc += sum(amount_page[mask])
  return acc

Never:
  for row in table:
    if row.ts >= T: acc += row.amount

Encodings that make scans cheap

is the default for strings and enums. After the dictionary is built, filters become integer set membership. then collapses long identical codes. Bit-packing and delta encoding handle integers and timestamps. The engine chooses per page; mixed encodings in one column across groups are normal.

Column shape	Typical encoding	Scan effect
Low-cardinality strings (country, status)	Dictionary + RLE	Equality filters on 1–2 byte codes
Sorted timestamps	Delta + bit-pack; strong zone maps	Range predicates skip whole groups
High-cardinality ids	Plain or dictionary if it still fits RAM	Weaker skip; still column-pruned I/O
Nested JSON / lists	Definition/repetition levels (Parquet Dremel)	Can still prune sibling fields

Compression (zstd, snappy) sits on top of encoding. Encoding removes redundancy the compressor would rediscover more slowly; compression then squeezes leftover entropy. Always measure bytes after both, and remember decode CPU: an over-compressed page can lose the scan to a lighter codec that saturates disk.

Vectorized execution and materialization

processes a column batch as an array. A hash aggregate builds from thousands of keys per call. Cache lines stay hot. Volcano-style “next tuple” iterators do the opposite: a virtual call per row, which drowns simple arithmetic. Warehouse engines (and modern OLAP like ClickHouse, DuckDB, Spark Tungsten) bet on vectors.

Early materialization reconstructs a row struct after each operator. On a 200-column table that is fatal. Late materialization keeps a row-id (or selection vector) and only gathers columns the next operator needs. Joins can probe on a key column first, then fetch payload columns for matches. That is the same idea as an index nested loop, expressed in columns.

plaintext

Early (slow on wide tables):
  for each row:
    tuple = assemble_all_columns(row)
    if pred(tuple): emit(tuple)

Late (columnar default):
  mask = pred(column_ts, column_status, ...)
  if need payload:
    payload = gather(column_json, mask)
  else:
    return aggregate(column_amount, mask)

Batch size 4k–8k:
  fits L2/L3, amortizes decode, enables SIMD compares

Writes, mutations, and why OLTP disagrees

Immutable files plus a table log (Iceberg/Delta/Hudi, ClickHouse parts, Redshift blocks) make ingest a sequential write. Small UPDATE/DELETE become : rewrite the row group, maintain a delete bitmap, or copy-on-write a partition. That is acceptable hourly; it is not a checkout API.

In-memory columnar OLAP (some real-time analytics stores) buffers rows, then converts to columns in batches. You still pay a conversion tax and you still should not treat it as the money ledger. Dual-write or CDC from a row-store primary into columnar serving is the usual SD pattern.

Good write grain

Partitioned daily loads, micro-batches of millions of events, MERGE of a dimension once an hour, compaction of small files into larger row groups.

Bad write grain

Per-click UPDATE of a counter, user-profile PATCH of one nested field, multi-row ACID checkout across facts and inventory in the warehouse.

Columnar files versus wide-column NoSQL

Interviews mash the words together. A answers “give me the timeline for user 42” by clustering cells under one partition key. A columnar warehouse answers “sum this metric across a day” by storing that metric contiguously for everyone. Access path, compression, and failure modes do not transfer.

Axis	Analytical columnar	Wide-column (Cassandra-like)
Cheap question	Scan few columns, many rows	Read/write one partition
Unit of locality	Column page / row group	Partition key + clustering
Cross-entity aggregate	Native	Full scan or Spark job
Single-row OLTP	Awkward	Fits if keyed correctly

Worked micro-estimate

plaintext

Events table, 10 TB logical, 50 columns
  2 columns needed, 4% of rows match date partition + filter
  encodings shrink those 2 columns 3×
  zone maps skip 70% of remaining groups as out of range

Bytes read ≈ 10 TB × (2/50) × 0.04 × (1/3) × 0.30
           ≈ 1.6 GB
At 500 MB/s sequential from object store cache: ~3 s scan
Plus shuffle if you grouped by 1e6 keys: budget network

Same query as SELECT * on row files:
  10 TB × 0.04 = 400 GB  →  minutes and a surprising bill

Design implication:
  partition by ds, cluster by high-selectivity filters,
  keep files ~100 MB–1 GB so listing does not dominate,
  never SELECT * in serving SQL

Trade-offs to weigh

Scan width versus point-get latency

Columnar layouts win when a query touches few fields and many rows. They lose when the API is “load this entity by id” because you gather columns from several pages. Serve OLTP from a row store or a KV replica; do not pretend the warehouse is also the profile service.

Compression ratio versus decode CPU

Heavy codecs save object-storage dollars and can still miss SLOs if every scan is CPU-bound. Measure both GB scanned and CPU per GB. Dictionary + light compression often beats maximum zstd on hot dashboards.

Small files versus huge row groups

Tiny files explode listing and footer reads. Enormous row groups delay availability of fresh data and make a skip miss expensive. Compact toward a size your planner and object store like (often hundreds of MB), on a schedule that matches ingest delay you can sell.

Freshness versus rewrite cost

Streaming into columnar serving needs a buffer, a convert-to-columns step, and compaction. Tighter freshness raises write amplification and small-file pain. CDC plus a few-minute delay is usually the honest SLA.

Star schema versus one wide denormalized table

Wide denormalized facts make scans simpler and joins rarer, at the cost of storage and update fan-out. Star schemas keep dimensions small but add join keys that must stay in cache. Choose from query graphs, not from elegance.

Choosing and explaining columnar databases

A complete explanation connects the mechanism to a requirement. Describe what improves when you introduce it, which new failure cases become possible, and how you would know that the trade-off is worthwhile.

Explain the core idea

Columnar storage packs each field across rows so a scan pays for selected columns, not whole tuples.

Row groups, zone maps, encodings, and vectorized execution turn filters into skipped I/O and SIMD-friendly loops.

This is the opposite of a wide-column NoSQL table: analytics across entities, not keyed operational partitions.

When it belongs

Dashboards, warehouse SQL, and event analytics that touch few columns and huge row counts.

You need to justify Parquet/ORC, ClickHouse-style serving, or a lakehouse table format.

You must explain why the OLTP database should not run the heavy GROUP BY.

How it fails

SELECT * and unpartitioned dumps that disable pruning.

Small-file explosions and stale zone maps after messy ingest.

Using columnar files as the primary store for point updates and transactions.

Trade-offs to weigh

Scan efficiency versus point-lookup and UPDATE cost.

Compression savings versus decode CPU and freshness from compaction.

Partition granularity: too coarse scans extra data; too fine creates tiny files.

Common misunderstandings

Confusing analytical columnar storage with Cassandra-style wide-column stores.

Assuming column stores are “faster databases” for every API.

Forgetting that late materialization and predicate pushdown are the actual speedups, not the brand name.

Wrap-up

Columnar engines make the cheap question “a few fields, many rows.” Everything else—encodings, zone maps, vectors, table formats—exists to skip bytes and keep the CPU in tight loops. Pair them with a row-oriented system of record, partition on the filters you actually run, and treat mutations as batch rewrites.

Primary sources

Key takeaway

Store columns together when the workload is scan-shaped. Prune, skip, decode, vectorize. Do not confuse that layout with wide-column OLTP, and do not ask it to UPDATE one shopping cart.