Set Up ClickHouse with Docker Compose

ClickHouse 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.

1. Define a Lightweight docker-compose.yaml

Save the following as docker-compose.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:

docker-compose up -d

Test connectivity with a quick SQL check:

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.

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:

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:

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.

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:

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:

<clickhouse>
    <listen_host>0.0.0.0</listen_host>
    <max_connections>200</max_connections>
    <max_concurrent_queries>50</max_concurrent_queries>

    <!-- Memory settings for 8GB container -->
    <max_memory_usage>6000000000</max_memory_usage>
    <max_bytes_before_external_group_by>4000000000</max_bytes_before_external_group_by>
    <max_bytes_before_external_sort>4000000000</max_bytes_before_external_sort>
</clickhouse>

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:

<clickhouse>
    <users>
        <default>
            <password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex>
            <networks>
                <ip>127.0.0.1</ip>
                <ip>10.0.0.0/8</ip>
                <ip>172.16.0.0/12</ip>
                <ip>192.168.0.0/16</ip>
            </networks>
            <profile>default</profile>
        </default>
    </users>

    <profiles>
        <default>
            <max_memory_usage>6000000000</max_memory_usage>
            <max_execution_time>300</max_execution_time>
        </default>
    </profiles>
</clickhouse>

The SHA256 hash above corresponds to the password "hello123". Generate your own using:

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.

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.

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.

<clickhouse>
    <remote_servers>
        <analytics_cluster>
            <shard>
                <replica>
                    <host>clickhouse-01</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard>
                <replica>
                    <host>clickhouse-02</host>
                    <port>9000</port>
                </replica>
            </shard>
        </analytics_cluster>
    </remote_servers>

    <zookeeper>
        <node>
            <host>zookeeper</host>
            <port>2181</port>
        </node>
    </zookeeper>
</clickhouse>

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.

-- 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.

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:

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:

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

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

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

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

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.

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:

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 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 or ClickHouse Keeper with StatefulSets.

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.