ClickHouse
ClickHouse is a columnar OLAP (online analytical processing) database built for one thing: running analytical queries — aggregations, filters, group-bys — over enormous datasets, fast. It ingests millions of rows per second and answers aggregations across billions of rows in well under a second.
It is not a transactional (OLTP) database. ClickHouse is where you put events, logs, and metrics to analyze them; your application's source-of-truth data stays in a transactional store like Postgres. Treating it like a relational OLTP database is the number-one mistake.
TL;DR
- For analytics (OLAP), not transactional workloads (OLTP).
- Columnar storage + compression: reads only the columns a query needs.
- Model data as append-only event tables.
- Partitioning and ordering choices dominate query performance.
Quick Example
A MergeTree table ordered for time-range queries, then a fast aggregation over it:
Core Concepts
- Columnar storage — values for each column are stored together, so analytical scans read only what they need.
- Compression — similar values compress well, slashing storage and I/O.
- MergeTree engines — the workhorse table family; the
ORDER BY(sorting) key drives scan speed. - Append-heavy model — built for high-volume inserts, not row-by-row updates.
Common Uses
- Product and clickstream analytics.
- Log and observability analytics.
- Time-series and event aggregations.
- Real-time dashboards over high-volume data.
Best Practices
- Pick an
ORDER BY(sorting) key that matches how you filter and aggregate. - Partition by time (e.g. by month) to enable cheap retention via partition drops.
- Manage retention with TTL and partition drops; avoid keeping raw data forever.
- Watch dimension cardinality — very high-cardinality group-bys are expensive.
Comparison: OLAP vs OLTP
See PostgreSQL and TimescaleDB.
Common Mistakes
Using ClickHouse as an OLTP database
Poor ordering/partitioning
FAQ
What's the difference between OLAP and OLTP?
OLTP (transactional) systems handle many small reads/writes for application state, with row storage and ACID. OLAP systems like ClickHouse handle large analytical scans and aggregations, using columnar storage optimized for reading many rows of a few columns. They're complementary, not interchangeable.
When should I use ClickHouse?
When you need fast analytics over large, append-heavy data — product analytics, logs, metrics, and real-time dashboards. If you need transactions, point updates, or to serve application state, use a transactional database instead.
Why is the ORDER BY key so important?
In a MergeTree table, data is physically sorted by the ORDER BY key, so queries that filter or aggregate along it can skip most of the data. A key aligned with your query patterns is the difference between scanning a slice and scanning everything.
ClickHouse or TimescaleDB?
ClickHouse for maximum analytical throughput over massive event data, as a separate analytics store. TimescaleDB for time-series when you want to stay in PostgreSQL with full SQL and Postgres tooling. Choose by scale and whether you want a dedicated OLAP system or Postgres-native time-series.
Related Topics
- Databases — Choosing a data store
- TimescaleDB — Postgres-native time-series
- Data Engineering — Feeding analytical stores
- System Design — Analytics architecture
- Elasticsearch — Search/analytics alternative