Careeroza

Cursor Pagination

Why Offsets Break at Scale

  • OFFSET 100000 makes the database read and throw away 100,000 rows: slow pages deep in a list.
  • If new rows are inserted while the user scrolls, offset pages show duplicates or skip items.
  • Cursor pagination says "give me 20 items after this one": WHERE (createdAt, id) < (cursorTime, cursorId). It is fast with an index and stable while data changes. Infinite scroll feeds use it.

Opaque Cursors

Example

The cursor is the last item's sort key, encoded as base64url so clients treat it as an opaque string:

type Item = { id: number; createdAt: string };
const feed: Item[] = Array.from({ length: 7 }, (_, i) => ({
  id: 100 - i,
  createdAt: new Date(Date.UTC(2026, 0, 15, 10, 0, 0) - i * 60_000).toISOString(),
}));

const encode = (it: Item) => Buffer.from(JSON.stringify([it.createdAt, it.id])).toString('base64url');
const decode = (c: string) => JSON.parse(Buffer.from(c, 'base64url').toString()) as [string, number];

function page(limit: number, cursor?: string) {
  let rows = feed;                          // already sorted newest first
  if (cursor) {
    const [t, id] = decode(cursor);
    rows = rows.filter((r) => r.createdAt < t || (r.createdAt === t && r.id < id));
  }
  const slice = rows.slice(0, limit + 1);   // fetch one extra to know if there is more
  const data = slice.slice(0, limit);
  return { ids: data.map((d) => d.id), nextCursor: slice.length > limit ? encode(data[data.length - 1]) : null };
}

let res = page(3);
console.log(res);
while (res.nextCursor) {
  res = page(3, res.nextCursor);
  console.log(res);
}

Output

{
  ids: [ 100, 99, 98 ],
  nextCursor: 'WyIyMDI2LTAxLTE1VDA5OjU4OjAwLjAwMFoiLDk4XQ'
}
{
  ids: [ 97, 96, 95 ],
  nextCursor: 'WyIyMDI2LTAxLTE1VDA5OjU1OjAwLjAwMFoiLDk1XQ'
}
{ ids: [ 94 ], nextCursor: null }

With Prisma

export class FeedService {
  constructor(private readonly prisma: PrismaService) {}

  async list(limit: number, cursor?: number) {
    const rows = await this.prisma.product.findMany({
      take: limit + 1,
      ...(cursor && { cursor: { id: cursor }, skip: 1 }),   // skip the cursor row itself
      orderBy: { id: 'desc' },
    });
    const hasMore = rows.length > limit;
    const data = rows.slice(0, limit);
    return { data, nextCursor: hasMore ? data[data.length - 1].id : null };
  }
}