ClickHouse is an open-source columnar database built for analytics at massive scale: fast aggregation and filtering over very large datasets. ClickHouse may look like a relational database, but it’s engineered for a fundamentally different purpose than Postgres or MySQL, and treating it as a drop-in replacement is the most common way to get poor results.
This post covers what ClickHouse is, the workloads it is designed for, what it is used for, and when to stay with a traditional relational database instead.
What ClickHouse is
ClickHouse is an OLAP (online analytical processing) database. Instead of storing data by row, it stores each column separately. This design allows ClickHouse to:
- Read only the columns a query needs
- Compress each column aggressively
- Scan millions or billions of rows extremely quickly
The result is analytical queries — counts, sums, averages, percentiles, time-series trends — that return in milliseconds even on massive datasets.
It was originally built at Yandex to power Metrica, a large web-analytics platform processing billions of events per day. It was open-sourced in 2016 and is now developed by ClickHouse Inc.
Column-oriented storage is the design decision that drives everything else about ClickHouse. The mechanics — and the habits it changes for anyone coming from a row based database — are a subject of their own. This post stays at the level of what ClickHouse is for.
OLTP vs OLAP: Two different jobs
Relational databases like Postgres and MySQL are OLTP (online transaction processing) systems. They handle an application’s live state: fetch one record, update an order, decrement a counter, etc. The workload is many small reads and writes of individual rows, wrapped in transactions.
ClickHouse is an OLAP system. It answers questions about large volumes of data: totals per region, percentiles per endpoint, trends over time, etc. The workload is fewer queries, each scanning a large range of rows and aggregating a few columns.
| Category | OLTP — Postgres / MySQL | OLAP — ClickHouse |
|---|---|---|
| Typical query | Fetch or update one record | Aggregate across millions of rows |
| Read pattern | A few rows, looked up by key | Large ranges, a few columns |
| Write pattern | Many small row-level inserts and updates | Large batches, append-only |
| Storage layout | Row-oriented | Column-oriented |
| Transactions | ACID, multi-statement | None — throughput over guarantees |
| Sweet spot | Application state | Analytics, reporting, dashboards |
The two are complementary. Most systems run an OLTP database for transactions and an OLAP database such as ClickHouse for analytics, rather than choosing one over the other.
What ClickHouse is used for
ClickHouse fits workloads where the main question is an aggregate over a large, append-mostly dataset. Common use cases include:
- Product, web, and clickstream analytics — page views, events, funnels, retention.
- Observability — storing and querying logs, metrics, and traces at scale.
- Real-time dashboards and business intelligence on top of large tables.
- Time-series and IoT data — sensor readings, application metrics, financial ticks.
- Business and financial reporting over orders, transactions, and usage events.
- Large-scale aggregation behind applications — usage metering, feature stores, ad and marketing analytics.
When to use ClickHouse
ClickHouse is a good choice when most of the following are true:
- The dataset is large and growing — tens of millions to billions of rows
- The data is mostly append-only.
- Queries aggregate, filter, and group over many rows rather than fetching individual records.
- Data can be inserted in batches, or a stream can be buffered into batches before it is written.
- Analytical results are needed in milliseconds to seconds
- The data is events, logs, metrics, clickstream, or time series.
When not to use ClickHouse
ClickHouse is the wrong choice for transactional workloads. Use Postgres or MySQL when:
- Individual rows are updated or deleted frequently, such as an order status, a user profile, or an inventory count. ClickHouse treats updates and deletes as rare, heavy operations.
- The workload looks up single records by key
- Multi-row ACID transactions are required, or the schema depends on enforced uniqueness and foreign keys.
- The dataset is small — a few million rows — where a well-indexed relational database is simpler and fast enough.
Many systems use both: a relational database as the transactional source of truth, with data streamed into ClickHouse for analytics. This arrangement — running the two side by side — is known as the two-database pattern.
Try ClickHouse in Docker
ClickHouse runs in a single Docker container with no configuration. Start the server and open a client:
docker run -d --name clickhouse-playground \
--ulimit nofile=262144:262144 \
clickhouse/clickhouse-server
docker exec -it clickhouse-playground clickhouse-clientClickHouse includes a built-in row generator, numbers(), so query speed can be tested without loading any data. The following query aggregates one billion rows:
SELECT count(), round(avg(number)) AS avg_n, max(number) AS max_n
FROM numbers(1000000000);Code language: PHP (php)On a typical laptop this returns in about 0.3 seconds.
Grouping a billion rows into buckets is similarly fast:
SELECT number % 10 AS bucket, count() AS rows
FROM numbers(1000000000)
GROUP BY bucket
ORDER BY bucket;Code language: PHP (php)Both queries run with no indexes and no tuning.
Remove the container when finished:
docker rm -f clickhouse-playgroundThat is the core of ClickHouse in a single container — no indexes, no tuning, and analytical queries over a billion rows in well under a second. Whether it fits a given workload comes down to the trade-offs above: an OLAP engine for large, append-mostly analytical data, not a replacement for a transactional database.