Cutting 50 M‑record Deep Paging from 10 min to 1 s – 600× Faster with ES Search‑After & Redis

The WuKong activity system originally used MySQL multi‑table joins to list hundreds of thousands of contest entries, but growing data volume caused high SQL cost, longer query times, and pressure under high concurrency.

To alleviate database load, the team migrated the listing logic to Elasticsearch, leveraging its strength in large‑scale, multi‑dimensional filtering. However, Elasticsearch only supports sequential pagination via scroll API or search_after, not arbitrary page jumps.

Basic scheme selection : Three native deep‑paging methods were compared— from+size, scroll API, and search_after. The team chose search_after for its stateless nature and lower memory impact.

Stage 1 – Basic cache : Implemented a cache‑plus‑ search_after solution. An asynchronous thread pre‑warms data in 1,000‑record steps, storing the last document’s sortValues for each step in a Redis hash. Multiple granularities (page sizes 10, 20, 50, 100) are cached. When a user requests a page beyond Elasticsearch’s 10,000‑record depth, the system computes the start from, jumps to the nearest cached anchor, and proceeds with search_after until the target page is reached. This reduced cached‑hit queries to under one second.

Limitations observed: first‑time deep jumps still required many sequential search_after calls; cache hit rate dropped for random page accesses; a jump to the last page of 800 k records still took ~10 minutes during initial pre‑heat.

Stage 2 – Performance optimization : Added a “recent‑anchor” locator using a Redis ZSet where the score is the query position and the value is the sortValues. The system finds the nearest anchor, computes the offset to the target page, and performs a single additional search_after to land on the desired page, shrinking the scan interval to a few thousand records. Further, the query was tuned by disabling _source and turning off track_total_hits, cutting each query to ~500 ms. The pre‑heat step size was increased to 5,000 records, bringing the first‑time jump to under one minute.

Stage 3 – Fine‑grained anchor cache : To eliminate the remaining ~1 s sequential scan, the team introduced a two‑layer cache: a large‑interval pre‑heat (5,000 records) plus an asynchronous split of that result into 10‑record micro‑pages stored in a Redis ZSet. When a user jumps, the exact micro‑anchor is retrieved, allowing a single search_after to return the page. Tests on 700 k records showed random‑page response times consistently under 1 s.

Current issue – Data drift : Because cached anchors are generated from a snapshot of the index, subsequent inserts or deletions can cause anchors to point to stale positions, leading to empty pages or duplicate/missing data. The root cause is the lack of strong consistency between Elasticsearch and Redis.

Mitigation strategies : Treat the solution as suitable for relatively stable data; on empty‑page detection, clear the cache and re‑pre‑heat; for highly volatile datasets, consider timestamp‑ or version‑based incremental updates instead of static anchors.

The overall process demonstrates a systematic engineering approach: identify bottlenecks, evaluate alternatives, iteratively refine caching granularity, and balance performance gains against consistency risks.