# Set Up ClickHouse with Docker Compose
> Spin up a full ClickHouse stack with Docker Compose, includes clustering, ZooKeeper, monitoring, and performance tuning tips.
Source: https://last9.io/blog/set-up-clickhouse-with-docker-compose/
[ClickHouse](https://clickhouse.com/) is built for high-performance OLAP workloads, capable of scanning billions of rows in seconds. If your analytical queries are bottlenecked on PostgreSQL or MySQL, or you're burning too much on Elasticsearch infrastructure, ClickHouse offers a faster and more cost-efficient alternative.
This blog walks through setting up ClickHouse locally with Docker Compose and scaling toward a production-grade cluster with monitoring in place.
## 30-Second ClickHouse Setup
Spin up ClickHouse locally using Docker, no manual install required. Ideal for testing queries, modeling data, or simulating workloads. When you start modeling real tables, column types drive storage and query speed: see [ClickHouse LowCardinality: when it helps and when it hurts](https://last9.io/blog/clickhouse-lowcardinality/).
#### 1\. Define a Lightweight `docker-compose.yaml`
Save the following as `docker-compose.yaml`:
```yaml
version: "3.8"
services:
clickhouse:
image: clickhouse/clickhouse-server:latest
ports:
- "8123:8123"
```
This pulls the latest ClickHouse image and exposes port 8123 for HTTP queries.
#### 2\. Start the ClickHouse Container
Start the container using:
```bash
docker-compose up -d
```
Test connectivity with a quick SQL check:
```bash
curl "http://localhost:8123" -d "SELECT 'ClickHouse is operational'"
```
You should see a plain-text response confirming it’s running.
#### 3\. Create a Simple Table for Event Data
Define a schema suited for analytics use cases.
```bash
curl "http://localhost:8123" -d "
CREATE TABLE page_views (
timestamp DateTime,
user_id UInt32,
page String,
duration UInt16
) ENGINE = MergeTree()
ORDER BY timestamp"
```
This creates a `page_views` table sorted by timestamp, optimized for time-based queries.
#### 4\. Insert Sample Data
Insert a few synthetic rows to simulate page view events:
```bash
curl "http://localhost:8123" -d "
INSERT INTO page_views VALUES
('2025-01-01 10:00:00', 1001, '/home', 30),
('2025-01-01 10:01:00', 1002, '/product', 120),
('2025-01-01 10:02:00', 1001, '/checkout', 230)"
```
#### 5\. Run a Time-Series Aggregation Query
Query page views by hourly bucket:
```bash
curl "http://localhost:8123" -d "
SELECT
toStartOfHour(timestamp) AS hour,
count() AS views,
avg(duration) AS avg_duration
FROM page_views
GROUP BY hour
ORDER BY hour"
```
This returns view counts and average duration per hour, exactly the kind of workload ClickHouse is built for.
If you're running ClickHouse in Docker, here's [how to collect logs from your containers](https://last9.io/blog/how-to-get-logs-from-docker-containers/) for better debugging and monitoring.
## When ClickHouse Is the Right Fit
ClickHouse is built for high-throughput, analytical workloads that overwhelm traditional systems like PostgreSQL, MySQL, or even Elasticsearch. Here’s where it shines:
### Log and Trace Analysis at Scale
Handling hundreds of gigabytes of logs per day? ClickHouse can outperform Elasticsearch by 10x–100x on aggregation-heavy queries. Ideal for scenarios where cardinality and volume are both high.
### Large-Scale Event Analytics
ClickHouse powers real-time analytics pipelines at companies like Uber and Cloudflare. Whether it’s tracking billions of user events or computing behavioral funnels in near real-time, the performance gains are often game-changing.
### Time-Series from IoT Devices
Thousands of sensors generating timestamped data? ClickHouse’s columnar engine and compression techniques reduce storage footprint by 10–20x compared to row-based databases, while enabling fast downsampling and rollups.
### Financial and Risk Analytics
ClickHouse handles workloads where millisecond-level performance is critical, fraud detection, market analysis, or real-time portfolio metrics. Its support for vectorized execution makes previously impractical queries feasible.
## Set Up a Production-Ready Single Node
Once you've validated that ClickHouse fits your workload, it's time to configure a local single-node instance that can handle sustained query loads with persistence and basic access control.
Start with the following `docker-compose.yml`:
```yaml
version: "3.8"
services:
clickhouse:
image: clickhouse/clickhouse-server:24.1
container_name: clickhouse-server
ports:
- "8123:8123"
- "9000:9000"
volumes:
- clickhouse_data:/var/lib/clickhouse
- ./config/config.xml:/etc/clickhouse-server/config.d/docker.xml
- ./config/users.xml:/etc/clickhouse-server/users.d/docker.xml
environment:
CLICKHOUSE_DB: analytics
ulimits:
nofile:
soft: 262144
hard: 262144
deploy:
resources:
limits:
memory: 8G
reservations:
memory: 4G
volumes:
clickhouse_data:
```
The `ulimits` section is essential. ClickHouse opens thousands of files concurrently during query execution. Without raising the file descriptor limit, you'll run into errors under concurrent workloads.
### Tune Memory and Concurrency Settings
Create `config/config.xml` to tune key memory and concurrency settings:
```xml
0.0.0.0
200
50
6000000000
4000000000
4000000000
```
These `max_bytes_before_external_*` parameters control when ClickHouse spills intermediate results to disk. Set them to around 60–70% of your container's available memory. If they're too low, you'll incur unnecessary disk I/O; too high, and queries may crash due to memory exhaustion.
### Add Basic Access Controls
Create `config/users.xml` to add password protection and network restrictions:
```xml
65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5
127.0.0.1
10.0.0.0/8
172.16.0.0/12
192.168.0.0/16
default
6000000000
300
```
The SHA256 hash above corresponds to the password `"hello123"`. Generate your own using:
```bash
echo -n 'your_password' | sha256sum
```
The network block ensures only requests from private subnets or localhost are allowed, adding a basic layer of security for local or controlled environments.
To understand what happens before and after ClickHouse starts up in Docker, read [this blog on the Docker container lifecycle](https://last9.io/blog/docker-container-lifecycle/).
## Multi-Node ClickHouse Cluster for High Volume Workloads
A single-node ClickHouse instance works well until you hit limits on storage, memory, or availability. At that point, moving to a clustered deployment becomes essential.
### Decide When to Use a Cluster
Clustering is required when:
- **Storage capacity**: The dataset no longer fits on a single node.
- **Query throughput**: CPU and memory on one machine aren't sufficient.
- **Fault tolerance**: Redundancy is needed to avoid single points of failure.
ClickHouse clusters use ZooKeeper to coordinate metadata across nodes.
### Configure a Two-Shard ClickHouse Cluster with Docker Compose
The following setup provisions a ZooKeeper instance and two ClickHouse nodes. Each node acts as a shard.
```yaml
version: "3.8"
services:
zookeeper:
image: zookeeper:3.9
container_name: zookeeper
ports:
- "2181:2181"
environment:
ZOO_MY_ID: 1
ZOO_SERVERS: server.1=zookeeper:2888:3888;2181
volumes:
- zookeeper_data:/data
clickhouse-01:
image: clickhouse/clickhouse-server:24.1
container_name: clickhouse-01
ports:
- "8123:8123"
- "9000:9000"
volumes:
- clickhouse_01_data:/var/lib/clickhouse
- ./config/cluster.xml:/etc/clickhouse-server/config.d/cluster.xml
depends_on:
- zookeeper
ulimits:
nofile:
soft: 262144
hard: 262144
clickhouse-02:
image: clickhouse/clickhouse-server:24.1
container_name: clickhouse-02
ports:
- "8124:8123"
- "9001:9000"
volumes:
- clickhouse_02_data:/var/lib/clickhouse
- ./config/cluster.xml:/etc/clickhouse-server/config.d/cluster.xml
depends_on:
- zookeeper
ulimits:
nofile:
soft: 262144
hard: 262144
volumes:
zookeeper_data:
clickhouse_01_data:
clickhouse_02_data:
```
### Define Cluster Topology in `cluster.xml`
The following config maps each ClickHouse instance to a shard and connects to ZooKeeper.
```xml
clickhouse-01
9000
clickhouse-02
9000
zookeeper
2181
```
Each shard contains one replica in this setup. You can extend this by adding more replicas per shard or more shards to scale horizontally.
### Create Distributed Tables Across Shards
Define a local table on each node and a distributed table that queries across them.
```sql
-- Local table on each shard
CREATE TABLE events_local (
timestamp DateTime,
user_id UInt32,
event_type String
) ENGINE = MergeTree()
ORDER BY timestamp;
-- Distributed table referencing both shards
CREATE TABLE events AS events_local
ENGINE = Distributed(analytics_cluster, default, events_local, rand());
```
Once created, you can insert data into the `events` table. ClickHouse automatically routes inserts to the appropriate shard using the `rand()` sharding key.
## Monitor ClickHouse at Scale
ClickHouse surfaces detailed metrics about queries, merges, memory usage, and more. But left unchecked, this data quickly snowballs into millions of high-cardinality series.
> ClickHouse’s performance at scale is a core reason we use it at Last9. We run complex analytical queries on live telemetry data, with no pre-aggregation or sampling required. If you’re curious how we built this, [Aditya shares the technical details here](https://clickhouse.com/blog/last9-clickhouse-delivering-seamless-observability-minus-the-chaos).
To keep your observability setup manageable (and cost-efficient), pair ClickHouse with Prometheus and an exporter. With native OpenTelemetry and Prometheus support, Last9 makes it easier to monitor ClickHouse at production scale.
Here’s how to extend your local ClickHouse setup to include monitoring:
### Add Prometheus and Exporter to docker-compose
Update your `docker-compose.yml` file with Prometheus and a ClickHouse metrics exporter:
```yaml
services:
prometheus:
image: prom/prometheus:latest
ports:
- "9090:9090"
volumes:
- ./monitoring/prometheus.yml:/etc/prometheus/prometheus.yml
- prometheus_data:/prometheus
command:
- "--config.file=/etc/prometheus/prometheus.yml"
- "--storage.tsdb.path=/prometheus"
- "--web.enable-lifecycle"
clickhouse-exporter:
image: f1yegor/clickhouse-exporter:latest
ports:
- "9116:9116"
environment:
CLICKHOUSE_URL: http://clickhouse-01:8123
CLICKHOUSE_USER: default
CLICKHOUSE_PASSWORD: hello123
depends_on:
- clickhouse-01
volumes:
prometheus_data:
```
### Configure Prometheus to Scrape ClickHouse Metrics
**Create or update `monitoring/prometheus.yml`:**
```yaml
global:
scrape_interval: 15s
scrape_configs:
- job_name: "clickhouse"
static_configs:
- targets: ["clickhouse-exporter:9116"]
scrape_interval: 10s
```
This configuration scrapes metrics every 10 seconds from the exporter.
### Query System Tables to Debug Performance
Once metrics collection is in place, use ClickHouse’s system tables to inspect query-level behavior. These queries can be run directly via SQL to spot trouble early:
### Analyze Slow Queries
```sql
SELECT
query_duration_ms,
memory_usage,
read_rows,
substring(query, 1, 100) AS query_preview
FROM system.query_log
WHERE query_duration_ms > 10000
ORDER BY query_duration_ms DESC
LIMIT 10;
```
- Flags queries that exceed 10 seconds.
- Useful for identifying slow joins, large scans, or suboptimal filters.
**Monitor Memory Usage Patterns**
```sql
SELECT
toStartOfMinute(event_time) AS minute,
max(memory_usage) AS peak_memory,
count() AS query_count
FROM system.query_log
WHERE event_date = today()
GROUP BY minute
ORDER BY minute DESC
LIMIT 20;
```
- Highlights traffic spikes.
- Useful when tracking memory leaks or sudden cost jumps in compute.
**Inspect Merge Operations**
```sql
SELECT
database,
table,
elapsed,
progress,
num_parts
FROM system.merges
WHERE is_mutation = 0;
```
- Reveals long-running merge operations.
- A rising `num_parts` count without merges finishing is a red flag — expect degraded read performance.
### Connect to a Scalable Backend
To avoid hitting local Prometheus retention or scrape limits, forward these metrics to a telemetry data platform - Last9. With native support for high-cardinality ClickHouse metrics, our platform helps:
- Avoid scrape overloads during spikes
- Monitor query patterns across clusters
- Keep cost visibility tied to actual resource usage
Now, troubleshoot ClickHouse performance issues faster using real-time production context from [Last9 MCP](https://last9.io/mcp/). Access logs, metrics, and traces right from your IDE, so you can identify slow queries, memory spikes, or merge lag without guessing.
## Common Operational Issues and Fixes
Misconfigured resource limits, unoptimized queries, or missing dependencies can cause container failures or degraded ClickHouse performance. Here are typical failure points and how to address them.
| Symptom | Root cause | First fix |
| ------------------------------------- | ------------------------------------------------------------------------------------------------ | ----------------------------------------------------------------------------------- |
| Container fails to start | Not enough memory; ClickHouse needs at least 2 GB of RAM to initialize its allocator and buffers | Raise the memory limit in `docker-compose.yml` or Docker Desktop |
| Queries consistently time out | Queries exceed memory thresholds but are not allowed to spill to disk | Raise `max_memory_usage` and enable external sort and group-by |
| ZooKeeper fails to connect | ClickHouse starts before ZooKeeper is available, or container networking is misconfigured | Add `depends_on`, plus a health check or retry loop |
| CPU stays high | Full table scans, missing primary key filters, or unordered merges | Audit heavy queries in `system.query_log` and align primary keys with query filters |
| Inserts are slow or saturate disk I/O | Frequent small batches create merge pressure and disk thrashing | Batch inserts into 10,000–50,000 rows and watch `system.merges` |
Each row is expanded below.
#### ClickHouse container fails to start
- **Root cause:** Insufficient memory allocation. ClickHouse requires at least 2 GB of RAM to initialize its memory allocator and buffers.
- **Resolution:**
- Increase memory limits in your Docker configuration (`docker-compose.yml` or Docker Desktop settings).
- If running in production, verify container runtime settings (`ulimits`, cgroup limits, etc.).
- Look for errors, like `DB::Exception: Memory limit (total) exceeded` in container logs.
#### Queries consistently time out
- **Root cause:** Query operations exceed memory thresholds or require disk-based execution but are not allowed to spill.
- **Resolution:**
- Review and raise `max_memory_usage` in your ClickHouse config.
- Use `optimize_read_in_order` for better use of indexes and sorted data.
Enable external sort and group-by operations:
```yaml
max_bytes_before_external_sort: 1000000000
max_bytes_before_external_group_by: 1000000000
```
#### ZooKeeper fails to connect
- **Root cause:** ClickHouse attempts to connect before [ZooKeeper](https://zookeeper.apache.org/) is available, or container networking is misconfigured.
- **Resolution:**
- Add `depends_on` to ensure ZooKeeper starts before ClickHouse nodes.
- Use health checks or retry loops to delay startup until _ZooKeeper_ is reachable.
- Confirm network connectivity between containers (check Docker bridge settings or Kubernetes service discovery).
#### CPU usage is consistently high
- **Root cause:** Inefficient query patterns, typically full table scans, missing primary key filters, or unordered merges.
- **Resolution:**
- Audit heavy queries using `system.query_log`.
- Ensure tables use sorted primary keys aligned with common query filters.
- Add `ORDER BY` and use sampling keys where appropriate to reduce scan costs.
#### Inserts are slow or saturate disk I/O
- **Root cause:** Inserting small batches frequently leads to high merge pressure and disk thrashing.
- **Resolution:**
- Batch inserts into chunks of 10,000–50,000 rows.
- Use `INSERT INTO ... SELECT` when transforming data instead of row-by-row inserts.
- Monitor the `system.merges` table to identify merge backlog or stalls.
This configuration supports most production use cases. For autoscaling, shard discovery, and fault tolerance in larger environments, look into Kubernetes operators like [Altinity Operator](https://github.com/Altinity/clickhouse-operator) or [ClickHouse Keeper](https://clickhouse.com/docs/guides/sre/keeper/clickhouse-keeper) with StatefulSets.
And if you still need help troubleshooting configs, tuning performance, or setting up observability, [join our Discor](https://discord.com/invite/W8gMppQC4b)d community. There's a dedicated channel for in-depth technical discussions with other engineers.
## FAQs
**How much data can ClickHouse handle in Docker?**
Single-node setups can handle multiple terabytes with fast local disks. Clusters can scale to petabytes. Disk I/O and storage bandwidth are usually the bottlenecks, not ClickHouse itself.
**Should I use ClickHouse as my application database?**
No. ClickHouse is purpose-built for analytics, not transactional workloads. It lacks full ACID compliance, updates are costly, and it's optimized for fast reads, not frequent writes.
**How does ClickHouse compare to Elasticsearch for logs?**
ClickHouse is typically 5–10x faster for aggregations and uses 3–5x less storage. Elasticsearch is better for full-text search. Use ClickHouse for metrics and analytics; Elasticsearch for rich search experiences.
**Can I run ClickHouse clusters across different servers?**
Yes, but Docker Compose isn’t suitable for multi-host clusters. Use Docker Swarm, Nomad, or Kubernetes for production-grade, distributed deployments.
**What's the best way to back up ClickHouse data?**
Use ClickHouse’s native `BACKUP` command or take filesystem-level snapshots of the data directory. For clusters, coordinate backups across replicas to avoid data inconsistency.
**How do I migrate data from PostgreSQL to ClickHouse?**
You can use the PostgreSQL table engine for live reads, or export the data to CSV/Parquet and perform bulk imports. Choose based on volume, consistency requirements, and acceptable downtime.