Why does MongoDB's skip(200000).limit(20) get slower the deeper the page number goes?

## What skip actually does `skip(n)` is often read as "start at position n", by analogy with an array index. MongoDB has no such positional access. The server executes the query plan, produces the result stream in sort order, and then discards the first `n` entries before emitting anything to the client. The discarded entries were still produced. So `db.events.find().sort({_id: 1}).skip(200000).limit(20)` walks 200,020 index entries to deliver 20 documents. The work is proportional to the offset, which is why deep pages degrade smoothly and then painfully: page 1 is instant, page 100 is fine, page 10,000 times out. Users rarely reach page 10,000, but crawlers and scripted exports do, and one such request can dominate a shard's working set. A second, worse case: if the sort field has no supporting index, the server must perform a **blocking sort** — materialize and sort the matching set in memory before it can skip anything. That has a memory limit (spilling to disk or failing depending on version and settings), and it repeats for every single page, since each page request re-runs the whole query from scratch. Deep paging over an unindexed sort is the classic "why did the database fall over at 2am" story. ## Sharding makes it worse On a sharded cluster, a sorted `skip`+`limit` cannot be pushed down as-is. Each shard must return the first `offset + limit` entries in sort order so that `mongos` can merge them and then discard the right ones globally — no shard knows which of its entries fall in the global first 200,000. The cost is therefore multiplied by the number of shards, and the router holds the merged stream in memory. ## Range (keyset) paging The alternative expresses "the next page" as a **filter** on the sort key rather than a count of things to throw away. Keep the sort key of the last document you showed, and ask for entries after it: ```javascript // page 1 db.events.find({}).sort({ _id: 1 }).limit(20) // page 2, where lastId is the _id of page 1's final document db.events.find({ _id: { $gt: lastId } }).sort({ _id: 1 }).limit(20) ``` The index on `_id` is a B-tree; a `$gt` predicate seeks directly to that key and scans 20 entries forward. Every page costs the same, whether it is page 2 or page 200,000, and on a sharded cluster the predicate is a range each shard can evaluate locally. ## Non-unique sort keys need a tiebreaker Range paging works only if the sort order is **total** — otherwise entries with the same key can be skipped or repeated at the page boundary. Sorting by `score` alone is not total; sorting by `{score: -1, _id: 1}` is, because `_id` is unique. The predicate then becomes a lexicographic "after this pair" comparison: ```javascript db.players.find({ $or: [ { score: { $lt: lastScore } }, { score: lastScore, _id: { $gt: lastId } } ] }).sort({ score: -1, _id: 1 }).limit(20) ``` Back it with a compound index on `{score: -1, _id: 1}` so the sort is satisfied by the index rather than blocking. Verify with `explain()` that the plan is an index scan and that keys examined stays near the page size instead of growing with depth. ## What you give up Range paging is strictly sequential: you can produce "next" and, with a reversed predicate and sort, "previous", but you cannot jump to an arbitrary page number, because the anchor for page N is only known after paging there. Numbered page links are therefore incompatible with it. That is usually an acceptable trade — infinite scroll, "load more", and API cursors all fit the model naturally, and the cursor token is simply the encoded sort-key tuple. Where a UI genuinely requires numbered pages, the pragmatic compromises are: cap how deep paging may go (page 1–100 with skip is cheap and bounded); or restrict the deep-paging path to a narrow, well-indexed query so the skipped traversal is over index keys only. ## Related but different: a live cursor Holding one open cursor and iterating it is a third option that avoids both problems for a batch job — no repeated queries, no offsets — but it ties the job to server-side cursor state with an idle timeout and dies on failover. For a stateless HTTP API, range paging is the right answer; for an in-process export, a cursor is fine. ## What to say in an interview State the mechanism first — `skip` produces and discards, so cost is O(offset) — then note the sharded multiplier and the blocking-sort trap, then show the range predicate with a unique tiebreaker and the compound index behind it, and finish by naming the tradeoff you accepted: no jump-to-page-N.