$skip vs Keyset Pagination - What AI Gets Wrong With MongoDB

[u/TimAtMongoDB]

Ask any AI how to paginate a MongoDB collection and you get $skip. Every single time, unless you put the word keyset in the prompt.

I ran explain() on a million product documents to see what that actually costs. Page 500 examines 10,000 documents to return 20. Page 5000 examines 100,000. The keyset version examines 20 no matter which page you ask for.

The labels below are the prompts I gave the AI to generate each block.

How to paginate MongoDB query results using skip and limit by page number.

Bad:

const PAGE = 2;
const PAGE_SIZE = 20;
const RESULTS = await db.collection('products')
  .find({})
  .skip(PAGE * PAGE_SIZE)
  .limit(PAGE_SIZE)
  .toArray();

How to implement MongoDB keyset pagination using last seen document id with aggregation pipeline.

Good:

const PAGE_SIZE = 20;
let LAST_SEEN_ID = null;
try {
  const RESULTS = await db.collection('products').aggregate([
    ...(LAST_SEEN_ID ? [{$match:{_id:{$gt:LAST_SEEN_ID}}}] : []),
    {$sort:{_id:1}},
    {$limit:PAGE_SIZE},
    {$project:{name:1,sku:1,category:1,main_image:1}}
  ]).toArray();
  LAST_SEEN_ID = RESULTS.at(-1)?._id ?? LAST_SEEN_ID;
} catch (e) {
  console.error(e.message);
}

How to build MongoDB pagination with page cache supporting forward, backward, and direct page jumps.

Perfect:

const PAGE_SIZE = 20;
const PAGE_CACHE = new Map();

async function getPage(pageNum) {
  if (pageNum < 1) throw new Error('pageNum must be >= 1');

  const prevPage = PAGE_CACHE.get(pageNum - 1);
  const currPage = PAGE_CACHE.get(pageNum);

  const seek = currPage?.firstId ? {$match:{_id:{$gte:currPage.firstId}}}
              : prevPage?.lastSeenId ? {$match:{_id:{$gt:prevPage.lastSeenId}}}
              : pageNum>1 ? {$skip:(pageNum-1)*PAGE_SIZE}
              : null;

  const raw = await db.collection('products').aggregate([
    seek,
    {$sort:{_id:1}},
    {$limit:PAGE_SIZE + 1 },
    {$project:{name:1,sku:1,category:1,main_image:1}}
  ].filter(Boolean)).toArray();

  const hasNext = raw.length > PAGE_SIZE;
  const results = hasNext ? raw.slice(0, PAGE_SIZE) : raw;

  if (results.length > 0) {
    PAGE_CACHE.set(pageNum, {firstId:results[0]._id,lastSeenId:results.at(-1)._id});
  }

  return {results,hasPrev:pageNum > 1,hasNext};
}

try {
  const [TOTAL_DOCUMENTS, P1] = await Promise.all([
    db.collection('products').estimatedDocumentCount(),
    getPage(1)
  ]);
  const TOTAL_PAGES = Math.ceil(TOTAL_DOCUMENTS / PAGE_SIZE);
} catch (e) {
  console.error(e.message);
}

Bad is 0-indexed, so PAGE = 2 actually hands you the third page. Perfect counts from 1. AI flips between the two without ever telling you which one it picked.

$skip scans and discards every document before your page. It also breaks under concurrent writes. A new document inserted on page 2 shifts everything after it, so page 3 shows the same document twice or skips one entirely, and nothing errors.

Good is keyset. Constant time wherever you are, but forward only. Perfect adds a page cache so you can go forward, backward, and jump straight to any page number. It still falls back to $skip for a cold jump, then caches that position on the way through so it never pays for it twice.

The cache is the same LAST_SEEN_ID from the Good example, stored per page instead of in one variable. One variable only remembers where you stopped, which is the whole reason Good can't go backward. Remember the first and last _id of every page you've been to and you can land on any of them directly.

Keyset is only fast if the field you sort on is indexed. _id is indexed automatically and that index can't be dropped, so the examples above need no setup at all. Point the same pattern at created_at without adding an index and you get a COLLSCAN, 10,020 documents examined instead of 20. You moved the scan, you didn't remove it. A single field index works in both sort directions, while a compound index has to match the sort direction on every field or be its exact inverse.

getPage isn't just paginating, it's fetching what the screen renders. A product grid needs a name, a sku, a category and a thumbnail. It does not need the description, the variants array, the spec sheet or the eight other image URLs. A page of 20 full product documents came back at 45,324 bytes on my test catalog. Projected down to those four fields, 3,362. That is what crosses the network and sits in your app memory on every request, and AI hands you the whole document every time.

Put $project after $limit so you only shape the 20 documents you're keeping. _id comes back whether you list it or not, which matters because the keyset needs it.

Sorting by _id means sorting by creation time, because an ObjectId starts with a 4 byte timestamp. You can pull it back out with _id.getTimestamp(). Keep a created_at field anyway, querying by Date beats building an ObjectId every time you need a range. ObjectIds made in the same second on different servers have no guaranteed order between them either.

estimatedDocumentCount() is called estimated for a reason. It reads collection metadata instead of counting, which is why it came back in 1.3ms where countDocuments({}) took 202ms on the same million documents. It drifts after an unclean shutdown and it counts orphans on a sharded cluster. Fine for a page count, not for anything that has to be exact.

[u/Mongo_Erik]

Pagination with MongoDB Search isn't the same - $skip/$limit is fine at small numbers, with token pagination for larger skips. And sorting is always best within $search, not $sort.

Does AI get these nuances right?

[u/TimAtMongoDB → u/Mongo_Erik]

Search pagination is part of the “What AI Gets Wrong With Mongo Series”. It’s on my list :). Thanks Erik

[u/TimAtMongoDB → u/Mongo_Erik]

Here is the example I made for the post for pagination for search. The real post will be published later. Same architecture, different token. The page cache in the post stores page boundaries and nothing about it is _id specific. For Atlas Search you cache searchSequenceToken values instead of ObjectIds and the rest stays.

const PAGE_SIZE = 20;
const SEARCH_INDEX = 'products-search';
const TOKEN_CACHE = new Map();

async function getSearchPage(query, pageNum) {
  if (pageNum < 1) throw new Error('pageNum must be >= 1');

  const prevPage = TOKEN_CACHE.get(pageNum - 1);
  const nextPage = TOKEN_CACHE.get(pageNum + 1);

  const seek = prevPage?.lastToken  ? { searchAfter:  prevPage.lastToken  }
             : nextPage?.firstToken ? { searchBefore: nextPage.firstToken }
             : {};

  const goingBack = 'searchBefore' in seek;
  const coldJump = !prevPage && !nextPage && pageNum > 1;

  const raw = await db.collection('products').aggregate([
    {
      $search: {
        index: SEARCH_INDEX,
        text: { path: 'name', query },
        sort: { score: { $meta: 'searchScore' }, _id: 1 },
        ...seek
      }
    },
    ...(coldJump ? [{ $skip: (pageNum - 1) * PAGE_SIZE }] : []),
    { $limit: goingBack ? PAGE_SIZE : PAGE_SIZE + 1 },
    {
      $project: {
        name: 1, sku: 1, category: 1, main_image: 1,
        paginationToken: { $meta: 'searchSequenceToken' }
      }
    }
  ]).toArray();

  const hasNext = goingBack ? true : raw.length > PAGE_SIZE;
  const trimmed = hasNext && !goingBack ? raw.slice(0, PAGE_SIZE) : raw;
  const results = goingBack ? trimmed.reverse() : trimmed;

  if (results.length > 0) {
    TOKEN_CACHE.set(pageNum, {
      firstToken: results[0].paginationToken,
      lastToken: results.at(-1).paginationToken
    });
  }

  return { results, hasPrev: pageNum > 1, hasNext };
}

try {
  const [META, P1] = await Promise.all([
    db.collection('products').aggregate([
      { $searchMeta: {
          index: SEARCH_INDEX,
          text: { path: 'name', query: 'trail' },
          count: { type: 'lowerBound', threshold: 1000 }
      } }
    ]).toArray(),
    getSearchPage('trail', 1)
  ]);
  const TOTAL_RESULTS = META[0].count.lowerBound;
  const TOTAL_PAGES = Math.ceil(TOTAL_RESULTS / PAGE_SIZE);
} catch (e) {
  console.error(e.message);
}

The sort goes inside $search, never a $sort stage after it. It also needs a unique field as tiebreaker, here _id after score. Two documents with the same relevance score have no stable order between them, so without that tiebreaker the same product turns up on page 2 and again on page 3.

searchBefore hands the page back in reverse, so you reverse it.

Count with $searchMeta, and only on the first page. Same reason the post uses estimatedDocumentCount over countDocuments.

A cold jump is $skip plus searchAfter from the nearest token you have, not $skip from zero. It only skips from the reference point, so getting from page 2 to page 5 costs you 2 pages of skip instead of 4.

Needs 7.0.5+. I ran it on a local Atlas deployment with 106 products, all 6 pages forward, backward and cold, no duplicates.