Performance analytics are critical to modern intelligent applications. Here’s an initial competitive analysis that compares SingleStore to ClickHouse.
Given the overlap in target use cases of SingleStore and ClickHouse, we wanted to provide objective performance data to help industry users understand where each system excels and how to leverage SingleStore to its best advantage. We were stunned by the poor performance shown by ClickHouse for join queries in particular, which are pervasive in most analytical use cases.
Why this comparison matters
SingleStore delivers the performance you need for enterprise AI — a simplified data architecture to provide single-shot retrieval for AI applications with real-time insights on operational data; transactions, analytics, search and more. ClickHouse identifies its key use cases as real-time analytics, machine learning and gen AI, data warehousing and observability.
We wanted to provide objective performance data to help industry users understand where each system excels and how to leverage SingleStore to its best advantage.
Our testing methodology
We chose to use the TPC-DS dataset as a basis for our analysis. TPC-DS makes use of many features needed by modern analytical workloads and utilizes a star schema. It is based on a realistic use case that aims to provide relevant, objective performance to industry users.
For the analysis, our queries are selected TPC-DS queries and customized queries. TPC-DS is complicated and has 99 queries using joins, common table expressions and more — all features used by modern analytical systems. Due to this complexity and the fact that ClickHouse cannot run all of the queries, we selected a subset of the queries and added some additional ones using the DS schema.
For our testing environment, we used a 1TB TPC-DS dataset with ClickHouse Cloud running on 2x(59 vCores, 236 GB RAM) and SingleStore Helios® on an S-8 configuration.
Round 1: Learning some hard truths
For our first round of experiments, we ran a set of 10 queries. These queries were designed to test a variety of query shapes, and included multiple types of aggregations and filters.
However, all queries except one included a join. We learned ClickHouse was surprisingly slow on joins, factors of 1,000s slower than SingleStore on many queries.
The results from round one were at first shocking, but then became … boring. The graphs all looked similar to the following graph, which shows SingleStore vs. ClickHouse on TPC-DS query 3 on a 1TB dataset. To be very clear, the following graph displays results from untuned ClickHouse with the TPC-DS schema as specified in the benchmark, and the query as written in the benchmark.
The pattern was consistent across queries: SingleStore dramatically outperformed ClickHouse, often by orders of magnitude. These weren't minor differences — they represented fundamental performance gaps that significantly impact real-world applications.
Round 2: Giving ClickHouse every advantage
So, take two. We again tried to understand system performance with a variety of query shapes to really give a solid comparison of where SingleStore and Clickhouse each shine, trying to give a real understanding of the differences between the two systems.
This time, we got rid of most of the joins — we included one small join (a join with a very small table) and we left in one larger join. Often you just need a join — like when you need to separate out privacy-sensitive data into a separate table — or you need the flexibility of accessing data from different perspectives; or you want the ability to update dimension data without rewriting a whole fact table.
In addition, we began to tune ClickHouse as it was clearly necessary. As a first step, we rewrote the query to reorder the joins. Re-writing queries to reorder joins is possible in a benchmark, but is unrealistic in a workload where queries are automatically generated by business intelligence tools. This optimization did significantly improve CH performance.
The results were still telling
The following graph shows SingleStore vs. untuned ClickHouse, and our version one of tuning ClickHouse. Even with tuning efforts, SingleStore maintained significant performance advantages on the large join scenario.
The small join results showed that even with small table joins — theoretically a stronger area for ClickHouse's — SingleStore demonstrated superior performance.
Key findings and critical implications
Our conclusions so far:
ClickHouse requires tuning to perform well — we were surprised at the poor performance of untuned ClickHouse.
SingleStore untuned is much better than untuned ClickHouse. Keep reading to see if we can make ClickHouse match SingleStore's performance.
These findings reveal several critical insights that go beyond simple performance metrics:
- Operational complexity. ClickHouse's requirement for extensive tuning presents a significant operational burden. Achieving acceptable performance requires deep expertise and ongoing optimization efforts, increasing total cost of ownership.
- Out-of-the-box performance. SingleStore's superior untuned performance suggests lower operational complexity and faster time-to-value for organizations implementing analytical solutions.
- Real-world join requirements. The join performance gap isn't just a benchmark artifact. Modern applications frequently require joins for privacy-sensitive data separation, flexible data access patterns, dynamic dimension updates and maintaining normalized data or star schema models for integrity, storage efficiency and ease of understanding by business analysts.
- Configuration complexity. ClickHouse functions more like a database framework than a traditional database system, with numerous configuration options that lead to dramatically different performance characteristics between deployments.
Why these results matter for modern analytics
The performance differences we observed have direct implications for organizations building AI and analytics applications. Join operations are fundamental to real-world analytical workloads, and ClickHouse's limitations in this area force organizations to choose between query performance and data architecture best practices — a compromise that shouldn't be necessary in modern analytical systems.
Furthermore, the tuning requirements for ClickHouse mean organizations must invest significant resources in database optimization and data transformations. This work makes pipelines complex, and adds latency and distracts focus and energy from delivering business value through their analytics applications.
Looking forward
Our work continues and will be described in future blogs. Stay tuned for more on single-table aggregate results, additional tuning experiments and more comprehensive analysis of different query patterns and optimization strategies.
The bottom line
While ClickHouse has earned recognition in the analytics space, our testing reveals significant performance gaps and operational complexity challenges that organizations should carefully consider. SingleStore delivers superior out-of-the-box performance across diverse workload types while maintaining the simplicity and reliability that enterprises need for production AI and analytics applications.
For organizations evaluating analytical databases, these findings suggest SingleStore offers a more robust, user-friendly solution that performs well without extensive tuning, allowing teams to focus on delivering business value rather than database optimization.
The evidence is clear: when it comes to real-world analytical workloads, SingleStore provides the performance and operational simplicity that modern enterprises require for their AI and analytics initiatives.
Want to test how SingleStore outperforms Clickhouse yourself? Start free today.
Benchmarking performed by benchANT.
Appendix
Methodology
These results used a 1TB TPC-DS data set with the schema as defined by TPC-DS. The systems used were SingleStore Helios S-8 and ClickHouse Cloud 2x(59 vCores, 236 GB RAM).
For round one, the workload was a mix of ten queries including TPC-DS query 3 (listed below) and analytics queries inspired by TPC-DS queries. The queries were run in single-threaded mode for a one hour runtime.
For round two the workload was a mix of three queries including the large and small join. The queries were run in single-threaded mode for a one hour runtime.
Queries
Figure 1 — TPC-DS query 3
1
define AGGC=2
text({"ss_ext_sales_price",1},{"ss_sales_price",1},{"ss_ext_discount_amt",1},{3
"ss_net_profit",1});4
define MONTH = random(11,12,uniform);5
define MANUFACT= random(1,1000,uniform);6
define _LIMIT=100;7
8
select dt.d_year9
,item.i_brand_id brand_id10
,item.i_brand brand11
,sum([AGGC]) sum_agg /* sum(ss_ext_sales_price) sum_agg */12
from date_dim dt13
,store_sales14
,item15
where dt.d_date_sk = store_sales.ss_sold_date_sk16
and store_sales.ss_item_sk = item.i_item_sk17
and item.i_manufact_id = [MANUFACT]18
and dt.d_moy=[MONTH]19
group by dt.d_year20
,item.i_brand21
,item.i_brand_id22
order by dt.d_year23
,sum_agg desc24
,brand_id25
limit 100
Figure 2 — Large join, modified TPC-DS query 52
1
define YEAR=random(1998, 2002, uniform);2
define _LIMIT = 100;3
define BP= text({"1001-5000",1},{">10000",1},{"501-1000",1});4
define MS= dist(marital_status, 1, 1);5
6
select i_item_desc7
,w_warehouse_name8
,d1.d_week_seq9
,sum(case when p_promo_sk is null then 1 else 0 end) no_promo10
,sum(case when p_promo_sk is not null then 1 else 0 end) promo11
,count(*) total_cnt12
from catalog_sales13
join inventory on (cs_item_sk = inv_item_sk)14
join warehouse on (w_warehouse_sk=inv_warehouse_sk)15
join item on (i_item_sk = cs_item_sk)16
join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk)17
join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk)18
join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk)19
join date_dim d2 on (inv_date_sk = d2.d_date_sk)20
join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk)21
left outer join promotion on (cs_promo_sk=p_promo_sk)22
left outer join catalog_returns on (cr_item_sk = cs_item_sk and23
cr_order_number = cs_order_number)24
where d1.d_week_seq = d2.d_week_seq25
and inv_quantity_on_hand < cs_quantity26
and d3.d_date > d1.d_date + 527
and hd_buy_potential = '[BP]'28
and d1.d_year = [YEAR]29
and cd_marital_status = '[MS]'30
group by i_item_desc,w_warehouse_name,d1.d_week_seq31
order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq32
LIMIT 100;
Figure 3 — Small join (loosely based on TPC-DS query 17)
1
define SDT = random(2450816,2452642,uniform);2
3
select s_store_id4
,count(ss_quantity) as store_sales_quantitycount5
,avg(ss_quantity) as store_sales_quantityave6
,stddev_samp(ss_quantity) as store_sales_quantitystdev7
,stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov8
,sum(ss_sales_price) as store_sales_spricesum9
,avg(ss_sales_price) as store_sales_spriceave10
,avg(ss_wholesale_cost) as store_sales_wscostave11
,sum(ss_list_price) as store_sales_lpricesum12
,avg(ss_list_price) as store_sales_lpriceave13
,sum(ss_ext_tax) as store_sales_taxsum14
,max(ss_ext_sales_price) as store_sales_espricemax15
,min(ss_ext_list_price) as store_sales_elpricemin16
from store_sales,17
store18
where s_store_sk = ss_store_sk AND19
ss_sold_date_sk = SDT20
group by s_store_id;21
