API Pagination: Offset vs Cursor-Based

Any list endpoint needs pagination. The two main approaches behave very differently on large or fast-changing data.

Offset pagination

GET /articles?page=3&per_page=20

SELECT * FROM articles ORDER BY published_at DESC LIMIT 20 OFFSET 40;

• Pros: simple, lets users jump to page 37, easy total counts.
• Cons: the database still walks past all skipped rows, so deep pages get slow.
• Cons: when new rows are inserted, items shift between pages and users see duplicates or miss items.

Cursor (keyset) pagination

Instead of "skip 40", ask for "the next 20 after this item":

-- first page
SELECT id, title, published_at FROM articles
ORDER BY published_at DESC, id DESC
LIMIT 21;   -- one extra row tells us whether there's a next page

-- next page, after the last row we returned (published_at, id)
SELECT id, title, published_at FROM articles
WHERE (published_at, id) < ($1, $2)
ORDER BY published_at DESC, id DESC
LIMIT 21;

With an index on (published_at DESC, id DESC), every page is equally fast, and inserts don't shift results.

An opaque cursor

const encodeCursor = (row) => Buffer.from(JSON.stringify([row.published_at, row.id])).toString('base64url');
const decodeCursor = (cursor) => JSON.parse(Buffer.from(cursor, 'base64url').toString());

{
  "data": [ { "id": 981, "title": "..." } ],
  "next_cursor": "WyIyMDI2LTEwLTA5VDA4OjAwOjAwWiIsOTgxXQ",
  "has_more": true
}

Clients pass ?cursor=... back unchanged. Keeping it opaque lets you change the implementation later.

Which to choose

• Infinite scroll, feeds, sync jobs and large tables: cursor.
• Admin tables where users jump to page numbers on modest data: offset is fine.
• Always cap the page size (for example 100) regardless of what the client asks for.