How to Optimize ClickHouse Query Performance: 2025 Playbook

ClickHouse's columnar architecture creates unique performance challenges that generic SQL tuning can't address. This playbook provides battle-tested optimization tactics for three core enterprise workloads: BI dashboards, ad-hoc analytics, and ETL/streaming pipelines.

‍

Performance Yardsticks

‍

Workload Taxonomy

‍

BI Dashboard Optimizations

1. Optimize Primary Key Selection for Data Skipping

The most impactful optimization for dashboard performance is selecting primary keys that maximize data skipping during query execution. ClickHouse uses primary key indices to skip entire data blocks, dramatically reducing the amount of data scanned.

When to apply: Dashboard queries consistently scan more rows than necessary when they should be targeting specific time ranges or dimension values. You'll see this in query logs where rows_read significantly exceeds rows_to_read_after_primary_key_condition, indicating that ClickHouse is reading entire granules (8192-row data blocks by default) instead of skipping them through primary key filtering.

How to implement: Order primary key columns by WHERE clause frequency: time columns first, then high-cardinality dimensions. ClickHouse only uses primary keys for range filtering.

1-- Instead of a generic primary key
2CREATE TABLE user_events (
3    user_id UInt64,
4    event_time DateTime,
5    event_type String,
6    value Float64
7) ENGINE = MergeTree()
8ORDER BY (user_id, event_time)
9PARTITION BY toYYYYMM(event_time);
10
11-- Optimize for time-range dashboard queries
12CREATE TABLE user_events_optimized (
13    user_id UInt64,
14    event_time DateTime,
15    event_type String,
16    value Float64
17) ENGINE = MergeTree()
18ORDER BY (event_time, event_type, user_id)
19PARTITION BY toYYYYMM(event_time);

Alternatives: If you need multiple access patterns, consider using projection indices to maintain different sort orders for the same data. For extremely high-frequency queries, materialized views with pre-aggregated data often provide better performance than primary key optimization alone.

2. Implement Skip Indices for Non-Primary Key Filtering

Skip indices act as bloom filters or other lightweight data structures that help ClickHouse skip entire data blocks when filtering on non-primary key columns. This becomes crucial when dashboard filters include dimensions not covered by your primary key.

When to apply: Dashboard queries filter on columns not in the primary key and show high rows_read values in query logs. Particularly effective for string columns with moderate cardinality that appear frequently in WHERE clauses.

How to implement: Add skip indices based on query patterns: bloom filters for equality, minmax for ranges.

1-- Add skip indices for common dashboard filters
2ALTER TABLE user_events_optimized 
3ADD INDEX idx_country_bloom country TYPE bloom_filter() GRANULARITY 8192;
4
5ALTER TABLE user_events_optimized 
6ADD INDEX idx_value_minmax value TYPE minmax() GRANULARITY 8192;
7
8-- For string pattern matching in dashboard search
9ALTER TABLE user_events_optimized 
10ADD INDEX idx_event_name_ngram event_name TYPE ngrambf_v1(4, 512, 3, 0) GRANULARITY 8192;

For extremely selective filters, consider partitioning by the filter column instead of using skip indices. Sparse primary indices can also be effective for timestamp-heavy workloads where you need both time-based and dimension-based filtering.

3. Configure Merge Tree Settings for Read-Heavy Workloads

Dashboard workloads benefit from merge tree configurations that prioritize read performance over write throughput. The key is balancing part sizes to minimize merge overhead while maximizing query performance.

When to apply: Dashboards experience inconsistent response times, particularly during peak usage hours when background merges compete with query processing. Monitor system.merges table for merge duration and frequency patterns.

How to implement: Tune merge tree settings for larger, stable parts: focus on max_parts_in_total and parts_to_throw_insert.

1-- Optimize for dashboard read performance
2CREATE TABLE dashboard_metrics (
3    timestamp DateTime,
4    metric_name String,
5    value Float64,
6    dimensions Map(String, String)
7) ENGINE = MergeTree()
8ORDER BY (timestamp, metric_name)
9PARTITION BY toYYYYMM(timestamp)
10SETTINGS 
11    index_granularity = 8192,
12    max_parts_in_total = 50,
13    parts_to_throw_insert = 3000,
14    merge_max_block_size = 8192000,
15    max_bytes_to_merge_at_max_space_in_pool = 161061273600;

Alternatives: For dashboards with strict SLA requirements, consider using ReplicatedMergeTree with read replicas to distribute query load. Alternatively, pre-aggregate data using materialized views to avoid complex merge tree optimizations entirely.

4. Leverage Materialized Views for Repeated Aggregations

Materialized views pre-compute repeated aggregations, eliminating redundant computation as data arrives.

When to apply: Dashboard queries perform the same GROUP BY operations repeatedly, especially when aggregating across large time windows (daily/weekly/monthly rollups). Most effective when aggregation logic is stable and query patterns are predictable.

How to implement: Create materialized views matching dashboard aggregation patterns. ClickHouse automatically maintains aggregated data.

1-- Create materialized view for hourly dashboard rollups
2CREATE MATERIALIZED VIEW hourly_user_metrics
3ENGINE = SummingMergeTree()
4ORDER BY (hour, user_segment)
5AS SELECT
6    toStartOfHour(event_time) as hour,
7    user_segment,
8    count() as event_count,
9    uniq(user_id) as unique_users,
10    sum(revenue) as total_revenue
11FROM user_events_optimized
12GROUP BY hour, user_segment;
13
14-- Dashboard queries become simple lookups
15SELECT hour, user_segment, total_revenue
16FROM hourly_user_metrics
17WHERE hour >= today() - INTERVAL 7 DAY
18ORDER BY hour;

Alternatives: For highly dynamic aggregation requirements, consider using AggregatingMergeTree with custom aggregate functions. Incremental materialized views work well when you need to aggregate only recent data changes.

5. Analyze Query Performance with Built-in Profiling Tools

ClickHouse provides built-in tools for query analysis and performance bottleneck identification.

When to apply: When queries perform slower than expected and you need to understand the root cause. Essential for identifying whether bottlenecks are in data scanning, memory usage, or processing algorithms.

How to implement: Use EXPLAIN statements and system tables to analyze granule skipping, index usage, and resource consumption.

1-- Analyze query execution plan and index usage
2EXPLAIN indexes = 1
3SELECT payment_type, COUNT() as trip_count
4FROM trips_table
5WHERE pickup_datetime >= '2023-01-01' 
6  AND pickup_datetime < '2023-04-01'
7GROUP BY payment_type;
8
9-- Check query logs for detailed performance metrics
10SELECT 
11    query_duration_ms,
12    read_rows,
13    read_bytes,
14    memory_usage,
15    rows_read - rows_read_after_primary_key as rows_skipped_by_primary_key,
16    result_rows
17FROM system.query_log
18WHERE query LIKE '%trips_table%'
19  AND event_time >= now() - INTERVAL 1 HOUR
20ORDER BY event_time DESC;

Alternatives: For production workloads, consider using query profiling with sampling to avoid overhead. Query condition cache can also help track recurring query patterns automatically.

6. Implement PREWHERE for Early Filtering

PREWHERE optimization allows ClickHouse to apply filters before reading all columns, significantly reducing I/O for queries that filter on specific columns while selecting many others.

When to apply: Queries that SELECT many columns but filter on just a few, particularly when filter conditions are highly selective. Most effective when filtering columns are stored separately from selected columns in the table structure.

How to implement: Use PREWHERE clause for the most selective conditions. ClickHouse can auto-move conditions, but explicit usage provides better control.

1-- Instead of using WHERE for all conditions
2SELECT user_id, session_data, event_properties, page_views
3FROM user_sessions
4WHERE user_segment = 'premium' 
5  AND session_date >= '2023-01-01'
6  AND session_duration > 300;
7
8-- Optimize with PREWHERE for selective filtering
9SELECT user_id, session_data, event_properties, page_views
10FROM user_sessions
11PREWHERE user_segment = 'premium' 
12  AND session_date >= '2023-01-01'
13WHERE session_duration > 300;

Alternatives: ClickHouse automatically applies PREWHERE optimization in many cases, but you can disable automatic optimization and manually control it. For extremely selective filters, consider using skip indices alongside PREWHERE.

‍

Ad-hoc Analytics Optimizations

1. Implement Codec Compression for Large Scan Queries

Column-level compression reduces I/O for large analytical queries by choosing appropriate codecs based on data patterns. Specifically useful in cases of large dataset scans with high disk I/O wait times, especially with repetitive data patterns or limited network bandwidth.

How to implement: Apply compression codecs: LZ4/ZSTD for strings, Delta/DoubleDelta for numeric patterns.

1-- Optimize analytics table with appropriate codecs
2CREATE TABLE analytics_events (
3    timestamp DateTime CODEC(DoubleDelta, LZ4),
4    user_id UInt64 CODEC(Delta, LZ4),
5    session_id String CODEC(LZ4),
6    page_url String CODEC(ZSTD(3)),
7    event_properties String CODEC(ZSTD(6)),
8    revenue_cents Int64 CODEC(Delta, LZ4)
9) ENGINE = MergeTree()
10ORDER BY (timestamp, user_id)
11PARTITION BY toYYYYMM(timestamp);

Alternatives: For extremely large datasets, consider using TTL policies to automatically compress older data with higher compression ratios. Tiered storage can also move cold data to cheaper storage with different codec settings.

2. Optimize Distributed Query Execution with Proper Sharding

Large analytical queries benefit from parallel execution across multiple nodes. The key is designing shard keys that distribute data evenly while allowing for effective query pruning.

When to apply: Uneven resource utilization across cluster nodes or queries that don't scale linearly with cluster size.

How to implement: Choose shard keys aligned with common GROUP BY dimensions while maintaining even distribution. Monitor system.clusters and query logs.

1-- Create distributed table with optimized sharding
2CREATE TABLE analytics_events_distributed AS analytics_events
3ENGINE = Distributed('analytics_cluster', 'default', 'analytics_events', rand());
4
5-- For queries that frequently group by user segments
6CREATE TABLE analytics_events_dist_by_segment AS analytics_events
7ENGINE = Distributed('analytics_cluster', 'default', 'analytics_events', 
8    cityHash64(user_segment));
9
10-- Analytics query with proper shard pruning
11SELECT 
12    user_segment,
13    toStartOfWeek(timestamp) as week,
14    count() as events,
15    uniq(user_id) as unique_users
16FROM analytics_events_distributed
17WHERE timestamp >= today() - INTERVAL 90 DAY
18  AND user_segment IN ('premium', 'enterprise')
19GROUP BY user_segment, week
20ORDER BY week, user_segment;

3. Leverage Secondary Indices and Projections for Faster Filtering

Complex analytical queries often filter on multiple dimensions that don't fit neatly into a single primary key order. While the primary key can only have one sort order, you can accelerate alternative query patterns using secondary data skipping indices or, more powerfully, projections.

When to apply: Unpredictable WHERE clause combinations on high-cardinality dimensions in analytical queries.

How to implement: Add secondary data skipping indices for targeted filtering, or use projections for more complex patterns.

1-- Add secondary indices for common analytical filtering
2ALTER TABLE product_analytics 
3ADD INDEX idx_category category_id TYPE set(1000) GRANULARITY 8192;
4
5ALTER TABLE product_analytics 
6ADD INDEX idx_country country_code TYPE set(100) GRANULARITY 8192;

Alternatives: Projections are often the superior and more powerful solution. Think of a projection as an automatically managed, pre-aggregated materialized view stored within the table itself. They allow you to define a completely different primary key on the same data, enabling fast filtering and aggregation on various dimensions without duplicating your dataset.

1-- Add a projection to optimize queries grouping by category and country
2ALTER TABLE product_analytics ADD PROJECTION p_category_country (
3    SELECT
4        category_id,
5        country_code,
6        sum(revenue_usd)
7    GROUP BY category_id, country_code
8);
9
10-- ClickHouse will automatically use this projection for matching queries
11SELECT
12    category_id,
13    country_code,
14    sum(revenue_usd)
15FROM product_analytics
16WHERE country_code = 'US'
17GROUP BY category_id, country_code;

4. Implement Memory-Efficient Aggregation Strategies

Large analytical aggregations can exceed available memory, causing queries to spill to disk or fail entirely. The solution involves configuring ClickHouse's aggregation algorithms and memory management for your specific workload patterns.

When to apply: "Memory limit exceeded" errors or excessive disk spill activity during GROUP BY operations with high-cardinality dimensions.

How to implement: Configure aggregation settings for external aggregation and use memory-efficient functions like uniqCombined.

Alternatives: For extremely large aggregations, consider using materialized views with incremental updates to pre-compute partial results. Sampling can also provide approximate results with significantly lower memory requirements.

5. Optimize Complex JOIN Performance with Dictionary Tables

Analytical queries often require enriching fact data with multiple dimension tables, leading to complex multi-way JOINs. Dictionary tables provide a more efficient alternative by keeping frequently accessed reference data in memory.

When to apply: Multiple JOINs with static reference tables (geography, catalogs, segments) that are accessed heavily but change infrequently.

How to implement: Convert dimension tables to ClickHouse dictionaries and use dictGet() functions instead of explicit JOINs.

1-- Create dictionary configuration for product catalog
2CREATE DICTIONARY product_dict (
3    product_id UInt64,
4    category String,
5    brand String,
6    price_tier String
7) PRIMARY KEY product_id
8SOURCE(CLICKHOUSE(
9    HOST 'localhost'
10    PORT 9000
11    TABLE 'product_catalog'
12    USER 'default'
13))
14LAYOUT(HASHED())
15LIFETIME(MIN 300 MAX 3600);
16
17-- Use dictionary functions in analytical queries
18SELECT 
19    dictGet('product_dict', 'category', product_id) as category,
20    dictGet('product_dict', 'brand', product_id) as brand,
21    count() as sales_count,
22    sum(revenue_usd) as total_revenue
23FROM product_analytics
24WHERE timestamp >= today() - INTERVAL 30 DAY
25GROUP BY category, brand
26HAVING total_revenue > 10000
27ORDER BY total_revenue DESC;

Alternatives: For very large dimension tables, consider using external dictionaries with HTTP sources to maintain fresh reference data. Alternatively, use cached JOINs with optimized join_use_nulls settings for better performance.

ETL/Streaming Optimizations

1. Optimize INSERT Performance with Proper Batching

High-throughput ETL workloads require careful batching strategies to balance ingestion latency with merge tree efficiency. The key insight here is that ClickHouse performs best with moderately-sized batches that align with its internal block structure.

When to apply: Inconsistent INSERT performance, excessive small parts causing merge storms, or INSERT latency affecting downstream processing.

How to implement: Configure batch sizes based on optimal INSERT patterns and monitor part creation rates.

1-- Optimize table settings for high-throughput ingestion
2CREATE TABLE streaming_events (
3    event_id UInt64,
4    timestamp DateTime64(3),
5    user_id UInt64,
6    event_data String
7) ENGINE = MergeTree()
8ORDER BY (timestamp, user_id)
9PARTITION BY toYYYYMMDD(timestamp)
10SETTINGS 
11    max_insert_block_size = 1048576,
12    min_insert_block_size_rows = 524288,
13    min_insert_block_size_bytes = 268435456;
14
15-- Efficient batch INSERT pattern
16INSERT INTO streaming_events
17SELECT * FROM input('event_id UInt64, timestamp DateTime64(3), user_id UInt64, event_data String')
18FORMAT TSV;

Alternatives: For extremely high-throughput scenarios, consider using Buffer tables to automatically batch small inserts. Kafka table engine integration can also provide automatic batching for streaming workloads.

2. Configure Merge Policies for Write-Heavy Workloads

ETL workloads create many small parts that must be efficiently merged without impacting query performance. The solution involves tuning merge policies to handle high ingestion rates while maintaining read performance.

When to apply: Excessive active parts per partition or background merges can't keep pace with INSERT rates.

How to implement: Adjust merge tree policies for more aggressive merging without resource starvation during peak periods.

Alternatives: For predictable ingestion patterns, consider using TTL expressions to automatically manage old data lifecycle. ReplacingMergeTree can also help deduplicate streaming data automatically.

3. Implement Efficient Deduplication for Streaming Data

Streaming ETL pipelines often receive duplicate events due to retry logic or at-least-once delivery guarantees. Efficient deduplication is crucial for maintaining data quality without impacting ingestion performance.

When to apply: Streaming data sources provide at-least-once delivery semantics or when ETL retry logic may create duplicate records. Most effective when you have natural deduplication keys and can tolerate eventual consistency.

How to implement: Use ReplacingMergeTree with window functions to get latest records. Avoid SELECT ... FINAL - it forces single-threaded merges and is extremely slow on large tables.

Alternatives: For exact-once semantics, consider using CollapsingMergeTree with sign columns to handle updates and deletes. External deduplication using Redis or other caching systems can also work for smaller data volumes.

4. Use Lightweight Deletes and Updates for Efficient Data Correction

ClickHouse's support for lightweight DELETE and UPDATE mutations is a game-changer for data correction and compliance, often replacing the need for more complex engines like CollapsingMergeTree.

When to apply: When you need to remove or modify specific rows for use cases like GDPR right-to-be-forgotten requests, correcting backfills, or removing bad data from a stream without a full table rewrite. These are asynchronous, background operations.

How to implement: Use ALTER TABLE ... DELETE/UPDATE syntax. ClickHouse plans and executes mutations in the background without blocking reads/writes.

1-- Example for a GDPR delete request
2ALTER TABLE deduplicated_events
3DELETE WHERE user_id = 12345;
4
5-- Example for correcting a backfilled data point
6ALTER TABLE deduplicated_events
7UPDATE data = '{"corrected": true}'
8WHERE event_id = 'abc-123' AND timestamp = '2025-01-10 12:00:00';
9
10-- You can monitor the progress of mutations
11SELECT
12    mutation_id,
13    command,
14    is_done
15FROM system.mutations
16WHERE table = 'deduplicated_events';

Alternatives: For frequent, high-volume updates of specific rows, a ReplacingMergeTree table design can still be more efficient. For scenarios requiring a log of changes, CollapsingMergeTree remains a valid option.

5. Implement Efficient Schema Evolution for Streaming Data

Schema evolution requires flexible designs that accommodate changes without interrupting ingestion or breaking queries.

When to apply: Streaming data sources frequently add new fields or change data types, requiring schema updates without ETL downtime. Particularly important for JSON-heavy workloads or when supporting multiple data source versions simultaneously.

How to implement: Use flexible column types and schema migration strategies for backward-compatible changes. Map and Tuple types handle semi-structured data well.

1-- Schema-flexible streaming table design
2CREATE TABLE flexible_events (
3    event_id String,
4    timestamp DateTime64(3),
5    source_system String,
6    event_type String,
7    structured_data Tuple(
8        user_id Nullable(UInt64),
9        session_id Nullable(String),
10        value Nullable(Float64)
11    ),
12    raw_data Map(String, String),  -- For unknown fields
13    schema_version UInt8 DEFAULT 1
14) ENGINE = MergeTree()
15ORDER BY (timestamp, source_system, event_type)
16PARTITION BY (toYYYYMMDD(timestamp), source_system);
17
18-- Handle schema evolution gracefully
19ALTER TABLE flexible_events 
20ADD COLUMN new_field Nullable(String) AFTER structured_data;
21
22-- Query that works across schema versions
23SELECT 
24    event_type,
25    count() as event_count,
26    countIf(structured_data.user_id IS NOT NULL) as events_with_user,
27    countIf(mapContains(raw_data, 'new_property')) as events_with_new_prop
28FROM flexible_events
29WHERE timestamp >= today() - INTERVAL 7 DAY
30GROUP BY event_type;

Alternatives: For highly dynamic schemas, consider using JSON columns with typed extraction for query performance. External schema registries can also help manage evolution across multiple systems.

6. Leverage Query Analyzer for Advanced Optimization

ClickHouse's query analyzer provides enhanced query optimization capabilities, including better join reordering, predicate pushdown, and cost-based optimization decisions that can dramatically improve complex query performance.

When to apply: Complex queries with multiple JOINs/subqueries that don't perform well with default optimization, especially multi-table queries where join order matters.

How to implement: Enable the new analyzer for better query planning and use EXPLAIN PLAN to understand optimization decisions.

1-- Enable modern analyzer for session
2SET allow_experimental_analyzer = 1;
3
4-- Analyze complex query optimization with new analyzer
5EXPLAIN PLAN
6SELECT 
7    p.category,
8    u.segment,
9    count() as orders,
10    avg(o.amount) as avg_order_value
11FROM orders o
12JOIN products p ON o.product_id = p.product_id
13JOIN users u ON o.user_id = u.user_id
14WHERE o.order_date >= '2023-01-01'
15  AND p.category IN ('electronics', 'books')
16  AND u.segment = 'premium'
17GROUP BY p.category, u.segment;
18
19-- Compare execution with traditional analyzer
20SET allow_experimental_analyzer = 0;
21EXPLAIN PLAN [same query];

Alternatives: For queries where the new analyzer doesn't provide benefits, you can selectively disable it. Manual join reordering and query restructuring can also achieve similar optimizations when analyzer improvements aren't sufficient.

7. Implement Query Condition Cache for Repeated Patterns

Query condition cache helps ClickHouse optimize frequently repeated query patterns by caching the results of condition evaluations and query planning decisions, reducing overhead for similar queries.

When to apply: Many similar queries differing only in parameter values, especially dashboard/reporting workloads with repeated patterns and different time ranges.

How to implement: Configure query cache settings to cache results and condition evaluations for parameterized queries with stable patterns.

‍

Alternatives: For highly dynamic queries, query caching may not provide benefits and can consume memory. Consider using materialized views for stable aggregation patterns or result set caching at the application level.

When ClickHouse optimization reaches its limits: The e6data alternative

Even after implementing primary key optimization, skip indices, materialized views, and distributed query tuning, some BI/SQL workloads still face performance bottlenecks. That's where e6data comes in.

e6data is a decentralized, Kubernetes-native lakehouse compute engine delivering 10x faster query performance with 60% lower compute costs through per-vCPU billing and zero data movement. It runs directly on existing data formats (Delta/Iceberg/Hudi, Parquet, CSV, JSON), requiring no migration or rewrites. Teams often keep their existing ClickHouse platform for development workflows while offloading performance-critical queries to e6data for sub-second latency and 1000+ QPS concurrency.

Key benefits of the e6data approach:

• Superior performance architecture: Decentralized vs. legacy centralized systems eliminates coordinator bottlenecks, delivers sub-second latency, and handles 1000+ concurrent users without SLA degradation through Kubernetes-native stateless services
• Zero vendor lock-in: Point directly at current lakehouse data with no movement, migrations, or architectural changes required. Full compatibility with existing governance, catalogs, and BI tools
• Predictable scaling & costs: Granular 1-vCPU increment scaling with per-vCPU billing eliminates cluster waste and surprise cost spikes. Instant performance with no cluster spin-up time or manual tuning overhead

‍Start a free trial of e6data and see performance comparison on your own workloads. Use our cost calculator to estimate potential gains.