SingleStore vs. ClickHouse: A Real-World Performance Analysis for Modern Analytics

Clock Icon

6 min read

Pencil Icon

May 29, 2025

Performance analytics are critical to modern intelligent applications. Here’s an initial competitive analysis that compares SingleStore to ClickHouse. 

SingleStore vs. ClickHouse: A Real-World Performance Analysis for Modern Analytics

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-mattersWhy 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-methodologyOur 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-truthsRound 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.

Figure 1: Round one: Untuned ClickHouse vs. untuned SingleStore. TPC-DS query 3.

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-click-house-every-advantageRound 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-tellingThe 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.

Figure 2: Round two: Tuned ClickHouse on large join
Figure 3: Round two: Small table join

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-implicationsKey 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-analyticsWhy 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-forwardLooking 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-lineThe 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.


appendixAppendix

methodologyMethodology

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.

queriesQueries

Figure 1 — TPC-DS query 3

1

define AGGC= text({"ss_ext_sales_price",1},{"ss_sales_price",1},{"ss_ext_discount_amt",1},{"ss_net_profit",1});

2

define MONTH = random(11,12,uniform);

3

define MANUFACT= random(1,1000,uniform);

4

define _LIMIT=100;

5

6

select

7

dt.d_year,

8

item.i_brand_id brand_id,

9

item.i_brand brand,

10

sum([AGGC]) sum_agg /* sum(ss_ext_sales_price) sum_agg */

11

from

12

date_dim dt,

13

store_sales,

14

item

15

where

16

dt.d_date_sk = store_sales.ss_sold_date_sk

17

and store_sales.ss_item_sk = item.i_item_sk

18

and item.i_manufact_id = [MANUFACT]

19

and dt.d_moy=[MONTH]

20

group by

21

dt.d_year,

22

item.i_brand,

23

item.i_brand_id

24

order by

25

dt.d_year,

26

sum_agg desc,

27

brand_id

28

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

7

i_item_desc,

8

w_warehouse_name,

9

d1.d_week_seq,

10

sum(case when p_promo_sk is null then 1 else 0 end) no_promo,

11

sum(case when p_promo_sk is not null then 1 else 0 end) promo,

12

count(*) total_cnt

13

from catalog_sales

14

join inventory on (cs_item_sk = inv_item_sk)

15

join warehouse on (w_warehouse_sk=inv_warehouse_sk)

16

join item on (i_item_sk = cs_item_sk)

17

join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk)

18

join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk)

19

join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk)

20

join date_dim d2 on (inv_date_sk = d2.d_date_sk)

21

join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk)

22

left outer join promotion on (cs_promo_sk=p_promo_sk)

23

left outer join catalog_returns on (cr_item_sk = cs_item_sk and cr_order_number = cs_order_number)

24

where d1.d_week_seq = d2.d_week_seq

25

and inv_quantity_on_hand < cs_quantity

26

and d3.d_date > d1.d_date + 5

27

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_seq

31

order by

32

total_cnt desc,

33

i_item_desc,

34

w_warehouse_name,

35

d_week_seq

36

limit 100;

Figure 3 — Small join (loosely based on TPC-DS query 17)

1

define SDT = random(2450816,2452642,uniform);

2

3

select

4

s_store_id ,

5

count(ss_quantity) as store_sales_quantitycount,

6

avg(ss_quantity) as store_sales_quantityave,

7

stddev_samp(ss_quantity) as store_sales_quantitystdev,

8

stddev_samp(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov,

9

sum(ss_sales_price) as store_sales_spricesum,

10

avg(ss_sales_price) as store_sales_spriceave,

11

avg(ss_wholesale_cost) as store_sales_wscostave,

12

sum(ss_list_price) as store_sales_lpricesum,

13

avg(ss_list_price) as store_sales_lpriceave,

14

sum(ss_ext_tax) as store_sales_taxsum ,

15

max(ss_ext_sales_price) as store_sales_espricemax,

16

min(ss_ext_list_price) as store_sales_elpricemin

17

from

18

store_sales,

19

store

20

where

21

s_store_sk = ss_store_sk AND

22

ss_sold_date_sk = SDT

23

group by s_store_id;

Share