Pagination bugs are some of the hardest to catch in code review, because the code looks correct. It passes tests. It works perfectly in staging, where the data is static and nobody is writing to the table while QA clicks through pages. Then it ships, and somewhere in production a support agent notices a customer record that keeps disappearing from page 2 and reappearing on page 3 an hour later. Nobody touched the record. The table just kept growing while someone was scrolling through it.
The mental model that causes the bug
Most developers think of pagination as slicing a fixed list: rows 1 through 25, then 26 through 50, and so on, the same way you'd paginate a static array in memory. That model is accurate for an array that isn't changing. It's inaccurate for a database table under active writes, because the "list" isn't fixed at all. New rows are being inserted, and sometimes deleted, between the moment a client requests page 1 and the moment it requests page 2, and the pagination code has no idea any of that happened.
A concrete walkthrough
Say a table is sorted by created_at DESC, newest first, and a client is paging through it with OFFSET/LIMIT. They fetch page 1: rows 1 to 25, the 25 most recent records at that moment. Before they request page 2, three new rows get inserted somewhere else in the system, a normal amount of write traffic for any active application. Those three new rows are now the newest records, which pushes everything else down by three positions in the sort order.
When the client requests page 2 with OFFSET 25 LIMIT 25, the database doesn't know or care that three new rows showed up in the meantime. It just skips the first 25 rows of the current result set and returns the next 25. But the current result set has shifted underneath the client. The three rows that used to sit at positions 23, 24, and 25 (visible on page 1) are now at positions 26, 27, and 28, which means they get skipped entirely and never shown to the client on either page. Three real records silently vanish from the user's view, and there's no error, no warning, nothing in the logs to flag it. The pagination "worked" exactly as designed. It just wasn't designed for a moving target, and that distinction matters enormously once real traffic hits it.
Why this is worse than it sounds
This isn't a rare edge case that only matters at extreme scale. Any table with regular writes, orders, support tickets, activity logs, comments, notifications, is affected the moment two users are browsing the same paginated list at the same time as writes are happening anywhere in that table. On a high-traffic application, that's not an edge case at all. That's every page load, every single day, quietly producing slightly wrong results that almost nobody notices until a customer complains about a specific missing record.
The fix: stop asking "skip N," start asking "after this point"
Cursor-based (also called keyset) pagination replaces the offset with a pointer to a specific row, built from the sort column plus a unique tiebreaker like the primary key:
SELECT * FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 25;
This query doesn't care how many rows exist before the cursor position. It doesn't skip anything at all. It seeks directly to the row identified by the cursor and reads forward. New inserts elsewhere in the table don't shift this query's results, because the query isn't defined in terms of position, it's defined in terms of a specific row's sort key, which stays valid no matter how many other rows get added or removed around it. The PostgreSQL project's official site documents how this seek pattern uses a composite index far more efficiently than an offset-based scan, if you want to verify the mechanism yourself with EXPLAIN ANALYZE against your own schema.
The tiebreaker is not optional
A cursor built from created_at alone breaks the instant two rows share a timestamp, which happens constantly at millisecond precision under real concurrency, especially on bulk-import or batch-write workloads where many rows land in the same transaction. Without a unique tiebreaker, ties get resolved arbitrarily by the database's internal storage order, which reintroduces the exact skip-or-duplicate bug cursor pagination is supposed to fix in the first place. Always pair the sort column with a unique column, almost always the primary key, so the ordering is strictly deterministic regardless of how many rows share the same timestamp.
Testing for this specific failure mode
Static test fixtures won't catch this, because the bug only exists under concurrent writes. Write a test that inserts new rows between page requests and asserts every row from page 1 and page 2 combined is unique, no repeats, no gaps compared to what was actually in the table. That's the one test that actually exercises the bug this whole class of pagination problem produces, and it's cheap to write once you know what you're testing for. Tools like pytest make this straightforward to set up as a repeatable integration test against a real database instance rather than a mock, and for JavaScript backends, Jest covers the same kind of setup against a real test database.
A variant of the same bug: deletions instead of insertions
Everything above focuses on insertions shifting the result set, but deletions cause a mirror-image version of the same problem. If a row gets deleted from earlier in the sort order while a client is mid-pagination, offset pagination will skip one row too many on the next request, since the position it's counting from has shifted backward instead of forward. The client silently misses whatever row would have been at that boundary. This is common on tables where rows get soft-deleted or archived out of the default query scope while users are actively browsing, like a task list where completed items get filtered out as they're marked done in real time by other users.
Cursor pagination handles this case the same way it handles insertions: because the cursor identifies a specific row's sort key rather than a numeric position, a deletion elsewhere in the table simply doesn't affect where the next query resumes from.
The broader lesson
This bug is a good example of why "it passed all the tests" and "it's correct" are not the same claim. The tests that existed were testing the wrong thing: static correctness on a snapshot, not correctness under the conditions the code will actually run in. Any pagination implementation destined for a table with regular writes needs a test that simulates those writes happening mid-pagination, or the bug simply won't surface until a real user hits it in production.
We cover the full implementation, including cursor encoding, backward pagination, and the index you need to make it fast, in a longer guide over on 137foundry.com. Worth a read before your next list endpoint ships against a table that's going to keep growing.
A checklist before you ship any paginated endpoint
Before merging a new list endpoint, it's worth running through a short checklist rather than trusting that "it returned the right rows in my manual test" is sufficient proof of correctness. Confirm the sort clause includes a unique tiebreaker alongside the primary sort column, not just the primary sort column alone. Confirm there's a composite index backing that exact sort order, verified with an actual query plan rather than assumed from the schema. Confirm a test exists that inserts or deletes rows mid-pagination and checks for duplicates or gaps across page boundaries. And confirm the API documentation, whether internal or public, states plainly whether the endpoint uses offset or cursor pagination, since client code that assumes the wrong one will misbehave in ways that are hard to trace back to the actual cause.
None of these four checks take more than a few minutes once you know to look for them, and together they cover almost every version of this bug we've seen show up in real applications. The alternative, discovering the problem after a customer reports vanishing data, costs a lot more than a few minutes, both in engineering time spent tracing the cause and in the trust cost of a customer wondering what else might be wrong with a system they're relying on. Building the check into a pull request template or a pre-merge checklist is a cheap way to make sure it doesn't depend on any one engineer remembering to think about it every time on every new endpoint.
Top comments (0)