v.21.11New Features
New Asynchronous INSERT Mode for Bulk Data Insertion in ClickHouse
New asynchronous INSERT mode allows to accumulate inserted data and store it in a single batch in background. On client it can be enabled by settingasync_insertforINSERTqueries with data inlined in query or in separate buffer (e.g. forINSERTqueries via HTTP protocol). Ifwait_for_async_insertis true (by default) the client will wait until data will be flushed to table. On server-side it controlled by the settingsasync_insert_threads,async_insert_max_data_sizeandasync_insert_busy_timeout_ms. Implements #18282. #27537 (Anton Popov). #20557 (Ivan). Notes on performance: with asynchronous inserts you can do up to around 10 000 individual INSERT queries per second, so it is still recommended to insert in batches if you want to achieve performance up to millions inserted rows per second.
Why it matters
The feature addresses the problem of efficiently handling a high volume of individualINSERT queries by accumulating data and flushing it in batches asynchronously. This reduces overhead on the server and client, enabling up to around 10,000 individual INSERT queries per second and improving overall insert performance, especially for use cases with many small inserts.How to use it
On the client side, enable asynchronous inserts by setting theasync_insert option for INSERT queries with data either inlined in the query or provided in a separate buffer (e.g., for HTTP protocol inserts). Control whether the client waits for the asynchronous insert to complete with the wait_for_async_insert setting (default is true). On the server side, manage asynchronous insert behavior and performance by configuring the settings async_insert_threads, async_insert_max_data_size, and async_insert_busy_timeout_ms.