How to Handle Pagination Across Sharded Databases Without Performance Pitfalls

1. Not Every Scenario Needs a Pagination Solution

Pagination only becomes complex when queries span multiple shards or tables.

Example: If the query always includes the shard key (e.g., user ID or time slice), it targets a single shard, just like a single‑table query, so no special pagination difficulty arises.

Therefore the first step is to ask: Is this query cross‑shard?

2. Three Main Solutions

When true cross‑shard pagination is required, common approaches are:

Solution 1: Dedicated Middleware/Database (e.g., Elasticsearch, TiDB)

Core idea: Sync sharded data to a system better suited for complex queries and let it handle pagination.

Advantages: High performance, widely used in industry.

Disadvantages: Must ensure data synchronization consistency (e.g., MySQL → ES).

Solution 2: Open‑source Framework (e.g., ShardingSphere)

Core idea: The application still writes “single‑table SQL”; the framework splits the SQL, fetches data from each shard, then sorts and paginates in memory.

Advantages: Almost zero changes to the application, good developer experience.

Disadvantages: Poor performance for deep pagination.

Solution 3: Business Compromise

Core idea: Work with product managers to limit query scope, avoiding cross‑shard pagination (e.g., query only the current shard or show “partial results”).

Advantages: Simplest, directly sidesteps the problem.

Disadvantages: May affect user experience.

3. The Truth About the “Global Search” Method

This method is often asked about in interviews. It works but has serious issues for deep pages.

Problem

Each shard may need to scan hundreds of thousands or millions of rows.

Sorting and trimming must happen in memory.

Easy OOM and extremely slow performance.

Thus the method is acceptable for shallow pages but fails for deep pagination.

4. Optimization: No Jump Paging, Sequential Paging Only

Best practice for deep pagination is to page based on the maximum ID of the previous page.

Implementation

Page 1: SELECT * FROM table ORDER BY id LIMIT size; Page 2 (and subsequent pages):

SELECT * FROM table WHERE id > {last_page_max_id} ORDER BY id LIMIT size;

Advantages

Only one page is queried each time, delivering excellent performance.

Independent of OFFSET size, safe for deep pagination.

Disadvantages

Cannot jump directly to an arbitrary page number.

Must scroll page by page, similar to infinite feeds.

For most scenarios such as news feeds or message lists, this is sufficient.

5. Not Recommended: Secondary Query Method

In short: Do not use it.

6. How to Answer in an Interview (STAR Method)

S (Situation): After sharding, cross‑shard pagination becomes a challenge.

T (Task): Need a solution that guarantees correct global pagination.

A (Action):

First evaluate whether the business can limit the query range to avoid the problem.

If it must be solved, prefer dedicated systems like Elasticsearch or TiDB.

If it must stay in the database, use ShardingSphere’s global search but mention its deep‑page limitation.

For deep pagination, propose the “max‑ID sequential paging” optimization.

R (Result): The approach satisfies business needs while keeping performance stable.

Summary

Only cross‑shard queries need special pagination handling. Common solutions are:

Middleware/Database (recommended).

Open‑source framework (convenient but has performance pitfalls).

Business compromise (directly avoids the issue).

The global search method works for shallow pages but breaks on deep pages.

Best practice: prohibit jump paging and use sequential paging based on the previous page’s maximum ID.

If you can avoid the problem, avoid it; if you can hand it to a dedicated system, do so; otherwise use continuous paging.