We benchmarked the five major cloud data warehouses at 1B–100B rows using their real billing models to measure performance per dollar. Results show how cost-performance shifts as data grows.

large_518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

medium_518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

small_518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

thumbnail_518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

518800743-54d259df-9a1b-4798-b91c-5fea9f2e00ab.jpg

How the 5 major cloud data warehouses compare on cost-performance

> **TL;DR** 
Replacing joins with in-memory dictionaries in ClickHouse Cloud gave us up to 6.6× faster query times and 60% lower cost, requiring only minor edits to the SQL. This post walks through the tuning technique (dictionaries), how to use them for dimension table lookups, and real benchmark results from queries over 1.4 billion rows. Charts for other dataset sizes are in GitHub.

## Why stop at fast?

If you’re joining us here first, no worries. Joins in ClickHouse already perform great with zero tuning. In [Part 1](https://clickhouse.com/blog/join-me-if-you-can-clickhouse-vs-databricks-snowflake-join-performance), we ran 17 join-heavy queries with no tweaks, and ClickHouse still came out ahead: faster and cheaper than Databricks and Snowflake.

This post is about going one step further. If you want even more speed, ClickHouse-native dictionaries can get you there, fast, cheap, and with minimal effort.

So now you might be wondering… should I migrate?

The good news: it’s low friction. In Part 1, we simply created tables with a matching schema and loaded the data directly from external Iceberg tables. (It could’ve been Delta Lake or just Parquet on S3.) The join queries ran unmodified, fast and cheap, right out of the gate.

But why stop there? Let’s see how far we can push performance with a little ClickHouse-native tuning.

In this post, we’ll show how turning your dimension tables into in-memory dictionaries can supercharge your queries. It’s one of the easiest ways to turbocharge join lookups, and it’s a drop-in change that can unlock serious performance gains.

Let’s dive in.


## Supercharge your joins with dictionaries

Let’s introduce the star of this tuning technique: [dictionaries](https://clickhouse.com/blog/faster-queries-dictionaries-clickhouse).

If you’re running queries with joins, especially foreign key joins on small dimension tables in star or snowflake schemas, dictionaries can make a big difference.


### Why dictionaries?

Dictionaries let you load a table into memory as a fast, key-value structure, optimized for ultra-low-latency lookups. When one side of a join fits into memory (like a dimension table), you can replace a traditional join with a dictionary lookup and get a speed boost in return.

Let’s revisit the benchmark setup.

The original dataset simulates orders at a national coffee chain and consists of:

* Sales: the main fact table (orders)
* Products: a product dimension table
* Locations: a store/location dimension table

These were the same tables we used in Part 1, and most of the queries rely on foreign key joins, typically between the large fact table and smaller dimension tables, making them ideal for in-memory dictionaries.

Instead of joining against tables on disk, we’ll load the Products and Locations tables into dictionaries, and show how this simple tuning step unlocks even faster performance.


### From tables to dictionaries: how to migrate dimension tables

Let’s walk through how to turn your existing dimension tables into dictionaries.

ClickHouse dictionaries are incredibly flexible; you can build them from virtually any source ClickHouse can query. You can load them from any format or location that ClickHouse can read from, which includes a wide range of file formats (like Parquet, Arrow, JSON) and integration points (like S3, HDFS, Kafka, and tables in open formats like Iceberg or Delta Lake). If ClickHouse can query it, you can likely build a dictionary from it.


#### Migrating the Locations dimension table: key lookups with a hashed key dictionary

We’re migrating the Locations dimension table from an existing Iceberg table in S3:

<pre><code type='click-ui' language='sql' show_line_numbers='false'>
CREATE DICTIONARY dicts.dict_locations
(
 location_id String,
 record_id String,
 city String,
 state String,
 country String,
 region String
)
PRIMARY KEY location_id
SOURCE(CLICKHOUSE(
 QUERY "
 SELECT
 COALESCE(location_id, '') AS location_id,
 COALESCE(record_id, '') AS record_id,
 COALESCE(city, '') AS city,
 COALESCE(state, '') AS state,
 COALESCE(country, '') AS country,
 COALESCE(region, '') AS region
 FROM icebergS3('s3://clickhouse-datasets/coffeeshop/dim_locations/')"))
LIFETIME(0)
LAYOUT(complex_key_hashed());
</code></pre>

This `dict_locations` dictionary loads from an external Iceberg table and will be automatically available on all ClickHouse Cloud compute nodes. We set the LIFETIME to 0 to indicate that the content is static and won’t be automatically refreshed.

All lookups into the Locations dimension table in the [benchmark queries](https://github.com/sdairs/coffeeshop-benchmark/blob/main/clickhouse-cloud/queries.sql) are **simple key lookups** by `location_id` (e.g. 'AUSTX156' or 'HOUTX133'). We use the [complex_key_hashed](https://clickhouse.com/docs/sql-reference/dictionaries#complex_key_hashed) dictionary layout to support fast in-memory access with String keys, since layouts like [flat](https://clickhouse.com/docs/sql-reference/dictionaries#flat) and [hashed](http://reference) only support UInt64 keys.

> The dictionary layout defines how content is stored in memory, optimized for specific lookup patterns.


#### Migrating the Products dimension table: time-aware lookups with a range dictionary

To give a second source data example, we’re migrating the Products dimension table directly from its native MergeTree table used in Part 1:

<pre><code type='click-ui' language='sql' show_line_numbers='false'>
CREATE OR REPLACE DICTIONARY dicts.dict_products (
 record_id String,
 product_id String,
 name String,
 category String,
 subcategory String,
 standard_cost Float64,
 standard_price Float64,
 from_date Date,
 to_date Date
)
PRIMARY KEY name
SOURCE(CLICKHOUSE(db 'coffeeshop' table 'dim_products'))
LIFETIME(0)
LAYOUT(COMPLEX_KEY_RANGE_HASHED())
RANGE(MIN from_date MAX to_date);
</code></pre>

All lookups into the Products dimension table in the benchmark queries are **range-based** using `from_date` and `to_date` to find which product version was active at a given time (e.g. as [seen](https://github.com/sdairs/coffeeshop-benchmark/blob/dd63a6c60f61d5ff0b145d4ee2cb0ea3406d38c8/clickhouse-cloud/queries.sql#L83) in Q4).

To handle this efficiently, we use a **[Range Dictionary](https://clickhouse.com/docs/sql-reference/dictionaries#range_hashed)**. It’s optimized for exactly this use case: matching a given key and a time to the correct product version. In memory, ClickHouse builds a hash map on the primary key and performs a fast binary search across sorted date ranges.

**Just one catch**: make sure the source data has no overlapping ranges for the same key, otherwise, the dictionary will only return the first match, which may not be correct. We [checked](https://pastila.nl/?01c0241e/fc6223d0aa17106f21f85d1bf84671e6#ROElgbNXajmPEZCgL3xrkQ==) that there are no overlapping ranges in the original Products dimension table.

> ClickHouse supports more dictionary layouts for advanced use cases. Like [regex matching](https://www.youtube.com/watch?v=ESlAhUJMoz8&amp;index=51), [CIDR range lookup](https://www.youtube.com/watch?v=4dxMAqltygk&amp;index=32), or [point-in-polygon geospatial queries](https://www.youtube.com/watch?v=FyRsriQp46E&amp;index=19). We won’t use them here, but if you’re classifying user agents, geolocating IPs, or mapping GPS points, they’re worth checking out.


### Updating your queries to use dictionaries

Once the dictionaries are set up, updating the queries is straightforward. Each join becomes a lightning-fast in-memory lookup. Here’s how we transformed the joins:

<pre><code type='click-ui' language='sql' show_line_numbers='false'>
 SELECT
 f.order_date,
 l.city,
 p.subcategory
 FROM fact_sales f
① JOIN dim_locations l
 ON f.location_id = l.location_id
② JOIN dim_products p
 ON f.product_name = p.name
 AND f.order_date BETWEEN p.from_date AND p.to_date
 WHERE …
 GROUP BY …
 ORDER BY …
</code></pre>

The Sales fact table is joined with:

① **The Locations dimension table** using `location_id` as join key

② **The Products dimension table** using a join key combination of `product_name` and a date range filter (`order_date` BETWEEN `from_date` AND `to_date`)

We swap the two JOINs from above with two dictionary lookups:

<pre><code type='click-ui' language='sql' show_line_numbers='false'>
 SELECT
 f.order_date,
① dictGet(
 'dicts.dict_locations', 'city',
 f.location_id) AS city,
② dictGet(
 'dicts.dict_products','subcategory',
 f.product_name, f.order_date) AS subcategory,
 FROM fact_sales f
 WHERE …
 GROUP BY …
 ORDER BY …
</code></pre>

① A straightforward **one-to-one lookup in the dict_locations dictionary** using `location_id` as the key.
 → This replaces a simple equality join with an in-memory dictionary lookup.


② A **range-based lookup in the dict_products dictionary** using a compound key of `product_name` and `order_date`.
 → This replaces a join with a range predicate (`order_date` BETWEEN `from_date` AND `to_date`) and ensures the correct product version is returned based on time.

These small changes remove full joins from the query plan, replacing them with ultra-fast lookups in memory. The pattern is repeatable and easy to apply across [all](https://github.com/sdairs/coffeeshop-benchmark/blob/main/clickhouse-cloud-tuned/queries.sql) 17 benchmark queries.


## Results: How much faster?

With all the dimension lookups swapped to dictionaries, and the queries adapted, we re-ran the benchmark queries. Let’s see how much performance we gained.

As a reminder, we ran the benchmark using ClickHouse Cloud services, varying the number of compute nodes (which also let us test ClickHouse Cloud’s [Parallel Replicas](https://clickhouse.com/docs/deployment-guides/parallel-replicas) feature). All service configurations used 30 cores and 120 GB RAM per node. The chart labels below follow this format:

`CH 2n_30c_120g`, where:
- `2n` = number of compute nodes

 - `30c` = CPU cores per node

- `120g` = RAM per node (in GB)

To keep things focused, we’re only showing results for the 1b scale (1.4 billion rows in the fact table). Charts for the 500m and 5b scales are available in the [GitHub repository](https://github.com/sdairs/coffeeshop-benchmark/tree/main/charts/tuned).


### Total runtime

As in Part 1, the chart below shows the total runtime (in seconds) per system for running all 17 benchmark queries sequentially. Each bar also includes the total cost to run all queries, shown in parentheses.

![image.png](https://cms.clickhouse-dev.com:1337/uploads/image_388111140b.png)

Here’s how tuned ClickHouse stacks up against its untuned baseline, same compute, same dataset, just faster queries:

* **2 nodes (2n_30c_120g)**
 * Untuned: 251.05s
 * Tuned: 133.78s
 → That’s a **1.9× speedup**, and cuts costs by 47% just by switching from joins to dictionaries.



* **4 nodes (4n_30c_120g)**
 * Untuned: 182.38s
 * Tuned: 71.54s
 → A **2.5× speedup**, and it also cuts costs by over 60% ($0.907 → $0.356).




* **8 nodes (8n_30c_120g)**
 * Untuned: 169.19s
 * Tuned: 47.31s
 → A **3.5× speedup**, and the cost is cut by 44%. It’s also cheaper than the fastest non-ClickHouse service, while being dramatically faster.


### Runtime per query (excluding Q10 & Q16)

The next chart breaks down the total runtime into individual query runtimes.

![perf_excl_q10_q16_1b.png](https://cms.clickhouse-dev.com:1337/uploads/perf_excl_q10_q16_1b_9eddc4e8af.png)

Let’s break down a few standout queries where dictionaries had the biggest impact:


Q04
* 2n: 7.96s → 2.95s
* 4n: 3.86s → 1.47s
* 8n: 2.06s → 0.79s
 → ~2.5–3× speedup, a huge benefit from replacing the join with a dictionary

Q08
* 2n: 8.19s → 3.55s
* 4n: 4.00s → 2.55s
* 8n: 2.17s → 1.29s
 → Big gains: ~2–2.7× faster

Q15
* 2n: 16.69s → 6.48s
* 4n: 8.22s → 3.95s
* 8n: 5.54s → 2.02s
 → ~2–3× speedup, particularly impressive at 2 and 8 nodes


Q17
* 2n: 7.30s → 3.95s
* 4n: 3.99s → 2.48s
* 8n: 2.66s → 1.37s
 → Another consistent ~2× gain


### Runtime per query (Q10 & Q16 only)

Finally, here’s a separate chart for Q10 and Q16. As noted in Part 1, these two are outliers, much slower than the others, and would distort the scale of the full chart. That’s why the original posts broke them out separately.

![perf_q10_q16_1b.png](https://cms.clickhouse-dev.com:1337/uploads/perf_q10_q16_1b_5f9196109d.png)

Here’s how much tuning improved performance for Query 10 and 16:

**Query 10**: Large join + filtering
* 2n config: From 92.69s → 35.20s
 → ~2.6× speedup

* 4n config: From 81.05s → 17.72s
 → ~4.6× speedup

* 8n config: From 89.44s → 13.84s,
 → ~6.6× speedup, faster than anything else on the chart

**Query 16**: Complex, expensive join
* 2n config: From 73.66s → 32.30s
 → ~2.3× speedup

* 4n config: From 59.00s → 23.34s
 → ~2.5× speedup

* 8n config: From 54.35s → 14.44s
 → ~3.7× speedup, once again the fastest overall


## Fast gets faster

ClickHouse was already fast out of the box. But with a few targeted changes, replacing traditional joins with in-memory dictionaries, we made it both faster and cheaper.



* On join-heavy queries, we saw speedups of up to 6.6× and cost savings of over 60%.

* Even the largest, most complex queries (like Q10 and Q16) finished minutes faster with simple dictionary lookups.

* These improvements required no data reloading, no schema rewrites, and scaled cleanly from 2 nodes to 8.

If you’re running joins against dimension tables, dictionaries are a no-brainer. They’re fast, flexible, and require almost no friction to adopt. And if you’re evaluating ClickHouse, this is your sign: the engine isn’t just fast, it keeps getting faster the more you use it.

We took the same JOIN-heavy benchmark from Part 1 and made ClickHouse even faster. By replacing JOINs with in-memory dictionaries, we saw up to 6.6× faster queries and over 60% cost savings, with no data reloading or schema rewrites required.

large_7tuned.png

medium_7tuned.png

small_7tuned.png

thumbnail_7tuned.png

7tuned.png

Join me if you can: ClickHouse vs. Databricks vs. Snowflake - Part 2

Discover the fastest and most efficient ClickHouse input formats with our benchmark-driven insights—plus how to optimize inserts for maximum performance.

large_Blog_InputFormatMatchup_022025_FNL (1).png

medium_Blog_InputFormatMatchup_022025_FNL (1).png

small_Blog_InputFormatMatchup_022025_FNL (1).png

thumbnail_Blog_InputFormatMatchup_022025_FNL (1).png

Blog_InputFormatMatchup_022025_FNL (1).png

ClickHouse Input format matchup: Which is fastest & most efficient

ClickHouse Cloud now scales analytical queries with parallel replicas, fanning a single query across thousands of cores for terabyte-per-second throughput. This post dives into the internals and lets you see and feel the speed.

large_494080434-75bfc47f-9956-43ed-94dd-f29e6487a2da.jpg

medium_494080434-75bfc47f-9956-43ed-94dd-f29e6487a2da.jpg

small_494080434-75bfc47f-9956-43ed-94dd-f29e6487a2da.jpg

thumbnail_494080434-75bfc47f-9956-43ed-94dd-f29e6487a2da.jpg

494080434-75bfc47f-9956-43ed-94dd-f29e6487a2da.jpg

How we scaled raw GROUP BY to 100 B+ rows in under a second

Explore how ClickHouse’s new JSON data type outperforms leading JSON databases with unmatched storage efficiency and lightning-fast query speed—all while storing JSON data in a single field and staying true to the promise of JSON databases

large_Blog_BillionDocsJSONchallenge_012025_FNL.png

medium_Blog_BillionDocsJSONchallenge_012025_FNL.png

small_Blog_BillionDocsJSONchallenge_012025_FNL.png

thumbnail_Blog_BillionDocsJSONchallenge_012025_FNL.png

Blog_BillionDocsJSONchallenge_012025_FNL.png

The billion docs JSON Challenge: ClickHouse vs. MongoDB, Elasticsearch, and more

Continuing the series on ClickHouse's support for JOINs, read about how to choose the right join algorithm that fits your scenario best.

large_thumbnail.png

medium_thumbnail.png

small_thumbnail.png

thumbnail_thumbnail.png

thumbnail.png

Choosing the Right Join Algorithm

Read about how using asynchronous data inserts can simplify your scenario by shifting the batching of data from the client side to the server side.

large_header.png

medium_header.png

small_header.png

thumbnail_header.png

header.png

Asynchronous Data Inserts in ClickHouse

Find out how our engineers boosted the performance of ClickHouse Cloud with a new table engine optimized for cloud-native data processing. What's more, it speeds up a new feature called Lightweight Updates.

large_banner.png

medium_banner.png

small_banner.png

thumbnail_banner.png

banner.png

ClickHouse Cloud boosts performance with SharedMergeTree and Lightweight Updates

Read about why we built a resource-efficient alternative for Zookeeper from scratch in C++, what our next big step with it is, and how you can join the Keeper community.

large_cover.png

medium_cover.png

small_cover.png

thumbnail_cover.png

cover.png

ClickHouse Keeper: A ZooKeeper alternative written in C++

![large_data_loads-p2-01.png](/uploads/large_data_loads_p2_01_e9832557ed.png)

This blog post is part of the `Supercharging your large ClickHouse data loads` series:
* [Performance and resource usage factors](/blog/supercharge-your-clickhouse-data-loads-part1)
* [Making a large data load resilient](/blog/supercharge-your-clickhouse-data-loads-part3)

## Going on a race track

In this second part of our three-part blog series about supercharging large data loads, we will put the theory from the [first](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1) post into practice by demonstrating how you can run a large multi-billion row insert 3 times faster than with default settings using the same hardware. We outline a formula that you can use to determine the best block size / parallelism ratio for your ingestion use case. And lastly, we will break the speed limit by utilizing ClickHouse Cloud’s new SharedMergeTree table engine combined with easy horizontal cluster scaling.

Note that we only demonstrate a tuning scenario here, where data is pulled by the ClickHouse servers themselves. You can read about a setting where clients push the data to ClickHouse [here](https://clickhouse.com/blog/asynchronous-data-inserts-in-clickhouse).

## The challenge


Using ClickHouse for <a href="https://clickhouse.com/blog/asynchronous-data-inserts-in-clickhouse#data-needs-to-be-batched-for-optimal-performance">large data loads</a> is like driving a high-performance Formula One car &#x1F3CE; .
A copious amount of raw horsepower is available, and you can reach top speed for your large data load. But, to achieve maximum ingestion performance, you must choose (1) a high enough gear (<a href="https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#insert-block-size">insert block size</a>) and (2) an appropriate acceleration level (<a href="https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#insert-parallelism">insert parallelism</a>) based on (3) the concrete amount of available horsepower (<a href="https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#hardware-size">CPU cores and RAM</a>).


Ideally, we would like to drive our race car in the **highest gear** with **full acceleration**:

* Highest gear: The larger we [configure the insert block size](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#configuration-when-data-is-pulled-by-clickhouse-servers), the fewer parts ClickHouse has to create, and the fewer [disk file](https://en.wikipedia.org/wiki/Category:Disk_file_systems) [i/o](https://en.wikipedia.org/wiki/Input/output) and [background merges](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#more-parts--more-background-part-merges) are required.
* Full acceleration: The higher we [configure the number of parallel insert threads](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#configuration-when-data-is-pulled-by-clickhouse-servers-1), the faster the data will be processed.

However, there is a conflicting tradeoff between these two performance factors (plus a tradeoff with background part merges). The amount of available main memory of ClickHouse servers is limited. Larger blocks use [more](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#impact-on-performance-and-resource-usage) main memory, which limits the number of parallel insert threads we can utilize. Conversely, a higher number of parallel insert threads requires [more](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#impact-on-performance-and-resource-usage-1) main memory, as the number of insert threads determines the number of insert blocks created in memory concurrently. This limits the possible size of insert blocks. Additionally, there can be resource contention between insert threads and [background merge threads](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#more-parts--more-background-part-merges). A high number of configured insert threads (1) creates more parts that need to be merged and (2) takes away CPU cores and memory space from background merge threads.

Our challenge is to find a sweet spot with a large enough block size and a large enough number of parallel insert threads to reach the best possible insert speed.


## The experiment

In the following sections, we put these block size / parallelism settings to the test and evaluate their effect on ingest speed.


### Test data

 The data we experiment with is from the [PyPi dataset](https://clickhouse.com/blog/clickhouse-vs-snowflake-for-real-time-analytics-benchmarks-cost-analysis#pypi-dataset) (hosted in a GCS bucket). It consists of **65.33 billion rows** and has a raw, uncompressed size of ~**14 TiB**. The compressed data size on disk (when stored in a ClickHouse table) is ~**1.2 TiB**.


### Test table and insert query

For these tests, we will ingest this data as fast as possible into a [table](https://gist.github.com/tom-clickhouse/50ec22c9cf6a5569c45d1aea958a09d4) in a ClickHouse Cloud [service that c](https://clickhouse.com/pricing)onsists of 3 ClickHouse servers with 59 CPU cores and 236 GiB of RAM per server. We’ll use an [INSERT INTO SELECT query](https://gist.github.com/tom-clickhouse/808dd03d7bc4cf061bf98b7bbb77c3a6) in combination with the [s3Cluster](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#parallel-servers) integration table function (which is compatible with gcs).
![large_data_loads-p2-02.png](/uploads/large_data_loads_p2_02_a34ea2bb28.png)

We’ll start by ingesting our large dataset with the default settings for [insert block size](https://clickhouse.com/docs/en/operations/settings/settings#min-insert-block-size-bytes) and [insert threads](https://clickhouse.com/docs/en/operations/settings/settings#max-insert-threads), and will then repeat this load with different numbers to assess the impact on load time.


### Queries for introspecting performance metrics

We are using the following SQL queries over [system tables](https://clickhouse.com/blog/clickhouse-debugging-issues-with-system-tables) for introspecting insert performance metrics visualized in our test [results](/blog/supercharge-your-clickhouse-data-loads-part2#top-speed):



* [Number of active parts at ingest finish](https://gist.github.com/tom-clickhouse/5c17431de8716041e10acde1ef379a8a)
* [Time to reach less than 3k active parts](https://gist.github.com/tom-clickhouse/d4df796c10a318a4c95061362e486ab5)
* [Number of initial parts written to storage ](https://gist.github.com/tom-clickhouse/beebf6b9f7876b4c100c4e234771b221)
* [Number of initial parts written to storage per cluster node](https://gist.github.com/tom-clickhouse/1435eba5e76701bee00a9145f40aec79)

## Shifting gears
![large_data_loads-p2-03.png](/uploads/large_data_loads_p2_03_dc77564d85.png)


To illustrate how insert block sizes impact the number of parts that get created (and need to be merged), we visualize below various [insert block sizes](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#insert-block-size) (in terms of rows) versus the (uncompressed) average in-memory size of a single data block and its corresponding ([compressed](https://clickhouse.com/docs/en/about-us/distinctive-features#data-compression)) average on-disk size of a single data [part](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-data-storage). This is overlayed with the average overall number of parts written to storage. Note that we give average values because, as [mentioned](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#configuration-when-data-is-pulled-by-clickhouse-servers) in the first post, the created blocks and parts rarely precisely contain the configured number of rows or bytes, as ClickHouse is [streaming](https://clickhouse.com/company/events/query-performance-introspection) and processing data row-[block](https://clickhouse.com/docs/en/operations/settings/settings#setting-max_block_size)-wise. Therefore these settings specify minimum thresholds.

_Also, note that we took these numbers from the [results](/blog/supercharge-your-clickhouse-data-loads-part2#top-speed) of different ingest test runs in the next section._

![large_data_loads-p2-04.png](/uploads/large_data_loads_p2_04_1b4f466d4f.png)
You can see that the larger we [configure](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#configuration-when-data-is-pulled-by-clickhouse-servers) the insert block size, the fewer parts need to be created (and [merged](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#more-parts--more-background-part-merges)) for storing the test data, ~1.2 TiB of compressed table data. For example, with an insert block size of 1 million rows, 65 thousand initial parts (with an average compressed size of 20 MiB) will be created on disk. Whereas with an insert block size of 80 million rows, only 817 initial parts get created (with an average compressed size of 1.5 GiB).


## Standard speed

The following chart visualizes the [result](https://gist.github.com/tom-clickhouse/6c2bd2b6bd18aca4a1cce665d0c806fe) of ingest runs with the default insert block size of [~1 million rows](https://clickhouse.com/docs/en/operations/settings/settings#min-insert-block-size-rows) but different numbers of insert threads. We show the peak memory used and the resulting time for the number of parts to reach 3000 (the [recommended](https://clickhouse.com/docs/en/operations/settings/merge-tree-settings#parts-to-throw-insert) number for efficient querying):
![large_data_loads-p2-05.png](/uploads/large_data_loads_p2_05_8bcb732865.png)
With the ClickHouse Cloud default setting of 4 ingest threads (default is no parallel ingest threads in OSS), the peak amount of consumed main memory is 7 GiB per server. It takes over 4 hours from the start of the ingest until the [65 thousand initially created parts](/blog/supercharge-your-clickhouse-data-loads-part2#shifting-gears) are merged to a [healthy](https://clickhouse.com/docs/en/operations/settings/merge-tree-settings#parts-to-throw-insert) number of less than 3 thousand parts. With its default settings, ClickHouse tries to offer a good balance of resource usage for supporting concurrent tasks from concurrent users. One size doesn’t fit all, though. Our goal here is to tune a dedicated service only used by us for one purpose: Achieving top speed for inserting our dataset.

If we match the amount of utilized parallel insert threads with the number of 59 available CPU cores, the time to 3k active parts is increased compared to lower thread settings.

The lowest time is achieved with 16 insert threads, leaving plenty of the 59 CPU cores available for [background part merge threads](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#more-parts--more-background-part-merges) that run concurrently with the data insertion.

Note that the blue horizontal bar on top in the chart above denotes the available memory per server (236 GiB RAM). We can see that there is much room for increasing insert block size and memory usage (blue vertical bars in the chart above), respectively.

Currently, we are pushing the accelerator pedal with full force (up to 59 insert threads) in a very low gear (only 1 million rows per initial part), resulting in a low overall ingestion speed. We will now find a better gear and acceleration combination in the next section.

## Top speed
![large_data_loads-p2-06.png](/uploads/large_data_loads_p2_06_0ed79d0f98.png)


We tried to identify the perfect combination of insert block size and number of insert threads by running ingestions with different systematic combinations for these insert speed factors. The following chart visualizes the results of these ingest test runs, where we doubled the insert block size a few times (from [1 million](https://gist.github.com/tom-clickhouse/6c2bd2b6bd18aca4a1cce665d0c806fe) to [5 million](https://gist.github.com/tom-clickhouse/bfd88290a696144de9b5f6d4d07f0c08) to [10 million](https://gist.github.com/tom-clickhouse/4cff7a19f89faac96f53f2e3963403b3) to [20 million](https://gist.github.com/tom-clickhouse/174b81144169ae02dcec1c4518839f53) to [40 million](https://gist.github.com/tom-clickhouse/8955d5a57ad4babf84690a10f90da97a), until [80 million](https://gist.github.com/tom-clickhouse/3570f02b38e8b5f11351cd2d446f7d8c)):


![large_data_loads-p2-07.png](/uploads/large_data_loads_p2_07_357c63e939.png)


For each block size, we doubled the number of insert threads (starting at 4) until the peak memory usage (blue vertical bars) almost reached the available memory per server (blue horizontal bar). The yellow circles indicate the time from the start of the ingest until all initially created parts are merged to a healthy number of less than 3000 parts.

We reached top speed when the 65.33 billion rows of our dataset were ingested by 32 parallel insert threads with an insert block size of 10 million rows. This is the sweet spot for our server size of 59 CPU cores and 236 GiB RAM. This causes a moderate amount of [6.5 thousand initial parts](/blog/supercharge-your-clickhouse-data-loads-part2#shifting-gears), that can be quickly merged to less than 3000 parts in the background by the free 27 CPU cores (32 cores are used for reading and ingesting the data in parallel). With 16 insert threads and less, the insert parallelism is too low. With 59 insert threads, a 10 million rows block size doesn’t fit into the RAM.

A 5 million row block size works with 59 insert threads. Still, it creates [13 thousand initial parts](/blog/supercharge-your-clickhouse-data-loads-part2#shifting-gears) without leaving any dedicated CPU cores for an efficient background merging, resulting in resource contention and a long time to reach 3 thousand active parts. With 32 and 16 insert threads, the data is processed slower but merged faster as more dedicated CPU cores are available for background merges. 4 and 8 insert threads don’t create enough insert parallelism with a 5 million row block size.

Insert block sizes of 20 million rows and more cause [low numbers of initial parts](/blog/supercharge-your-clickhouse-data-loads-part2#shifting-gears), but the high memory overhead restricts insertion to a low number of insert threads creating insufficient insert parallelism.

**With our identified sweet spot settings, we sped up the ingest by a factor of almost 3.** We used exactly the same underlying hardware, but we utilized it more effectively for our data ingestion, without creating too many initial parts by pressing the accelerator pedal in half force (32 insert threads) with the highest possible gear (10 million rows block size).
## Formula One

We are aware that identifying perfect settings for large multi-billion row inserts via several test runs is impractical. Therefore we give you this handy formula for calculating approximately the settings for top speed of your large data loads:


 <code>• `max_insert_threads`: choose ~ half of the available CPU cores for insert threads (to leave enough dedicated cores for background merges)
 • `peak_memory_usage_in_bytes`: choose an intended peak memory usage; either all available RAM (if it is an isolated ingest) or half or less (to leave room for other concurrent tasks) Then: `min_insert_block_size_bytes` = `peak_memory_usage_in_bytes` / (~3 * `max_insert_threads`)
</code>


With this formula, you can set `min_insert_block_size_rows` to 0 (to disable the row based threshold) while setting `max_insert_threads` to the chosen value and `min_insert_block_size_bytes` to the calculated result from the above formula.


## Breaking the speed limit with ClickHouse Cloud
![large_data_loads-p2-08.png](/uploads/large_data_loads_p2_08_ac7345f2e0.png)

The next measure to make the ingestion even faster is to put more horsepower into our engine and increase the number of available CPU cores, respectively, by adding additional servers.

Combined with its [SharedMergeTree table engine](https://clickhouse.com/blog/clickhouse-cloud-boosts-performance-with-sharedmergetree-and-lightweight-updates), ClickHouse Cloud [allows](https://clickhouse.com/blog/clickhouse-cloud-boosts-performance-with-sharedmergetree-and-lightweight-updates#automatic-cluster-scaling) you to freely (and [quickly](https://clickhouse.com/blog/clickhouse-cloud-boosts-performance-with-sharedmergetree-and-lightweight-updates#seamless-cluster-scaling)!) either change the size (CPU and RAM) of existing servers or add additional servers. We did the latter with our service and ingested our dataset with different numbers of ClickHouse servers (each with 59 CPU cores and 236 GB RAM). This chart shows the [results](https://gist.github.com/tom-clickhouse/5517b902d3990f7299d747bedbd5fd77):
![large_data_loads-p2-09.png](/uploads/large_data_loads_p2_09_92bb211121.png)
For each ingestion test run, we used the optimal tuning settings (32 parallel insert threads with an insert block size of 10 million rows) identified in the section [above](/blog/supercharge-your-clickhouse-data-loads-part2#top-speed).

We can see that the ingest throughput scales perfectly linearly with the number of CPU cores and ClickHouse servers, respectively. This enables us to run your large data inserts as fast as you want.


### It can be as fast as you want it to be

ClickHouse Cloud allows you to easily scale ingest duration linearly with additional CPU cores and servers, respectively. Therefore, you can run large ingests as fast as required. The following chart visualizes this:
![large_data_loads-p2-10.png](/uploads/large_data_loads_p2_10_ad4238e5c3.png)
A single ClickHouse Cloud server (with 59 CPU cores and 236 GB RAM) ingests our 65 billion row dataset with a throughput of [~4 million rows per second](/blog/supercharge-your-clickhouse-data-loads-part2#breaking-the-speed-limit-with-clickhouse-cloud) in ~240 minutes. Based on our ingest test runs [above](/blog/supercharge-your-clickhouse-data-loads-part2#breaking-the-speed-limit-with-clickhouse-cloud) proving linear scalability, we project how the throughput and ingest time can be improved with additional servers.


## Summary

In this second part of our three-part blog series, we gave guidance and demonstrated how to tune the major insert performance factors for drastically speeding up a large multi-billion row insert. On the same hardware, the top ingestion speed is almost 3 times faster than with default settings. Additionally, we utilized ClickHouse Cloud’s seamless cluster scaling to make the ingest even faster and illustrated how you can run your large data inserts as fast as you want by scaling nodes.

We hope you learned some new ways to supercharge your large ClickHouse data loads.

In the next and last post of this series we will demonstrate how you can load a large dataset incrementally and reliably over a long period of time.

Stay tuned!

Learn about how you can make your large data load 3 times faster than with default settings. On the same hardware.

Supercharging your large ClickHouse data loads - Part 2: Tuning a large data load for speed

Learn how to guarantee sub-100ms ClickHouse response times for snappy dashboards—no matter how many billions of JSON documents your table holds.

large_Blog_AcceleratingQueriesJSONBluesky_202503_V2.png

medium_Blog_AcceleratingQueriesJSONBluesky_202503_V2.png

small_Blog_AcceleratingQueriesJSONBluesky_202503_V2.png

thumbnail_Blog_AcceleratingQueriesJSONBluesky_202503_V2.png

Blog_AcceleratingQueriesJSONBluesky_202503_V2.png

Accelerating ClickHouse queries on JSON data for faster Bluesky insights

ClickHouse learned to procrastinate strategically. Discover how lazy materialization skips unnecessary column reads to accelerate queries.

large_Blog_CHLazyMaterialization_202504_V2.1.png

medium_Blog_CHLazyMaterialization_202504_V2.1.png

small_Blog_CHLazyMaterialization_202504_V2.1.png

thumbnail_Blog_CHLazyMaterialization_202504_V2.1.png

Blog_CHLazyMaterialization_202504_V2.1.png

ClickHouse gets lazier (and faster): Introducing lazy materialization

ClickHouse is built to run fast on Parquet, the storage format behind open table formats like Iceberg and Delta Lake. This post takes you deep inside the query engine to reveal the tricks behind its speed, and what’s coming next.

large_Blog_CHandParquet_202504_FNL.png

medium_Blog_CHandParquet_202504_FNL.png

small_Blog_CHandParquet_202504_FNL.png

thumbnail_Blog_CHandParquet_202504_FNL.png

Blog_CHandParquet_202504_FNL.png

ClickHouse and Parquet: A foundation for fast Lakehouse analytics

ClickHouse Cloud now has a distributed cache for object storage: shared, low-latency, and built for speed. This post dives into the architecture, why it matters, and how it performs.

large_446559747-9d77c836-9abd-458b-95fe-5de1e15bb634.png

medium_446559747-9d77c836-9abd-458b-95fe-5de1e15bb634.png

small_446559747-9d77c836-9abd-458b-95fe-5de1e15bb634.png

thumbnail_446559747-9d77c836-9abd-458b-95fe-5de1e15bb634.png

446559747-9d77c836-9abd-458b-95fe-5de1e15bb634.png

Building a Distributed Cache for S3

ClickHouse now supports standard SQL UPDATEs using lightweight patch-part updates, up to 1,000× faster than classic mutations in our tests, with minimal worst‑case query overhead.

large_Fast UPDATEs in ClickHouse 6s.png

medium_Fast UPDATEs in ClickHouse 6s.png

small_Fast UPDATEs in ClickHouse 6s.png

thumbnail_Fast UPDATEs in ClickHouse 6s.png

Fast UPDATEs in ClickHouse 6s.png

How we made ClickHouse UPDATEs 1,000× faster (Part 3: Benchmarks)

Read about our performance comparison of ClickHouse vs. Elasticsearch for workloads commonly present in large-scale data analytics and observability use cases – count(*) aggregations over billions of table rows. 

large_Elasticsearch_blog1_banner.png

medium_Elasticsearch_blog1_banner.png

small_Elasticsearch_blog1_banner.png

thumbnail_Elasticsearch_blog1_banner.png

Elasticsearch_blog1_banner.png

ClickHouse vs. Elasticsearch: The Billion-Row Matchup

We took a public benchmark that tests JOIN-heavy SQL queries on Databricks and Snowflake and ran the exact same queries on ClickHouse Cloud. ClickHouse was faster and cheaper at every scale, from 721 million to 7.2 billion rows.

large_5.png

medium_5.png

small_5.png

thumbnail_5.png

5.png

Join me if you can: ClickHouse vs. Databricks vs. Snowflake - Part 1

See how ClickHouse’s update speed stacks up against PostgreSQL on the same data and hardware.

Fast at scale. Efficient by design.
Second to none in cost and performance.

100B-row analytical workload

Our latest results

Cost and performance you can verify

How the 5 major cloud data warehouses compare on cost-performance

How we scaled raw GROUP BY to 100 B+ rows in under a second

You can’t UPDATE what you can’t find: ClickHouse vs PostgreSQL

How we made ClickHouse UPDATEs 1,000× faster (Part 3: Benchmarks)

Join me if you can: ClickHouse vs. Databricks vs. Snowflake - Part 2

Join me if you can: ClickHouse vs. Databricks vs. Snowflake - Part 1

Building a Distributed Cache for S3

ClickHouse and Parquet: A foundation for fast Lakehouse analytics

ClickHouse gets lazier (and faster): Introducing lazy materialization

ClickHouse keeps getting faster, and we're not done yet.

Query time is 2.24× faster than in 2018

Combination of four benchmarks:

Queries per benchmark

Rows per benchmark (log scale)

All proven by benchmarks that can be reproduced by anyone.

Talk to us about your use case

Fast at scale. Efficient by design.Second to none in cost and performance.