Index/OLAP stores

SponsorGitHub
Key technologyAnalytics store3 min

OLAP stores

Columnar databases — ClickHouse, Druid, BigQuery — that aggregate billions of rows and cannot do point updates.

At a glance

Storage
By column, not by row: a query reads only the columns it names
Compression
10x is routine — one type per column, mostly sorted
Execution
Vectorised: batches of thousands of values, not row at a time
Indexing
A sort key with a sparse index, one entry per block
Writes
Large appends; a point update rewrites a block
Names
ClickHouse, Druid, Pinot; BigQuery, Snowflake, Redshift

Key concepts and capabilities

The short listwhat it gives you
Columnar storage — a query touching 3 of 50 columns reads 6% of the bytes, and compresses ~10x on the way
Sort key, not index — a sparse index per block; filter on its leading columns or you scan the table
Partition by time — so retention is dropping a partition, not a mass delete
Pre-aggregation — rollup tables and materialized views turn a billion-row scan into a few thousand rows
Approximate counting — HyperLogLog for distincts, t-digest for percentiles; exact COUNT(DISTINCT) is a trap
Denormalised, star-shaped — dimensions copied into fact rows or broadcast as small tables, because joins are dear
Updates are rewrites — corrections and GDPR erasure are partition-level operations
Ingest is streaming or batch — Kafka to a stream processor for seconds, a nightly job to correct what the stream got wrong
Not an application database — one row by id belongs in Postgres, Cassandra or DynamoDB

Use cases

Counting clicks per minute, at a billion a day

The standard pipeline: events land in Kafka, a stream processor windows and pre-aggregates them, and the rollup table is what the dashboard queries. The batch path exists because streams get late and duplicate events wrong, and recomputing a partition is the cheapest way to make the numbers finally correct.

DatabaseObject storageQueue / streamFocusClick a node for details

A rollup for the dashboard, raw rows for the drill-down

Two tables, and knowing which query hits which is the design. The rollup is bounded — a handful of dimensions, one row per minute per combination — and the raw table keeps the high-cardinality columns for the rare query that needs a single user or request.

primary or partition keysort key

Keeping the bill sane as data ages

Retention is a partition operation, not a DELETE. Recent partitions stay on fast disk, older ones are tiered to object storage, and the oldest are dropped whole — which is also the answer to "what does keeping a year of this cost?"

DatabaseObject storageFocusClick a node for details