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.
The whole idea
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 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.
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
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
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.
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
LowCardinalitytype; 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
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
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
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
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_SPLITand 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
- 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_logdirectory: 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.
- 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
- Stonebraker et al., “C-Store: A Column-oriented DBMS” (VLDB 2005): projections, multiple sort orders, and the writeable/read-optimised store split.
- Boncz, Zukowski & Nes, “MonetDB/X100: Hyper-Pipelining Query Execution” (CIDR 2005): why vectorized execution beats row-at-a-time by orders of magnitude.
- Melnik et al., “Dremel: Interactive Analysis of Web-Scale Datasets” (VLDB 2010): nested columnar storage with repetition and definition levels, and the 87 TB vs 0.5 TB result.
- Apache Parquet format specification: file layout, encodings, page index and Bloom filters, straight from the source.
- Schulze et al., “ClickHouse: Lightning Fast Analytics for Everyone” (VLDB 2024): MergeTree parts, sparse indexes, codecs and vectorized execution in 14 pages.
- van Renen et al., “Why TPC Is Not Enough: An Analysis of the Amazon Redshift Fleet” (VLDB 2024): what real warehouse workloads look like, and how they differ from benchmarks.
- Armbrust et al., “Lakehouse” (CIDR 2021) and the Delta Lake paper (VLDB 2020): the argument for one tier, and a commit protocol built on object storage.
- Apache Iceberg table spec: snapshots, manifests, hidden partitioning and format v3.
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).