Scaling Real‑Time Analytics at KaptureCX: Best Practices with RisingWave and StarRocks

KaptureCX, a customer‑support automation platform serving e‑commerce, healthcare, and finance, faced two major challenges as its business grew: massive ticket state upserts (average 15 mutations per ticket, millions of tickets daily) and heavy multi‑table joins across 5‑6 tables per report.

Initial Architecture: ClickHouse Limitations

Upserts : Used ReplacingMergeTree and had to force FINAL on every query, causing extreme CPU consumption; each OPTIMIZE ... FINAL merge spiked CPU to 100% and made the system unavailable during peak hours.

Multi‑table Joins : ClickHouse’s early versions lacked a cost‑based optimizer, resulting in very poor join performance for the required dynamic, wide‑table queries.

Core Engine Refactor: Introducing StarRocks

StarRocks provides native primary‑key tables that automatically deduplicate rows with the same key, eliminating the need for manual MERGE operations and preventing CPU spikes during frequent ticket updates.

Benefit : Directly reads the latest state without extra processing, perfectly matching the 15‑times‑per‑day ticket mutation pattern.

Colocate Joins & Hash Bucketing : Data is bucketed by customer_id / CM_ID into 32 buckets, ensuring that all rows for a specific customer reside on the same BE node. Declaring a Colocate Group for related tables forces them onto the same physical node, removing network shuffle during joins and compressing query latency to near‑zero.

CBO Optimizer : StarRocks’ FE node evaluates table sizes and distributions to automatically choose the optimal join order and driver table for complex 4‑5 table joins.

Seamless Migration: MySQL Compatibility

Because StarRocks is MySQL‑protocol compatible, KaptureCX only needed to change the connection string in environment variables; no application‑level code changes were required.

Real‑Time Ingestion Pipeline: RisingWave + Kafka

After stabilizing the analytical engine, the next challenge was to stream data from the legacy MySQL databases to StarRocks in real time. The team evaluated Debezium but rejected it because a StarRocks cluster failure would require a multi‑day full MySQL re‑pull, which would overload the production MySQL.

Instead, they adopted RisingWave, a Rust‑based high‑concurrency stream processing database (a modern alternative to Flink), which offers:

Built‑in CDC : Directly reads MySQL binlog.

S3 Checkpointing : Persists state and checkpoints to S3, enabling fast replay within minutes after a failure.

Cloud‑Native Deployment : Both RisingWave and StarRocks are deployed via Helm on Kubernetes, allowing effortless scaling by adding compute nodes.

Kafka is placed between RisingWave and StarRocks as a buffer to absorb spiky CDC bursts; StarRocks’ routine load consumes data from Kafka in micro‑batches (every few seconds), preventing overload.

The final data flow is represented as:

MySQL (Binlog) → RisingWave → Kafka → StarRocks (Routine Load)

Business Outcomes & AI Enablement

Performance Leap : Report generation that previously took 15‑20 minutes on MySQL now completes in milliseconds.

R&D Efficiency : Custom dashboards that once required weeks of backend work can now be delivered within one day by creating SQL views in StarRocks.

Agentic Data Plane : Leveraging StarRocks’ sub‑millisecond query speed, KaptureCX built an AI‑driven data plane where a large language model generates SQL, executes it via an MCP server on StarRocks, and returns answers to users.

Conclusion

The case study demonstrates how combining StarRocks’ primary‑key engine, colocate joins, and cost‑based optimizer with RisingWave’s CDC and checkpointing, plus Kafka buffering, resolves high‑frequency updates and complex joins while delivering millisecond‑level latency, dramatically improving reporting speed and enabling AI‑powered analytics.