Cursor Pagination for Large, Changing Lists
· PoolByte Technology
In a management screen with thousands of records, the first page may be fast and the following pages may be slow. While new records are being added, users may see duplicate rows or miss some records. The pagination method is not only the design of the “next” button, but also the decision in which order and with what limit the results will be retrieved.
OFFSET is easy, but has its limit
The OFFSET approach skips a certain number of rows and requests the next section. Records skipped in deep pages may also need to be processed. The order of results should also be clear and decisive. PostgreSQL LIMIT and OFFSET document describes behavior regarding sorting and large OFFSET values.
In a screen with a small number of records and where going directly to the page number is important, OFFSET may be sufficient. In a transaction history where new records are constantly appearing, another approach may be more appropriate. Evaluate the actual usage pattern and query plan rather than modifying each list.
Cursor describes the next position
Cursor pagination continues from the last seen position. For example, the sort date and the unique ID together can form a continuation point. The second field prevents the order from becoming ambiguous when there are two records with equal dates. It is also important that the relevant index supports this query format.
The token that the client sees should not carry internal database design or confidential information. When the token is changed, there should be no access to another customer's records. Google API pagination guide gives useful guidelines on the opacity of page tokens and the parameters of subsequent requests. Authorization is done separately for each request.
When the filter changes, the old location does not remain valid
If the user switches to the “open requests only” filter, they should not use the continuation token of the previous list. Sorting direction, search phrase, and customer scope also affect the meaning of the continuation point. Associate the token with this context; Don't silently translate the mismatched request to the wrong list.
Rows may be deleted or the sorting field may change while the list is displayed. Using a cursor does not automatically freeze all concurrent changes. Determine whether the user needs a static snapshot of a report or an updated stream. Report export and on-screen navigation may not have the same expectation of consistency.
checklist
- Check for missing or double lines on multiple records with the same date.
- Adding new records and deleting existing records between pages.
- Resetting the position when changing the filter and sorting.
- Invalid, expired or out-of-scope token.
- Correct display of the empty result at the end of the list.
- Query time and number of rows read in large dataset.
Calculating the total number of records for each request may also create a separate cost. Question whether the interface actually requires an exact total count. A thoughtful pagination design combined with Index guide makes it easier to understand why the list experience changes as data grows.