ClickHouse Real-time OLAP and MergeTree Optimization Guide

• 1. Introduction: OLTP vs OLAP, and Why ClickHouse
• 2. ClickHouse Architecture: Columnar Storage and Compression
  • How Column-Oriented Storage Works
  • Compression Algorithms: LZ4 vs ZSTD
  • Vectorized Query Execution
• 3. The MergeTree Engine Family
  • How MergeTree Works
  • Engine Family Comparison
  • ReplacingMergeTree Example
• 4. Schema Design Best Practices
  • ORDER BY Design Principles
  • Partitioning Strategy
  • The LowCardinality Type
• 5. Materialized Views and Pre-Aggregation
  • AggregatingMergeTree + State/Merge Pattern
• 6. Query Optimization Techniques
  • PREWHERE: Filtering Before WHERE
  • Getting Rough Results Quickly with Sampling
  • Parallel Execution and Resource Control
  • Query Performance Analysis
• 7. ClickHouse vs PostgreSQL vs BigQuery vs Druid
• 8. Major New Features in 2025~2026
  • Vector Search
  • SharedCatalog and Object Storage Separation
  • AI Integration and Query Generation
  • Other Notable Improvements
• 9. Operational Cautions
  • Disk Management
  • Replication and High Availability
  • Backup Strategy
  • INSERT Batch Optimization
• 10. Failure Cases and Recovery Procedures
  • Case 1: The "Too many parts" Error
  • Case 2: Replica Divergence
  • Case 3: Service Outage Caused by a Full Disk
  • Case 4: Materialized View Data Mismatch
• 11. Production Checklist
  • Schema Design
  • Data Ingestion
  • Query Performance
  • Operations and Stability
  • Monitoring Metrics
• 12. References

1. Introduction: OLTP vs OLAP, and Why ClickHouse

The first thing to pin down when choosing a database is the nature of the workload. OLTP (Online Transaction Processing) is optimized for short transactions and row-level reads and writes, while OLAP (Online Analytical Processing) specializes in aggregation queries and analysis spanning billions of rows.

You can also attach the pg_analytics extension to PostgreSQL, or use TimescaleDB, to handle analytical queries. But if the data grows past billions of rows, if you have to run near-real-time dashboards, and if you need sub-second responses, a dedicated OLAP engine is essential.

ClickHouse is a column-oriented analytical database that Yandex open-sourced in 2016 for its web analytics service Metrica. ClickHouse Inc. now leads its development, and over 2025~2026 innovative features such as vector search, SharedCatalog, and AI integration have been added. A single server can scan billions of rows per second, and scaling out to a cluster lets you analyze petabyte-scale data with sub-second latency.

The scenarios that call for ClickHouse are clear.

• Event/log/metric data is ingested at hundreds of millions of records or more per day
• Dashboards have to return hundreds of GROUP BY aggregations in sub-second time
• Append-heavy workloads with large batch INSERTs and almost no UPDATE/DELETE
• You want to dramatically cut storage cost through column-level compression

Conversely, there are cases where ClickHouse is not a good fit. When row-level point lookups are the main workload, when ACID transactions are mandatory, or when frequent UPDATE/DELETE is required, PostgreSQL or MySQL suits better.

2. ClickHouse Architecture: Columnar Storage and Compression

How Column-Oriented Storage Works

A traditional row-oriented database stores every column value belonging to a single row in one contiguous block. Even a query that needs only one column, such as SELECT country FROM events, has to read all the remaining column data from disk, which wastes a great deal of I/O.

ClickHouse stores the values of each column contiguously in a separate file. Only the columns an analytical query needs are read, so disk I/O drops dramatically. Because values of the same type sit next to each other, the compression ratio is also far higher.

Row-oriented (PostgreSQL):
┌──────┬─────────┬─────────┬──────────┐
│ id   │ country │ browser │ duration │  ← row 1
├──────┼─────────┼─────────┼──────────┤
│ id   │ country │ browser │ duration │  ← row 2
└──────┴─────────┴─────────┴──────────┘

Column-oriented (ClickHouse):
┌──────────────────────┐
│ id: 1, 2, 3, 4, ...  │  ← column file 1
├──────────────────────┤
│ country: KR, US, ... │  ← column file 2
├──────────────────────┤
│ browser: Chrome, ... │  ← column file 3
├──────────────────────┤
│ duration: 42, 17, ...│  ← column file 4
└──────────────────────┘

Compression Algorithms: LZ4 vs ZSTD

ClickHouse uses LZ4 compression by default. LZ4 has a middling compression ratio but very fast compression and decompression. If you need a higher compression ratio, you can choose ZSTD.

Being able to specify a codec per column is one of the strongest advantages of ClickHouse.

CREATE TABLE events
(
    event_time DateTime CODEC(DoubleDelta, LZ4),
    user_id    UInt64   CODEC(Delta, ZSTD(3)),
    country    LowCardinality(String) CODEC(ZSTD(1)),
    duration   Float32  CODEC(Gorilla, LZ4),
    raw_json   String   CODEC(ZSTD(5))
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id);

For a monotonically increasing timestamp such as event_time the DoubleDelta codec is effective, and for a floating-point metric such as duration the Gorilla codec is a good fit. For large JSON fields that are rarely accessed, raise the ZSTD level to save storage.

Vectorized Query Execution

ClickHouse performs operations on column vectors that bundle thousands of values, rather than one row at a time. This maximizes CPU cache efficiency and enables parallel processing with SIMD instructions (SSE4.2, AVX2, AVX-512). This vectorized execution engine is exactly why even a single core can process hundreds of millions of rows per second.

3. The MergeTree Engine Family

MergeTree is the core table engine of ClickHouse. As the name says, it stores data in parts and merges those parts in the background, following an LSM-Tree-like structure.

How MergeTree Works

1. INSERT: Data accumulates in a memory buffer and, once it reaches a threshold, is written to disk as a sorted part.
2. Merge: Background threads periodically merge small parts into larger ones. Special logic such as deduplication or aggregation can run during the merge.
3. SELECT: At query time the Primary Key (a sparse index) is used to read only the granules that are needed. The default granule size is 8,192 rows.

Engine Family Comparison

ReplacingMergeTree Example

When several versions of a row for the same key arrive in a CDC (Change Data Capture) pipeline and you want to keep only the latest state, use ReplacingMergeTree.

CREATE TABLE user_profiles
(
    user_id    UInt64,
    name       String,
    email      String,
    updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

-- The same user_id can be INSERTed multiple times
INSERT INTO user_profiles VALUES (1, 'Kim Youngju', 'yj@example.com', '2026-03-01 10:00:00');
INSERT INTO user_profiles VALUES (1, 'Kim Youngju', 'yj_new@example.com', '2026-03-06 09:00:00');

-- Before the merge, both rows may still be visible
-- The FINAL keyword returns only the latest version
SELECT * FROM user_profiles FINAL WHERE user_id = 1;

Note: The FINAL keyword performs deduplication at query time, so it carries a performance overhead. In production it is often more efficient to run OPTIMIZE TABLE ... FINAL on a regular schedule, or to use the argMax function at the query level.

4. Schema Design Best Practices

ORDER BY Design Principles

ORDER BY is the most important design decision in ClickHouse. It also acts as the Primary Key (a sparse index) and determines the physical sort order of the data.

Core principles:

• Put the columns you filter on most often first.
• Put low-cardinality columns first and high-cardinality columns later.
• For time-series data, (low_cardinality_dim, timestamp) is the usual order.

-- Bad design: a high-cardinality column comes first
CREATE TABLE events_bad
(
    event_id   UUID,
    user_id    UInt64,
    event_type LowCardinality(String),
    event_time DateTime
)
ENGINE = MergeTree()
ORDER BY (event_id);  -- UUID first: sparse index efficiency is extremely low

-- Good design: sorted to match the filter pattern
CREATE TABLE events_good
(
    event_id   UUID,
    user_id    UInt64,
    event_type LowCardinality(String),
    event_time DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, user_id, event_time);

Partitioning Strategy

PARTITION BY separates data physically, which makes deleting old data (via TTL or DROP PARTITION) efficient and enables partition pruning at query time.

Warning: If the partition count grows excessively (into the thousands), the metadata load on ZooKeeper/ClickHouse Keeper spikes and part-management overhead grows. Monthly partitioning is the safest choice.

The LowCardinality Type

For string columns whose cardinality is in the thousands or below, always use LowCardinality(String). Internally it applies dictionary encoding, which reduces storage space and improves query performance.

-- Compare before and after applying LowCardinality
CREATE TABLE logs_v1 (level String) ENGINE = MergeTree() ORDER BY tuple();
CREATE TABLE logs_v2 (level LowCardinality(String)) ENGINE = MergeTree() ORDER BY tuple();

-- Compare sizes after inserting data
SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed
FROM system.columns
WHERE database = currentDatabase() AND table IN ('logs_v1', 'logs_v2')
GROUP BY table;

Once cardinality goes beyond 10,000 the benefit of LowCardinality shrinks and it can even become overhead, so check with SELECT uniq(column) FROM table before applying it.

5. Materialized Views and Pre-Aggregation

A Materialized View (MV) is the core mechanism for implementing real-time pre-aggregation in ClickHouse. It is fundamentally different from the Materialized View in PostgreSQL. A ClickHouse MV behaves like an INSERT trigger: every time data is inserted into the source table, it automatically writes the transformed/aggregated result to the target table.

AggregatingMergeTree + State/Merge Pattern

The most powerful pre-aggregation pattern combines the AggregatingMergeTree engine with the -State/-Merge combinators.

-- Step 1: the source event table
CREATE TABLE raw_events
(
    event_time DateTime,
    event_type LowCardinality(String),
    user_id    UInt64,
    revenue    Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time);

-- Step 2: the pre-aggregation target table (AggregatingMergeTree)
CREATE TABLE hourly_stats
(
    hour       DateTime,
    event_type LowCardinality(String),
    user_count AggregateFunction(uniq, UInt64),
    total_rev  AggregateFunction(sum, Float64),
    p99_rev    AggregateFunction(quantile(0.99), Float64)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (event_type, hour);

-- Step 3: create the Materialized View
CREATE MATERIALIZED VIEW hourly_stats_mv
TO hourly_stats
AS SELECT
    toStartOfHour(event_time) AS hour,
    event_type,
    uniqState(user_id)    AS user_count,
    sumState(revenue)     AS total_rev,
    quantileState(0.99)(revenue) AS p99_rev
FROM raw_events
GROUP BY hour, event_type;

-- Step 4: use the -Merge combinator when querying
SELECT
    hour,
    event_type,
    uniqMerge(user_count)    AS unique_users,
    sumMerge(total_rev)      AS total_revenue,
    quantileMerge(0.99)(p99_rev) AS revenue_p99
FROM hourly_stats
WHERE hour >= '2026-03-01'
GROUP BY hour, event_type
ORDER BY hour;

The key benefits of this pattern are as follows.

• Real-time: Aggregates are updated immediately whenever an INSERT happens.
• Accuracy: The -State/-Merge functions store the intermediate state as binary, so mathematically exact results are guaranteed even after parts are merged.
• Query speed: Queries hit the aggregate table instead of billions of source rows, so responses drop to the millisecond range.
• Approximation support: Approximate aggregate functions such as uniq (HyperLogLog-based) and quantile (t-digest-based) are also fully compatible with the State/Merge pattern.

Warning: A Materialized View is triggered only at INSERT time. To apply an MV retroactively to existing data, you need a manual backfill with INSERT INTO hourly_stats SELECT ... FROM raw_events.

6. Query Optimization Techniques

PREWHERE: Filtering Before WHERE

ClickHouse's own PREWHERE clause evaluates part of the WHERE condition before reading columns, reducing disk I/O for unnecessary column data. Recent versions have the optimizer convert WHERE into PREWHERE automatically, but you can also specify it explicitly.

-- Use PREWHERE explicitly
SELECT user_id, raw_json
FROM raw_events
PREWHERE event_type = 'purchase'
WHERE revenue > 100.0;

In the query above, only the event_type column is read first for filtering, and raw_json (a large String column) is read only for the rows that pass. On tables that contain large columns the effect of PREWHERE is dramatic.

Getting Rough Results Quickly with Sampling

During data exploration, fast feedback can matter more than exact aggregation. Using the SAMPLE clause reads only part of the data and returns an approximate result.

-- SAMPLE BY must be specified when the table is created
CREATE TABLE events_sampled
(
    event_time DateTime,
    user_id    UInt64,
    event_type LowCardinality(String),
    revenue    Float64
)
ENGINE = MergeTree()
ORDER BY (event_type, sipHash64(user_id))
SAMPLE BY sipHash64(user_id);

-- Query by sampling only 10% of the full data
SELECT
    event_type,
    count() * 10 AS estimated_count,  -- scaled up 10x
    avg(revenue) AS avg_revenue
FROM events_sampled
SAMPLE 0.1
GROUP BY event_type;

Parallel Execution and Resource Control

By default ClickHouse uses every available CPU core to execute a query in parallel. In production you need appropriate limits to prevent resource contention between queries.

-- Query-level resource limits
SET max_threads = 8;                    -- max threads per query
SET max_memory_usage = 10000000000;     -- max memory per query (10GB)
SET max_execution_time = 30;            -- max execution time (seconds)
SET max_rows_to_read = 1000000000;      -- max rows to read

-- Per-user/per-profile limits (config.xml or SQL)
CREATE SETTINGS PROFILE 'analyst' SETTINGS
    max_threads = 4,
    max_memory_usage = 5000000000,
    max_execution_time = 60
TO analyst_role;

Query Performance Analysis

When diagnosing a slow query, use the profiling options of clickhouse-client.

# Run with query execution statistics included
clickhouse-client --query "
    SELECT event_type, count()
    FROM raw_events
    WHERE event_time >= '2026-03-01'
    GROUP BY event_type
    FORMAT PrettyCompactMonoBlock
    SETTINGS send_logs_level = 'trace'
"

# Analyze slow queries from system.query_log
clickhouse-client --query "
    SELECT
        query_duration_ms,
        read_rows,
        formatReadableSize(read_bytes) AS read_size,
        formatReadableSize(memory_usage) AS peak_memory,
        query
    FROM system.query_log
    WHERE type = 'QueryFinish'
      AND query_duration_ms > 1000
    ORDER BY query_duration_ms DESC
    LIMIT 10
"

7. ClickHouse vs PostgreSQL vs BigQuery vs Druid

This section summarizes the characteristics of the four engines most often compared for OLAP workloads.

Key selection criteria:

• Already on PostgreSQL with tens of GB of data or less: stay on PostgreSQL. The pg_analytics extension may well be enough.
• Tens of TB or more, your own infrastructure, sub-second responses required: ClickHouse is the best fit.
• You do not want to manage infrastructure and are flexible on cost: BigQuery is a good fit.
• High-performance real-time dashboards are the core and JOINs are unnecessary: Druid is also an option.

8. Major New Features in 2025~2026

ClickHouse shipped large-scale feature updates through more than 50 releases over the course of 2025. This section summarizes the major changes through early 2026.

Vector Search

From ClickHouse 25.1, ANN (Approximate Nearest Neighbor) search using the usearch index is officially supported. You can keep analytical data and embedding vectors in the same table without a separate vector DB.

CREATE TABLE embeddings
(
    doc_id   UInt64,
    content  String,
    vector   Array(Float32),
    INDEX vec_idx vector TYPE usearch(256) GRANULARITY 1
)
ENGINE = MergeTree()
ORDER BY doc_id;

-- Cosine-similarity search
SELECT doc_id, content,
       cosineDistance(vector, [0.1, 0.2, ...]) AS distance
FROM embeddings
ORDER BY distance ASC
LIMIT 10;

SharedCatalog and Object Storage Separation

SharedCatalog, introduced in ClickHouse Cloud, fully separates compute from storage so that several compute nodes can access the same data on S3/GCS. This lets you scale out read-only replicas instantly.

AI Integration and Query Generation

ClickHouse is integrating capabilities such as LLM-based natural-language-to-SQL conversion and AI-driven query optimization suggestions into the ClickHouse Cloud console. In addition, UDFs (User Defined Functions) let you wire external ML models directly into the query pipeline.

Other Notable Improvements

• Parallel Replicas: The ability to spread a single query across multiple replicas to cut latency reached GA.
• Lightweight DELETE/UPDATE: Row-masking-based lightweight deletion, in place of mutations, has stabilized.
• Refreshable Materialized View: Support for MVs that refresh in full on a schedule was added (separate from the existing INSERT-trigger approach).

9. Operational Cautions

Disk Management

ClickHouse temporarily uses extra disk space during background merges. In production you have to keep at least 30% of the disk free, and monitoring disk utilization is essential.

# /etc/clickhouse-server/config.d/storage.yaml
# Multi-disk (hot/cold) storage policy
storage_configuration:
  disks:
    hot:
      type: local
      path: /data/clickhouse/hot/
    cold:
      type: s3
      endpoint: https://s3.ap-northeast-2.amazonaws.com/my-bucket/clickhouse/
      access_key_id: '${S3_ACCESS_KEY}'
      secret_access_key: '${S3_SECRET_KEY}'
  policies:
    tiered:
      volumes:
        hot_volume:
          disk: hot
          max_data_part_size_bytes: 10737418240 # 10GB
        cold_volume:
          disk: cold
      move_factor: 0.8 # move to cold once the hot disk is 80% full

Using TTL (Time to Live), you can automatically move old data to cold storage or delete it.

ALTER TABLE raw_events
    MODIFY TTL
        event_time + INTERVAL 30 DAY TO VOLUME 'cold_volume',
        event_time + INTERVAL 365 DAY DELETE;

Replication and High Availability

In production, always use the ReplicatedMergeTree engine. ClickHouse Keeper (a ZooKeeper-compatible consensus protocol) handles metadata synchronization between replicas.

-- Create a Replicated table
CREATE TABLE raw_events ON CLUSTER 'production'
(
    event_time DateTime,
    event_type LowCardinality(String),
    user_id    UInt64,
    revenue    Float64
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/raw_events', '{replica}')
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time);

ClickHouse Keeper operational cautions:

• Always configure an odd number of nodes (3 or 5). A 3-node setup tolerates the failure of up to 1 node.
• Assign a dedicated SSD to Keeper nodes. Disk latency directly affects consensus protocol performance.
• Set raft_logs_level appropriately so that debugging is possible.
• Set retention periods for Keeper logs and snapshots to prevent the disk from filling up.

Backup Strategy

Use the built-in ClickHouse backup feature, or the clickhouse-backup tool.

# Built-in BACKUP command (ClickHouse 22.8+)
clickhouse-client --query "
    BACKUP TABLE raw_events
    TO S3('https://s3.ap-northeast-2.amazonaws.com/my-backup-bucket/raw_events_20260306/',
           '${S3_ACCESS_KEY}', '${S3_SECRET_KEY}')
    SETTINGS compression_method = 'lz4'
"

# Restore
clickhouse-client --query "
    RESTORE TABLE raw_events
    FROM S3('https://s3.ap-northeast-2.amazonaws.com/my-backup-bucket/raw_events_20260306/',
            '${S3_ACCESS_KEY}', '${S3_SECRET_KEY}')
"

# Using the clickhouse-backup tool (finer-grained control)
clickhouse-backup create --tables="default.raw_events" daily_20260306
clickhouse-backup upload daily_20260306
clickhouse-backup list remote

INSERT Batch Optimization

The most common mistake when inserting data into ClickHouse is sending INSERTs row by row. Every INSERT creates a new part, and an excessive part count triggers the "Too many parts" error.

Recommendations:

• Batch INSERT at least 1,000 rows at a time. Ideally 10,000~100,000 rows.
• When consuming from Kafka, RabbitMQ and the like, use ClickHouse's table engines (the Kafka engine) to set up automatic batching.
• With the async_insert=1 setting, ClickHouse gathers the client's small INSERTs on the server side and processes them as batches.

-- Enable async_insert
SET async_insert = 1;
SET wait_for_async_insert = 1;  -- wait for the actual write before the INSERT returns
SET async_insert_max_data_size = 10485760;  -- flush every 10MB
SET async_insert_busy_timeout_ms = 1000;    -- wait at most 1 second

10. Failure Cases and Recovery Procedures

Case 1: The "Too many parts" Error

Symptom: INSERT fails with the error DB::Exception: Too many parts (N). Merges are processing significantly slower than inserts.

Cause: Row-level INSERTs were run thousands of times per second, so the part count exploded. The default threshold is 300 per partition.

Recovery procedure:

1. Immediately stop the INSERT traffic or increase the batch size.
2. Wait until background merges catch up. Monitor progress in the system.merges table.
3. If needed, run OPTIMIZE TABLE events FINAL manually. (Caution: this command generates very heavy disk I/O, so avoid peak hours.)
4. Fix the root cause: enable async_insert, increase the batch size, adopt the Kafka engine, and so on.

-- Check the part count per partition
SELECT
    partition,
    count() AS part_count,
    formatReadableSize(sum(bytes_on_disk)) AS total_size
FROM system.parts
WHERE table = 'raw_events' AND active
GROUP BY partition
ORDER BY part_count DESC;

-- Monitor merge progress
SELECT
    table, partition_id,
    progress, elapsed,
    formatReadableSize(total_size_bytes_compressed) AS size
FROM system.merges
WHERE table = 'raw_events';

Case 2: Replica Divergence

Symptom: The data on two replicas differs, so SELECT count() returns different results. In system.replicas, is_session_expired or queue_size is abnormally high.

Cause: This can happen when the ClickHouse Keeper connection drops, on a network partition, or when one of the replicas comes back after being down for a long time.

Recovery procedure:

1. Check the state in system.replicas.
2. Confirm that the replication queue is being processed normally.
3. If the queue is stuck, try SYSTEM RESTART REPLICA table_name.
4. In the worst case, remove the data on the problem replica and replicate again from a healthy replica.

-- Check replica status
SELECT
    database, table,
    is_leader, is_readonly, is_session_expired,
    future_parts, parts_to_check,
    queue_size, inserts_in_queue, merges_in_queue,
    log_pointer, total_replicas, active_replicas
FROM system.replicas
WHERE table = 'raw_events';

-- Try restarting replication
SYSTEM RESTART REPLICA raw_events;

-- If that still does not work, resync the replica from a healthy copy
-- (Caution: the local data on that replica is deleted)
SYSTEM RESTORE REPLICA raw_events;

Case 3: Service Outage Caused by a Full Disk

Symptom: Background merges fail, INSERTs fail, and in extreme cases the server cannot start.

Cause: No TTL configured, no compression codec applied, or data growing faster than expected.

Recovery procedure:

1. Identify the largest partition and immediately remove unnecessary data with DROP PARTITION.
2. Free up temporary disk space (delete system logs and temporary files).
3. Add a TTL policy to configure automatic deletion/movement.
4. Add cold storage (S3) through a multi-disk policy.

-- Check disk usage per partition
SELECT
    partition,
    formatReadableSize(sum(bytes_on_disk)) AS disk_usage,
    min(min_time) AS oldest_data,
    max(max_time) AS newest_data,
    count() AS part_count
FROM system.parts
WHERE table = 'raw_events' AND active
GROUP BY partition
ORDER BY sum(bytes_on_disk) DESC;

-- Drop an old partition immediately (irreversible!)
ALTER TABLE raw_events DROP PARTITION '202501';

Case 4: Materialized View Data Mismatch

Symptom: The aggregation results of the source table and the MV do not match.

Cause: Data that already existed before the MV was created is not reflected in the MV. In addition, if the server fails during an INSERT, the write may land in the source table without being reflected in the MV.

Recovery procedure:

1. TRUNCATE the MV target table.
2. Backfill manually from the source table.
3. For a large backfill, split it by date range to keep memory usage under control.

-- Reset the MV target table, then backfill
TRUNCATE TABLE hourly_stats;

INSERT INTO hourly_stats
SELECT
    toStartOfHour(event_time) AS hour,
    event_type,
    uniqState(user_id)    AS user_count,
    sumState(revenue)     AS total_rev,
    quantileState(0.99)(revenue) AS p99_rev
FROM raw_events
WHERE event_time >= '2026-01-01' AND event_time < '2026-02-01'
GROUP BY hour, event_type;
-- Repeat month by month

11. Production Checklist

This section collects the items to check before deploying ClickHouse to production.

Schema Design

• Does the ORDER BY key match the main query patterns?
• Is PARTITION BY set to an appropriate granularity (month/week)? (fewer than 1,000 partitions)
• Is the LowCardinality type applied to low-cardinality strings?
• Is the optimal compression codec specified per column?
• Has Nullable usage been minimized? (Nullable creates an extra column file)

Data Ingestion

• Is the INSERT batch size at least 1,000 rows?
• Is async_insert configured appropriately? (when small INSERTs are frequent)
• Are table engines used for the Kafka/RabbitMQ integration?
• Is a "Too many parts" alert configured?

Query Performance

• Is Materialized View pre-aggregation applied to dashboard queries?
• Are per-user/per-role resource limits (max_threads, max_memory_usage) configured?
• Is slow-query monitoring based on system.query_log in place?
• Are dictionaries or IN subqueries used instead of large JOINs?

Operations and Stability

• Are at least 2 replicas configured with ReplicatedMergeTree?
• Is ClickHouse Keeper running on an odd number of 3+ nodes?
• Is an 80% disk utilization alert configured?
• Is the data retention period managed by a TTL policy?
• Are backups automated, and are restore tests run regularly?
• Is hot/cold storage tiering configured? (for large data volumes)

Monitoring Metrics

• Monitor BackgroundMergesAndMutationsPoolTask in system.metrics
• Monitor MaxPartCountForPartition in system.asynchronous_metrics (warn when it exceeds 300)
• Monitor queue_size and is_readonly in system.replicas
• Visualize the key metrics on a Prometheus + Grafana dashboard

12. References

• ClickHouse Academic Overview - In-Depth Architecture Analysis
• ClickHouse Query Optimisation - The Definitive Guide
• ClickHouse 2025 Roundup - Annual Feature Summary
• ClickHouse vs PostgreSQL with Extensions - OLAP Performance Comparison
• Data Modeling Guide for Real-Time Analytics with ClickHouse
• ClickHouse Official Docs - MergeTree Engine Family
• ClickHouse Official Docs - Materialized View