Mastering Pagination in Sharded Databases: Global Query and No‑Skip Strategies

As the Internet expands rapidly, large companies experience explosive growth in business data, putting increasing pressure on databases; tables often contain billions of rows, such as order or logistics data. To handle these massive data volumes, a common solution is sharding (splitting data across multiple databases or tables) to improve read/write performance.

While sharding solves the problem of oversized tables, it introduces new challenges, notably pagination queries across distributed data. The following analysis explores pagination under horizontal sharding and proposes solutions.

1. Root Cause of Pagination Issues Under Sharding

Assume orders are distributed to two databases (database_0 and database_1) using an order_id modulo strategy, as illustrated below:

After sharding, each database loses a global view of the data. In a single‑table scenario, fetching the second page (3 rows per page) is straightforward:

select * from order order by create_time asc limit 3,3

However, after sharding, rows with IDs 4 and 5 reside in different databases. Executing the same SQL on each shard returns no results for the second page, exposing the pagination problem.

2. Global Query Method

To address pagination in a sharded environment, the global query method retrieves a larger data set from each shard and then performs in‑memory sorting to obtain a global view.

Scenarios:

Even distribution: Data is uniformly split between the two databases; each shard returns half of the required page, and the combined result yields the correct page.

All results in one shard: Data is unevenly distributed; the needed rows are all in a single database, requiring that shard to return more rows.

Results span both shards: Data is relatively balanced; each shard contributes part of the page.

In all cases, the service layer requests two pages from each shard, merges the results, sorts them globally, and then extracts the desired page.

Advantages: Provides a complete, accurate view of data across shards without loss.

Disadvantages: Increases network traffic because each shard returns more rows; the service layer must perform additional sorting, and deep pagination suffers from performance degradation.

3. Optimizing the Global Query – No‑Skip (Prohibit Jump) Method

Although the global query method ensures data integrity, its performance degrades with deeper pages. Many products avoid offering a direct "jump to page" feature, providing only "next page" instead, which simplifies the implementation.

Applying this idea, the no‑skip method works as follows: the first request fetches the first page from each shard and records the maximum ID returned. Subsequent pages include this maximum ID as a lower bound, effectively preventing jumps and reducing the amount of data each shard must scan.

This approach can significantly improve the efficiency of global pagination.

Summary

1) The global query method enables lossless, accurate pagination across sharded databases but suffers from deep‑page performance issues.

2) When business requirements allow, the no‑skip method can optimize the global query by eliminating page jumps.

3) Middleware such as Elasticsearch can be used for additional capabilities, though its own deep‑pagination and data‑sync challenges must be considered.

4) Tools like ShardingSphere provide built-in pagination optimizations (e.g., automatic SQL rewriting, global query, streaming processing).

5) If feasible, limiting queries to "partial data" (showing only a subset) can further reduce load.