7.8.1 — OLTP, OLAP and Columnar Storage

One query: total revenue by region, by month, for the last two years.

The orders table has 40 columns and 800 million rows. The query needs three of them. In the row-oriented layout of Chapter 7.3.1, every row is stored contiguously in a page — so reading region, placed_at and total_minor means reading all 40 columns of all 800 million rows. About 400 GB of I/O to answer a question about 12 GB of data.

No index fixes that, because the query is not selective — it wants nearly every row. The layout is wrong for the question, and that is the entire subject of this page.

1. Two workloads that want opposite things

	OLTP (transactional)	OLAP (analytical)
Query shape	Few rows, most columns	Billions of rows, few columns
Typical query	Fetch order 1001	Revenue by region by month
Writes	Constant, single-row	Bulk loads, append-only
Latency target	Milliseconds	Seconds to minutes
Concurrency	Thousands of users	Tens of analysts
Data	Current state	History, immutable
Schema	Normalised	Denormalised
Best layout	Row-oriented	Column-oriented

The names come from the 1990s — online transaction processing, online analytical processing — and are worth keeping because they name a real split. The mistake is not choosing wrong; it is running both on one system. An analyst's four-minute scan evicts the buffer pool that the checkout page depends on, and suddenly the shop is slow because someone opened a dashboard.

The first and most valuable answer is separation, and it has three levels of cost. Run analytics on a read replica — free, and enough for many companies. Extract to a dedicated analytical store on a schedule — more work, far better performance. Build a full warehouse with modelled data — Chapter 7.8.2.

2. Columnar layout

Row-oriented stores each row's fields together:

[1|Ana|London|2499][2|Ben|Leeds|1899][3|Cal|London|3200]

Column-oriented stores each column's values together:

ids:     [1, 2, 3]
names:   [Ana, Ben, Cal]
regions: [London, Leeds, London]
totals:  [2499, 1899, 3200]

Same data, different physical grouping. The saving is not one effect but two: fewer columns read, and each of those columns far smaller.

Three wins follow, and they multiply.

1. Read only the columns you need. Three columns out of forty is roughly a 13× reduction before anything else.

2. Compression gets dramatically better. In a row, neighbouring bytes are an integer, a name, a date — unrelated. In a column, neighbouring values are the same field, so:

• Run-length encoding — London repeated 40,000 times becomes (London, 40000).
• Dictionary encoding — map each distinct region to a small integer and store the integers. A 12-byte string becomes 4 bits when there are 12 regions.
• Delta encoding — sorted timestamps become small differences.
• Bit packing — a column whose values are 0–200 needs 8 bits, not 64.

Together these routinely give 5–20× on real data. Combined with reading three columns instead of forty, the 400 GB scan becomes a few gigabytes.

3. Execution gets faster per byte. A column is an array of identical fixed-width values, which is exactly what a CPU's vector instructions want (Chapter 1.5). Engines process values in batches of thousands rather than a row at a time, and — because dictionary-encoded data can be filtered without decoding — many predicates run directly on the compressed form.

Late materialisation is the last trick: evaluate the filter on one compressed column, produce a list of matching positions, and only then read the other columns at those positions.

3. What columnar gives up

Single-row reads are bad. Reconstructing one whole row means touching every column's storage. SELECT * FROM orders WHERE id = 1001 is the worst case for a column store and the best case for a row store.

Single-row writes are worse. Appending one row means appending to forty separate structures and breaking the compression that assumed sorted, repeated neighbours. So column stores are built for bulk loading, and most treat data files as immutable — a new batch is a new file, and updates are handled by writing a change and merging later (the same merge-on-read idea as the LSM tree in Chapter 7.3.3).

Updates and deletes are expensive. Modern formats support them, and they remain far more costly than in a row store. If your analytical data needs constant single-row updates, something upstream is modelled wrong: analytical tables should be append-only records of what happened.

No enforced constraints, usually. Warehouses generally do not enforce foreign keys or uniqueness — the load pipeline is responsible. That is a real loss and a deliberate one, because checking a constraint per row defeats bulk loading.

4. Modelling for analytics: the star schema

Normalisation (Chapter 7.1) exists to make updates safe. Analytical data is not updated, so the reason for normalising it is gone, and the cost — joins on every query — remains.

So analytical schemas are deliberately denormalised into a star schema:

A fact table holds the events: one row per order line, per page view, per payment. It is enormous, narrow, and mostly foreign keys plus numbers you can add up (measures).

Dimension tables hold the descriptive attributes: customer, product, store, date. They are small and wide, and they are the things you group and filter by.

fact_order_lines
  date_key, product_key, customer_key, store_key,   -- who/what/when
  quantity, unit_price_minor, discount_minor, revenue_minor   -- measures

dim_product   (product_key, sku, name, category, brand, supplier, …)
dim_customer  (customer_key, name, segment, city, country, …)
dim_date      (date_key, date, day_of_week, month, quarter, year, is_holiday, …)

The grain is the first decision and the one that goes wrong. "One row per order line" is a grain. Mixing grains in one fact table — some rows per line, some per order — makes every sum wrong in a way that is hard to detect. Write the grain down as a sentence before designing anything.

A date dimension is always worth building. A table with one row per date and columns for day of week, month, quarter, fiscal period and holiday flags turns "revenue on working days in Q3" into a join and a filter, rather than date arithmetic repeated in every query and inconsistently.

Slowly changing dimensions handle attributes that change over time, and the choice changes what history means:

• Type 1 — overwrite. A customer moves from Leeds to London, and every past order now reports London. Simple, and it silently rewrites history.
• Type 2 — add a new row with valid_from, valid_to and a current flag. Each fact points at the version that was current when it happened, so last year's revenue stays attributed to Leeds. This is what you almost always want, and it is why dimension keys are surrogate integers rather than natural keys — a customer has several dimension rows over time.

A snowflake schema normalises the dimensions further (product → category → department as separate tables). It saves a little space and costs more joins. Star is the default; snowflake when a dimension is genuinely huge or shared.

5. Partitioning, pruning and statistics

The biggest analytical speed-up is usually not compression. It is not reading the file at all.

Partitioning splits a table by a column, physically:

/warehouse/fact_orders/year=2026/month=07/part-0001.parquet
/warehouse/fact_orders/year=2026/month=08/part-0001.parquet

A query filtering WHERE placed_at >= '2026-08-01' skips every other directory. That is partition pruning, and it is why nearly every analytical table is partitioned by date.

Choose the partition column by what queries filter on, and size the partitions deliberately. Partitions of roughly 100 MB to 1 GB work well. Partitioning by a high-cardinality column produces the small-files problem — a million tiny files, where the per-file overhead dominates and listing them is slower than reading them.

Within a file, statistics prune further. Parquet stores minimum and maximum values per column chunk, so a query for total_minor > 100000 skips any chunk whose maximum is 90,000 without reading it. This only works if the data is sorted or clustered by that column, which is why warehouses offer clustering keys — sorting on load so the min/max ranges are narrow and non-overlapping. Unsorted data gives every chunk nearly the full range, and the statistics prune nothing.

Predicate pushdown is the general name: the filter is pushed as far down as possible — to the file reader, to the storage layer, and in cloud object storage to a byte-range request that fetches only the needed column chunks.

6. File formats

Analytical data increasingly lives as files in object storage rather than inside a database, so the format matters.

Parquet — columnar, the de facto standard. A file is divided into row groups (a horizontal slice, typically 128 MB); within a row group each column is a column chunk with its own encoding and min/max statistics; a footer holds the schema and all statistics. The footer being at the end is deliberate: a reader fetches the footer first, decides which chunks it needs, and then issues byte-range reads for exactly those.

ORC — very similar, with stronger built-in indexing, common in the Hive ecosystem.

Avro — row-based, with a schema travelling alongside the data and good schema evolution rules. Right for streaming and for the write side of a pipeline, wrong for analytical scans. The pair to remember: Avro for moving records, Parquet for analysing them.

JSON and CSV — human-readable, no types, no statistics, poor compression. Fine as an interchange format, and a bad choice for anything you will query more than once.

Table formats sit on top of file formats. Iceberg, Delta Lake and Hudi add a metadata layer over Parquet files that provides atomic commits, schema evolution, time travel and row-level updates. That is what turns "a folder of Parquet files" into something you can safely write to concurrently, and Chapter 7.8.2 covers why it matters.

7. You may not need a warehouse

Two developments have made the middle ground much larger, and both are worth knowing before proposing a platform.

Columnar inside a row database. PostgreSQL can use columnar storage through the Citus extension; MySQL HeatWave keeps an in-memory column store alongside InnoDB; SQL Server has columnstore indexes. A columnstore index on a fact table inside your existing database can turn a two-minute report into two seconds with no new system to operate.

Embedded analytical engines. DuckDB is a columnar engine that runs inside your process, reads Parquet and CSV directly, and handles tens of gigabytes on a laptop.

-- No server, no load step: query files in place
SELECT region, date_trunc('month', placed_at) AS m, SUM(total_minor)/100.0 AS revenue
FROM 'orders/year=*/month=*/*.parquet'
GROUP BY 1, 2 ORDER BY 1, 2;

A very large fraction of "we need a data warehouse" conversations are actually "our reports run on the transactional database". Moving them to a replica, or to Parquet plus DuckDB, solves it for a fraction of the cost. Reach for a warehouse when data volume, concurrent analysts, or governance genuinely demand it — Chapter 7.8.2 draws that line.

What the interviewer will push on

"Why is columnar faster for analytics?" Three multiplying effects: you read only the columns you need, each column compresses 5–20× because neighbouring values are the same field, and execution is vectorised over fixed-width arrays. The tell is naming compression as a consequence of the layout rather than a separate feature.

"When is columnar the wrong choice?" Single-row reads and writes. Reconstructing one row touches every column, and appending one row appends to every column while breaking the compression assumptions. That is why column stores are bulk-load systems and analytical tables are append-only.

"Explain a star schema." A large narrow fact table of events at one stated grain, joined to small wide dimension tables you group and filter by. Then say why denormalisation is correct here: normalisation exists to make updates safe, analytical data is not updated, so only the join cost remains.

"What is a slowly changing dimension?" Type 1 overwrites and silently rewrites history; Type 2 adds a versioned row with validity dates so a fact stays attributed to the attributes that were true at the time. Volunteer that Type 2 is why dimension keys are surrogates — one customer has several dimension rows.

"How do you make a query over a billion rows fast?" Partition pruning first — skip whole files by the partition column — then min/max statistics within files, which only prune if the data is sorted or clustered on that column. That conditional is the part that separates someone who has tuned a warehouse from someone who has read about one.

"Parquet or Avro?" Parquet is columnar with per-chunk statistics and a footer read first, for analytical scans. Avro is row-based with strong schema evolution, for streaming and moving records. One sentence: Avro to move, Parquet to analyse.

One thing to volunteer: point out that many "we need a warehouse" problems are really "our reports run on the transactional database", and that a read replica, a columnstore index, or Parquet plus DuckDB often solves it for a fraction of the cost. Naming the cheap option first is what a senior engineer does.

Recall

• OLTP wants few rows and most columns; OLAP wants billions of rows and few columns. The failure is running both on one system — an analyst's scan evicts the buffer pool the checkout depends on. Separate, starting with a read replica.
• Columnar storage stores each column contiguously, giving three multiplying wins: read fewer columns, compress 5–20× (run-length, dictionary, delta, bit packing — possible because neighbours share a field), and vectorised execution over fixed-width arrays, often on the compressed form.
• It gives up single-row reads and writes, so column stores are bulk-load, append-only systems with immutable files and merge-on-read updates, and usually enforce no constraints.
• Star schema: one narrow fact table at a stated grain, plus small wide dimension tables. Denormalisation is correct because analytical data is not updated, so only the join cost remains. Always build a date dimension.
• Slowly changing dimensions: Type 1 overwrites and rewrites history; Type 2 versions the row with validity dates so facts stay attributed to what was true then. Type 2 is why dimension keys are surrogates.
• Partition pruning skips whole files (usually by date; watch for the small-files problem). Min/max statistics prune within a file only if the data is sorted or clustered on that column. Predicate pushdown carries the filter all the way to a byte-range read.
• Parquet = columnar, row groups, column chunks, statistics, footer read first. Avro = row-based, strong schema evolution. Avro to move, Parquet to analyse. Iceberg/Delta/Hudi add atomic commits and time travel on top.
• Often you do not need a warehouse: a read replica, a columnstore index inside your existing database, or DuckDB reading Parquet in place solves most reporting problems.

Self-test: Why does the columnar layout make compression better rather than the other way round? · What is the worst query shape for a column store? · What does "grain" mean and what breaks if you mix it? · Why does Type 2 force surrogate dimension keys? · Why do min/max statistics prune nothing on unsorted data? · When is Avro the right format?

Next: 7.8.2 covers the platform around all this — warehouses, lakes and lakehouses, how MapReduce became Spark, and what a golden record is when four systems disagree about a customer.