Insert data efficiently

Batch your inserts#

Insert data in batches of at least 1,000 rows, and ideally between 10,000 and 100,000 rows. Fewer, larger inserts write fewer parts, reduce merge load, and lower overall resource usage.

Just as important, keep the number of insert queries to around one per second. New parts are merged into larger ones in the background, and too many insert queries per second means background merging cannot keep up with the parts being created.

INSERT INTO events (timestamp, user_id, event) VALUES
    ('2024-01-01 10:00:00', 101, 'view'),
    ('2024-01-01 10:00:01', 102, 'click'),
    -- ... thousands more rows in the same statement

One insert of 20,000 rows creates one part. Twenty thousand single-row inserts create 20,000 parts, each needing sorting, compression, a directory, and eventual merging.

If you cannot batch client-side, use async inserts and let the server batch for you.

The Too many parts error#

When parts accumulate faster than merges consume them, ClickHouse throws Too many parts, often with the message Merges are processing significantly slower than inserts. Two settings trigger it:

Common causes:

• Frequent, small synchronous inserts.
• A high-cardinality partitioning key.
• Inserts containing rows for many partition values at once.
• Merges that cannot keep up due to limited storage throughput or insufficient free disk.

Find which partitions are accumulating parts:

SELECT
    database,
    table,
    partition_id,
    count() AS active_parts,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active
  AND database = 'your_database'
  AND table = 'your_table'
GROUP BY database, table, partition_id
ORDER BY active_parts DESC;

Raising the part limits does not address the cause. Higher limits delay the error while increasing filesystem and metadata overhead and reducing query performance. Change them only after identifying the cause and confirming the system has capacity.

Async inserts when you cannot batch#

When hundreds of agents each send small payloads continuously, client-side batching would require a centralized queue. Async inserts move that responsibility to the server: incoming data goes to an in-memory buffer, which is flushed to storage on a threshold.

INSERT INTO events SETTINGS async_insert = 1, wait_for_async_insert = 1 VALUES (...);

A flush happens when whichever condition trips first:

Since version 24.2, the flush timeout adapts to the incoming data rate between async_insert_busy_timeout_min_ms (50 ms) and async_insert_busy_timeout_max_ms, flushing sooner under heavy traffic and batching longer when data is sparse.

Do not disable wait_for_async_insert#

wait_for_async_insert = 1, the default, acknowledges the insert only after data is flushed to disk. Errors come back to the client, and durability is guaranteed.

wait_for_async_insert = 0 acknowledges as soon as data is buffered. It is faster, but there is no guarantee the data is persisted, errors only surface during flush, and there is no dead-letter queue — tracing a failure means reading server logs afterwards.

Use async_insert = 1, wait_for_async_insert = 1. Setting it to 0 is risky: your client may never learn about errors, and it can overload a server that is trying to apply backpressure.

What async inserts do not change#

• Parsing errors reject the whole query. If any row fails to parse, none of that insert's data is flushed.
• A flush still creates at least one part per partition value in the buffer, so a high-cardinality partition key can still produce Too many parts.
• INSERT INTO ... SELECT is always synchronous, regardless of the setting.
• Deduplication is off by default for async inserts, unlike synchronous ones. Do not enable it if you have dependent materialized views.

Enable it per user rather than per query when it applies to a whole workload:

ALTER USER default SETTINGS async_insert = 1

Choose an efficient format#

For applications, format choice measurably affects ingestion cost:

Compress the payload with LZ4 unless bandwidth or egress cost is the constraint — then consider ZSTD, which compresses harder at more CPU cost. The native interface uses LZ4 by default; over HTTP, set Content-Encoding.

Load a file with the client#

clickhouse-client \
  --host HOSTNAME.clickhouse.cloud \
  --port 9440 \
  --user default \
  --password YOUR_PASSWORD \
  --secure \
  --query "INSERT INTO cell_towers FORMAT CSVWithNames" \
  < cell_towers.csv

The same command loads Parquet, TSV, JSON, Avro, and ORC — change the FORMAT clause and pipe the matching file. ClickHouse supports over 70 input and output formats.

The server can also read the file itself, with glob support:

INSERT INTO table_from_file FROM INFILE 'input_*.csv' FORMAT CSV;

Compression is detected from the file extension, or stated explicitly with COMPRESSION. Supported types are none, gzip, deflate, br, xz, zstd, lz4, and bz2.

Load directly from S3#

The s3() table function reads remote files as a table, making a load an INSERT ... SELECT:

INSERT INTO uk_price_paid
SELECT *
FROM s3('https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst');

Inspect a remote file before loading it:

DESCRIBE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv', 'CSVWithNames');

A raw CSV carries no type information, so ClickHouse infers most columns as Nullable(String). Declare real types in your CREATE TABLE and cast in the SELECT — Nullable costs you on every read.

Public buckets need no credentials, using NOSIGN:

SELECT * FROM s3(
   'https://datasets-documentation.s3.eu-west-3.amazonaws.com/aapl_stock.csv',
   NOSIGN,
   'CSVWithNames'
)
LIMIT 5;

Glob patterns load many files at once, in readonly mode:

INSERT INTO trips
SELECT * FROM s3('https://bucket.s3.amazonaws.com/trips_{1..100}.gz', 'TabSeparatedWithNames');

url() and file() work the same way for HTTP endpoints and local files.

Group data by partition key before loading#

INSERT sorts input by primary key and splits it by partition key. Inserting into many partitions at once significantly reduces insert performance. Group rows by partition key client-side before uploading, and prefer data that arrives roughly sorted by time.

Continuous ingestion#

For a stream rather than a load, attach a Kafka engine table to a materialized view, or use ClickPipes in Cloud for managed Kafka, S3, Postgres CDC, MySQL CDC, and Kinesis. See integrations.

Checklist#

• Batch 10,000 to 100,000 rows per insert, at roughly one insert per second.
• Enable async inserts when the client cannot batch, keeping wait_for_async_insert = 1.
• Prefer Native or RowBinary over JSONEachRow for application traffic.
• Partition coarsely — by month, not by day or hour.
• Declare explicit column types instead of accepting inferred Nullable(String).
• Watch system.parts rather than waiting for Too many parts.

Next steps#

• MergeTree and data parts — what each insert physically creates
• Materialized views — transform rows as they arrive
• Monitor a running server — track parts, merges, and insert health