Free tools Windows power users keep installed
One-click scans. No signup required.
To speed up ClickHouse inserts, avoid sending tiny synchronous writes one row at a time. Buffer rows at the producer and send larger batches when practical; when producers cannot batch conveniently, test ClickHouse asynchronous inserts. For production reliability, explicitly use wait_for_async_insert=1 so the client learns whether the buffered data was flushed successfully.
Why row-by-row inserts slow ClickHouse down
ClickHouse stores inserted data in parts. Frequent small inserts can create parts faster than background merges can consolidate them, increasing CPU and I/O work and potentially affecting query performance. The issue is not simply that each request has overhead: the resulting parts also need to be managed and merged.
| # | Preview | Product | Price | |
|---|---|---|---|---|
| 1 |
|
Up and Running with ClickHouse: Learn and Explore ClickHouse, It's Robust Table Engines for... | $19.95 | Buy on Amazon |
As an Amazon Associate I earn from qualifying purchases.
In a ClickHouse 2023 illustration, 200 synchronous inserts every 10 seconds produced around 200 new parts per second in that workload; the authors reported reaching the active-parts safeguard after five minutes and aborting. This is an example of part pressure, not a general throughput limit. ClickHouse’s asynchronous inserts article describes the example and the underlying trade-off.
Choose where batching should happen
Batching can happen before data reaches ClickHouse, or in ClickHouse’s server-side asynchronous-insert buffers. The right choice depends on whether producers can tolerate the buffering delay and complexity, and whether the application needs an acknowledgment only after data has been flushed.
| Approach | Best fit | Visibility and acknowledgment | Main trade-off |
|---|---|---|---|
| Producer-side synchronous batches | Producers can aggregate rows without unacceptable latency or memory cost. | The client receives the synchronous insert result. | Producers must implement and operate buffering; larger batches can add time before data is sent. |
| Server-side asynchronous inserts | Many independent producers cannot conveniently batch, but their compatible inserts can be combined by ClickHouse. | With wait_for_async_insert=1, acknowledgment follows flush and flush errors are returned. |
Flushes still consume server resources and create parts; buffering and flush timing affect visibility. |
Batch at the producer when practical
If your application can hold rows briefly and send fewer, larger inserts, start there. ClickHouse’s current concurrency resource guidance recommends 10,000–100,000 rows per synchronous insert as an ideal range and at least 1,000 rows as a minimum recommendation. Treat those as vendor guidance and a test range, not guarantees: appropriate batches depend on row size, schema, latency targets, and workload. ClickHouse’s bulk-insert guidance provides the current recommendation.
Use asynchronous inserts when client batching is impractical
Asynchronous inserts move the batching work to ClickHouse. Compatible incoming insert queries are accumulated in server-side buffers and flushed when an applicable trigger is reached. A flush does not necessarily mean one part: separate buffers or nodes, multiple partitions, and oversized data can still result in multiple parts. Async inserts reduce the pressure caused by many small writes; they do not eliminate part creation, merges, or the need to test the workload.
ClickHouse’s 26.3 LTS release announcement says asynchronous inserts are enabled by default beginning with version 26.3. The same announcement describes flush triggers based on timeout, accumulated size, or number of inserts. Behavior and defaults depend on the deployed version and configuration, so check your server rather than assuming the release default applies. Read the ClickHouse 26.3 LTS announcement.
Keep flush acknowledgment when reliability matters
For production ingestion where the application needs actionable error reporting, explicitly set wait_for_async_insert=1. The client is acknowledged after the buffer is flushed, and flush errors are returned rather than being separated from the original request. ClickHouse documents this as its default and recommended production mode in its current resource guidance, but version and configuration can change the actual default. Confirm the setting in ClickHouse’s bulk-insert documentation.
INSERT INTO events FORMAT JSONEachRow
{"event":"opened","user_id":42}
SETTINGS async_insert = 1, wait_for_async_insert = 1;The example makes both settings explicit, avoiding reliance on defaults. Adapt the table, format, and row to your schema and client. A client may instead set these settings through its connection or query options.
Why fire-and-forget is a different reliability choice
With wait_for_async_insert=0, acknowledgment can mean the data was accepted into memory buffering, not that the flush succeeded. ClickHouse warns that this mode can hide errors and risk buffered data loss. Use it only if the application deliberately accepts those reliability and visibility trade-offs; it is not a general-purpose speed switch.
Optimize format and compression after fixing batch shape
Once inserts are batched appropriately, test the encoding and compression used to send them. ClickHouse’s FastFormats article says it considered more than 70 input formats and found the Native format led in essentially all tested scenarios. The result is vendor benchmarking, not a promise for every client library, schema, or network. Compare candidate formats on throughput, CPU, memory, payload size, and implementation cost using your own traffic. See ClickHouse’s FastFormats benchmark.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →The same article identifies LZ4 as a strong compression choice and says ZSTD can matter when bandwidth is the constraint. ClickHouse reports that Netflix ingests about 5 PB of logs per day after adopting native-protocol encoding with LZ4; that is a vendor-reported case study, not an independently verified benchmark or a prediction of what another workload will achieve.
Benchmark the production mix, not just insert throughput
A faster insert benchmark can still be a poor production configuration if it increases query latency, memory use, or merge pressure. ClickHouse’s resource guidance recommends testing the production workload mix rather than sizing query classes in isolation. Compare options with representative data, concurrency, and query traffic.
- Measure insert throughput and acknowledgment latency, including the delay until rows are visible to queries.
- Track part counts and merge activity to see whether incoming data is creating sustainable part pressure.
- Test producer parallelism and batch size alongside async-insert settings and flush behavior.
- Compare input formats and compression while recording CPU, memory, and payload size.
- Include partitioning and query effects; a batch that spans partitions can still produce multiple parts.
- Use your actual schema, hardware, data distribution, and production traffic mix.
Asynchronous inserts gained adaptive timeout behavior in ClickHouse 24.2, and a consistent deduplication mechanism for asynchronous inserts with materialized views arrived in 26.1, according to the 26.3 release announcement. If either behavior matters to your deployment, verify the exact version and settings before relying on it.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
