Pagination breaks the moment you reorder page one

The request

A community member list, paginated. Product wants admins first, then the person viewing the page if they're a member, then everyone else. Reasonable — it's what makes the first screen useful.

The naive implementation sorts by a priority field and pages over the result. That's fine if the priority is a stable property of the row. Here it isn't: "is this the requesting user" depends on who is asking, so the ordering is different per viewer, and any offset-based paging on top of it is computed against an ordering the database doesn't have a stable index for.

Why duplicates appear

Suppose an admin sorts to the top of page one by priority, but their natural position — by join date, say — falls on page three. Page one shows them as pinned. Page three shows them again in their natural place. The same person appears twice, and because the page size is fixed, someone else gets pushed off the end and is never shown at all.

What actually works

Treat the priority rows as a separate query, and subtract them from the rest:

async function listMembers(communityId, viewerId, page, limit) {
  const priority = page === 1
    ? await members.find({
        community: communityId,
        $or: [{ role: 'ADMIN' }, { account: viewerId }],
      })
    : [];

  const excluded = await priorityIds(communityId, viewerId);

  const rest = await members
    .find({ community: communityId, _id: { $nin: excluded } })
    .sort({ createdAt: 1 })
    .skip(offsetFor(page, limit, excluded.length))
    .limit(limit - priority.length);

  return { data: [...priority, ...rest], page, limit,
           total: await members.countDocuments({ community: communityId }) };
}

Three details carry the correctness:

• The exclusion applies to every page, not just page one. That's what stops the duplicate. It's also the part most implementations forget, because page one looks right.
• Page one returns fewer natural rows — the pinned ones consumed part of the limit. Otherwise page one is longer than every other page.
• The offset has to account for the excluded rows, or page two starts in the wrong place.

The deeper problem with offsets

All of the above is still offset pagination, and offset pagination has a race that no amount of care removes: if a row is inserted or deleted between two page requests, the window shifts under the reader. Items get skipped or repeated.

For a member list that changes slowly, that's an acceptable trade for a simple API and a real total count. For a feed with constant writes it isn't, and cursor pagination — carrying the last seen sort key rather than a count — is the honest answer.

The important thing is knowing which one you shipped. "Page 3 of 40" and an infinite scroll have different correctness guarantees, and picking the API shape before thinking about write frequency is how you end up with the wrong one.

Where the total comes from

One last trap. If the total is computed with the exclusion applied, it disagrees with the number of rows a user can actually page through, and the last page appears short or empty. The total should count the whole set — the pinned rows are being reordered, not removed.

← All engineering notes