How I parsed billions of rows for every user in 2 seconds

A wrapped-style analytics feature that once took 10–20 minutes per user was brought down to under 10 seconds—and in some cases to a few hundred milliseconds—by changing how usage statistics are computed and cached. The core problem wasn’t the UI or even the analytics logic; it was the data plumbing. Convex powered the app backend, but it wasn’t built for scanning and aggregating billions of analytics rows per request. Post Hog was better suited for analytics, yet its ClickHouse-based query model and rate limits made repeated, heavy queries too slow and too error-prone when multiplied across thousands of users.

The initial architecture ran a Convex workflow that queued a “compute wrapped” job and then executed multiple Post Hog queries to assemble the stats shown in the wrapped experience: model usage counts, feature usage (edits, branches, retries), and time breakdowns (by day, weekday, and hour). Several steps required full scans and grouping—especially percentiles, which had to compare each user’s total usage against the distribution across all users. Early timing showed the first query step in hundreds of milliseconds, but model usage and feature usage ballooned into tens of seconds, and percentile calculations reached roughly 30 seconds. Worse, Post Hog rate limits (with concurrency and per-minute/hour caps) turned the situation into a cascading failure: the system attempted about 10 queries per user, and the queue quickly accumulated thousands of long-running generations.

The turnaround came from two tactics: reducing query pressure and shifting expensive computation into precomputed caches. First, the team added operational controls (feature flags, higher parallelism limits within reason, more logging, and fixing a default Post Hog date limit that capped results at 100 days). Then they rewrote queries to reduce the number of separate requests—combining logic into fewer SQL statements using unions to stay under rate limits.

The biggest leap, however, came from materialized views inside Post Hog. Instead of recomputing “usage by user” and “model usage by user” from raw event tables every time, the system precomputed subqueries into cached tables (effectively stored results that could be queried like normal tables). Once those materialized views existed, the wrapped queries became dramatically simpler: percentile calculations could be expressed as counts over cached per-user aggregates rather than repeated full-table scans. That change collapsed end-to-end runtimes from 6+ minutes to under a second for key steps, and production generations began completing in roughly 10 seconds on average.

From there, further refinements squeezed the remaining overhead: collapsing multiple actions into fewer Convex workflow steps, using Post Hog Endpoints (a beta feature) to expose dashboard-defined queries over HTTP for better caching behavior, and experimenting with “single-shot” query shapes that compute more at once. After these iterations, reruns sometimes landed under 200 milliseconds due to warm caching.

The practical takeaway is that analytics at wrapped scale demands precomputation and careful respect for query limits. The performance win wasn’t magic SQL—it was architectural alignment: compute once, cache aggressively, and design queries to avoid repeated scans of massive event histories.