Data stores & formats
ClickHouse
Columnar DBMS for analytics on large volumes with extremely fast queries.
What is it
ClickHouse is an open-source columnar DBMS for online analytical processing (OLAP). It stores data by column and compresses it heavily, so aggregate queries over hundreds of millions and billions of rows run in fractions of a second. ClickHouse is built for analytical workloads — fast reads and bulk inserts rather than pinpoint updates of individual records.
Where and why it is used
I use ClickHouse as the core of an analytical warehouse and data marts when dashboard responsiveness on large data matters — for example, for end-to-end analytics, event streams and logs. It works well as a hot layer under BI and as an engine for interactive reports.
Used in my services
Alternatives
Google BigQuerySnowflakeApache DruidAmazon RedshiftPostgreSQL + Citus
Pros and cons
Pros
- Extremely high speed of analytical queries
- Strong data compression — savings on storage
- Scales linearly, runs on-premise and in the cloud
- Open source, no vendor lock-in
Cons
- – Not designed for frequent pinpoint UPDATE/DELETE and transactions
- – Requires a well-thought-out schema and sorting keys
- – JOINs on large tables are weaker than in classic DBMSs
- – Operating a cluster requires expertise
