Pagination looks like a front-end problem until a page deep in a list takes four seconds, or a user scrolls through a feed and sees the same item twice while another one never appears. Both are database problems, and both come from paging with skip over a sort that isn’t unique. This is a favourite senior interview topic because the fix is simple to state and easy to get subtly wrong.
The examples are mongosh for MongoDB 7 and 8 and are illustrative; no server was available while writing.
Offset pagination and its two problems
The obvious approach:
const pageSize = 20
db.posts.find({ status: "published" })
.sort({ publishedAt: -1 })
.skip((page - 1) * pageSize)
.limit(pageSize)Problem one: cost grows with depth. skip doesn’t jump. The server walks the index (or the sorted results) and discards the first n entries. Page 1 reads 20 keys. Page 5,000 reads 100,000 keys and throws away 99,980. Explain shows it plainly: totalKeysExamined grows linearly with the page number while nReturned stays at 20. Deep pages also tend to be the ones crawlers and scrapers hit, so they’re not as rare as you’d think.
Problem two: the window moves. Between a user loading page 1 and page 2, a new post is published. Every existing post shifts down one position. The last post on page 1 is now the first post on page 2, so the user sees it twice. Delete a post instead and one item shifts up and is never shown. Offsets describe positions, and positions change whenever the data does.
Ties make the order undefined
There’s a third, quieter problem. If two posts have the same publishedAt (bulk imports, second-precision timestamps, a scheduled batch), the sort doesn’t say which comes first. MongoDB doesn’t promise the same order across queries for tied documents. One request might put post A at position 20 and B at 21, and the next request reverses them. With offset pagination, A appears on both pages and B on neither.
The fix is a unique tie-breaker in every sort used for pagination. _id is the natural choice because it’s unique and always indexed:
.sort({ publishedAt: -1, _id: -1 })Now the order is total: every pair of documents has a defined order.
Range (keyset) pagination
Instead of “skip 20 and give me 20”, ask “give me 20 that come after the last one I saw”. The client sends back the sort values of the last item it received:
// first page
const page = db.posts.find({ status: "published" })
.sort({ publishedAt: -1, _id: -1 })
.limit(20).toArray()
const last = page[page.length - 1]
const cursor = { publishedAt: last.publishedAt, _id: last._id } // send to client, e.g. base64 JSON// next page
db.posts.find({
status: "published",
$or: [
{ publishedAt: { $lt: cursor.publishedAt } },
{ publishedAt: cursor.publishedAt, _id: { $lt: cursor._id } }
]
}).sort({ publishedAt: -1, _id: -1 }).limit(20)The $or reads as: strictly older posts, or posts at exactly the same time with a smaller _id. That’s the “comes after in this sort order” condition for a two-field descending sort. Getting the comparison operators wrong ($lte instead of $lt, or forgetting the tie branch) is the classic mistake: $lte on publishedAt alone repeats tied items, and $lt alone skips them.
With the right index, each page costs about the same as the first:
db.posts.createIndex({ status: 1, publishedAt: -1, _id: -1 })The equality on status comes first, then the sort fields in sort order. The planner turns the $or into index bounds and reads roughly 20 keys per page regardless of depth.
Inserts and deletes no longer cause duplicates or gaps in the pages the user hasn’t seen yet, because the next page is defined by a value, not a position. A new post appears at the top of the feed (before the cursor), and doesn’t shift what comes next.
What you give up
Keyset pagination isn’t free:
- No jumping to page 347. You can go next (and previous, by reversing the comparison and sort, then reversing the results), but not to an arbitrary page number. For an infinite-scroll feed, that’s fine. For an admin table where people genuinely jump to page 40, offset paging on a narrow filtered set may still be acceptable.
- The sort must be fixed per cursor. If the user changes the sort column, the cursor from the old sort is meaningless. Encode the sort in the cursor token and reject mismatches.
- Total counts are separate. “Page 3 of 812” needs a
countDocuments, which on large collections is expensive. Many products show “more” instead, or useestimatedDocumentCount()for the unfiltered total.
Treat the cursor as opaque to the client: base64-encode the JSON and, if users shouldn’t tamper with it, sign it. Validate it on the way back in, since it’s user input that ends up in a query.
Database cursors are a different thing
The word “cursor” also means the server-side cursor a find returns, which the driver iterates in batches with getMore. Those time out after 10 minutes of inactivity by default and aren’t meant to be held open while a user reads a page. Don’t build web pagination by keeping a server cursor alive between HTTP requests. Use keyset tokens; they’re stateless and survive a restart or failover.
Interview practice
An activity feed uses sort({ createdAt: -1 }).skip(n).limit(25). Users report duplicates when scrolling quickly during busy periods, and deep scrolls are slow. A teammate proposes switching to find({ createdAt: { $lt: lastCreatedAt } }).sort({ createdAt: -1 }).limit(25). Is that enough?
Answer
It fixes the cost problem and the shifting-window problem, because each page is now anchored to a value instead of an offset, and with an index on createdAt each page reads about 25 keys. But it isn’t enough, because createdAt isn’t unique. During busy periods many activities share the same timestamp (especially at millisecond or second precision). If the page boundary falls in the middle of a group of tied items, $lt: lastCreatedAt skips the rest of that group entirely. Users will now miss items instead of seeing duplicates.
The fix is to add _id as a tie-breaker to the sort, the cursor and the index:
find({ $or: [
{ createdAt: { $lt: c.createdAt } },
{ createdAt: c.createdAt, _id: { $lt: c._id } }
] }).sort({ createdAt: -1, _id: -1 }).limit(25)with an index { createdAt: -1, _id: -1 } (prefixed by any equality filters such as userId). I’d then test the boundary explicitly: insert 60 documents with identical createdAt, page through with a size of 25, and assert that the union of pages has 60 distinct ids. That test catches both the duplicate and the gap bug.