What Replo learned optimizing 100+ billion events in ClickHouse

Summary

Replo built an analytics pipeline on ClickHouse to serve more than 4,000 Shopify merchants processing 3,000 to 5,000 events per second. The initial single-table architecture recalculated session metrics on the fly, leading to poor query efficiency as volume scaled. To speed up dashboard queries, the team implemented a mark-and-unmark pattern with SummingMergeTree and refreshable materialized views, but real-time deduplication and fractional attribution caused runaway query backlog loops. Replo resolved the issue by limiting real-time recomputations to a specialized table holding only the last 40 minutes of purchase-related events. This architecture allowed processing over 100 billion records while keeping query times fast and ingestion lag to approximately one minute.

Context

Replo Analytics processes 3,000 to 5,000 frontend events per second for over 4,000 Shopify merchants on ClickHouse. Calculating metrics on the fly across a single event table led to repetitive calculations and slow dashboards. Adding fractional attribution and query-time deduplication clauses caused materialized views to run longer than their one-minute refresh intervals, triggering continuous reprocessing backlogs across billions of records.

Approach / What changed

Replo isolated live session processing by creating a table restricted to the last 40 minutes of purchase-related events rather than scanning all historical records. The team replaced a complex mark-and-unmark strategy and nested CTEs with joins inside a single materialized view flusher. They backfilled historical events using offline migration scripts and added optimizations including LowCardinality columns, materialized JSON fields, and non-Nullable defaults.

Takeaways

• ClickHouse merge trees deduplicate data eventually rather than immediately, requiring careful handling when writing intentional duplicates in real-time pipelines.
• Applying query-time deduplication across massive datasets inside fast materialized view refresh loops can trigger runaway execution backlogs.
• Isolating real-time recomputation to a sliding 40-minute window of purchase events prevented full-table historical scans across 100 billion records.

Related reading