List page 2 shows the last record from page 1 again—can frontend primary-key deduplication ship first?

Conclusion: When a list is paginated by creation time descending and page 2 shows the last record from page 1 again, the common root cause is not a miscalculated LIMIT or OFFSET. It is usually that the ORDER BY sort field has many duplicate values, and the database does not guarantee a stable order for rows with the same value. Frontend deduplication by primary key only hides duplicate display; it cannot hide missed records. Based on 2026 delivery experience, appending a unique primary key to the end of the sort is enough to stabilize most cases; for deep pagination or infinite scroll, consider cursor pagination; for export and reconciliation scenarios, use snapshot pagination. The applicable boundary is lists with more than 100,000 rows, many duplicate sort-field values, and continuous writes; internal lists with a few thousand rows or fewer and a unique sort key usually do not need this rework.

Pagination duplicates: first check whether the sort field has colliding values

A common pagination query is ORDER BY create_time DESC LIMIT 20 OFFSET 20. The database sorts by create_time first, then skips the first 20 rows and takes 20 rows. When multiple rows have exactly the same create_time, the database only guarantees that they are grouped together; it does not guarantee that the relative order among those equal-value rows will be consistent on every return. When you page to page 2, equal-value rows at the end of page 1 may be ordered to the beginning of page 2 again, causing duplicates; conversely, they may be skipped, causing missed records. This is less likely to surface with small data volume and few concurrent writes; it becomes more common with hundreds of thousands of rows and multiple records created in the same second or minute. When reviewing list APIs in 2026 projects, confirming whether the sort key is unique first saves more trouble than fixing it after launch.

How to verify: unstable sorting, genuinely duplicate data, or frontend cache?

Do not immediately add caching or switch solutions just because you see pagination duplicates. Run several low-cost checks first; the typical range to locate the issue is half an hour to half a day.

1. Run the same page SQL twice in a row: keep parameters unchanged and compare the returned primary-key sets; if the two results differ, unstable sorting is the likely suspect.
2. Check sort-field duplication: group and count fields such as create_time to see whether many identical values exist.
3. Temporarily append the primary key at the end: change to ORDER BY create_time DESC, id DESC, then page through ten pages in a row and compare.
4. Check the execution plan: confirm whether filesort appears and whether the index is suitable, then decide whether to change the SQL or the index.
5. Simulate concurrent writes: paginate while inserting, and observe whether duplicates and omissions cluster at page boundaries.

An acceptable criterion can be defined as: page through the same list for ten consecutive pages, and the primary-key set has no duplicates and no omissions; it is still reproducible after changing the sort direction. If results still do not match, then check duplicate data writes, frontend cache, or API cache.

Trade-offs among appending a unique column, cursor pagination, and snapshot pagination

The three approaches are not better simply because they are more complex. Based on common 2026 delivery habits, first look at the list use case: admin lists with page jumping, App infinite scroll, and export reconciliation correspond to different solutions. The cost figures below are all experience ranges and will vary with existing code and index conditions.

• Append a unique column: ORDER BY create_time DESC, id DESC. The change is small, with a typical implementation range of about 0.5–1 person-day; suitable for small-to-medium data volumes and admin lists that need page jumping. Limitation: large OFFSET deep pagination will still be slow, and at millions of rows it needs to be combined with other measures.
• Cursor pagination: use the create_time and id of the last record on the previous page as the starting point for the next page, for example WHERE (create_time, id) < (previous page value). Pagination performance is more stable, with a typical range of about 1–3 person-days; suitable for infinite scroll and timeline lists. Limitation: it cannot jump directly to page N, and the product side must accept previous-page or next-page navigation.
• Snapshot pagination: first write the set of matching ids to a temporary table or cache, then fetch data in batches by id. Results can be reconciled, with a typical range of about 3–5 person-days; suitable for one-off batch scenarios such as admin exports and reconciliation. Limitation: storage and invalidation logic are complex, so it is not suitable for high-frequency frontend lists.

Most business lists can mitigate duplicates by appending a unique column first; upgrade to cursors only when there are deep-pagination performance requirements. Do not force cursor pagination just to satisfy page-jumping needs, and do not rely on large OFFSET for exports long term.

Applicable and non-applicable boundaries

• Suitable for addressing: single-table pagination results above 100,000 rows; many duplicate sort-field values; continuous writes to the list; users have reported pagination duplicates or missing data; admin pagination requires stable reconciliation.
• No major change needed: data volume within a few thousand rows; the sort field itself is unique, such as an auto-increment primary key or unique order number; only the first few pages are queried and no deep pagination is done; internal temporary lists are not sensitive to duplicates.
• Not suitable to rely only on frontend deduplication: frontend deduplication can only hide duplicate display; it cannot prevent missed records, and pagination totals and statistics may still fail to match.
• Not suitable for snapshot pagination: for high-frequency frontend lists and scenarios with fast-changing data, temporary snapshot invalidation costs are high and may introduce new inconsistencies.

Delivery reality: frontend deduplication shipped first, why was there rework?

There was an admin order list project with tight budget and timeline. The team first had the frontend deduplicate by primary key and ship it, and the page 2 duplicates appeared to be gone. But when operations later exported data for reconciliation, some orders were still found missing at page boundaries; after data grew from tens of thousands to millions of rows, the list continued to use large OFFSET, and pagination also became slow. Later, id sorting was appended and the interface was changed to cursor-based, costing about 1–3 person-days in total. The experience range is: reserve half a day to a day before launch to verify sort-key uniqueness and page boundaries; it is usually more cost-effective than fixing afterward.

FAQ

If the sort field has duplicate values, can adding id sorting alone solve pagination duplicates?

It can significantly mitigate the issue for most lists. Appending a unique primary key at the end of ORDER BY while keeping direction consistent gives equal-value rows a deterministic order, and the cost is low; consider cursor pagination only when data volume is very large.

If there are only a few thousand rows, can page 2 still repeat?

It is possible, but the probability is usually low. With small data volume and few writes, the execution plan is relatively stable; if the sort field has many duplicates and there are concurrent writes, it is still advisable to append a unique column, and the verification cost is not high.

Can cursor pagination be made to jump to page 10?

It is not directly supported. Cursor pagination usually provides only previous-page and next-page anchors; page jumping requires extra page-boundary maintenance or a switch to snapshots, and the product side needs to confirm this in advance.

After appending a unique sort, pagination still occasionally duplicates—what should I do?

First check whether frontend cache or API cache is returning an old page, then verify whether the sort direction is consistent and whether the composite index is taking effect; unique sorting solves database-layer instability, not dirty data at the cache layer.

For missing data in admin export reconciliation, which solution should be chosen?

Export scenarios are usually more suitable for snapshot pagination: first fix the set of matching ids, then fetch in batches to avoid data changes during export; the cost is higher than appending sorting, but the results are easier to reconcile.

The conclusion can be brought back to list API reviews: first confirm whether the sort key is unique, then decide whether to append a unique column, use cursor pagination, or use snapshot pagination. The applicable boundary is lists with more than 100,000 rows, many duplicate sort-field values, and continuous writes; internal lists within a few thousand rows with a unique sort key do not need major changes for this. Before delivery, reserve half a day to a day according to the typical range for pagination verification; it is usually more cost-effective than post-launch rework.