Databases
ClickHouse
A columnar database for fast analytics (OLAP) over billions of rows.
What it is
ClickHouse was built at Yandex for web analytics, open sourced in 2016 and later spun into its own company. Data is stored by column and compressed heavily, so queries that read a few columns run very fast.
The main MergeTree engine inserts data in batches and merges parts in the background. It uses an SQL dialect with many analytical functions. It scales through sharding and replication.
It is not a database for frequent single-row updates and not a replacement for a transactional database. Typical uses: logs, events, metrics, product analytics.
Use it when
- Analytics over billions of rows with answers in seconds.
- A store for events, logs and metrics.
- Dashboards and aggregations over data streams.
Avoid it when
- Transactional apps with frequent row updates and deletes.
- Point lookups by key as the main model: Redis or PostgreSQL.
Key concepts
- Columnar storage
- Data by column: only needed ones are read.
- MergeTree
- A family of engines with sorting, partitions and background merging.
- Order by key
- The sort key drives filter speed.
- Materialized view
- Pre-aggregation at insert time.
Example
Interview questions
1. Why is ClickHouse fast for analytics?
Columnar storage, compression and vectorized execution read only needed columns in large blocks. The sort key lets it skip irrelevant granules.
2. How do you update data in ClickHouse?
Updates and deletes are heavy because data sits in immutable parts. More often you insert new versions (ReplacingMergeTree) or rebuild partitions.
3. Why insert data in batches?
Each insert creates a part that is merged later. Many tiny inserts overload merging, so insert in batches of thousands of rows or more.
Interview questions from the bank
Related technologies
- PostgreSQLA powerful open source relational database with extensions, JSONB, full-text search and strong SQL support.
- Apache KafkaKafka is a distributed event log for high-throughput data streaming with durable storage.
- ElasticsearchElasticsearch is a distributed search and analytics engine built on Lucene with a REST API and JSON documents.
- Apache CassandraA distributed wide-column database with no single point of failure, built for massive write volumes.
- GrafanaA visualization platform: dashboards and alerts on top of Prometheus, Loki, SQL databases and dozens of other sources.
Logos belong to their owners and are used for identification only. Icons: Simple Icons (CC0), Devicon (MIT).