Extremely fast for analytical queries.
Installation¶
docker run -d --name ch -p 8123:8123 -p 9000:9000 clickhouse/clickhouse-server
Table¶
CREATE TABLE events (
event_date Date,
user_id UInt64,
event_type String
) ENGINE=MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type,event_date);
Queries¶
SELECT event_type, count(), uniq(user_id)
FROM events
WHERE event_date>='2024-01-01'
GROUP BY event_type
ORDER BY count() DESC;
- 10-100x faster than PG for analytics
- Columnar format
- 10x compression
- SQL compatible
When to Deploy ClickHouse¶
ClickHouse excels in analytical scenarios where you process billions of rows — typically log analytics, event tracking, real-time dashboards, and ad-hoc reporting. The columnar storage format means queries read only the columns they need, dramatically reducing I/O compared to row-based databases.
Materialized views in ClickHouse allow you to pre-compute aggregations in real time as data is inserted. For example, you can automatically calculate daily metrics without running expensive batch queries. For importing data from PostgreSQL or MySQL, integration via Kafka and Debezium works well. ClickHouse is not suitable for OLTP workloads (frequent single-row updates), but for analytics it is orders of magnitude faster than traditional relational databases.
ClickHouse for Analytics¶
OLTP=PostgreSQL, OLAP=ClickHouse.