High latency observed when using Mongoose skip/limit for deep page numbers

Answer to the Question

When you paginate a collection that contains millions of documents using skip() and limit(), the latency grows roughly linearly with the page number. The driver sends a query that MongoDB must walk skip + limit documents before it can return the requested slice, even if the sort field is indexed. This is why docsExamined in executionStats matches skip + limit.

Replacing skip/limit with a range query on an indexed monotonic field (commonly _id) removes that linear scan. Because the index can be used to jump directly to the starting document, the query time stays constant regardless of page depth, while preserving the original sort order.

When using Query.cursor() to stream results, a batch size of about 1,000 documents keeps driver memory bounded (typically <200 MB) without sacrificing throughput. Batch sizes below 100 cause many round‑trips, and sizes above 10,000 can spike memory.

Likely Explanation

• MongoDB performs a full collection scan for the first skip documents, even with an index on the sort field.
• Server‑side latency dominates; client memory usage is negligible unless the limit is extremely large.
• Deep pages (e.g., skip > 1,000,000) force MongoDB to traverse the entire index, making the query prohibitively slow.

Steps to Verify and Fix

1. Confirm the skip/limit cost:

   // Example with Mongoose
   const stats = await Model.find()
     .skip(N)
     .limit(M)
     .explain('executionStats');
   console.log(stats.executionStats.totalDocsExamined); // should ≈ N + M
   

2. Run the same query using a range on _id:

   // Determine start and end ObjectIds for the desired page
   const startId = ...; // e.g., the _id of the last doc on the previous page
   const endId   = ...; // optional, can use $gt only
   const rangeStats = await Model.find({ _id: { $gt: startId } })
     .limit(M)
     .explain('executionStats');
   console.log(rangeStats.executionStats.totalDocsExamined); // should be close to M
   

3. Check that _id is indexed and that the natural order matches your sort order.
4. Stream with a cursor and set batchSize:

   const cursor = Model.find().sort({ _id: 1 }).cursor().batchSize(1000);
   for await (const doc of cursor) {
     // process doc
   }
   

5. Monitor server metrics: enable server‑side profiling or use MongoDB Atlas Performance Advisor to verify that the range query uses the index and that there is no full collection scan.

Diagnostic Question

Do you have an index on the field currently used for sorting (other than _id)? If not, creating that index is a prerequisite for the range‑query approach to be effective.