# Making Forem’s cold analytics backfill 10× faster

> An open source Rails performance optimization that replaced per-article analytics recomputation with bulk SQL aggregation, cutting median backfill time by 90.1%.

- Canonical: https://haseebeqx.com/case-studies/forem-analytics-activity-bulk-backfill/
- Published: 2026-09-08


## At a glance

| | |
|---|---|
| **Problem** | A cold analytics dashboard recomputed missing activity data one article at a time |
| **Scope** | Forem’s `ArticleActivity` backfill path for page views, reactions, comments, and referrers |
| **Result** | 90.1% lower median backfill time, 92.7% fewer SQL queries, and 84.7% fewer allocations |
| **Delivery** | Regression-tested open source optimization merged upstream |

![Forem analytics activity backfill results at a glance: 90% lower median backfill time, 93% fewer SQL queries, 86% less SQL time, and 85% fewer allocated objects, with ten article backfills completed in six queries.](/images/case-studies/forem-analytics-backfill-focus-points.svg)

## The problem

Forem stores pre-aggregated `ArticleActivity` rows for its analytics dashboard. When those rows were missing, the backfill worker processed each article independently: it loaded the article, created its activity record, and separately recomputed page views, reactions, and comments.

That approach repeated the same categories of database work for every article in a batch. In the ten-article benchmark, the cold backfill executed 82 SQL queries, took a median 169.86 ms, and allocated 54,082 objects. The cost therefore grew with the number of uncached articles just when a dashboard needed to populate its cache.

## What I changed

I replaced the per-article recomputation loop with a set-based bulk backfill:

- the worker still divides input into bounded chunks of 100 and excludes activity rows that already exist;
- `ArticleActivity.bulk_backfill!` filters the requested IDs to existing articles and initializes one in-memory result per article;
- one grouped page-view query provides the per-article, per-day, and per-domain aggregates used to build view totals, logged-in views, reading time, and referrer data across the complete batch;
- one grouped reaction query provides the per-article and per-day aggregates used to build analytics-eligible reaction totals, per-category counts, and unique reactor IDs;
- one grouped comment query counts positively scored comments by article and day, with totals accumulated from those groups; and
- one `insert_all` writes all generated activity rows.

The bulk path preserves the existing activity data shape, including daily hashes and total counters. Its insert uses the unique article-activity index, so a row created concurrently after aggregation is not overwritten. Existing rows are skipped rather than recomputed.

I also made the analytics service materialize its article IDs once. Missing IDs are sorted before the Sidekiq job is enqueued, giving equivalent requests a canonical argument order while preserving the raw-table fallback for the current cold dashboard request.

## Measured result

I benchmarked ten articles, each with three days of data, nine page-view rows, six reactions, and three positively scored comments. The figures are medians from five measured runs after three warmups, with Rails’ SQL query cache disabled.

| Metric | Before median | After median | Change |
|---|---:|---:|---:|
| Cold backfill duration | 169.86 ms | 16.84 ms | **−90.1%** |
| SQL queries | 82 | 6 | **−92.7%** |
| SQL duration | 56.58 ms | 8.14 ms | **−85.6%** |
| Allocated objects | 54,082 | 8,270 | **−84.7%** |

The cold backfill became approximately 10× faster. The benchmark also measured the surrounding cold dashboard service path, which continued to return data through its existing fallback while scheduling the backfill:

| Metric | Before median | After median | Change |
|---|---:|---:|---:|
| Service duration | 11.18 ms | 10.52 ms | **−6.0%** |
| SQL queries | 8 | 7 | **−12.5%** |
| SQL duration | 5.94 ms | 5.50 ms | **−7.4%** |
| Allocated objects | 3,880 | 3,501 | **−9.8%** |

The direct worker and service calls ran in the test environment with Sidekiq in fake mode. Fixture setup, reset, and verification were excluded, and the measurements did not cover Redis uniqueness or concurrent job execution.

## Validation

The model spec builds two articles with different page-view, reaction, referrer, reading-time, and comment data. It asserts a bounded query count, checks every generated aggregate, and then compares the bulk result with the existing per-record `recompute_all!` implementation. A separate race-condition example confirms that a row created between aggregation and insertion retains its existing values.

Worker specs verify that multiple missing rows are sent through one bulk call and that existing rows remain untouched. Analytics service specs cover string article IDs and canonical ordering of IDs passed to the worker. The affected model, service, and worker specs accompanied the change, which was reviewed, approved, and merged into Forem.

[View the merged Forem pull request →](https://github.com/forem/forem/pull/23822)