Pagination: offset vs cursor

OFFSET pages repeat or skip rows when data changes, and get slower the deeper you go. A cursor, the last row's sort key, does neither.

How it works

OFFSET 10000 LIMIT 20 makes the database read and throw away 10000 rows. If a row is inserted between page 1 and page 2, everything shifts by one and the user sees a duplicate. Keyset pagination keeps the last row's sort key and asks for rows after it: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20. With an index on (created_at, id) every page is one index seek, page 1 or page 10000. The id breaks ties so rows with equal timestamps are never skipped.

Reach for it when

• Feeds, infinite scroll, activity logs
• API list endpoints (return an opaque next cursor)
• Batch jobs that walk a whole table

Copy// OFFSET pages shift when rows are inserted; a cursor (the last row's sort key) does not.
// SQL: SELECT * FROM jobs WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT $3
const newestFirst = (a, b) => b.at - a.at || b.id - a.id; // id breaks ties
function offsetPage(rows, page, size) {
  return [...rows].sort(newestFirst).slice(page * size, page * size + size);
}
function keysetPage(rows, cursor, size) {
  const after = r => !cursor || r.at < cursor.at || (r.at === cursor.at && r.id < cursor.id);
  const items = [...rows].sort(newestFirst).filter(after).slice(0, size);
  const last = items.at(-1);
  return { items, next: items.length === size ? { at: last.at, id: last.id } : null };
}

Traps

• Sorting by a non unique column alone skips ties. Add the id.
• The index must match the ORDER BY, columns and direction.
• Return the cursor as an opaque string, so clients don't build it and you can change it later.