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

Описание и применение

Оптимизация процесса заполнения аналитики Dev.to за счёт пакетной обработки вместо обработки каждой статьи отдельно, что привело к снижению времени выполнения на 90 % и уменьшению количества запросов и выделяемой памяти.

Задача

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

Источник