Order list spins when scrolled far down, and the larger the offset the slower it gets—can I just reduce the page size?

An order list getting slower as you scroll further down is usually not because the page size is too large, but because limit offset must first scan a large number of preceding rows and then discard them; the larger the offset, the slower it gets. Based on 2026 project delivery experience, for lists such as order transactions and operation logs that mainly rely on continuous downward paging, a common approach is to switch to cursor pagination based on an ordered and non-repeating field; however, report APIs that require arbitrary page jumps and exact total pages are not suitable for a hard change, otherwise the problem will surface in the front end or in the statistical definition.

Why a larger offset gets slower, and why reducing page size does not help

Look at the semantics first. limit 100000, 20 does not jump directly to row 100000; it starts counting from the first matching row and only after counting to row 100000 does it take 20 rows. Even if the query uses an index, the database still has to walk a large number of rows along the index and then discard the preceding rows. The cost follows the offset size, not the page size.

A common misconception is that “changing pageSize from 50 to 20 will make the API fast.” Page size only affects the final return and network transfer; the number of rows scanned and discarded before that is almost unchanged. Experience range: with tens of thousands of rows and within dozens of pages, the difference may not be obvious; at hundreds of thousands of rows and with offset reaching tens of thousands or more, a single query rising from tens of milliseconds to hundreds of milliseconds or more is a common phenomenon, and the specifics depend on hardware, index coverage, filter conditions, and sorting method.

• The time to reach page N generally grows with N, not with pageSize.
• When the sort field has no suitable index, the database also has to sort additionally, making deep pagination more obvious.
• When the list has filter conditions such as status or tenant_id, the number of table lookups and scanned rows adds up.

In deep pagination scenarios, offset is a cost variable, not an ordinary pagination parameter; reducing the page size changes only the last small segment, not the large scan before it.

What is the difference between cursor pagination and limit offset?

Cursor pagination does not count preceding rows; it uses the ordered key value of the last record on the previous page as the starting point. For example, when sorting by ID descending, write where id < the previous page's last id order by id desc limit 20. The database can use the index to locate the starting point directly and then fetch one page forward; pagination depth usually has much less impact on single-query time.

• Locating method: offset counts and discards rows; a cursor locates directly by ordered key.
• Effect of pagination depth: offset grows with page number; a cursor is basically stable when sorting is stable.
• Page jump capability: offset supports jumping to any page number; a cursor usually only supports previous page or next page.
• Sort field requirements: offset has low requirements for the sort field; a cursor must be ordered and guaranteed non-repeating.
• Total count: offset easily pairs with count; a cursor requires a separate total count or only whether there is a next page.
• Applicable targets: offset suits admin reports and lists that need page numbers; a cursor suits order transactions, message streams, and log streams.

Cursor pagination is not a more advanced pagination; it simply replaces pagination by position with pagination by value, at the cost of losing page jumps and exact page numbers.

Which order lists are suitable for cursor pagination, and which are not

The criterion is not whether the API is slow, but whether the business needs arbitrary page jumps. If operations mainly look continuously at recent orders and rarely need to jump to page 50, deep pagination is a cost that can be cut. Conversely, if a report page must jump directly and also show how many pages there are in total, cursor pagination pushes these requirements to another set of logic and is not necessarily worthwhile.

Suitable cases:

• Transaction lists sorted by creation time or ID descending, where users page continuously downward.
• Single-page data volume is relatively large, and pagination depth often exceeds dozens of pages.
• The sort field is non-repeating, or a combination of “time + ID” can guarantee non-repetition.
• The front end uses scroll loading or load more and does not rely on page number jumps.

Unsuitable cases:

• Operations reports that need to jump directly to a specified page number.
• Scenarios that must display an exact total page count or total record count.
• The sort field can be switched arbitrarily, and a stable non-repeating key cannot be found.
• The data volume is very small and pagination is shallow; when the experience range is within ten thousand, the benefit of refactoring is not obvious.

If the business does not need page jumps, deep pagination is a cost that can be cut; if it does need page jumps, a hard change to cursors often just moves the problem to the front end or the statistical definition.

What fields and definitions should be checked before implementing cursor pagination?

Cursor pagination does not require much code; the hard part is field selection and the API contract. Based on delivery habits, you can first run a four-step checklist to block missing records, duplicate records, and compatibility issues in advance.

1. Determine an ordered non-repeating key: a single auto-increment ID is easier; if sorting by creation time, use a (create_time, id) combination to avoid skipping or duplicating records with the same timestamp.
2. Clarify cursor passing: the API returns next_cursor and has_more, and the front end passes them back as-is; do not let the front end assemble page numbers or add/subtract on its own.
3. Handle boundaries: the first page does not pass a cursor; the last page returns has_more=false; when paging backward, reverse the comparison operator.
4. Keep compatibility: keep the old offset API running in parallel for a period, let new and old clients integrate separately, and take it offline after coverage rises.

The reason for this division is: the ordered key determines no missing or duplicate records, cursor passing determines consistent understanding between front end and back end, boundaries determine the last-page experience, and compatibility determines release risk. Sorting and index behavior can be checked against the official documentation of the database you use; do not assume based on feeling.

Delivery scenario: constraints and costs of changing only one transaction list

A common delivery constraint is “the budget and timeline are only enough to change one list, and operations also require continuous reverse-chronological paging of orders from the last three months.” In 2026, a common approach is to change only the order transaction API first and keep the original offset API for the export page that needs page jumps.

Checkable comparison (experience range, not a fixed quote): only adding indexes or trimming fields, typical effort is about half a day to 1 person-day, helpful for shallow pagination and with filter conditions, but limited benefit for deep pagination; refactoring to cursor pagination, typical effort is about 1 to 2 person-days of development and half a day of integration testing, continuous paging time is more stable, but has_more and the cursor contract must be added; splitting the export pipeline separately, typical effort is about 2 to 3 person-days, which can reduce API memory and timeout pressure, but the cycle is longer. The cost is that the export page or page-jump report is still slow and needs to be split later.

The acceptance criterion for cursor pagination is not “the API does not time out,” but “paging to any depth has stable time, no missing records, no duplicate records, and old clients do not error.”

Frequently asked questions

Can reducing only the page size solve slow deep pagination?

Usually not. Page size only affects the final return and network transfer; the number of rows that offset must scan and discard before it is unchanged, so the deep pagination cost remains.

Does cursor pagination always have to use an auto-increment ID?

No. An auto-increment ID is just an easier ordered non-repeating key; a combination of creation time plus ID also works. The key is that the sort result is stable and non-repeating.

After switching to cursor pagination, can it still show the total page count?

An exact total page count requires a separate count or approximate statistic; in deep pagination scenarios, it is recommended to show only “load more” and not force a total page count.

If the data volume is not large, does it still need cursor pagination?

Not necessarily. When the experience range is within ten thousand and pagination depth is shallow, limit offset is usually enough, and refactoring instead adds integration cost.

What if old clients still use the page parameter?

You can keep the old offset API for a period and provide the new API in parallel, then take the old one offline after client coverage rises, avoiding direct switching that causes errors in old versions.

Action guidance: First pull the offset distribution and P95 latency of online deep pagination requests. If most requests are concentrated on the first few pages, prioritize indexing and field trimming; only when pagination depth stably exceeds dozens of pages and the business does not need page jumps should you refactor to cursor pagination. Applicable boundary: report APIs that require exact page numbers, arbitrary page jumps, or unstable sorting are not recommended for forced refactoring; you can continue using offset and optimize the export pipeline separately. Inapplicable boundary: when the sort key repeats, the data volume is small and pagination is shallow, or the API contract cannot temporarily add cursor fields, do not force a change first.