The pattern
Page boundary math skips, repeats, or leaks records - usually an off-by-one in offset arithmetic, an unstable sort order, or a cursor that breaks when the underlying data changes between pages. The bug only shows at specific page sizes and record counts, which is why it survives casual testing.
( 01 )Symptoms
How this failure announces itself.
- warningThe final page is empty or missing the last few records.
- warningThe same record appears at the end of one page and the start of the next.
- warningThe total count and the sum of page lengths disagree.
- warningResults shift when new records are inserted while a user is paging.
( 02 )First moves
The first ten minutes — establish facts before touching code.
- 1Reproduce with a tiny dataset (say 7 records, page size 3) and write out which records each page should contain.
- 2Print the actual query - with real LIMIT/OFFSET or cursor values - for the failing page.
- 3Check the sort: is the ORDER BY unique, and does it match the cursor's resume key?
- 4Walk the boundary: request the last page, then one past it, and compare against your expectations.
( 03 )Where to look
The code and config that usually owns this bug.
- searchLimit and offset arithmetic - the page numbering origin (0 or 1) and every place that multiplies.
- searchSort stability - paging over a non-unique sort key without a tiebreaker reshuffles equal rows on every query.
- searchCursor encoding - does the cursor capture enough state (sort key plus id) to resume exactly where it left off?
- searchBoundary coverage - exact multiples of the page size, page size one, and the empty result.
( 04 )Common fixes
Fix the cause, then make the regression impossible.
- buildUse a stable total order - append a unique tiebreaker (id) to every paged ORDER BY.
- buildTest exact page boundaries: counts that are multiples of the page size, the final partial page, and empty results.
- buildPrefer cursor (keyset) pagination for data that changes while being paged.
- buildCentralize the page math in one function so the off-by-one can only exist in one place.
( 05 )Prove the fix
A fix you can't demonstrate is a guess. Close the loop.
- verifiedProperty check: the union of all pages equals the full set with no duplicates, across several page sizes.
- verifiedThe originally failing page-size and record-count combination now passes.
- verifiedInserting records mid-pagination no longer skips or repeats rows in cursor mode.