Fixing insert shape so merges can keep up

Overview

A measurement platform started rejecting inserts with too-many-parts errors during peak traffic. Each application instance was inserting small row batches on a short timer, creating far more parts than the merge scheduler could retire. AceMQ engaged under the emergency SLA.

Challenge

The parts limit is a guardrail rather than the problem — raising it lets the table degrade further until queries slow to a crawl reading across thousands of parts. The real causes were insert batch size, insert frequency, and a partition key with high enough cardinality that every insert touched many partitions at once, multiplying part creation.

Environment

ClickHouse cluster on AWS ingesting event streams from Kafka for real-time campaign measurement.

Approach

AceMQ measured part creation rate against merge throughput per table to quantify the actual gap, then addressed insert shape first because no amount of merge tuning compensates for inserts arriving in the wrong form. The partition key change was staged so existing data remained queryable throughout.

Solution

• Measured part creation rate against merge throughput per table to quantify the real deficit
• Consolidated many small application inserts into fewer large batches through a buffering ingestion layer
• Reduced partition key cardinality so each insert touched a bounded number of partitions
• Retuned background merge pool size and merge settings against the available CPU and I/O
• Staged the partition key change so existing data remained queryable through the transition
• Added part count per table and merge backlog to alerting well ahead of the rejection threshold

Outcome

Technologies