Asynchronous inserts (async_insert)

In practice, if deduplication is turned on and the same insert is retried — due to, for instance, a timeout or network drop — ClickHouse can safely ignore the duplicate. This helps maintain idempotency and avoids double-writing data.

### Enabling asynchronous inserts[​](#enabling-asynchronous-inserts "Direct link to Enabling asynchronous inserts")

Asynchronous inserts can be enabled for a particular user, or for a specific query:

- Enabling asynchronous inserts at the user level. This example uses the user `default`, if you create a different user then substitute that username:

  ```
  ALTER USER default SETTINGS async_insert = 1
  ```
- You can specify the asynchronous insert settings by using the SETTINGS clause of insert queries:

  ```
  INSERT INTO YourTable SETTINGS async_insert=1, wait_for_async_insert=1 VALUES (...)
  ```
- You can also specify asynchronous insert settings as connection parameters when using a ClickHouse programming language client.

  As an example, this is how you can do that within a JDBC connection string when you use the ClickHouse Java JDBC driver for connecting to ClickHouse Cloud:

  ```
  "jdbc:ch://HOST.clickhouse.cloud:8443/?user=default&password=PASSWORD&ssl=true&custom_http_params=async_insert=1,wait_for_async_insert=1"
  ```

Note

Asynchronous inserts don't apply to `INSERT INTO ... SELECT` queries. When the insert contains a `SELECT` clause, the query is always executed synchronously regardless of the `async_insert` setting.

### Flushing buffers on shutdown[​](#flushing-buffers-on-shutdown "Direct link to Flushing buffers on shutdown")

To flush all pending async insert buffers — for example, during a graceful shutdown or before maintenance — run:

```
SYSTEM FLUSH ASYNC INSERT QUEUE
```

This ensures any buffered data is written to storage before the server stops.

### Comparison with buffer tables[​](#comparison-with-buffer-tables "Direct link to Comparison with buffer tables")

Asynchronous inserts are the modern replacement for [Buffer tables](/docs/engines/table-engines/special/buffer). Key differences:

- **No DDL changes required.** Async inserts are transparent — you enable a setting, not create additional tables.
- **Per-shape buffering.** Async inserts maintain separate buffers per unique query shape and settings combination, enabling granular flush policies. Buffer tables use a single buffer per target table.
- **Durability.** In default mode (`wait_for_async_insert=1`), data is confirmed on disk before the client receives acknowledgment. Buffer tables behave like fire-and-forget — buffered data is lost on crash.
- **Cluster behavior.** In clusters, async insert buffers are maintained per node. Buffer tables require explicit creation on each node.

- [Choosing a return mode](#choosing-a-return-mode)- [Adaptive async inserts](#adaptive-async-inserts)- [Error handling](#error-handling)- [Deduplication and reliability](#deduplication-and-reliability)- [Enabling asynchronous inserts](#enabling-asynchronous-inserts)- [Flushing buffers on shutdown](#flushing-buffers-on-shutdown)- [Comparison with buffer tables](#comparison-with-buffer-tables)

Was this page helpful?