Notes
What it is
A column store keeps each column of a table in its own file instead of keeping whole rows together. It is built for analytics, sometimes called OLAP: few huge scans and aggregations rather than many small lookups. ClickHouse is the popular open-source one. Warehouses like BigQuery, Redshift and Snowflake use the same idea, and time-series databases like Prometheus specialise it for metrics.
- column-oriented analytical
- (OLAP) database
- ClickHouse, Apache Druid,
- Pinot, BigQuery, Redshift,
- Snowflake, DuckDB
- time-series: Prometheus,
- InfluxDB, TimescaleDB,
- VictoriaMetrics
Rows vs columns
Here is why it wins. This query touches three columns of a fifty-column table. A row store must read every whole row from disk, all fifty columns, to pick out three. A column store reads just the day, country and amount files. On a wide table that is often ten to a hundred times less data. And it gets better, because a file of one column compresses extremely well.
SELECT country, sum(amount)
FROM orders WHERE day >= today()-90
GROUP BY country
row store: read all 50 columns
of every row
column store: read 3 columns:
day, country, amount
→ often 10-100x less I/O Compression
Values in one column look alike: the same few country codes, timestamps a second apart, prices in a narrow range. So they compress hugely, typically five to ten times, using dictionaries, delta encoding for timestamps and special float encodings. Less data means less disk and memory traffic. Then the engine processes values in blocks of thousands at once, using the CPU's vector instructions, instead of one row at a time.
- one column = similar values
- country: IN, IN, IN, US...
- → dictionary + LZ4 / ZSTD
- timestamps: delta encoding
- floats: Gorilla encoding
- typical: 5-10x smaller
- vectorised execution: run
- each operation on blocks of
- thousands of values (SIMD)
MergeTree
In ClickHouse, the main table engine is MergeTree. Every insert writes a new immutable part, sorted by the table's order by key, and background merges combine small parts into big ones, much like an LSM tree. Partition by month so old months can be dropped instantly, and set a time to live to expire data. The order by key matters most: put the columns you filter on first.
CREATE TABLE events ( ts DateTime, user_id UInt64, country LowCardinality(String), event LowCardinality(String), amount Decimal(12,2) ) ENGINE = MergeTree PARTITION BY toYYYYMM(ts) ORDER BY (event, country, ts) TTL ts + INTERVAL 13 MONTH
Sparse index
Because data is sorted by that key, ClickHouse keeps a sparse index: one entry for every eight thousand one hundred and ninety-two rows, small enough to sit in memory even for billions of rows. A filter on the leading key columns binary-searches that index and reads only the matching blocks, skipping the rest. Note that this is not a unique key, and not built for finding one row; it is built for skipping data in big scans.
- rows sorted by ORDER BY key
- stored in granules of 8,192
- sparse primary index: one
- key per granule, fits in RAM
- WHERE event = 'purchase'
- → binary search the index,
- read only matching granules
- skip indexes: min/max, bloom
Inserts
That design gives one firm rule: insert in big batches. Every insert creates a part, so thousands of single-row inserts a second create thousands of parts, merges cannot keep up, and the server starts rejecting writes. Batch tens of thousands to a million rows, roughly once a second per table. In practice you put Kafka in front and let a consumer or the built-in Kafka engine insert in batches, or turn on asynchronous inserts.
- each INSERT = a new part
- thousands of tiny inserts
- per second → "too many
- parts" errors, merges fall
- behind
- batch: 10k-1M rows, about
- once per second per table
- via Kafka engine, a buffer
- or async_insert
Rollups
To make dashboards instant, pre-aggregate. A materialized view in ClickHouse runs on every inserted batch and writes summaries, like revenue per country per hour, into a summing table that merges matching rows. Ten billion raw events become a couple of million rollup rows, and the dashboard reads those. Keep the raw table for drill-downs and ad-hoc questions.
CREATE MATERIALIZED VIEW rev_hourly ENGINE = SummingMergeTree ORDER BY (country, hour) AS SELECT country, toStartOfHour(ts) AS hour, sum(amount) AS revenue FROM events GROUP BY country, hour raw: 10 B rows → rollup: 2 M
Scaling
To scale, first scale up; one large server scans hundreds of millions of rows a second. Then replicate for safety: replicated tables copy parts between replicas, coordinated by ClickHouse Keeper, a Raft-based service compatible with ZooKeeper. And shard for size: a distributed table sends each query to every shard, each computes partial aggregates on its own data, and the results are merged. Since every shard scans in parallel, more shards means faster queries.
- scale up first: one server
- scans 100s of millions rows/s
- ReplicatedMergeTree: copies
- parts between replicas,
- coordinated by Keeper
- (Raft, ZooKeeper-compatible)
- Distributed table: fans the
- query to every shard, each
- aggregates, then merge
Time-series DBs
Time-series databases are column stores specialised for metrics. A series is a metric name plus labels, like requests for the cart service with status five hundred in one region, and it holds a sequence of timestamp and value samples. Prometheus scrapes servers on an interval, commonly fifteen seconds, and packs samples into two-hour blocks with clever encodings, around one to two bytes per sample. Old data is deleted after a retention period; long-term stores can downsample it.
series:
http_requests_total{
service="cart", status="500",
region="ap-south-1"}
samples: (t, value), (t, value)...
storage: 2 h blocks, delta-of-
delta timestamps, XOR floats
→ ~1-2 bytes per sample
old data dropped by retention Cardinality
The number one time-series mistake is cardinality. Every unique combination of label values is its own series, with its own memory and index entries. Fifty services times ten statuses times five regions is two and a half thousand series, fine. Add a user id label with ten million values and you have billions of series, and the database falls over. Never put ids, emails or raw URLs in labels; that detail belongs in logs and traces, Concepts episode 37.
- series count = product of
- label value counts
- service(50) x status(10) x
- region(5) = 2,500 series: ok
- add user_id (10 M) →
- 25 billion series: dead
- never label with ids, emails,
- URLs with ids; use logs/traces
Great at
Column stores shine at aggregations over billions of append-only rows: product analytics and funnels, real-time dashboards, metrics and monitoring, log and trace analytics, ad impressions and billing rollups. Compression makes long retention cheap, and ClickHouse speaks SQL, so analysts can use it directly. Fresh data is queryable within seconds of arriving.
- • product analytics, funnels
- • real-time dashboards
- • metrics and monitoring
- • log and trace analytics
- • ad-tech, billing rollups
- • cheap long retention
- thanks to compression
- • SQL that analysts know
Where they hurt
Where they hurt. Fetching one row by id is slower than in an ordinary database. Updates and deletes are expensive, because parts are immutable and must be rewritten; ClickHouse has lighter deletes now, but the model is append-mostly. There are no multi-row transactions or unique constraints, so duplicates must be handled with special engines. Tiny inserts break it, and joins between two huge tables need planning.
- • single-row lookups: slow
- compared to an OLTP DB
- • UPDATE / DELETE rewrite
- whole parts (mutations)
- • no multi-row transactions
- • tiny inserts: too many parts
- • joins of two huge tables
- need care
- • no unique key enforcement
Use it when
Use a column store when queries scan and aggregate millions to trillions of rows of append-mostly data, like events, metrics and logs, and dashboards must answer in under a second. Do not use it as the application's main database. The pattern is two systems: an OLTP database serves the product, and events flow through Kafka into the column store that answers questions about the product. Concepts episode 41 covers batch and stream.
- USE when
- scans + aggregates over
- millions to trillions of rows
- append-mostly events/metrics
- dashboards need sub-second
- AVOID when
- the app reads/writes single
- records (Postgres, DynamoDB)
- frequent updates, transactions
Numbers
Numbers to have ready. A single ClickHouse server scans hundreds of millions to over a billion rows a second for simple aggregations. Compression of five to ten times is typical. The index granule is eight thousand one hundred and ninety-two rows. Insert ten thousand to a million rows per batch, about once a second. Prometheus stores a sample in roughly one to two bytes, scraped every fifteen to sixty seconds.
scan rate (simple agg) 100M-1B+ rows/s
per server
compression 5-10x typical
granule 8,192 rows
insert batch 10k-1M rows,
~1 per second
Prometheus sample ~1-2 bytes
scrape interval 15-60 s
dashboard query goal under 1 s Say it like this
How to say it. Click events go to Kafka, and a consumer inserts them into ClickHouse in batches about once a second. The events table is a MergeTree ordered by event, country and time, partitioned by month, with a thirteen month time to live. A materialized view keeps hourly rollups for the dashboard. Two shards with two replicas each. The product's own data stays in Postgres.
- "Click events go to Kafka,
- then batch-insert into
- ClickHouse every second.
- MergeTree ordered by (event,
- country, ts), partitioned by
- month, 13-month TTL. A
- materialized view keeps hourly
- rollups for the dashboard.
- 2 shards x 2 replicas."