Why ClickHouse® Ingestion Slows Down: A Merge Pressure Diagnostic Guide

Short answer: Slow ClickHouse ingestion is usually a queueing problem before it is a storage problem. Identify whether the delay is in acknowledgements, queries, data freshness or replication. Then check whether parts, merges, mutations or replicas are falling behind. Change the work being created before adding infrastructure unless the measurements already point to storage.

Which latency is actually slow?

Start by naming the symptom you are trying to reduce, then measure that endpoint before changing a setting. INSERT acknowledgement is the time the producer waits for a response. Query latency is how long a representative SELECT takes while writes continue. Data freshness is the delay between an event arriving and becoming queryable. Replication delay tells you whether the replica serving reads has caught up.

Record a normal window and a slowdown window with the same query mix. Include rows and bytes per second, batch size, writer count, errors and retries. A faster acknowledgement is not an improvement if fresh data becomes visible later.

Are parts accumulating faster than merges clear them?

MergeTree tables write immutable parts and merge compatible parts in the background. Tiny batches and partition fan-out can create more housekeeping than the row count suggests.

SELECT
    partition_id,
    count() AS active_parts,
    sum(rows) AS stored_rows,
    sum(bytes_on_disk) AS stored_bytes,
    round(avg(rows)) AS mean_rows_per_part
FROM system.parts
WHERE active
  AND database = 'analytics'
  AND table = 'events'
GROUP BY partition_id
ORDER BY active_parts DESC
LIMIT 20;

Take repeated snapshots. If active parts keep rising in the same partitions, inspect batch size, writer concurrency and partition values per batch. On distributed deployments, inspect the underlying local tables and do not count replica copies as unique logical data.

What is already running?

SELECT
    database, table, elapsed,
    round(progress * 100, 1) AS progress_pct,
    num_parts, is_mutation,
    total_size_bytes_compressed,
    memory_usage
FROM system.merges
WHERE database = 'analytics'
  AND table = 'events'
ORDER BY elapsed DESC
LIMIT 20;

system.merges shows active merges and part mutations. A long-running merge may be large, progressing normally or competing for CPU, memory or storage bandwidth. Check progress and input size beside those resource metrics. No rows at one instant does not prove the table is healthy, so compare successive samples with part growth.

What do the query timings say?

SELECT
    toStartOfMinute(event_time) AS minute,
    query_kind,
    count() AS completed_queries,
    quantile(0.95)(query_duration_ms) AS p95_ms
FROM system.query_log
WHERE event_time >= now() - INTERVAL 15 MINUTE
  AND type = 'QueryFinish'
  AND is_initial_query = 1
  AND query_kind IN ('Insert', 'Select')
  AND has(tables, 'analytics.events')
GROUP BY minute, query_kind
ORDER BY minute, query_kind;

This system.query_log query separates successful INSERT and SELECT timings. Narrow it to the affected application or query shape, and review exceptions separately. For distributed traffic, the log may name the Distributed table rather than the local table. Collect the relevant nodes without double-counting initial and child queries.

Is the extra work coming from mutations, views or replication?

SELECT
    mutation_id, create_time,
    parts_to_do, latest_fail_reason
FROM system.mutations
WHERE database = 'analytics'
  AND table = 'events'
  AND is_done = 0
ORDER BY create_time
LIMIT 20;

Unfinished mutations and repeated failures can explain a new slowdown. Incremental materialized views also transform incoming blocks and write to target tables. Include that downstream work when you calculate what each incoming block costs to process. For replicated tables, compare local part growth with replica delay and queue failures.

Observation	Likely source	Next check
Small parts keep accumulating	Tiny writes or partition fan-out	Batch size, writer count and partition values
SELECT slows during merges	Shared CPU, memory or I/O contention	Same-window query and resource metrics
Writes slowed after a pipeline change	Views, mutations or transformations	Recent changes and downstream targets
Acknowledgement is fast but data is late	Buffering or replication lag	Event-to-queryable time and replica health
Parts stabilise but storage latency stays high	Storage or instance limit	Queueing, throughput and application p95/p99

Once excess work is understood, test whether the infrastructure can sustain the remaining insert rate while representative queries continue. Altinity provides a managed service for ClickHouse. Nirvana supplies the cloud infrastructure beneath it, including ABS with 20,000 baseline IOPS per volume. That specification is not a query-latency guarantee.

For a ClickHouse workload, start with a 14-day Altinity.Cloud trial. Use representative data and queries so the evaluation reflects the production path.

A workload review should produce a repeatable comparison plan: schema, data volume, insert rate, query mix, durability settings, recovery test, required p95 or p99 latency and total cost at that target. Book a ClickHouse workload review.

FAQ

Should I run OPTIMIZE TABLE FINAL?

Not as a routine fix. It can add substantial CPU and I/O work and bypass normal merge safeguards. Use it only when you understand the table state and the cost.

How many parts is too many?

There is no useful universal number. Watch whether active parts keep rising, whether inserts are delayed or rejected, and whether merges are catching up in the affected partitions.

Does async insert fix small batches?

It can combine small writes when client batching is impractical. It does not fix partition fan-out. With wait_for_async_insert=1, acknowledgement follows a successful flush; setting it to zero acknowledges buffering earlier and changes failure handling.

When does more IOPS help?

When storage latency and queueing rise with the workload after write amplification, mutations and replica health are understood. Test the full insert and query mix rather than treating peak IOPS as the result.

---

About Nirvana Labs

Nirvana Labs is a high-performance storage cloud purpose built for blockchain, AI and databases i.e. the most demanding, real-time, stateful workloads. Accelerated Block Storage (ABS) offers 20K baseline IOPS included, no over provisioning. Nirvana Kubernetes Service (NKS) with Karpenter auto-scaling, high clock-speed compute and private networking. Backed by Jump Trading, Crucible, etc with 50+ customers live in production today.

Learn more at Nirvana Labs

Nirvana Cloud | Pricing | Blog | Docs | Changelog | LinkedIn | Twitter | Telegram | YouTube