ClickHouse on Docker: Setup and High-Performance OLAP Query Optimization

Quick Start (5 min): Spin Up ClickHouse with Docker Compose

Running a standard aggregation like COUNT(DISTINCT user_id) across 50 million rows in PostgreSQL or MySQL often takes 20 to 40 seconds while pinning your CPU at 100%. ClickHouse handles the same query in under 15 milliseconds. It achieves this speed by storing data in columns instead of rows, compressing similar data types together by up to 90%, and scanning only the specific columns requested by your queries.

To get started, create a project directory and a docker-compose.yml file:

mkdir clickhouse-docker && cd clickhouse-docker
touch docker-compose.yml

Add the following configuration to docker-compose.yml. This setup exposes port 8123 for HTTP traffic (REST clients, Grafana, and web dashboards) and port 9000 for native TCP connections:

version: '3.8'

services:
  clickhouse:
    image: clickhouse/clickhouse-server:24.3
    container_name: clickhouse-server
    ports:
      - "8123:8123"
      - "9000:9000"
    environment:
      CLICKHOUSE_DB: analytics
      CLICKHOUSE_USER: analyst
      CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT: 1
      CLICKHOUSE_PASSWORD: StrongPassword123!
    volumes:
      - ./clickhouse_data:/var/lib/clickhouse/
      - ./clickhouse_logs:/var/log/clickhouse-server/
    ulimits:
      nofile:
        soft: 262144
        hard: 262144
    restart: unless-stopped

Start the container in detached mode:

docker compose up -d

Once the container is healthy, connect to the interactive client session:

docker exec -it clickhouse-server clickhouse-client -u analyst --password StrongPassword123!

Test the installation by checking the engine version and active database:

SELECT version(), currentDatabase();

Deep Dive: Table Engines and Data Ingestion

ClickHouse relies on specialized storage engines. The MergeTree family powers nearly every production analytical setup. When data arrives, ClickHouse writes sorted data parts to disk and merges them asynchronously in the background to ensure compact storage and fast index scans.

Designing a MergeTree Schema

Consider an event table tracking user interactions on a web platform. The sorting key aligns directly with the most common query filters:

CREATE TABLE analytics.page_views (
    event_date Date,
    event_timestamp DateTime,
    user_id UInt64,
    url LowCardinality(String),
    referrer LowCardinality(String),
    device_type LowCardinality(String),
    country LowCardinality(FixedString(2)),
    duration_seconds UInt16
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, country, user_id)
SETTINGS index_granularity = 8192;

Key Architectural Choices

• LowCardinality(T): Replaces repetitive strings with compact numeric dictionary keys. For fields with under 10,000 unique values (such as country, device_type, or HTTP status codes), this cuts disk usage and memory consumption by 60% to 80%.
• PARTITION BY: Isolates records into discrete monthly directories on disk (e.g., 202610_1_1_0). When a query filters for event_date >= '2026-10-01', ClickHouse skips older monthly folders entirely without reading their bytes. Avoid partitioning by hour or day, as having more than 1,000 total partitions degrades merge performance.
• ORDER BY: Constructs a sparse primary index. ClickHouse writes an index mark every 8,192 rows (the default index_granularity). Because the entire index for 100 million rows requires only a few megabytes of RAM, range scans locate matching blocks in fractions of a millisecond.

Importing Data via HTTP and Native Tools

ClickHouse natively parses CSV, TSV, Parquet, and JSON formats. When preparing ad-hoc CSV files for JSON streaming, you can convert them locally using toolcraft.app/en/tools/data/csv-to-json. It runs client-side in your browser, keeping sensitive operational data on your machine.

To stream newline-delimited JSON directly into ClickHouse using curl:

curl -s -u "analyst:StrongPassword123!" -X POST "http://localhost:8123/?query=INSERT+INTO+analytics.page_views+FORMAT+JSONEachRow" \
  --data-binary '{"event_date":"2026-10-01","event_timestamp":"2026-10-01 10:00:00","user_id":1024,"url":"/pricing","referrer":"google.com","device_type":"desktop","country":"US","duration_seconds":45}
{"event_date":"2026-10-01","event_timestamp":"2026-10-01 10:05:12","user_id":2048,"url":"/docs","referrer":"direct","device_type":"mobile","country":"VN","duration_seconds":120}'

Advanced Usage: Materialized Views and In-Flight Aggregations

Scanning 500 million raw events to calculate real-time totals can still take 300 to 800 milliseconds during traffic spikes. ClickHouse Materialized Views eliminate this latency. They function as insert triggers, aggregating data during writes and storing summary rows in a companion table.

Pre-aggregating Real-Time Metrics

Create a target table using SummingMergeTree to store your aggregated metrics:

CREATE TABLE analytics.daily_country_stats (
    event_date Date,
    country LowCardinality(FixedString(2)),
    total_views UInt64,
    total_duration UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (event_date, country);

CREATE MATERIALIZED VIEW analytics.mv_daily_country_stats
TO analytics.daily_country_stats AS
SELECT
    event_date,
    country,
    count() AS total_views,
    sum(duration_seconds) AS total_duration
FROM analytics.page_views
GROUP BY event_date, country;

Every time you insert rows into page_views, the materialized view processes those incoming batches and increments the counters in daily_country_stats. Queries against this aggregated table return in 2 to 4 milliseconds:

SELECT 
    country,
    sum(total_views) AS views,
    round(sum(total_duration) / sum(total_views), 2) AS avg_duration_sec
FROM analytics.daily_country_stats
WHERE event_date >= today() - 7
GROUP BY country
ORDER BY views DESC
LIMIT 5;

Production Guidelines for OLAP Workloads

1. Batch Every Insert

Always send data in batches of 10,000 to 100,000 rows. ClickHouse creates a distinct physical part directory for each INSERT statement. Inserting single rows hundreds of times per second quickly triggers DB::Exception: Too many parts (300) in all data parts in table. If your ingest services receive events one by one, place an intermediate buffer (such as Apache Kafka, Vector, or ClickHouse’s Buffer table engine) ahead of the destination table.

2. Order Primary Keys by Filter Specificity

Structure your ORDER BY clause so the columns most frequently used in equality filters come first, ordered from lowest to highest cardinality. An index defined as (event_date, country, user_id) allows ClickHouse to skip data efficiently when filtering on dates and countries. Placing high-cardinality fields like user_id first would break date-range index pruning.

3. Treat Mutations as Heavy Administrative Tasks

ClickHouse is not built for transactional updates. Running ALTER TABLE ... UPDATE or DELETE triggers asynchronous mutations that rewrite entire data parts on disk, consuming significant I/O bandwidth. For updating record states or tracking deduplication, implement ReplacingMergeTree or CollapsingMergeTree instead of direct SQL mutations.

4. Configure Docker Ulimits for High Concurrency

A single complex query spanning multiple columns across several threads can open hundreds of file descriptors at once. Setting nofile to 262144 in Docker Compose prevents the server process from crashing with Too many open files under heavy analytical loads.