How to Boost ClickHouse Query Performance with Best Practices

Query Optimization Techniques for ClickHouse Performance

Using Primary Keys and Indexes Effectively

ClickHouse's sparse indexing system is different from traditional databases. Instead of indexing every row, it stores an index entry for every 8192 rows by default. This makes queries fast but means that primary key selection is crucial for performance. If your queries filter by a specific column, it should be part of the primary key.

Best Practices:

• Choose primary keys that match your most common filtering conditions (e.g., ORDER BY timestamp for time-series data).

• Index columns that are frequently used in WHERE clauses.

• Skip indexing columns with very high cardinality unless necessary, as it may not yield performance benefits.

Reducing Over-Fetching: Avoid SELECT *

Fetching unnecessary columns can slow down queries and waste resources. ClickHouse is designed for columnar storage, meaning you should only select the columns you need.

Instead of:

SELECT * FROM logs WHERE event_time > now() - INTERVAL 7 DAY;

Use:

SELECT event_id, user_id, event_time FROM logs WHERE event_time > now() - INTERVAL 7 DAY;

This reduces the amount of data scanned and speeds up query execution.

Filtering and Aggregation Strategies

Efficient filtering and aggregation can significantly reduce processing time:

• Use materialized views to precompute data for frequent queries.

• Leverage projections to store alternative data layouts optimized for specific query patterns.

• Apply WHERE filters before aggregation to limit the number of rows processed.

Example: If your queries frequently calculate daily revenue, create a precomputed materialized view:

CREATE MATERIALIZED VIEW daily_sales AS  
SELECT date, SUM(revenue) FROM sales GROUP BY date;

This avoids expensive recalculations and speeds up analysis.

Best Practices for Filtering and Aggregation

Effective filtering and aggregation techniques can significantly enhance query performance. Materialized views are one of the best practices for query optimization. These views pre-compute and store aggregated data, enabling faster retrieval. For example, you can pre-compute aggregates like counting tacos by price, which allows quick access to results.

Projections offer another powerful tool. They create alternative data representations optimized for specific query patterns. This feature pre-computes data, reducing query execution time. Additionally, leveraging aggregate functions built into ClickHouse minimizes data transfer and processing load. For instance, using functions like SUM or COUNT directly in queries ensures efficient aggregation.

Avoiding full table scans is equally important. Use filtering conditions that leverage primary keys and specify only the necessary columns. This approach reduces the amount of data processed, leading to faster queries. By adopting these techniques, you can pre-compute aggregates and achieve better performance in your ClickHouse database.

Optimizing JOIN Operations and Subqueries

ClickHouse performs best when working with pre-aggregated or denormalized data, as JOINs can be expensive. However, when JOINs are necessary, they should be optimized:

1. Use Distributed Tables for Large Datasets

For large datasets, distributing tables across multiple nodes helps balance the load and ensures queries run in parallel.

2. Filter Data Before JOINs

Instead of joining full tables, apply filtering conditions before the JOIN to reduce the number of rows processed.

SELECT * FROM orders o  
JOIN customers c ON o.customer_id = c.customer_id  
WHERE o.order_date > now() - INTERVAL 30 DAY;

This ensures ClickHouse only processes relevant data.

3. Choose the Right JOIN Strategy

ClickHouse offers multiple JOIN algorithms:

• Hash JOIN: Best for smaller tables.

• Merge JOIN: Works well for sorted datasets.

• Collocated JOIN: Useful for distributed tables.

4. Avoid Nested Subqueries

Nested subqueries can slow down performance. Instead, use Common Table Expressions (CTEs):

WITH filtered_orders AS (SELECT * FROM orders WHERE revenue > 1000)  
SELECT * FROM filtered_orders;

This makes queries easier to debug and often improves performance.

For real-time workloads, LZ4 is ideal due to its speed, while ZSTD provides a good balance between storage savings and performance.

Using Projections for Faster Queries

Projections in ClickHouse act as precomputed, optimized views of your data, designed to speed up specific query types. Unlike traditional indexes, projections store data in formats tailored for faster reads.

How projections improve performance:

• Precompute aggregations, reducing query execution time.

• Minimize the data scanned for common query patterns.

• Avoid expensive on-the-fly calculations.

Steps to effectively use projections:

1. Identify repetitive query patterns – Look for queries with frequent filtering, aggregation, or sorting.

2. Design projections based on use cases – If filtering by date is common, create a projection sorted by date.

3. Keep projections up to date – ClickHouse updates projections automatically when new data is inserted.

4. Test and monitor – Use ClickHouse profiling tools to measure query improvements.

By designing projections based on your workload, you can drastically speed up query execution without restructuring your data.

Optimizing ClickHouse System Configuration

Tuning Memory and Disk Settings

Efficient resource management is key to a well-performing ClickHouse cluster. Adjusting memory and disk settings helps avoid slow queries and out-of-memory errors.

Key configurations:

• Memory limits: Adjust max_memory_usage and max_memory_usage_for_all_query to prevent queries from consuming excessive memory.

• Disk I/O optimization: Use max_bytes_before_external_group_by to control how much data is processed before external sorting.

• Compression settings: Adjust min_compress_block_size and max_compress_block_size to reduce unnecessary disk reads.

Regularly monitor resource usage with tools like system.query_log or system monitoring dashboards.

Optimizing Parallel Query Execution

Parallel query execution helps ClickHouse leverage multiple CPU cores for faster data processing.

Ways to optimize parallelism:

• Increase max_threads to allow queries to use more CPU threads.

• Adjust max_concurrent_queries to balance workload distribution.

• Use distributed tables to enable parallel processing across multiple nodes.

For large analytical workloads, properly tuning parallel execution parameters can dramatically improve performance.

Monitoring and Profiling Queries

Monitoring query performance helps identify inefficiencies and improve execution speed. ClickHouse integrates with tools like Prometheus, Grafana, and Zabbix for real-time monitoring.

Key monitoring techniques:

• Use EXPLAIN to analyze query execution plans.

• Track slow queries in system.query_log.

• Set up alerts for high memory or disk usage.

Continuous monitoring ensures your ClickHouse setup remains efficient and scalable.

Using the OPTIMIZE Command for Maintenance

Over time, ClickHouse creates many small data parts, which can slow down queries. Running the OPTIMIZE command merges these parts into larger, more efficient segments.

Example usage:

OPTIMIZE TABLE sales_data FINAL;

This reduces fragmentation and speeds up queries. However, running this command too often can consume resources, so it’s best scheduled during low-traffic periods.

Hardware and Scaling Considerations

Leveraging SSDs for High-Performance Storage

Storage plays a huge role in ClickHouse performance. SSDs are significantly faster than HDDs and help with rapid data retrieval.

Storage recommendations:

• Use Provisioned IOPS SSDs for workloads requiring high throughput.

• Implement tiered storage, keeping frequently accessed data on SSDs and colder data on cheaper storage.

Scaling ClickHouse Clusters Effectively

Scaling ClickHouse ensures your system can handle growing workloads. Key techniques include:

• Replication: Keeps multiple copies of data for high availability.

• Sharding: Distributes data across multiple nodes for better performance.

• Autoscaling: If using cloud-based ClickHouse, enable autoscaling to dynamically adjust resources.

Choosing the Right Hardware

Selecting the right hardware optimizes ClickHouse’s speed and reliability.

Matching your hardware to your workload ensures efficient performance and smooth scaling.

If ClickHouse Isn’t Enough, Consider Alternatives

ClickHouse is excellent for real-time analytics, but it has limitations when dealing with complex JOINs, schema changes, and multi-table queries. If these limitations become bottlenecks, StarRocks offers a strong alternative.

Why Demandbase Migrated from ClickHouse to StarRocks

Demandbase initially relied on ClickHouse for real-time analytics but encountered several challenges:

Problems with ClickHouse:

• Required denormalization because JOINs were slow.

• Storage costs increased 10x due to data duplication.

• Schema changes required full data reloads.

• Managing 40+ ClickHouse clusters was operationally complex.

How StarRocks Helped:

• Handled JOINs efficiently, eliminating the need for denormalization.

• Reduced storage costs by allowing normalized data structures.

• Enabled faster schema evolution, so adding columns did not require full data reloads.

• Simplified infrastructure, reducing 40+ clusters to a single StarRocks cluster.

• Integrated seamlessly with Iceberg, allowing queries on data lakes without ETL pipelines.

When to Choose StarRocks Over ClickHouse

ClickHouse is a powerful tool for real-time analytics, but it has limitations when dealing with complex JOINs, schema evolution, and multi-table workloads. If your workload involves:

• Frequent schema changes

• Heavy reliance on JOINs

• Integration with a lakehouse architecture

StarRocks provides a better alternative, offering faster queries on normalized data, better scalability, and a more flexible schema evolution process. Demandbase’s migration demonstrates how companies dealing with large, dynamic datasets can benefit from moving beyond ClickHouse when needed.

FAQ

What is the best way to monitor ClickHouse performance?

To effectively monitor ClickHouse performance, use a combination of built-in system tables and external monitoring tools:

• System Tables:

  • system.query_log: Provides insights into query execution, including duration, read/written rows, and errors.
  • system.processes: Displays currently running queries and their resource consumption.
  • system.metrics & system.events: Track internal ClickHouse metrics like memory usage, CPU load, and background merges.

• Monitoring Tools:

  • Prometheus: Collects ClickHouse metrics for real-time monitoring.
  • Grafana: Visualizes metrics and alerts on performance issues.
  • ClickHouse Keeper (if used as a ZooKeeper replacement): Monitor for leader election or failure scenarios.

Regular monitoring helps detect slow queries, resource bottlenecks, and storage inefficiencies. Automated alerting based on thresholds (e.g., excessive memory consumption or slow merge operations) is recommended.

How often should I run the OPTIMIZE command?

The OPTIMIZE command consolidates parts in MergeTree tables to improve query efficiency and reduce storage overhead. However, running it too frequently can impact performance.

• When to run OPTIMIZE:

  • Check system.parts for excessive small parts, which can slow down queries.
  • If you use ReplacingMergeTree, ensure that duplicates have been removed before running OPTIMIZE FINAL.
  • If background merges are not keeping up due to high ingestion rates, manual optimization might be required.

• Best Practices:

  • Schedule during low-traffic periods to avoid unnecessary CPU and disk I/O strain.
  • Use OPTIMIZE table FINAL sparingly, as it forces a full merge and can be expensive.
  • Enable optimize_on_insert=1 for small batches to minimize fragmentation.
  • Monitor system.merges to assess whether automatic merging is sufficient before scheduling manual optimizations.

Can I use ClickHouse for real-time analytics?

Yes, ClickHouse is highly optimized for real-time analytics, but best practices should be followed to maximize performance:

• Storage & Compression:

  • ClickHouse’s columnar storage allows efficient read queries on high-velocity data.
  • Use LZ4 compression for fast decompression, balancing speed and storage efficiency.

• Indexing & Partitioning:

  • Primary key indexing (ORDER BY clause) ensures faster lookups and sorting.
  • Granularity tuning (e.g., index_granularity) optimizes storage scanning.
  • Partitioning by date/time can significantly improve query speed on time-series data.

• Streaming & Ingestion:

  • Kafka Engine: Directly ingest streaming data from Kafka.
  • Materialized Views: Pre-aggregate real-time data for instant insights.
  • MergeTree engine: Supports efficient inserts while balancing read performance.

ClickHouse is widely used for use cases like real-time dashboards, anomaly detection, and time-series analytics.

How do I choose the right table engine for my workload?

ClickHouse offers multiple table engines, each optimized for different workloads:

Choose an engine based on your query patterns, storage needs, and workload.

What hardware upgrades improve ClickHouse performance the most?

ClickHouse is CPU-bound but also benefits from fast storage and memory upgrades. Key hardware optimizations include:

• Storage (Most Critical):

  • Use NVMe SSDs for high IOPS and lower query latency.
  • RAID-10 is recommended for redundancy and performance.
  • Store frequently accessed data in RAM with storage_policy configurations.

• CPU:

  • High-core-count multi-threaded CPUs (e.g., AMD EPYC, Intel Xeon) improve query parallelization.
  • AVX-512 instructions enhance vectorized query execution.

• Memory (RAM):

  • More RAM reduces disk reads by caching query results.
  • Aim for at least 64GB+ RAM, or enough to fit hot data in memory.
  • Adjust max_memory_usage and max_threads settings for better resource utilization.

• Network (for clusters):

  • Use 10GbE+ networking for low-latency distributed queries.
  • Optimize ClickHouse’s replication settings (max_replication_lag) to avoid slow query performance in multi-node setups.

Tuning ClickHouse parameters (e.g., merge_tree_max_rows_to_use_cache) along with hardware optimizations can dramatically improve performance.