ClickHouse ingestion performance is decided long before the query planner ever touches your data. It is decided by how many parts your writers create per second, how much background merge work those parts generate, and how much extra work you have quietly moved onto the insert path with materialized views. This article is written for senior engineers who already run MergeTree in production and now need to raise ClickHouse ingestion performance without watching part counts, merge queues, and Kafka consumer lag spiral.
Every recommendation below names the system table or metric that justifies it, because ClickHouse ingestion performance tuning without instrumentation is guessing dressed up as engineering. Where behaviour differs between community ClickHouse and cloud or vendor-managed offerings, the difference is called out. Any numeric ranges are illustrative starting points, not benchmarks — measure your own workload before you commit to a value.
Why Small, Frequent Inserts Destroy ClickHouse Ingestion Performance
A MergeTree insert is not a row append. For every INSERT, the server decompresses and parses the payload, builds an in-memory block in MergeTree format, sorts rows by the primary key if they did not arrive pre-sorted, builds a sparse primary index, applies per-column compression, and writes a new immutable part directory to disk. Almost all of that cost is fixed per part, not per row. A ten-row insert therefore pays a very similar price to a hundred-thousand-row insert, which is why batch shape dominates ClickHouse ingestion performance far more than disk speed does.
Measuring part creation cost in system.part_log
The authoritative evidence lives in system.part_log, which is only populated if the part_log server setting is enabled — confirm that first, because on trimmed-down community installs it is sometimes switched off. Filter on event_type = 'NewPart' and look at the rows, size_in_bytes, and duration_ms columns:
SELECT
toStartOfHour(event_time) AS hour_bucket,
count() AS new_parts,
median(rows) AS median_rows_per_part,
formatReadableSize(median(size_in_bytes)) AS median_part_size,
median(duration_ms) AS median_write_ms
FROM system.part_log
WHERE event_type = 'NewPart'
AND database = 'analytics'
AND table = 'events_local'
AND event_date >= today() - 1
GROUP BY hour_bucket
ORDER BY hour_bucket ASC;If median_rows_per_part sits in the hundreds while new_parts runs into the thousands per hour, your ClickHouse ingestion performance ceiling is a batching problem, not a hardware problem. On ClickHouse Cloud and other managed platforms these log tables are node-local, so wrap the query in clusterAllReplicas() to get a cluster-wide picture rather than one node’s slice.
Merge amplification is the real bill
Every part you create must eventually be merged into larger parts, and merges are hierarchical: the same bytes get read and rewritten across several generations before they land in a large final part. That hidden ClickHouse ingestion performance cost is visible in the MergeParts rows of system.part_log, where read_bytes, merged_from, peak_memory_usage, and duration_ms describe exactly how much I/O and RAM your ingest pattern is charging to the background pool.
SELECT
sumIf(size_in_bytes, event_type = 'NewPart') AS bytes_ingested,
sumIf(read_bytes, event_type = 'MergeParts') AS bytes_reread_by_merges,
round(bytes_reread_by_merges / nullIf(bytes_ingested, 0), 2) AS merge_amplification,
countIf(event_type = 'MergeParts') AS merge_events,
round(avgIf(length(merged_from), event_type = 'MergeParts'), 1) AS avg_parts_per_merge
FROM system.part_log
WHERE database = 'analytics'
AND table = 'events_local'
AND event_date >= today() - 7
GROUP BY ALL;Track merge_amplification as a first-class ClickHouse ingestion performance KPI. When it climbs after a deployment, the cause is almost always smaller batches, a higher-cardinality partition key, or a new materialized view — and system.part_log will tell you which, because it records partition_id and table for every part.
Batch Sizing Guidance That Actually Improves ClickHouse Ingestion Performance
The official ClickHouse insert strategy guidance is to send at least 1,000 rows per insert, ideally 10,000 to 100,000 rows, and to keep the rate at roughly one insert query per second per table so that background merging can keep up. Those are the numbers to design your producer around for predictable ClickHouse ingestion performance; they are documented recommendations rather than measured results for your schema.
Two schema-level multipliers routinely cap ClickHouse ingestion performance despite otherwise correct batching. First, each flush creates at least one part per distinct partition value in the batch, so a well-sized 100,000-row batch spread across 200 partitions still produces 200 small parts. Second, max_insert_block_size (default roughly 1,048,576 rows) splits very large blocks, so a single enormous insert is not automatically a single part. Prove both with a per-query breakdown:
SELECT
query_id,
uniqExact(partition_id) AS partitions_touched,
count() AS parts_created,
sum(rows) AS rows_inserted,
round(sum(rows) / count()) AS rows_per_part
FROM system.part_log
WHERE event_type = 'NewPart'
AND database = 'analytics'
AND table = 'events_local'
AND event_date = today()
GROUP BY query_id
HAVING parts_created > 1
ORDER BY parts_created DESC
LIMIT 20;When partitions_touched is high, coarsen the partition key rather than shrinking the batch. Monthly partitioning is usually the right default for high-throughput event tables; daily partitioning is a query-pruning optimisation that you pay for on every single insert.
CREATE TABLE analytics.events_local
(
event_time DateTime64(3, 'UTC'),
event_date Date MATERIALIZED toDate(event_time),
tenant_id UInt32,
session_id UUID,
event_name LowCardinality(String),
payload String CODEC(ZSTD(3)),
ingested_at DateTime DEFAULT now()
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/analytics/events_local', '{replica}')
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_name, event_time)
TTL event_date + INTERVAL 90 DAY DELETE
SETTINGS index_granularity = 8192,
parts_to_delay_insert = 1000,
parts_to_throw_insert = 3000,
max_insert_block_size = 1048576;Also remember that synchronous inserts into MergeTree tables are deduplicated by block hash by default, which makes retries safe provided the client resends byte-identical batches in the same order. Retry logic that reshuffles or re-batches rows silently defeats that protection; deduplication_block_ids in system.part_log is where you confirm dedup is doing what you expect.
Async Insert Settings for ClickHouse Ingestion Performance
When hundreds of agents each emit tiny payloads and client-side buffering is impractical, asynchronous inserts move batching into the server and become the main ClickHouse ingestion performance lever available to you. With async_insert = 1 the server accumulates rows in an in-memory buffer and flushes when the first of three thresholds trips: async_insert_max_data_size (documented default 100 MiB), async_insert_busy_timeout_ms (200 ms in community builds, 1000 ms on ClickHouse Cloud), or async_insert_max_query_number (450 queries).
Pin these against your version before you copy anything into production. The defaults have moved more than once across releases, and since version 24.2 ClickHouse enables async_insert_use_adaptive_busy_timeout by default, which slides the flush interval between async_insert_busy_timeout_min_ms (50 ms) and async_insert_busy_timeout_max_ms based on the observed arrival rate. Read the effective values from system.settings on the actual server.
The durability trade-off is the whole decision
The durability setting is where ClickHouse ingestion performance and data safety trade directly against each other. With the default wait_for_async_insert = 1, the server acknowledges only after the buffer has been flushed to storage, so flush errors surface to the client and normal retry logic works. Setting wait_for_async_insert = 0 acknowledges as soon as data is buffered: lowest latency, highest throughput, and no guarantee the rows were ever persisted. There is no dead-letter queue in that mode, so failures are only reconstructable after the fact from server logs and system tables. ClickHouse’s own documentation recommends against it for anything you cannot afford to lose, and it also removes the natural backpressure that protects an overloaded server.
Two further behaviours catch teams out. Parsing and schema validation happen at flush time, not at receipt, and a single malformed row rejects that entire insert query’s payload. And automatic insert deduplication, which is on by default for synchronous inserts, is off for async inserts unless explicitly enabled — and enabling it is not recommended when the target table has dependent materialized views. Async inserts also do not apply to INSERT ... SELECT, which always runs synchronously.
ALTER USER ingest_service
SETTINGS async_insert = 1,
wait_for_async_insert = 1,
async_insert_max_data_size = 10485760,
async_insert_busy_timeout_min_ms = 200,
async_insert_busy_timeout_max_ms = 2000,
async_insert_max_query_number = 1000;Applying settings to a dedicated user or settings profile rather than to the global config keeps the blast radius to one service. Before a graceful shutdown or maintenance window, drain buffers with SYSTEM FLUSH ASYNC INSERT QUEUE, otherwise buffered rows are lost.
Measuring async insert ClickHouse ingestion performance
There are two instruments. system.asynchronous_inserts shows the live queue — one row per pending insert shape with total_bytes, first_update, and the entries.query_id array. Because buffers are keyed by query shape plus settings combination and maintained per node, an unexpectedly large number of rows here means your producers are fragmenting buffers with varying column lists or formats.
SELECT
database,
table,
format,
count() AS pending_shapes,
sum(total_bytes) AS pending_bytes,
max(dateDiff('second', first_update, now64())) AS oldest_pending_seconds
FROM system.asynchronous_inserts
GROUP BY database, table, format
ORDER BY pending_bytes DESC;system.asynchronous_insert_log records outcomes: status (‘Ok’, ‘ParsingError’, ‘FlushError’), rows, bytes, flush_query_id, and timeout_milliseconds, the adaptive timeout actually chosen for that entry. Rows per flush is the single number that best summarises async ClickHouse ingestion performance — drive it into the 10,000 to 100,000 window that the batching guidance recommends.
SELECT
toStartOfMinute(event_time) AS minute_bucket,
status,
count() AS buffered_entries,
uniqExact(flush_query_id) AS flushes,
round(sum(rows) / nullIf(uniqExact(flush_query_id), 0)) AS rows_per_flush,
round(avg(timeout_milliseconds)) AS avg_adaptive_timeout_ms
FROM system.asynchronous_insert_log
WHERE database = 'analytics'
AND table = 'events_local'
AND event_date = today()
GROUP BY minute_bucket, status
ORDER BY minute_bucket ASC;A rising count of FlushError or ParsingError rows is your early warning that a producer has changed its schema. This log must be enabled via the asynchronous_insert_log server settings section; it is on by default in ClickHouse Cloud but not guaranteed on every self-managed build.
Kafka Pipelines and ClickHouse Ingestion Performance: Engine or External Consumer
The Kafka table engine gives you an in-database consumer with no extra moving parts, and for many community deployments it is the shortest path to acceptable ClickHouse ingestion performance. Declare it with the full settings list so the configuration is reviewable in version control, and never select from it directly — a Kafka engine table is a one-shot stream, so a debugging SELECT can consume messages your materialized view then never sees.
CREATE TABLE analytics.events_kafka_queue
(
raw_message String
)
ENGINE = Kafka()
SETTINGS kafka_broker_list = 'broker_1:9093,broker_2:9093,broker_3:9093',
kafka_topic_list = 'events.raw',
kafka_group_name = 'clickhouse_events_ingest',
kafka_format = 'JSONAsString',
kafka_security_protocol = 'sasl_ssl',
kafka_sasl_mechanism = 'SCRAM-SHA-512',
kafka_num_consumers = 4,
kafka_thread_per_consumer = 1,
kafka_max_block_size = 1048576,
kafka_poll_max_batch_size = 65536,
kafka_poll_timeout_ms = 5000,
kafka_flush_interval_ms = 7500,
kafka_commit_every_batch = 0,
kafka_skip_broken_messages = 0,
kafka_handle_error_mode = 'stream',
kafka_consumer_reschedule_ms = 500,
kafka_client_id = 'clickhouse_shard_1_replica_1';Keep SASL credentials out of the DDL. Put them in a named collection or in the server’s kafka configuration section, because table DDL is readable through system.tables.
Consumer group and block sizing for ClickHouse ingestion performance
kafka_max_block_size is the single biggest lever on part size for Kafka ingestion: it caps how many messages accumulate before a block is written to the target table, and it defaults to max_insert_block_size. Altinity’s long-running Kafka engine guidance has consistently been to raise it into the high hundreds of thousands, because a small block threshold produces exactly the tiny-part pathology described earlier. Confirm the effect on ClickHouse ingestion performance in system.part_log — median rows per NewPart on the target table should rise.
kafka_num_consumers must not exceed the topic’s partition count, since Kafka assigns at most one consumer per partition, and should not exceed the physical core count on the node. Pair it with kafka_thread_per_consumer = 1 so each consumer flushes independently instead of having its rows squashed into one shared block. Older ClickHouse releases were effectively single-threaded per Kafka table, and the workaround was several Kafka tables each with its own materialized view; on modern versions prefer the threaded consumer setting, but verify on your build.
Every Kafka engine table also occupies a slot in the background message broker pool. If you run many topics, raise background_message_broker_schedule_pool_size and watch BackgroundMessageBrokerSchedulePoolTask in system.metrics against that limit; saturation there shows up as stalled consumption rather than as an error.
Exactly-once caveats
The Kafka table engine is at-least-once, not exactly-once. Offsets are committed after the block is written, so a crash in that window causes the batch to be replayed. ReplicatedMergeTree block-hash deduplication can absorb a replay only when the replayed block is identical, which means any non-deterministic transformation in the materialized view — now(), rand(), generateUUIDv4() — defeats it. Multiple or cascaded materialized views on one Kafka table are also not atomic: a mid-flight failure can leave targets inconsistent.
Recent versions offer an experimental mode that stores committed offsets in ClickHouse Keeper via kafka_keeper_path and kafka_replica_name, recording the last batch size so replays are deterministic. It is explicitly experimental, requires kafka_thread_per_consumer for multiple consumers, and should not carry production traffic yet.
External consumers are the alternative. A Debezium change-data-capture stream landing in Kafka and consumed by Flink, Kafka Connect, or a purpose-built service gives you deduplication keyed on business identifiers, schema-registry-driven evolution, independent scaling of the consumer tier, and explicit control over batch size and back-off — at the cost of another system to operate. For CDC, pair it with a versioned collapsing target:
CREATE TABLE analytics.orders_cdc
(
order_id UInt64,
tenant_id UInt32,
status LowCardinality(String),
amount Decimal64(4),
source_ts DateTime64(3, 'UTC'),
source_lsn UInt64,
is_deleted UInt8 DEFAULT 0
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/analytics/orders_cdc', '{replica}', source_lsn, is_deleted)
PARTITION BY toYYYYMM(source_ts)
ORDER BY (tenant_id, order_id)
SETTINGS index_granularity = 8192;On ClickHouse Cloud the vendor recommendation is ClickPipes rather than the Kafka engine, because it scales ingestion independently of cluster resources and supports private networking. That advice is specific to the managed product; on community ClickHouse the choice remains Kafka engine versus your own consumer, and the deciding factor for ClickHouse ingestion performance is usually whether you need transformation logic and replay control that the engine does not provide.
Materialized View Fan-Out Cost on the Insert Path
Materialized views in ClickHouse are insert triggers, and they execute in the inserting thread. Three views on one table mean every INSERT builds four blocks, performs four sorts, and writes at least four parts — more once partition fan-out is applied to each target. Insert latency and part creation rate scale with view count, so materialized view design is often the largest single factor in ClickHouse ingestion performance, and that cost is charged to the writer rather than to a background pool.
The precise instrument is system.query_views_log, which records view_name, view_duration_ms, read_rows, written_rows, peak_memory_usage, status, and exception for each view execution, keyed by initial_query_id. This is how you attribute a ClickHouse ingestion performance regression to a specific view instead of blaming the disk.
SELECT
view_name,
count() AS executions,
round(avg(view_duration_ms), 1) AS avg_view_ms,
quantile(0.99)(view_duration_ms) AS p99_view_ms,
sum(written_rows) AS rows_written,
formatReadableSize(max(peak_memory_usage)) AS max_peak_memory,
countIf(status != 'QueryFinish') AS failures
FROM system.query_views_log
WHERE event_date = today()
GROUP BY view_name
ORDER BY p99_view_ms DESC;Cross-check with system.part_log grouped by table for a single query_id: one insert should show you every target it touched and how many parts each one produced. To protect ClickHouse ingestion performance, keep view SELECTs cheap, replace joins against dimension tables with dictionary lookups via dictGet, and let SummingMergeTree or AggregatingMergeTree targets do the aggregation work during merges instead of at insert time.
CREATE TABLE analytics.events_per_minute
(
minute_bucket DateTime,
tenant_id UInt32,
event_name LowCardinality(String),
event_count UInt64,
payload_bytes UInt64
)
ENGINE = ReplicatedSummingMergeTree('/clickhouse/tables/{shard}/analytics/events_per_minute', '{replica}', (event_count, payload_bytes))
PARTITION BY toYYYYMM(minute_bucket)
ORDER BY (tenant_id, event_name, minute_bucket)
SETTINGS index_granularity = 8192;
CREATE MATERIALIZED VIEW analytics.events_per_minute_mv TO analytics.events_per_minute
AS
SELECT
toStartOfMinute(event_time) AS minute_bucket,
tenant_id,
event_name,
count() AS event_count,
sum(length(payload)) AS payload_bytes
FROM analytics.events_local
GROUP BY minute_bucket, tenant_id, event_name;parallel_view_processing can execute views concurrently and shorten wall-clock insert time, but it multiplies peak memory by roughly the number of views — validate against peak_memory_usage in system.query_views_log and the MemoryTracking metric in system.metrics before enabling it on a busy ingest path.
Backpressure Detection and ClickHouse Ingestion Performance Monitoring
ClickHouse gives you explicit backpressure signals, and they belong on a ClickHouse ingestion performance dashboard rather than in a postmortem. In escalating order of severity: the DelayedInserts metric in system.metrics and the DelayedInsertsMilliseconds counter in system.events mean active parts crossed parts_to_delay_insert and the server is deliberately sleeping your writers. The RejectedInserts counter, together with the familiar “Too many parts” exception, means parts_to_throw_insert was crossed and writes are now failing outright.
Behind those, the capacity picture comes from system.parts (active part count per table), system.merges (in-flight merges with progress, elapsed, and num_parts), BackgroundMergesAndMutationsPoolTask in system.metrics compared against the configured pool size, and system.replication_queue for replicated tables, where a growing queue means replicas are falling behind the writer.
SELECT
hostname() AS node,
(SELECT value FROM system.metrics WHERE metric = 'DelayedInserts') AS delayed_inserts_now,
(SELECT value FROM system.events WHERE event = 'DelayedInsertsMilliseconds') AS delayed_ms_total,
(SELECT value FROM system.events WHERE event = 'RejectedInserts') AS rejected_inserts_total,
(SELECT value FROM system.metrics WHERE metric = 'BackgroundMergesAndMutationsPoolTask') AS merge_pool_busy,
(SELECT count() FROM system.parts WHERE active AND database = 'analytics' AND table = 'events_local') AS active_parts,
(SELECT count() FROM system.merges WHERE database = 'analytics') AS merges_running,
(SELECT count() FROM system.replication_queue WHERE database = 'analytics') AS replication_queue_size;For Kafka pipelines, ClickHouse ingestion performance lag has two halves. Consumer health comes from system.kafka_consumers: assignments.current_offset per partition, staleness of last_poll_time and last_commit_time, num_rebalance_assignments and num_rebalance_revocations, and the exceptions.text array holding the ten most recent errors.
SELECT
table,
consumer_id,
assignments.partition_id AS partition_ids,
assignments.current_offset AS current_offsets,
num_messages_read,
num_commits,
num_rebalance_assignments,
num_rebalance_revocations,
dateDiff('second', last_poll_time, now()) AS seconds_since_poll,
dateDiff('second', last_commit_time, now()) AS seconds_since_commit,
arrayStringConcat(exceptions.text, ' | ') AS recent_errors
FROM system.kafka_consumers
WHERE database = 'analytics'
ORDER BY seconds_since_commit DESC;A climbing num_rebalance_revocations with an otherwise healthy broker almost always means block flush time is exceeding the consumer’s max.poll.interval.ms, which you fix by lowering kafka_max_block_size or kafka_flush_interval_ms — the opposite direction from the part-size optimisation, which is exactly why both need to be measured rather than assumed. The second half of lag is end-to-end freshness, measured against the target table itself and compared with broker-side consumer group lag:
SELECT
dateDiff('second', max(event_time), now()) AS ingest_lag_seconds,
count() AS rows_last_hour
FROM analytics.events_local
WHERE event_date >= today() - 1
AND event_time >= now() - INTERVAL 1 HOUR;As an illustrative alerting scheme — not a benchmark, and not a substitute for your own baselining — many teams warn when active parts exceed roughly half of parts_to_delay_insert, page when DelayedInsertsMilliseconds starts increasing at all, and treat any non-zero RejectedInserts as a hard incident.
Validate on Staging Before You Touch Production
Every ClickHouse ingestion performance change discussed here alters durability semantics, failure modes, or memory behaviour, and several of them cannot be cleanly reverted once data has landed. Reproduce your production traffic shape on a staging cluster first and rerun the ClickHouse ingestion performance queries above there: same partition key, same materialized views, same producer concurrency and message sizes. Keep part_log, asynchronous_insert_log, query_views_log, and metric_log enabled there, capture the queries above as a before-and-after baseline, and apply settings through a dedicated user or settings profile rather than global config so that a bad value affects one service instead of the whole cluster.
Pay particular attention to Kafka engine changes. On older releases, changing engine settings required dropping and recreating the table, and ALTER TABLE ... MODIFY SETTING support is version dependent, so plan the detach-and-reattach sequence for dependent materialized views as part of the change — and rehearse it on staging, where a mistaken DETACH costs nothing but a rerun.
Get Expert Help Tuning ClickHouse Ingestion Performance
Sustained ClickHouse ingestion performance is a systems problem: batch sizing, partitioning, async insert thresholds, Kafka consumer topology, and materialized view design all interact, and the right trade-off depends on your durability requirements and query patterns. If you would rather not discover the interactions in production, ChistaDATA ClickHouse Consulting works with engineering teams to design and benchmark ingest pipelines against their own workloads instead of generic recommendations.
For ongoing operations, ChistaDATA ClickHouse Managed Services covers capacity planning, merge and part monitoring, and upgrade paths, while our 24/7 ClickHouse support team handles ingest incidents when part counts and consumer lag start moving in the wrong direction. Talk to us about a ClickHouse ingestion performance review before your next traffic increase.
