performance - MongoDB ranged pagination

Question

It's said that using skip() for pagination in MongoDB collection with many records is slow and not recommended.

Ranged pagination (based on >_id comparsion) could be used

db.items.find({_id: {$gt: ObjectId('4f4a3ba2751e88780b000000')}});

It's good for displaying prev. & next buttons - but it's not very easy to implement when you want to display actual page numbers 1 ... 5 6 7 ... 124 - you need to pre-calculate from which "_id" each page starts.

So I have two questions:

1) When should I start worry about that? When there're "too many records" with noticeable slowdown for skip()? 1 000? 1 000 000?

2) What is the best approach to show links with actual page numbers when using ranged pagination?

Answer 1

Good question!

"How many is too many?" - that, of course, depends on your data size and performance requirements. I, personally, feel uncomfortable when I skip more than 500-1000 records.

The actual answer depends on your requirements. Here's what modern sites do (or, at least, some of them).

First, navbar looks like this:

1 2 3 ... 457

They get final page number from total record count and page size. Let's jump to page 3. That will involve some skipping from the first record. When results arrive, you know id of first record on page 3.

1 2 3 4 5 ... 457

Let's skip some more and go to page 5.

1 ... 3 4 5 6 7 ... 457

You get the idea. At each point you see first, last and current pages, and also two pages forward and backward from the current page.

Queries

var current_id; // id of first record on current page.

// go to page current+N
db.collection.find({_id: {$gte: current_id}}).
              skip(N * page_size).
              limit(page_size).
              sort({_id: 1});

// go to page current-N
// note that due to the nature of skipping back,
// this query will get you records in reverse order 
// (last records on the page being first in the resultset)
// You should reverse them in the app.
db.collection.find({_id: {$lt: current_id}}).
              skip((N-1)*page_size).
              limit(page_size).
              sort({_id: -1});