Storage · 3 of 3

Analytics & Columnar Storage

A transaction touches one row and every column. An analytic query touches every row and a few columns. Turning the table on its side, then compressing, sorting and skipping, is how warehouses scan billions of rows in seconds.

≈ 30 min

The whole idea

Press ← → to step, or Space to play and pause, whenever a diagram has focus.

Two kinds of workload

The storage engines in the previous lessons, B-trees and LSM-trees, were built for one kind of work: a user does something, and the application reads or writes a handful of rows found by key. Look up order 4417. Insert one payment. Update one balance. That pattern is online transaction processing (OLTP): many small, latency-sensitive requests, each touching a few rows but all of each row’s columns.

Then someone asks a different kind of question. What was revenue per region last quarter? Which products sell together? Did the new checkout flow lift conversion? These queries don’t look up a record. They scan millions or billions of rows, read only a few columns of each, and reduce them to a handful of numbers. That is online analytical processing (OLAP), and almost everything about it is the opposite of OLTP.

OLTP OLAP
Who asks end users, through the app analysts, dashboards, data scientists, ML pipelines
Typical read a few rows, found by key through an index an aggregate over millions to billions of rows
Typical write random single-row inserts and updates bulk loads, append-mostly
Columns touched all of a row’s columns a few of a table’s many columns
What matters latency per request, concurrency throughput: rows scanned per second
Data size gigabytes to terabytes, the current state terabytes to petabytes, often the full history

You can run analytics on the OLTP database for a while. Then it hurts.

Analytics on the production database

A scan and a transaction can share a database, until the scan gets big. Then the fix is a second copy, laid out for scans.

A big scan competes with user transactions for disk bandwidth, CPU and the buffer cache, and it pushes out the hot pages users need. A read replica isolates the load, but it is still a row store, still reading every byte of every row it scans. The lasting fix is to copy the data into a separate system whose physical layout is built for scanning.

The data warehouse

A data warehouse is that separate system: a database of read-mostly, historical copies of data from all of a company’s operational systems, organised for analysis. Sales, inventory, payments and clickstream each live in their own OLTP database, owned by their own team; the warehouse is where they meet.

Data arrives through a pipeline. The classic shape is ETL: extract data from the sources (periodic dumps or a continuous stream of changes), transform it into an analysis-friendly schema, cleaning and joining along the way, and load it into the warehouse. Cloud warehouses made compute cheap enough that many teams now load raw data first and transform it inside the warehouse with SQL, which is called ELT.

The design that reshaped the industry was separating storage from compute. Snowflake, generally available since June 2015, keeps table data in cloud object storage (originally Amazon S3) as large immutable files and runs queries on independent clusters it calls virtual warehouses, which only cache data on local disk. Two teams can query the same tables on separate clusters without slowing each other, and storage grows without buying CPUs. Google’s Dremel, the engine behind BigQuery, made the same move. Its authors name disaggregated storage and compute, in situ analysis and columnar storage for semistructured data among the ideas that defined it.

Real warehouse workloads are not what benchmarks suggest. Amazon’s 2024 study of the Redshift fleet found that 98% of tables have fewer than a billion rows, the median query runs in about 100 ms, and in half of all clusters 80% of queries are exact repeats of earlier ones. Writes matter too. Inserts, COPY loads, deletes, updates and CREATE TABLE AS made up nearly 40% of queries, and COPY alone took more runtime than SELECT.

98%of Redshift tables hold fewer than 1 billion rows (fleet study, 2024)
≈ 100 msmedian Redshift query runtime
80%of queries are exact repeats, in half of all clusters

Stars and snowflakes

Warehouses are usually modelled as a star schema, also called dimensional modelling. At the centre is a fact table whose rows are events at a declared grain: one row per item sold, per page view, per ride. Fact tables are huge, often billions of rows, and simple in what they hold: foreign keys and numeric measures (quantity, price, duration). Around them sit dimension tables, the who, what, where and when of each event: the product with its brand and category, the store with its city and region, the calendar date with its week, quarter and holiday flag.

A star schema

Facts in the middle, dimensions around it. Every query is a fan of joins out from the centre.

Normalise the dimensions further, so product points to a separate category table, and you get a snowflake schema. It removes duplication, but every query that groups by category now pays one more join. Ralph Kimball, who popularised dimensional modelling, advised against it: a flattened dimension holds exactly the same information, and dimension tables are tiny next to the facts, so the duplication costs almost nothing.

Analytic tables also tend to grow wide. A fact table that has accumulated measures for years, or a denormalised “one big table” that folds the dimension attributes in, can have hundreds of columns. A typical query still reads four or five of them. That mismatch is what column storage exploits.

Storing by column

In a row store, all of a row’s values are laid out next to each other on disk, row after row. That’s perfect for OLTP: fetching one record is a single contiguous read. For a scan it’s wasteful, because disks and object stores read in pages and blocks, not individual values. To use two columns of every row, you read every column of every row.

Rows vs columns on disk

logical table#dateproductstoreqty1MonteaNY32MonjamSF13TueteaLA24TueoilNY55WedteaSF46WedjamLA2on disk: one row-oriented fileMonteaNY3MonjamSF1TueteaLA2TueoilNY5WedteaSF4WedjamLA2
The same 24 values in two orders. Watch which bytes the query has to touch.

Column-oriented storage turns the table on its side. Each column is stored contiguously, in its own file or its own region of a file. A query reads only the columns it references. The one rule that makes it work: every column stores rows in the same order, so the 23rd value of product and the 23rd value of qty belong to the same row. To rebuild a full row you take the k-th entry of every column, which is why column stores are poor at “give me everything about order 4417”.

The idea is old. The modern wave began with academic systems around 2005. MonetDB/X100 showed what cache-conscious processing of columns could do, and C-Store (Stonebraker, Abadi and colleagues, VLDB 2005) laid out the architecture most column stores still follow; its authors founded Vertica the same year. Google’s Dremel (VLDB 2010) brought columns to nested data. It “shreds” each record into columns plus two small integers per value, repetition and definition levels, which say where in the nesting a value sits and which optional fields were missing. That’s the encoding Parquet adopted.

87 TBread by a row-oriented MapReduce job for one query in Dremel’s paper
0.5 TBread for the same query from compressed columns

Compressing columns

Storing columns separately has a second, bigger payoff: a column is one data type, often with few distinct values and long repeats, which makes it far more compressible than rows. The techniques are deliberately lightweight, cheap enough to decode at memory speed:

  • Dictionary encoding: store each distinct value once and replace values with small integer codes. ClickHouse exposes this as the LowCardinality type; Parquet uses it by default, and falls back to plain encoding when a column chunk’s dictionary grows too big.
  • Bit-packing: store each code in exactly the bits it needs: 2 bits for 3 distinct values, 10 bits for 1,000.
  • Run-length encoding (RLE): store a repeated value once with a count. It shines on sorted columns.
  • Delta encoding: store differences between neighbours. Timestamps and IDs become small numbers that then bit-pack well.

How a column shrinks

column: product teajamteaoiljamteateaoiljamteaoiljamthe same column at 1 billion rows≈ 7 GB
One column, four encodings, and what each would cost at a billion rows.

Each step multiplies the last: dictionary codes make the values small, bit-packing makes them dense, sorting makes them repeat and RLE collapses the repeats. A general-purpose codec such as LZ4 or Zstandard usually runs on top of the encoded pages.

Sort order and skipping

A column store can keep rows in any order, as long as every column uses the same one, so you get to choose it. Sorting the table by the columns most queries filter on does two things. It lengthens runs, so RLE and dictionaries compress better, and the first sort key compresses best of all. And it makes data skipping possible.

Engines keep small statistics for every block of rows. Parquet stores the min and max of each column per row group, and per page in its optional page index. ClickHouse calls its blocks granules (up to 8,192 rows by default) and keeps one sparse primary-index entry per granule, the sort-key value of its first row, so about a thousand entries index 8 million rows; other columns get min/max only through an explicit skip index. A query compares its predicate with the statistics and never reads a block that can’t match.

Why the sort key matters

rows in arrival order · min/max of date per blockblock 1block 2block 3block 4block 5block 6Mar 1Mar 8Mar 15Mar 22Mar 29date
Each bar is one block's min-to-max range. A block is read only if the query's line crosses its bar.

The sort key is a per-table decision with real consequences. Skipping works well on the leading sort column, less on the second, and hardly at all on a column outside the key. C-Store’s answer was to store the same data several times in different sort orders, called projections, and let the optimizer pick the copy that suits each query. ClickHouse offers projections too, plus skip indexes (min/max, set and Bloom filter) for columns that aren’t in the key.

Writing to a column store

Everything that makes columns fast to read makes them awkward to write. Rows are sorted and compressed, so a single inserted row belongs in the middle of every column file. Updating in place, the way a B-tree does, would mean decompressing and rewriting a block in each column for one row.

Parts and merges

Column stores borrow the LSM idea: never edit a file, write new sorted ones and merge them later.

So column stores use the LSM pattern from the storage-engines lesson. C-Store had a small writeable store that took inserts and a tuple mover that periodically merged them into the read-optimised store. ClickHouse’s MergeTree writes each insert as a new immutable part, sorted by the table’s key, and background threads merge parts into larger ones. Queries read every live part and combine the results. Deletes are recorded as marks and only physically removed when the part is next merged; ClickHouse’s lightweight DELETE writes a hidden row-exists mask for exactly this reason.

The consequence for anyone loading data: batch your inserts. ClickHouse’s docs ask for at least 1,000 rows per insert, ideally 10,000 to 100,000, and roughly one insert per second. With a stream of tiny inserts, part counts outrun merges. At 1,000 active parts in a partition ClickHouse starts delaying inserts, and at 3,000 it rejects them with “Too many parts”. Asynchronous inserts, which buffer small writes on the server, exist for clients that can’t batch.

Vectorized execution

Once the bytes are off disk, the bottleneck moves to the CPU. A classic query engine processes one row at a time, calling a function per operator per row. With billions of rows, the overhead of those calls and of interpreting the query plan swamps the actual arithmetic.

Vectorized execution processes a batch of values from one column at a time: MonetDB/X100’s default vector was 1,024 values. Each operator runs a tight loop over a small array that fits in the CPU’s L1 cache, which compilers can turn into SIMD instructions that handle several values per cycle. X100 measured one to two orders of magnitude more raw execution speed than the engines of its day. Nearly every modern analytic engine (ClickHouse, DuckDB, Snowflake, Velox, Photon) works on column batches; the alternative family compiles each query to machine code, as HyPer did, and some engines mix both.

Inside a Parquet file

Column stores used to mean column databases. Then the format itself became the interface. Apache Parquet, announced in March 2013 as a collaboration between Twitter and Cloudera, is an open columnar file format: any engine (Spark, Trino, DuckDB, Polars, Snowflake, BigQuery and dozens more) can read files any other engine wrote.

Anatomy of a Parquet file

PAR1PAR1row group 0dpsqrow group 1dpsqrow group 2dpsqfooterlend = date · p = product · s = store · q = qty
Data first, metadata last, so a writer can stream the file in one pass, and a reader can find anything from the footer.

A Parquet file is a column store in miniature:

  • Row groups split the rows horizontally. parquet-java’s default target is 128 MB; the spec recommends 512 MB to 1 GB so each column’s chunk is big enough for efficient sequential reads.
  • Column chunks: within each row group, each column is stored contiguously.
  • Pages are the unit of encoding and compression (1 MB by default in parquet-java): an optional dictionary page, then data pages encoded with RLE_DICTIONARY, DELTA_BINARY_PACKED, BYTE_STREAM_SPLIT and so on, each compressed with Snappy, Zstandard or another codec.
  • The footer holds the schema, the byte offset of every chunk and min/max statistics. An optional page index adds per-page min/max, and optional Bloom filters cover high-cardinality columns where min/max can’t help.

Because the footer is at the end, a reader does one small ranged read for the last 8 bytes (the footer length and PAR1), one for the footer, and then fetches only the chunks it needs. On object storage that’s a few HTTP range requests, which is why engines can query terabytes of Parquet in S3 without copying it anywhere first. Its in-memory counterpart is Apache Arrow, a columnar layout designed for computation rather than storage: Parquet on disk, Arrow in RAM, is the standard pairing.

The lakehouse

Cheap object storage full of Parquet files is a data lake. For years the common architecture was two tiers: data landed in the lake, and a curated subset was copied again into a warehouse for BI. The 2021 Lakehouse paper (Armbrust, Ghodsi, Xin and Zaharia, CIDR) lists what that costs: two copies of storage, a second pipeline that can break, and staleness. It cites a survey in which 86% of analysts said they used out-of-date data.

The problem with querying the lake directly is that a pile of files isn’t a table. A job that writes 50 files and crashes halfway leaves readers seeing 25 of them. Two writers can overwrite each other. There’s no schema enforcement, no safe delete, no consistent snapshot. Table formats add exactly those things, using nothing more than files and one atomic operation.

An atomic commit on object storage

Data files are written first and are invisible; one compare-and-swap makes them all visible at once.
  • Apache Iceberg, created at Netflix, keeps a tree of metadata: a metadata file points to a manifest list, which points to manifests, which list data files with per-file statistics. Each commit writes new metadata and swaps the table’s pointer in a catalog; a writer whose base was stale retries. Iceberg’s hidden partitioning derives partitions from columns (day of a timestamp, a hash bucket of an ID), so queries filter on the real column and never mention the partition.
  • Delta Lake, from Databricks, keeps an ordered log in a _delta_log directory: numbered JSON commit files plus a Parquet checkpoint every 10 commits. A commit succeeds only for the writer that creates the next numbered file first, using put-if-absent on stores that offer it. S3 has only offered conditional writes since August 2024, so Delta’s documented multi-cluster setup on S3 still uses a DynamoDB-backed log store for that mutual exclusion.
  • Apache Hudi, from Uber (2016), focused on upserts and incremental processing.

A data lakehouse is that combination: open files on cheap storage, a transactional table format on top, and warehouse-grade engines reading it. You get ACID commits, snapshots and time travel, schema evolution, and many engines on one copy of the data. Iceberg’s format v3, shipped in 2025, added deletion vectors (bitmaps marking deleted rows, instead of rewriting files), row lineage and a variant type for semi-structured data.

The open table format is now the industry’s battleground. In June 2024 Databricks agreed to buy Tabular, the company founded by Iceberg’s creators, for over $1 billion; the same month Snowflake announced the Polaris catalog (now Apache Polaris) and Databricks open-sourced Unity Catalog. In December 2024 AWS launched S3 Tables, buckets that manage Iceberg tables natively, and in May 2025 DuckDB’s team proposed DuckLake, which keeps all table metadata in an ordinary SQL database instead of files.

Trade-offs

Row store (OLTP) Column store (OLAP)
Point lookup of a whole row one page read one read per column
Scan of a few columns reads every column reads only those columns
Compression modest (mixed types per page) high: dictionary, RLE, bit-packing, delta
Single-row insert or update cheap, in place expensive; batched into parts, merged later
Deletes in place marks or deletion vectors, cleaned up on merge
Indexes many secondary B-trees sort key, min/max per block, skip indexes
Execution row at a time through indexes vectorized over column batches

And within analytics:

Choice Gains Costs
Star schema (denormalised dimensions) simpler, faster queries duplicated attribute values
Snowflake schema less duplication an extra join per normalised level
Warehouse with proprietary storage tight integration, fewer moving parts lock-in; data copied in
Lakehouse on open formats one copy, many engines, cheap storage you operate compaction, catalogs and file layout

In the real world

ClickHouse began at Yandex for its web-analytics product, Yandex.Metrica, ran in production from 2012 and was open-sourced in June 2016. By 2017 Metrica’s main cluster held 22 trillion rows on 472 servers. It’s the clearest open example of everything above: MergeTree parts, a sparse index over granules of up to 8,192 rows, LZ4 by default (Zstandard in ClickHouse Cloud) with per-column codecs (Delta, DoubleDelta, Gorilla, ZSTD) and vectorized execution.

1,630 → 37 Bestimated storage per HTTP request at Cloudflare, raw Cap’n Proto logs vs ClickHouse (2018)
> ½of Uber’s logging hardware cost cut by moving from Elasticsearch to ClickHouse (2021)
11×cheaper, Contentsquare’s ClickHouse vs its 14 Elasticsearch clusters (2022)
  • Cloudflare rebuilt its HTTP analytics on ClickHouse in 2018, ingesting an average of 6 million requests per second (8 million at peak) into 36 nodes. Its capacity planning put a request at 1,630 bytes as raw Cap’n Proto logs (360 bytes compressed with Zstandard) and 36.74 bytes in ClickHouse. One finding is a good lesson in sparse indexes: lowering index granularity from 8,192 to 32 rows halved query latency for their access pattern.
  • Uber moved its logging platform off more than 20 Elasticsearch clusters per region. It found that over 80% of queries were aggregations and only 5% of indexed fields were ever used, which is a column store’s ideal workload. A single ClickHouse node ingested about 300,000 logs per second, roughly ten times an Elasticsearch node, and hardware cost fell by more than half.
  • Google’s Dremel, the engine behind BigQuery, has run since 2006. Its 2020 retrospective reports that about half of queries return less than 1% of the data and 15% return nothing at all, which is why skipping data cheaply matters so much. Reordering rows to lengthen RLE runs saved 17% of storage on average across 40 datasets, and up to 75% on one.
  • Netflix built Iceberg because its Hive tables on S3 couldn’t give consistent reads or safe concurrent writes. Snowflake, BigQuery and Athena now read and write Iceberg tables, and it is the native format of S3 Tables.

Pitfalls

  • SELECT * on a column store. It throws away the main advantage. Name the columns you need.
  • Single-row inserts into an analytic database. They create one part each, and merges fall behind. Batch, or use the engine’s async or buffered insert path.
  • A sort key nobody filters on. Skipping only helps on leading sort columns. Choose the key from real query filters, low-cardinality columns first; a random or high-cardinality leading key kills both skipping and compression.
  • The small-files problem. Thousands of tiny Parquet files mean thousands of footers to read and almost no row-group skipping. Lakehouse tables need regular compaction into files of hundreds of megabytes.
  • Over-partitioning. Partitioning a table by a high-cardinality column (user ID, minute) multiplies files and metadata. In ClickHouse it multiplies parts too, which brings back “Too many parts”.
  • Treating the warehouse as fresh. It lags the source. Know the lag, and don’t serve user-facing reads from it that assume read-your-writes.
  • Benchmarking on TPC-H alone. Real fleets are dominated by small, repetitive queries and a lot of loading; measure your own workload.

Recall

Go deeper

Keep going

The atomic notes for this lesson live in the knowledge graph:

Next on the map: Batch Processing (the jobs that produce and consume these files at scale) and Encoding & Schema Evolution (how row-oriented formats like Avro and Protobuf handle changing schemas).

Done with this lesson?

Mark it learned and the concepts it teaches will light up in your galaxy.

Storage: roadmapBack to the roadmap