Selecting an insert strategy

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/best-practices/selecting_an_insert_strategy.md)

Efficient data ingestion forms the basis of high-performance ClickHouse deployments. Selecting the right insert strategy can dramatically impact throughput, cost, and reliability. This section outlines best practices, tradeoffs, and configuration options to help you make the right decision for your workload.

Note

The following assumes you're pushing data to ClickHouse via a client. If you're pulling data into ClickHouse e.g. using built in table functions such as [s3](/docs/sql-reference/table-functions/s3) and [gcs](/docs/sql-reference/table-functions/gcs), we recommend our guide ["Optimizing for S3 Insert and Read Performance"](/docs/integrations/s3/performance).

## Synchronous inserts by default[​](#synchronous-inserts-by-default "Direct link to Synchronous inserts by default")

By default, inserts into ClickHouse are synchronous. Each insert query immediately creates a storage part on disk, including metadata and indexes.

Use synchronous inserts if you can batch the data client side

If not, see [Asynchronous inserts](#asynchronous-inserts) below.

We briefly review ClickHouse's MergeTree insert mechanics below:

#### Client-side steps[​](#client-side-steps "Direct link to Client-side steps")

For optimal performance, data must be ① [batched](https://clickhouse.com/blog/asynchronous-data-inserts-in-clickhouse#data-needs-to-be-batched-for-optimal-performance), making batch size the **first decision**.

ClickHouse stores inserted data on disk, [ordered](/docs/guides/best-practices/sparse-primary-indexes#data-is-stored-on-disk-ordered-by-primary-key-columns) by the table's primary key columns. The **second decision** is whether to ② pre-sort the data before transmission to the server. If a batch arrives pre-sorted by primary key columns, ClickHouse can [skip](https://github.com/ClickHouse/ClickHouse/blob/94ce8e95404e991521a5608cd9d636ff7269743d/src/Storages/MergeTree/MergeTreeDataWriter.cpp#L595) the ⑩ sorting step, speeding up ingestion.

If the data to be ingested has no predefined format, the **key decision** is choosing a format. ClickHouse supports inserting data in [over 70 formats](/docs/interfaces/formats). However, when using the ClickHouse command-line client or programming language clients, this choice is often handled automatically. If needed, this automatic selection can also be overridden explicitly.

The next **major decision** is ④ whether to compress data before transmission to the ClickHouse server. Compression reduces transfer size and improves network efficiency, leading to faster data transfers and lower bandwidth usage, especially for large datasets.

The data is ⑤ transmitted to a ClickHouse network interface—either the [native](/docs/interfaces/tcp) or [HTTP](/docs/interfaces/http) interface (which we [compare](https://clickhouse.com/blog/clickhouse-input-format-matchup-which-is-fastest-most-efficient#clickhouse-client-defaults) later in this post).

#### Server-side steps[​](#server-side-steps "Direct link to Server-side steps")

After ⑥ receiving the data, ClickHouse ⑦ decompresses it if compression was used, then ⑧ parses it from the originally sent format.

Using the values from that formatted data and the target table's [DDL](/docs/sql-reference/statements/create/table) statement, ClickHouse ⑨ builds an in-memory [block](/docs/development/architecture#block) in the MergeTree format, ⑩ [sorts](/docs/parts#what-are-table-parts-in-clickhouse) rows by the primary key columns if they're not already pre-sorted, ⑪ creates a [sparse primary index](/docs/guides/best-practices/sparse-primary-indexes), ⑫ applies [per-column compression](/docs/parts#what-are-table-parts-in-clickhouse), and ⑬ writes the data as a new ⑭ [data part](/docs/parts) to disk.

### Batch inserts if synchronous[​](#batch-inserts-if-synchronous "Direct link to Batch inserts if synchronous")

The above mechanics illustrate a constant overhead regardless of the insert size, making batch size the single most important optimization for ingest throughput. Batching inserts reduce the overhead as a proportion of total insert time and improves processing efficiency.

We recommend inserting data in batches of at least 1,000 rows, and ideally between 10,000–100,000 rows. Fewer, larger inserts reduce the number of parts written, minimize merge load, and lower overall system resource usage.

**For a synchronous insert strategy to be effective this client-side batching is required.**

If you're unable to batch data client-side, ClickHouse supports asynchronous inserts that shift batching to the server ([see Asynchronous inserts](/docs/best-practices/selecting-an-insert-strategy#asynchronous-inserts)).

Tip

Regardless of the size of your inserts, we recommend keeping the number of insert queries around one insert query per second. The reason for this recommendation is that the created parts are merged to larger parts in the background (in order to optimize your data for read queries), and sending too many insert queries per second can lead to situations where the background merging can't keep up with the number of new parts. However, you can use a higher rate of insert queries per second when you use asynchronous inserts (see [Asynchronous inserts](/docs/best-practices/selecting-an-insert-strategy#asynchronous-inserts)).

### Ensure idempotent retries[​](#ensure-idempotent-retries "Direct link to Ensure idempotent retries")

Synchronous inserts are also **idempotent**. When using MergeTree engines, ClickHouse will deduplicate inserts by default. This protects against ambiguous failure cases, such as:

- The insert succeeded but the client never received an acknowledgment due to a network interruption.
- The insert failed server-side and timed out.

In both cases, it's safe to **retry the insert** — as long as the batch contents and order remain identical. For this reason, it's critical that clients retry consistently, without modifying or reordering data.

### Choose the right insert target[​](#choose-the-right-insert-target "Direct link to Choose the right insert target")

For sharded clusters, you have two options:

- Insert directly into a **MergeTree** or **ReplicatedMergeTree** table. This is the most efficient option when the client can perform load balancing across shards. With `internal_replication = true`, ClickHouse handles replication transparently.
- Insert into a [Distributed table](/docs/engines/table-engines/special/distributed). This allows clients to send data to any node and let ClickHouse forward it to the correct shard. This is simpler but slightly less performant due to the extra forwarding step. `internal_replication = true` is still recommended.

**In ClickHouse Cloud all nodes read and write to the same single shard. Inserts are automatically balanced across nodes. You can simply send inserts to the exposed endpoint.**

### Choose the right format[​](#choose-the-right-format "Direct link to Choose the right format")

Choosing the right input format is crucial for efficient data ingestion in ClickHouse. With over 70 supported formats, selecting the most performant option can significantly impact insert speed, CPU and memory usage, and overall system efficiency.

While flexibility is useful for data engineering and file-based imports, **applications should prioritize performance-oriented formats**:

- **Native format** (recommended): Most efficient. Column-oriented, minimal parsing required server-side. Used by default in Go and Python clients.
- **RowBinary**: Efficient row-based format, ideal if columnar transformation is hard client-side. Used by the Java client.
- **JSONEachRow**: Easy to use but expensive to parse. Suitable for low-volume use cases or quick integrations.

### Use compression[​](#use-compression "Direct link to Use compression")

Compression plays a critical role in reducing network overhead, speeding up inserts, and lowering storage costs in ClickHouse. Used effectively, it enhances ingestion performance without requiring changes to data format or schema.