Pagination in MongoDB: Right Way to Do it VS Common Mistakes

Working with Data
, ,
1

Hello fellow community members! :smiley:

I’m excited to share my latest article: Pagination in MongoDB: The Only Right Way to Implement it (Avoid Common Mistakes).

I noticed that many articles, tutorials, and courses do not implement pagination correctly, leading to issues such as data inconsistency and decreased performance. So, in this article I showed how to implement the pagination correctly in MongoDB with the use of Aggregation Framework, as well as how to avoid common mistakes.

I’ve put a lot of effort into creating and I would be thrilled if you could take a look and provide me with any feedback you have. Your input would be greatly appreciated as I am just starting my blog.

Thank you for your time and support!

2 Likes
2

Thanks for sharing.

An alternative way of paging is described at MongoDB Pagination, Fast & Consistent | by Mosius | The Startup | Medium.

1 Like
3

Hey @steevej,

Thanks for sharing that article. I just checked it. :smiley:

I personally don’t like 2 things from that approach:

  1. You can only fetch the next batch based on current batch, which means you have to start from the first page in order to get the second, and so on. If you want to jump directly to page 5 for example, you can not do it.
  2. Frontend app will not get the total number of items, which is really important for UX in many applications.

I would say that article covers only specific use-case, and not the pagination in general. But it’s definitely a nice solution for the use-case it covers. :smiley:

1 Like
4

Thanks.

Your 2 points are valid and something that needs to be considered.

1 Like
5

Hello can some one help how to do pagination in mongodb .

I used facet but it’s take too much time

6

@Anjana_Varandani Hi,

Can you share your model and your aggregate pipeline?

7

[
{
“$match”: {
“customerStatus”: {
“$nin”: [
“Not Prospective”,
“Not Interested”
]
},
“ownerId”: {
“$in”: [
ObjectId(“63c63c5d04f654e24dbec031”),

    ]
  }
}

},
{
“$sort”: {
“name”: 1
}
},
{
“$facet”: {
“customers”: [
{
“$skip”: 0
},
{
“$limit”: 10
}
],
“totalCount”: [
{
“$count”: “count”
}
]
}
},
{
“$project”: {
“customers”: 1,
“totalCount”: {
“$arrayElemAt”: [
“$totalCount.count”,
0
]
}
}
}
]

8

@Anjana_Varandani

You should create indexes for the following fields:

  • customerStatus
  • ownerId
  • name

You can even consider creating one compound index.

9

still take time we have 1.6M Records

11

@NeNaD , when we do skip , it actually scans all the docs till it actually starts returning docs, it is bad to perform such actions on large collections

1 Like
12

Hi Nenad! I have a participateTransaction collection. There is campaign participation transactions with given voucher codes. There is 22 milion users in the platform. The total count of records in the collection increased to 67 milion in one year. In admin panel, there is a page that provide filter and search functionalities to admin users. I am using Java, SpringBoot, MongoRepository for db operations. I am using indexing and pagination already but the query time takes about 4-6 minutes. I want to decrease this time to 30 seconds at least. I applied archive solution for older than one year records, because the participants can use the voucher codes over one year than participation. So I have to store the data in the participateTransaction collection for one year at least.

Is there a vertical solution that you think can decrease query time to 30 seconds at least ?

1 Like
13

Hello,
Thanks for sharing such an idea
I have few questions/concerns however:

According to MongoDB documentation $facet stage doesn’t use indexes if it is the first stage in the pipeline:

  • If the $facet stage is the first stage in a pipeline, the stage will perform a COLLSCAN. The $facet stage does not make use of indexes if it is the first stage in the pipeline.

So it seems like the more optimal way would be to use
$match
$sort
$facet (with count stage and [{$skip: pageSize * (page-1)}, {$limit: pageSize}])

$match and $sort will benefit from indexes because of $sort + $match Sequence Optimization
And then $sort and $limit will benefit from $sort + $limit Coalescence

What do you think about such adjustments to the original pipeline with $facet stage as the first one?

1 Like
14

Hi @Ilyas_Ziyaoglu,

Can you please provide your schema models and the current queries you are using?

15

Hi @Yuriy_Osychenko,

You are absolutely right!

In practice, the pipeline should follow the steps you described:

  1. $match
  2. $sort
  3. $facet

The $facet stage should be at the end of the pipeline for optimal pagination.

In my article, I focused solely on the pagination logic and kept it simple with just the $facet stage. However, I’ll update the article to include this important optimization for a more complete solution.

Thanks for pointing this out!

3 Likes
16

Please, disregard this line above since limit is part of facet:

And then $sort and $limit will benefit from $sort + $limit Coalescence

1 Like
17

Thanks for the great article!
I have a question regarding the alerts I might be getting from Mongo Atlas, as I have faced this before, which is the Query Targeting: Scanned Objects / Returned ratio, as I assume it will trigger the warning as I am scanning the documents that I want and return only one document as a result which has the data + total count.
Any idea regarding that?