How Trophy Aggregates Over 30 Million Streaks in ClickHouse

TL;DR

• Migrated dashboard analytics from Postgres to ClickHouse Cloud, reducing query times from tens of seconds to hundreds of milliseconds
• Streaming CDC from Postgres via ClickPipes — zero application code changes for ingestion
• 60 million metric events, 30 million streak periods, and 1 million users tracked across 40+ customer applications
• Built-in table-level sampling keeps large-customer dashboards fast without sacrificing accuracy

What Trophy Does

Trophy is a gamification infrastructure platform. Product teams use our APIs to add streaks, achievements, points, leaderboards, and lifecycle emails to their apps — features that companies like Duolingo and Spotify have proven drive retention and engagement.

Our customers ship gamification in days instead of months. A single API call tracks a user interaction; from there, Trophy handles streak calculations, achievement unlocks, point economies, leaderboard rankings, and automated email and push notification delivery. Everything is backed by a dashboard where product teams monitor how their gamification features affect user behavior.

That dashboard is the analytical heart of Trophy, and it's where our Postgres-based stack started to hurt.

Why We Needed ClickHouse

Trophy is a multi-tenant platform. Every API call carries an authId that scopes data to a specific customer. Our analytics dashboard lets each customer slice and dice their data by date ranges, user attributes, streak frequency, metric type, and more — all in real time.

For a long time, Postgres handled this well. But as our customer base grew and individual customers scaled to hundreds of thousands of users, the cracks appeared:

• Streak queries hit 10+ seconds. Our streak analytics join streak_periods against users and attribute_values to support attribute-based filtering. On large customers, these queries routinely took over 10 seconds — far too slow for an interactive dashboard.
• Raw event tables grew past what Postgres wanted to scan. With 60 million metric events and counting, date-range aggregations across events, points_events, and leaderboard_events became increasingly painful.
• Attribute-based filtering required correlated subqueries. Every dashboard filter like "show me streaks for users where plan = 'pro'" required an EXISTS subquery into attribute_values. In Postgres, these correlated subqueries don't optimize well at scale.

We evaluated several options. What made ClickHouse the clear winner was the combination of columnar compression, materialized views for continuous aggregation, and ClickPipes — which meant we could stream our existing Postgres data into ClickHouse without changing a single line of application code.

Setting Up the Data Pipeline with ClickPipes

The most important architectural decision we made was keeping Postgres as the source of truth for all operational and API-facing paths. ClickHouse is analytics-only. This meant we needed a reliable, low-latency CDC pipeline, not a dual-write architecture.

ClickPipes made this straightforward. The setup:

1. Create a Postgres publication covering the 19 tables we need for analytics — everything from events and streak_periods to lookup tables like metrics and achievements.
2. Configure ClickPipes CDC in the ClickHouse Cloud console, mapping each source table to a ReplacingMergeTree target.
3. Wait for the initial snapshot to complete, then verify row counts.

That's it for ingestion. No Kafka, no custom connectors, no application-side changes. Postgres writes happen exactly as before; ClickPipes tails the WAL and streams changes into ClickHouse with sub-minute latency.

We use ReplacingMergeTree for every table, even append-only ones. The overhead is negligible, and it provides safety against duplicate CDC deliveries, accidental replays, and rare one-off updates. Each table has a carefully chosen ORDER BY that matches our query access patterns — for example, streak_periods is ordered by (authId, streakFrequency, userId, uuid) because nearly every streak query filters by customer and frequency first.

Architecture: Two Layers of Analytics Tables

Raw CDC tables mirror Postgres, which means they share the same 90-day data retention policy that we maintain to keep our primary database lean. To keep older data available in ClickHouse, our architecture has two layers:

Direct-Query Enriched Tables

For data sources that analytics queries read directly — streak_periods, scheduled_messages, and achievement_completions — we maintain enriched copies. A refreshable materialized view runs every minute, picks up new CDC rows, and joins in user attributes from a user_attributes_latest table.

streak_periods (CDC) ──► streak_periods_enriched
                              ↑
              user_attributes_latest (Map per user)

The user_attributes_latest table deserves a mention. It aggregates every user's attribute key-value pairs into a single Map(String, String) column. This turns what was a correlated EXISTS subquery in Postgres into a simple map lookup in ClickHouse:

-- Postgres (slow)
WHERE EXISTS (
  SELECT 1 FROM attribute_values av
  WHERE av."userId" = u.uuid AND av."attributeId" = ? AND av."value" = ?
)

-- ClickHouse (fast)
WHERE userAttributes['plan'] = 'pro'

The materialized view that maintains this table is incremental — each refresh only rebuilds maps for users whose attributes changed in the last 2 minutes, not the entire table.

Durable Enriched Aggregate Tables

For metrics, points, and leaderboard data, we don't query raw events at all. Instead, we maintain durable daily aggregate tables:

• metric_daily_totals — one row per customer, metric, user, and day
• metric_attribute_daily_totals — same grain with an additional attribute breakdown
• points_daily_totals — daily point deltas per user, trigger, and boost
• leaderboard_daily_latest — latest rank and value per user per day

These tables are the long-term source of truth. When the archive cron deletes 91-day-old raw events from both Postgres and ClickHouse, the aggregates remain untouched. A repair materialized view runs every minute and recomputes the most recent 2 days, ensuring late-arriving CDC data is captured without requiring a full rebuild.

Sampling for Large Customers

As some of our customers grew past hundreds of thousands of users, even ClickHouse dashboard queries needed a budget. A query scanning a year of daily retention buckets for a 500K-user customer touches a lot of data.

We use ClickHouse's native SAMPLE BY to handle this. Each analytics table includes a sampleHash column — a stable per-user hash derived from cityHash64(userId) — placed inside the ORDER BY and declared as the SAMPLE BY key. This lets ClickHouse skip entire granules at the storage level rather than reading everything and filtering after the fact:

SELECT date, bucket, count() * _sample_factor AS estimated_users
FROM user_retention_bucket_daily FINAL SAMPLE 100000
WHERE authId = {authId:String}
GROUP BY date, bucket

Sampling by user rather than by row means the same user stays in or out of the sample across all dates, keeping cohorts stable over time. This also means cross-table joins between sampled tables remain meaningful — a sampled retention query and a sampled metric query include the same set of users.

The Results

After completing the migration, the difference was immediate:

• Streak dashboard queries dropped from 10+ seconds to hundreds of milliseconds, even for our largest customers.
• Long-range analytics now work seamlessly across the 90-day archive boundary. Customers can view metric trends, point accumulation, and retention curves going back to their first day on Trophy.
• Attribute filtering went from the slowest part of every query to effectively free, thanks to the `Map(String, String)` enrichment pattern.
• No ingestion code changes. ClickPipes handles the entire data flow. Our application still writes exclusively to Postgres.

Advice For Others Considering ClickHouse

A few things we learned along the way:

• ClickPipes removes the hardest part. If your source of truth is Postgres, CDC via ClickPipes is dramatically simpler than building and maintaining a Kafka-based pipeline. The initial snapshot, ongoing replication, and schema handling just work.
• Design your ORDER BY for your queries, not your data model. The sorting key is the most consequential decision for each table. Get it right and ClickHouse is absurdly fast; get it wrong and you're doing full scans.
• ReplacingMergeTree is a safe default. Even for append-only tables, the deduplication safety net is worth the negligible overhead. Combined with `FINAL`, it gives you correct results without worrying about duplicate CDC deliveries.
• Enrich at the aggregate level, not the raw level. We initially planned to create enriched copies of every raw CDC table. The simpler design was to enrich only the tables that analytics actually reads — either direct-query enriched tables or durable aggregates.
• Refreshable materialized views are underrated. They replaced what would have been a fleet of cron jobs or a dbt pipeline. A 1-minute refresh interval with a 2-day rebuild window gives us eventual consistency without any external orchestration.

About Trophy

Trophy is a gamification infrastructure platform that helps product teams build retention-driving features like streaks, achievements, points, leaderboards, and lifecycle emails. Product teams use Trophy to power gamification experiences for more than 1 million users across education, fitness, social, and productivity applications.

Trophy's API-first approach lets developers integrate gamification in days, while no-code tools give product managers and marketers control over campaigns, templates, and A/B tests. Customers like RevisionDojo, Campfire, and Lexioo have seen retention boosts of up to 22% after implementing Trophy.

For more information, visit trophy.so.