How I made Dev.to’s cold analytics backfill 10× faster

Description and use

Optimized Dev.to’s analytics backfill by replacing per‑article recomputation with a set‑based bulk approach, cutting backfill time by 90 % and reducing SQL queries and allocations dramatically.

Problem

Forem’s ArticleActivity backfill recomputed missing activity data one article at a time, causing many SQL queries and high latency.

Outcome

The bulk backfill reduced median backfill time by 90.1 %, SQL queries by 92.7 %, and object allocations by 84.7 % for ten articles, achieving roughly 10× speedup.

Workflow

• Divide input into chunks of 100 and exclude existing rows
• Filter requested IDs to existing articles and init in‑memory results
• Run one grouped page‑view query for per‑article, per‑day, per‑domain aggregates
• Run one grouped reaction query for per‑article and per‑day aggregates
• Run one grouped comment query for positively scored comments
• Insert all generated activity rows with insert_all
• Materialize article IDs once in analytics service and sort before enqueuing

Evidence

The bulk path preserves the existing activity data shape, including daily hashes and total counters.

Source

90.1% lower median backfill time, 92.7% fewer SQL queries, and 84.7% fewer allocations

Source