Offset pagination silently shows duplicates. Use a cursor.
Gepubliceerd · 2 min leestijd
?page=2&per_page=5 is easy to build and wrong in a busy system. The proof fits in a few lines. Take a list sorted newest-first, read page 1, let one new row arrive, then read page 2:
import { encodeCursor, decodeCursor } from "@sandsoftwaresolutions/cursor-pagination";
// Newest first. In a real database this is: SELECT ... WHERE id < ? ORDER BY id DESC LIMIT ?
let rows = Array.from({ length: 10 }, (_, i) => ({ id: 10 - i }));
const offsetPage = (n, size) => rows.slice(n * size, n * size + size).map((r) => r.id);
const keysetPage = (cursor, size) => {
const before = cursor ? decodeCursor(cursor).id : Infinity;
const data = rows.filter((r) => r.id < before).slice(0, size);
return { ids: data.map((r) => r.id), next: data.length === size ? encodeCursor({ id: data.at(-1).id }) : null };
};
const o1 = offsetPage(0, 5), k1 = keysetPage(null, 5);
rows = [{ id: 11 }, ...rows]; // a new row arrives between the two requests
const o2 = offsetPage(1, 5), k2 = keysetPage(k1.next, 5);
console.log("offset:", o1, o2, "-> duplicate:", o1.filter((x) => o2.includes(x)));
console.log("keyset:", k1.ids, k2.ids, "-> duplicate:", k1.ids.filter((x) => k2.ids.includes(x)));
rows = rows.filter((r) => r.id !== 6); // the last row of page 1 gets deleted
console.log("keyset after deleting the cursor row:", keysetPage(k1.next, 5).ids, "(no error, nothing skipped)");
Real output:
offset: [ 10, 9, 8, 7, 6 ] [ 6, 5, 4, 3, 2 ] -> duplicate: [ 6 ]
keyset: [ 10, 9, 8, 7, 6 ] [ 5, 4, 3, 2, 1 ] -> duplicate: []
keyset after deleting the cursor row: [ 5, 4, 3, 2, 1 ] (no error, nothing skipped)
With offsets, the new row pushed everything down by one, so row 6 appeared on both pages. Delete a row instead and the client skips one. Neither is visible to anyone until a user asks why an order is missing from the export.
A cursor says "continue after this row" instead of "skip N rows", so inserts and deletes elsewhere in the list don't shift your position.
Two ways to use @sandsoftwaresolutions/cursor-pagination
npm install @sandsoftwaresolutions/cursor-pagination
Small, already-loaded lists: paginate.
import { paginate } from "@sandsoftwaresolutions/cursor-pagination";
const page = paginate(items, { limit: 20, after: url.searchParams.get("after") ?? undefined });
// { data, pageInfo: { hasMore, nextCursor } }
Be aware of how it works: it finds the cursor row by scanning the array, so it's for lists you already hold in memory. If the cursor row is no longer in the list, or the cursor is garbage, it throws (RangeError/TypeError), so catch that and answer 400 rather than letting it become a 500.
Real tables: encodeCursor / decodeCursor and a keyset query. The example above does it in the keysetPage function. In SQL this is:
SELECT id, total FROM orders
WHERE id < :cursor_id -- omit on the first page
ORDER BY id DESC
LIMIT :size + 1; -- fetch one extra to know whether there is a next page
Decode the incoming cursor to get cursor_id, and encode the last row's id as the next cursor. Because the database uses an index to jump straight to id < :cursor_id, page 1,000 costs the same as page 1; OFFSET 5000 makes the database walk and discard 5,000 rows every time. Deleting the row the cursor points at is harmless, as the last line of the output shows, because the query asks "ids below X", not "the row with id X".
Rules that save you later
- Sort by something unique and stable. Sorting by
created_atalone breaks when two rows share a timestamp. Use(created_at, id)and put both in the cursor:encodeCursor({ createdAt, id }), withgetCursorreturning the same shape forpaginate. - A cursor is a position, not a secret. It's base64url JSON, trivially decoded and editable. Never put anything in it you wouldn't show the client, and always validate the decoded values server-side (parameterised query, as above). If tampering matters, sign the whole link with
@sandsoftwaresolutions/signed-links. - Cursors don't give page numbers or "jump to page 40". That's the real trade-off. If your UI needs page numbers, offset may be acceptable for small, slow-changing tables. For feeds, exports, sync endpoints and anything an API client loops over, use a cursor.