Boost Weekly Report Service Performance: Indexing, Sharding, and Caching Strategies

Background

Recently assigned a requirement that adds tens of millions of new records each week, projected to exceed 100 million rows in two months, prompting a performance‑optimization case study.

Content

Requirement Overview

The service generates weekly reports for users, delivering usage duration and last active time. The backend only needs an API that queries each user's weekly data.

With tens of millions of users, about 10 million of them need weekly statistics, meaning roughly 10 million new rows per week and over 100 million rows in two months.

Problem Description

The service stores data in a single database table. As data grows, naïve queries cause full‑table scans, leading to unacceptable latency and inability to handle request volume.

Optimization 1: Add Indexes

Creating a composite index on user_id and report_week reduces query time from over 10 seconds to under 10 milliseconds on a 10 million‑row test set.

Optimization 2: Table Partitioning (Sharding)

Since data is added weekly, partition tables by week (e.g., t_weekly_info_20210705). Queries target the specific weekly table, keeping each table’s size manageable and allowing the index to drop the week field, leaving only user_id.

Optimization 3: Add Cache

Use Redis to cache weekly report data. On a request, first check Redis; if a miss occurs, query the database, then populate Redis. Cache empty results for non‑existent users, but avoid caching when the miss is due to transient errors. Set a TTL of about one day, matching typical weekly‑report access patterns.

Optimization 4: Data Pre‑warming

Pre‑warm the cache by asynchronously loading recent weeks’ data into Redis, updating it periodically. Because older weeks are rarely accessed, cache only the most recent one or two weeks, which requires less than 700 MB per week (≈70 bytes per record), fitting comfortably in a 4 GB Redis instance.

Summary

The four simple techniques—indexing, sharding, caching, and data pre‑warming—significantly improve service performance by minimizing database queries and reducing the amount of data scanned per request.