Introduction
Modern data platforms are expected to ingest massive volumes of data in real time. Whether it's application logs, IoT sensor readings, monitoring metrics, clickstream events, or messages from streaming platforms like Kafka, many workloads generate thousands—or even millions—of small INSERT operations every second.
ClickHouse® is designed for high-performance analytical workloads and can ingest data at remarkable speeds. However, one common performance bottleneck remains: a large number of very small inserts.
Each individual INSERT requires ClickHouse to parse the query, validate the data, compress blocks, update metadata, and create new data parts. While these operations are efficient individually, repeating them thousands of times per second creates unnecessary overhead and increases the work performed by background merge processes.
ClickHouse® 26.3 improves this scenario by enhancing the batching behavior of asynchronous inserts. Instead of writing every small insert immediately, ClickHouse temporarily buffers incoming asynchronous insert requests and combines multiple small inserts into larger writes before flushing them to storage.
The result is:
- Higher ingestion throughput
- Fewer data parts
- Reduced merge overhead
- Better compression efficiency
- Lower CPU and disk utilization
In this article, we'll explore how automated insert batching works, why it improves performance, and when you should consider enabling asynchronous inserts in your ClickHouse deployments.
Note: "Automated Insert Batching" is not an official ClickHouse feature name. Throughout this article, the term refers to the enhanced batching behavior of asynchronous inserts introduced in ClickHouse® 26.3, where multiple small insert requests are automatically grouped into larger writes before being persisted.
Why Small INSERT Statements Hurt Performance
Imagine an application receiving telemetry events every second.
Instead of accumulating events into larger batches, it continuously executes tiny insert operations.
INSERT (10 rows)
INSERT (15 rows)
INSERT (8 rows)
INSERT (12 rows)
INSERT (20 rows)
...
Although each insert contains only a handful of rows, ClickHouse still performs the complete insert workflow for every request.
Each insert requires:
- SQL parsing
- Query validation
- Block creation
- Data compression
- Metadata updates
- New data part creation
- Scheduling future merge operations
When applications generate thousands of tiny inserts, these fixed costs are repeated continuously, reducing overall ingestion efficiency.
Traditional INSERT Workflow
Without batching, every insert is processed independently.
Application
│
├── INSERT
├── INSERT
├── INSERT
├── INSERT
│
▼
ClickHouse
│
├── Part 1
├── Part 2
├── Part 3
├── Part 4
│
▼
MergeTree Storage
Every insert creates its own data part.
Although MergeTree is optimized for immutable data parts, creating thousands of tiny parts introduces unnecessary overhead throughout the storage engine.
Why Too Many Small Parts Are a Problem
A large number of tiny data parts negatively impacts multiple areas of ClickHouse performance.
Some common consequences include:
- More background merge operations
- Increased CPU utilization
- Higher disk I/O
- Larger metadata structures
- Slower query planning
- Increased memory consumption
- Additional storage fragmentation
Background merges become especially busy trying to combine many small parts into larger ones.
This is why ClickHouse documentation consistently recommends:
Avoid sending many tiny INSERT statements whenever possible.
Automated Insert Batching
ClickHouse® 26.3 improves this workflow by batching asynchronous inserts automatically.
Instead of writing every request immediately, ClickHouse buffers incoming asynchronous insert requests for a short period.
Multiple insert requests are then combined into one larger write.
Application
INSERT
INSERT
INSERT
INSERT
INSERT
│
▼
Asynchronous Buffer
│
Combine Requests
▼
Large INSERT
▼
ClickHouse Storage
From the application's perspective, nothing changes.
The application continues sending small inserts.
Internally, however, ClickHouse performs fewer, larger writes.
Why This Improves Throughput
Every insert has a fixed processing cost regardless of whether it contains:
- 5 rows
- 20 rows
- 100 rows
When ClickHouse combines many small inserts into a single larger write, those fixed costs occur only once.
Instead of repeating expensive operations thousands of times, ClickHouse performs them for the combined batch.
Benefits include:
- Fewer metadata updates
- Fewer compression operations
- Fewer data parts
- Reduced write amplification
- Lower CPU overhead
- Less merge activity
The overall result is significantly higher ingestion throughput.
Example
Suppose a monitoring platform generates:
- 2,000 INSERT statements
- 20 rows per INSERT
Without batching:
2,000 INSERTS
↓
2,000 Data Parts
With automated batching:
2,000 INSERTS
↓
40 Large Batches
↓
40 Data Parts
The total number of inserted rows remains exactly the same.
However, ClickHouse creates only a small fraction of the data parts.
This dramatically reduces merge operations and improves overall system efficiency.
How Asynchronous Inserts Work
Automatic batching is powered by asynchronous inserts.
Instead of immediately writing each insert to disk, ClickHouse temporarily stores incoming insert requests in memory.
The buffered data is flushed when configurable thresholds are reached.
Typical controls include:
- Maximum buffer size
- Flush timeout
- Queue size limits
These settings allow administrators to balance:
- Insert latency
- Memory usage
- Throughput
Depending on the workload, ClickHouse can automatically optimize write efficiency without requiring application changes.
Enabling Asynchronous Inserts
Asynchronous inserts can be enabled at the session level.
SET async_insert = 1;
SET wait_for_async_insert = 1;
Or configured for an individual insert statement.
INSERT INTO events
SETTINGS
async_insert = 1,
wait_for_async_insert = 1
VALUES (...);
What do these settings mean?
async_insert = 1
Enables asynchronous inserts, allowing ClickHouse to buffer incoming requests before writing them to storage.
wait_for_async_insert = 1
Waits until the buffered data has been successfully written before acknowledging the insert to the client.
These settings can also be configured globally using server configuration files, making them suitable for production deployments with consistent ingestion patterns.
When Does Automatic Batching Help?
Automatic batching is particularly valuable for workloads that continuously generate many small inserts.
Common examples include:
IoT Platforms
Thousands of sensors continuously publish measurements.
Instead of immediately writing every reading, ClickHouse batches them into larger writes.
Application Logging
Modern applications generate logs every few milliseconds.
Batching dramatically reduces write overhead.
Monitoring Systems
Monitoring agents continuously send metrics.
Automatic batching helps reduce part creation while maintaining near real-time visibility.
Event Streaming
Applications consuming events from Kafka, RabbitMQ, or Pulsar often insert relatively small batches.
ClickHouse combines them automatically for higher throughput.
Clickstream Analytics
User interactions arrive continuously throughout the day.
Batching improves scalability without requiring changes to event producers.
Benefits
| Feature | Benefit |
|---|---|
| Larger insert batches | Higher throughput |
| Fewer data parts | Lower merge overhead |
| Better compression | Reduced storage usage |
| Lower CPU utilization | More efficient ingestion |
| Lower disk I/O | Faster writes |
| Better scalability | Handles higher event rates |
Before vs After
| Without Automatic Batching | With Automatic Batching |
|---|---|
| Many tiny writes | Writes combined automatically |
| Large number of data parts | Significantly fewer parts |
| Frequent merges | Reduced merge activity |
| Higher CPU usage | Lower CPU usage |
| Lower throughput | Higher throughput |
Monitoring Insert Performance
Several ClickHouse system tables help monitor insert behavior.
| System Table | Purpose |
|---|---|
system.parts |
Active data parts |
system.part_log |
Part creation history |
system.merges |
Background merge activity |
system.metrics |
Insert-related metrics |
Example:
SELECT
table,
count() AS active_parts
FROM system.parts
WHERE active = 1
GROUP BY table;
If a table contains an unusually large number of active parts, it may indicate that inserts are arriving in very small batches and causing excessive merge activity.
Monitoring these tables regularly can help identify ingestion bottlenecks before they affect query performance.
Best Practices
To maximize ingestion throughput:
- Enable asynchronous inserts for high-frequency workloads.
- Prefer larger batches whenever your application allows.
- Avoid single-row inserts whenever possible.
- Monitor active data parts using
system.parts. - Watch background merges using
system.merges. - Tune asynchronous insert thresholds based on workload characteristics.
- Measure throughput before and after enabling batching to quantify improvements.
When Automatic Batching Provides Limited Benefit
Although batching improves many workloads, it isn't beneficial in every situation.
Performance gains may be limited when:
- Your application already sends large batch inserts.
- Insert operations occur infrequently.
- Immediate data visibility is more important than throughput.
- Your workload already produces relatively few data parts.
In these cases, batching provides little additional optimization because the application has already minimized insert overhead.
Things to Consider
Automatic batching introduces a small trade-off.
Because ClickHouse briefly buffers insert requests before writing them to disk, data may become visible slightly later than with synchronous inserts.
For most analytical workloads, this delay is negligible.
The improvements in throughput, storage efficiency, CPU utilization, and merge performance typically outweigh the small increase in insert latency.
The optimal configuration depends on your workload's balance between:
- Freshness requirements
- Write throughput
- Resource utilization
Final Thoughts
ClickHouse® 26.3 continues to improve one of its greatest strengths—high-speed data ingestion.
By enhancing the batching behavior of asynchronous inserts, ClickHouse automatically combines multiple small insert requests into larger writes, reducing the overhead associated with part creation, compression, metadata updates, and background merges.
For workloads involving logs, metrics, IoT telemetry, event streaming, clickstream analytics, or real-time monitoring, enabling asynchronous inserts can significantly improve scalability while reducing CPU usage, disk I/O, and storage fragmentation.
If your applications generate thousands of small inserts every second, automated insert batching is a simple optimization that can deliver substantial performance improvements with minimal changes to your existing ingestion pipeline.
As ClickHouse continues to evolve, features like this demonstrate how thoughtful engineering can improve both performance and operational efficiency, making it even better suited for modern, data-intensive applications.
References
- ClickHouse® 26.3 Release Notes
- ClickHouse® Documentation – Asynchronous Inserts
- ClickHouse® Documentation – Bulk Inserts Best Practices
Top comments (0)