Using Async Inserts for Peak Data Loading Rates in ClickHouse®

The Challenge: Too Many Small Inserts

Picture this scenario: You’re running a monitoring platform that collects metrics from thousands of IoT devices. Each device sends a small JSON payload every few seconds—temperature readings, battery levels, network latency. You’ve chosen ClickHouse for its incredible analytical capabilities, but now you’re facing a problem that keeps many engineers up at night: how to efficiently handle thousands of tiny inserts per second.

If you’ve worked with ClickHouse before, you know the golden rule: batch your inserts. ClickHouse performs best when you insert data in larger chunks—ideally millions of rows at once. But what happens when your architecture doesn’t allow for client-side batching? What if you have legacy systems, third-party integrations, or simply too many data sources to coordinate?

This is where async inserts come to the rescue.

Understanding Async Inserts: Server-Side Batching Magic

Async inserts are ClickHouse’s answer to the small insert problem. Instead of immediately writing each tiny insert to disk (which would create performance nightmares from merges), ClickHouse collects these inserts in memory and intelligently batches them together before writing.

When you enable async inserts with `SET async_insert = 1`, your inserts go into a buffer. ClickHouse then decides when to flush this buffer based on three conditions:

1. Size threshold: The buffer reaches a certain size (default: 1MB)
2. Time threshold: A timer expires (default: 1 second)  
3. Query count: Too many queries are waiting (default: 100)

Whichever condition is met first triggers the flush, ensuring your data doesn’t sit in memory indefinitely while still achieving efficient batching.

When Async Inserts Shine (And When They Don’t)

Perfect Use Cases

Async inserts are your best friend when:

You have hundreds of independent data sources: Imagine a fleet management system where each vehicle sends GPS coordinates every few seconds. Coordinating client-side batching across thousands of vehicles would be a nightmare. With async inserts, each vehicle can send its data independently, and ClickHouse handles the batching.

You’re dealing with legacy systems: Perhaps you’re migrating from a traditional database where applications expect to INSERT row by row. Rewriting all your applications for batching might not be feasible. Async inserts let you keep your existing code while getting better ClickHouse performance.

Real-time data with relaxed latency requirements: If your dashboard updates every minute, waiting a second for data to be flushed is perfectly acceptable. The slight delay is a small price for the massive performance improvement.

When to Think Twice

However, async inserts aren’t always the answer:

You need immediate consistency: If your application needs to read data immediately after inserting it, async inserts add complexity. You’d need to use `wait_for_async_insert = 1` (which we strongly recommend anyway), but this adds latency to your insert operations.

You’re already batching effectively: If you have a proper data pipeline with Kafka, or you’re using tools like Vector or Fluentbit that batch naturally, async inserts add unnecessary complexity. Stick with your client-side batching.

You’re using INSERT SELECT: Async inserts don’t work with `INSERT .. SELECT` statements. If your workflow relies heavily on transforming data during insertion, you’ll need alternative approaches.

Configuration: Getting It Right the First Time

Let’s walk through setting up async inserts properly. The configuration might seem simple, but the details matter enormously.

By the way, the information in this guide comes from the Altinity Knowledgebase for ClickHouse where you can go to get the latest information on this topic or to contribute your own learnings—it’s open source!

-- Enable async inserts for your session
SET async_insert = 1;

-- CRITICAL: Always wait for confirmation
SET wait_for_async_insert = 1;

-- Configure buffer behavior
SET async_insert_max_data_size = 5000000;    -- 5MB buffer

SET async_insert_busy_timeout_ms = 2000;      -- 2 second timeout

SET async_insert_max_query_number = 200;      -- Up to 200 queries

The most critical setting here is `wait_for_async_insert = 1`. Without it, you’re in “fire-and-forget” mode, which sounds fast but is actually dangerous. Your application won’t know if inserts fail due to disk issues, table locks, or other problems. You might lose data silently, and debugging becomes a nightmare. Always wait for confirmation—the small latency cost is worth the reliability.

A Real-World Example: Building a Metrics Collection System

Let’s implement a practical example. Suppose you’re building a system to collect application metrics from multiple services:

-- Create a table for metrics
CREATE TABLE metrics (
    timestamp DateTime,
    service String,
    metric String,
    value Float64,
    tags Map(String, String)
) ENGINE = MergeTree()
ORDER BY (service, metric, timestamp);

-- Configure optimal async insert settings for this use case
SET async_insert = 1;
SET wait_for_async_insert = 1;
SET async_insert_max_data_size = 10000000;     -- 10MB buffers for metrics
SET async_insert_busy_timeout_ms = 5000;       -- 5 second timeout

Now, each service can send metrics independently:

-- Service A sends CPU metrics
INSERT INTO metrics FORMAT JSONEachRow
{"timestamp": "2024-01-15 10:00:00", "service": "api-gateway", "metric": "cpu_usage", "value": 45.2, "tags": {"host": "server1", "dc": "us-east"}}

-- Service B sends memory metrics (can execute simultaneously)
INSERT INTO metrics FORMAT JSONEachRow
{"timestamp": "2024-01-15 10:00:01", "service": "auth-service", "metric": "memory_usage", "value": 2048.5, "tags": {"host": "server2", "dc": "us-west"}}

Behind the scenes, ClickHouse collects these inserts and writes them together, dramatically reducing the overhead compared to individual inserts.

The Adaptive Timeout Revolution (ClickHouse 24.3+)

Starting with ClickHouse 24.3, there’s a game-changing feature: adaptive timeouts. Instead of using fixed timeouts, ClickHouse now automatically adjusts flush timing based on server load. When your server is under pressure, it flushes more frequently to prevent memory buildup. When things are calm, it waits longer to create larger, more efficient batches.

This feature is enabled by default with `async_insert_use_adaptive_busy_timeout = 1`. While powerful, it can override your manual settings, so you might want to disable it if you’ve carefully tuned your timeouts:

-- Disable adaptive timeout for predictable behavior
SET async_insert_use_adaptive_busy_timeout = 0;

Monitoring: Keeping an Eye on Your Async Inserts

You can’t optimize what you can’t measure. ClickHouse provides comprehensive metrics for async inserts:

-- Check current async insert queue status
SELECT 
    metric,
    value,
    description
FROM system.metrics
WHERE metric LIKE '%AsyncInsert%'
ORDER BY metric;

-- Review async insert history
SELECT 
    query_id,
    query,
    rows,
    bytes,
    exception,
    flush_time_microseconds
FROM system.asynchronous_insert_log
WHERE event_date = today()
ORDER BY event_time DESC
LIMIT 10;

-- Monitor for failures
SELECT 
    count() as failed_inserts,
    exception
FROM system.asynchronous_insert_log
WHERE event_date = today()
    AND exception != ''
GROUP BY exception
ORDER BY failed_inserts DESC;

Pay special attention to `AsynchronousInsertQueueBytes` and `PendingAsyncInsert` metrics. If these keep growing, your flushes might not be keeping up with incoming data, and you need to adjust your thresholds.

Common Pitfalls and How to Avoid Them

Through years of helping teams implement async inserts, we’ve seen some patterns emerge:

The Deduplication Trap: By default, async inserts disable deduplication. If your application might send duplicate data and you rely on ClickHouse’s deduplication, you need to explicitly enable it:

SET async_insert_deduplicate = 1;

But be careful—this doesn’t work well with materialized views that have their own deduplication enabled.

The Silent Data Loss: Never, ever use `wait_for_async_insert = 0` in production unless you fully understand the risks and have explicit monitoring for data loss. We’ve seen teams lose millions of records because they optimized for speed without understanding this setting.

The Version Mismatch: If you’re running ClickHouse versions before 23.8, be extra careful. Many critical bugfixes for async inserts landed in 23.8 and later. If you’re on an older version and experiencing issues, upgrading might solve your problems.

Making the Decision: A Simple Framework

Here’s a simple decision framework for whether to use async inserts:

1. Do you have many small inserts from independent sources? → Consider async inserts
2. Can you batch on the client side reasonably? → Skip async inserts, batch yourself
3. Do you need immediate read-after-write consistency? → Be cautious with async inserts
4. Are you on ClickHouse 23.8 or later? → Green light for async inserts
5. Can you accept 1-5 second delays in data availability? → Async inserts fit well

The Bottom Line

Async inserts are a powerful tool in your ClickHouse toolkit, but they’re not a silver bullet. They excel at solving the specific problem of many small, independent inserts when client-side batching isn’t practical. When configured correctly—especially with `wait_for_async_insert = 1`—they provide a robust solution that balances performance with reliability.

Remember: the best insert strategy is still client-side batching when possible. But when life gives you thousands of tiny inserts, async inserts help you make efficient batches. Use them wisely, monitor them closely, and your ClickHouse cluster will thank you with smooth, predictable performance even under challenging insert patterns.

Whether you’re building a new system or optimizing an existing one, understanding when and how to use async inserts can be the difference between a ClickHouse deployment that struggles and one that sings. The key is knowing when this tool fits your use case and configuring it properly for your specific needs.