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

说明与用途

通过将 Dev.to 的分析回填从逐篇重算改为批量处理，显著降低了回填时间（90%）并减少了 SQL 查询和内存分配。

问题

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

结果

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.

工作流

• 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

证据

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

来源

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

来源