---
title: "What Replo learned optimizing 100+ billion events in ClickHouse"
description: "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."
---

# What Replo learned optimizing 100+ billion events in ClickHouse

[Clickhouse](https://yomu.fyi/company/clickhouse) · ClickHouse · Mar 9, 2026

**Type:** Problem & solution

## 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.

**Tags:** [Architecture](https://yomu.fyi/topic/architecture), [Data Pipelines](https://yomu.fyi/topic/data-pipelines), [Performance](https://yomu.fyi/topic/performance), [Scalability](https://yomu.fyi/topic/scalability), [Streaming](https://yomu.fyi/topic/streaming)

- Source: [Clickhouse](https://clickhouse.com/blog/replo)
- Source URL: https://clickhouse.com/blog/replo
- Ingested by Yomu: 2026-08-28T01:21:41.873Z

[Read original post](https://clickhouse.com/blog/replo)