Inserting ClickHouse data

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/starter_guides/inserting-data.md)

## Inserting into ClickHouse vs. OLTP databases[​](#inserting-into-clickhouse-vs-oltp-databases "Direct link to Inserting into ClickHouse vs. OLTP databases")

As an OLAP (Online Analytical Processing) database, ClickHouse is optimized for high performance and scalability, allowing potentially millions of rows to be inserted per second.
This is achieved through a combination of a highly parallelized architecture and efficient column-oriented compression, but with compromises on immediate consistency.
More specifically, ClickHouse is optimized for append-only operations and offers only eventual consistency guarantees.

In contrast, OLTP databases such as Postgres are specifically optimized for transactional inserts with full ACID compliance, ensuring strong consistency and reliability guarantees.
PostgreSQL uses MVCC (Multi-Version Concurrency Control) to handle concurrent transactions, which involves maintaining multiple versions of the data.
These transactions can potentially involve a small number of rows at a time, with considerable overhead incurred due to the reliability guarantees limiting insert performance.

To achieve high insert performance while maintaining strong consistency guarantees, you should adhere to the simple rules described below when inserting data into ClickHouse.
Following these rules will help to avoid issues users commonly encounter the first time they use ClickHouse, and try to replicate an insert strategy that works for OLTP databases.

## Best practices for Inserts[​](#best-practices-for-inserts "Direct link to Best practices for Inserts")

### Insert in large batch sizes[​](#insert-in-large-batch-sizes "Direct link to Insert in large batch sizes")

By default, each insert sent to ClickHouse causes ClickHouse to immediately create a part of storage containing the data from the insert together with other metadata that needs to be stored.
Therefore, sending a smaller amount of inserts that each contain more data, compared to sending a larger amount of inserts that each contain less data, will reduce the number of writes required.
Generally, we recommend inserting data in fairly large batches of at least 1,000 rows at a time, and ideally between 10,000 to 100,000 rows.
(Further details [here](https://clickhouse.com/blog/asynchronous-data-inserts-in-clickhouse#data-needs-to-be-batched-for-optimal-performance)).

If large batches aren't possible, use asynchronous inserts described below.

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

By default, inserts into ClickHouse are synchronous and idempotent (i.e. performing the same insert operation multiple times has the same effect as performing it once).
For tables of the MergeTree engine family, ClickHouse will, by default, automatically [deduplicate inserts](https://clickhouse.com/blog/common-getting-started-issues-with-clickhouse#5-deduplication-at-insert-time).

This means inserts remain resilient in the following cases:

- 1. If the node receiving the data has issues, the insert query will time out (or give a more specific error) and not get an acknowledgment.
- 2. If the data got written by the node but the acknowledgement can't be returned to the sender of the query because of network interruptions, the sender will either get a time-out or a network error.

From the client's perspective, (i) and (ii) can be hard to distinguish. However, in both cases, the unacknowledged insert can just be immediately retried.
As long as the retried insert query contains the same data in the same order, ClickHouse will automatically ignore the retried insert if the (unacknowledged) original insert succeeded.

### Insert to a MergeTree table or a distributed table[​](#insert-to-a-mergetree-table-or-a-distributed-table "Direct link to Insert to a MergeTree table or a distributed table")

We recommend inserting directly into a MergeTree (or Replicated table), balancing the requests across a set of nodes if the data is sharded, and setting `internal_replication=true`.
This will leave ClickHouse to replicate the data to any available replica shards and ensure the data is eventually consistent.

If this client side load balancing is inconvenient then you can insert via a [distributed table](/docs/engines/table-engines/special/distributed) which will then distribute writes across the nodes. Again, it is advised to set `internal_replication=true`.
It should be noted however that this approach is a little less performant as writes have to be made locally on the node with the distributed table and then sent to the shards.

### Use asynchronous inserts for small batches[​](#use-asynchronous-inserts-for-small-batches "Direct link to Use asynchronous inserts for small batches")

There are scenarios where client-side batching isn't feasible e.g. an observability use case with 100s or 1000s of single-purpose agents sending logs, metrics, traces, etc.
In this scenario real-time transport of that data is key to detect issues and anomalies as quickly as possible.
Furthermore, there is a risk of event spikes in the observed systems, which could potentially cause large memory spikes and related issues when trying to buffer observability data client-side.
If large batches can't be inserted, you can delegate batching to ClickHouse using [asynchronous inserts](/docs/best-practices/selecting-an-insert-strategy#asynchronous-inserts).

With asynchronous inserts, data is inserted into a buffer first and then written to the database storage later in 3 steps, as illustrated by the diagram below:

With asynchronous inserts enabled, ClickHouse:

(1) receives an insert query asynchronously.
(2) writes the query's data into an in-memory buffer first.
(3) sorts and writes the data as a part to the database storage, only when the next buffer flush takes place.

Before the buffer gets flushed, the data of other asynchronous insert queries from the same or other clients can be collected in the buffer.
The part created from the buffer flush will potentially contain the data from several asynchronous insert queries.
Generally, these mechanics shift the batching of data from the client side to the server side (ClickHouse instance).

Note

Note that the data isn't searchable by queries before being flushed to the database storage and that the buffer flush is configurable.

Full details on configuring asynchronous inserts can be found [here](/docs/optimize/asynchronous-inserts#enabling-asynchronous-inserts), with a deep dive [here](https://clickhouse.com/blog/asynchronous-data-inserts-in-clickhouse).

### Use official ClickHouse clients[​](#use-official-clickhouse-clients "Direct link to Use official ClickHouse clients")

ClickHouse has clients in the most popular programming languages.
These are optimized to ensure that inserts are performed correctly and natively support asynchronous inserts either directly as in e.g. the [Go client](/docs/integrations/language-clients/go/clickhouse-api#async-insert), or indirectly when enabled in the query, user or connection level settings.

See [Clients and Drivers](/docs/interfaces/cli) for a full list of available ClickHouse clients and drivers.

### Prefer the native format[​](#prefer-the-native-format "Direct link to Prefer the native format")

ClickHouse supports many [input formats](/docs/interfaces/formats) at insert (and query) time.
This is a significant difference with OLTP databases and makes loading data from external sources much easier - especially when coupled with [table functions](/docs/sql-reference/table-functions) and the ability to load data from files on disk.
These formats are ideal for ad hoc data loading and data engineering tasks.