How to choose a pagination style for a list endpoint#

A customer exports their invoices by walking your list endpoint, and the export is missing rows. No error was returned and every page came back 200. New invoices arrived while they were reading, every later row shifted along by one, and each page boundary swallowed the row that moved across it.

What you get

You will end up able to pick a pagination style for one endpoint and say what it costs. This is for you if you are designing a list endpoint, or explaining why an existing one loses rows.

Short answer

Use keyset pagination, which asks for rows after the last one the caller saw, when the collection changes while callers read it. Use offset when the data is static or the caller needs to jump to an arbitrary page. Cursors are keyset with the position encoded opaquely, and Link headers are a way to advertise any of them.

You will need

An endpoint returning a list, and a total order over it. Every style below depends on a sort that never ties, which usually means the sort column plus the primary key as a tiebreaker. Without that, two rows can swap places between one request and the next, and every style on this page inherits the problem the choice was meant to solve.

Approaches compared

Keyset and offset are the real choice, and the other two rows are about presentation. Cursors are keyset with the position hidden, which buys you the freedom to change it later. Link headers say where the next page is, whatever the position underneath is made of.

Watch a row disappear

The failure is not theoretical and it does not need concurrency to reproduce. One insert between two reads is enough.

/** Offset: skip N, take M. The skip is counted at the moment of the query. */
export function offsetPage(table, { limit, offset }) {
  const ordered = [...table.rows].sort((a, b) => a.id - b.id)
  return ordered.slice(offset, offset + limit)
}

/** Keyset: everything after the last id the caller saw, in the same order. */
export function keysetPage(table, { limit, afterId = 0 }) {
  const ordered = [...table.rows].sort((a, b) => a.id - b.id)
  return ordered.filter((r) => r.id > afterId).slice(0, limit)
}

The difference is what the second page is defined against. Offset counts from the start of the collection as it is now. Keyset counts from a row the caller is holding, so it does not matter what arrived in front of it.

node drift.mjs

offset
  page 1: 10, 20, 30
  page 2: 30, 40, 50
  rows the caller never saw: 60
keyset
  page 1: 10, 20, 30
  page 2: 40, 50, 60
  rows the caller never saw: none

Offset returned row 30 twice and lost row 60 entirely. A caller writing rows into a database as they arrive gets a duplicate key error on 30, and nothing at all tells them about 60. A delete moves the rows the other way, so a caller can skip a row without any insert happening.

Count the cost at page 900

The second decision is what the query does at depth. An offset of 90,000 makes the database produce and discard 90,000 rows before it reaches the ones you want. Page 900 is far slower than page 1 on the same index. Keyset turns every page into the same indexed range scan, so page 900 costs what page 1 costs.

That difference only matters if callers go deep. An internal dashboard nobody pages past screen three can use offset forever. An export, a sync job or an agent walking a collection will go as deep as the collection is long, and those are the callers who feel it.

Two published APIs show the two answers side by side. GitHub’s REST API pages with numbered pages and advertises the next one in a Link header, which suits a catalog people browse. Stripe’s API pages with starting_after, an object id the caller last saw, which is keyset with the position in plain sight. Neither is wrong. They are answering different questions about what happens when the collection changes mid-walk.

Check it worked

The property to assert is the one the opening paragraph describes: every row appears exactly once across the pages a caller reads, even when the collection changes between requests.

test('an insert before the reader makes offset skip a row', () => {
  const table = seed()
  offsetPage(table, { limit: 3, offset: 0 })
  insertAtFront(table, { id: 5 })

  assert.deepEqual(offsetPage(table, { limit: 3, offset: 3 }).map((r) => r.id), [30, 40, 50])
})

test('keyset returns the same second page whatever was inserted', () => {
  const table = seed()
  const page1 = keysetPage(table, { limit: 3 })
  insertAtFront(table, { id: 5 })

  assert.deepEqual(keysetPage(table, { limit: 3, afterId: page1.at(-1).id }).map((r) => r.id), [40, 50, 60])
})

node --test paginate.test.mjs

1..3
# tests 3
# suites 0
# pass 3
# fail 0
# cancelled 0
# skipped 0
# todo 0
# duration_ms 108.247747

When it goes wrong

The sort is not a total order. Two rows with the same created_at can come back in either order, so a keyset position built on that column alone can skip one of them or repeat it. Sort on the column and the primary key together, and put both in the position.

The second failure is a keyset position that outlives its query. A caller who pages with after_id=42 and then changes the sort gets a position that means something else, and the results are silently wrong. Put the sort in the position and reject a request whose sort does not match it.

The third is a total count nobody can afford. Callers ask for one, and an exact count of a large filtered collection is a full scan on every page. Offer an estimate, or a count only on the first page, or a has_more boolean, which is what most callers were computing from the count anyway.

When not to do this

Do not change the pagination style of a published endpoint in place. Existing callers have offsets in their code and jobs half way through a run, so add the new style alongside and give them a release to move.

Do not expose a raw database cursor or a row identifier that means something outside your system. A position is an implementation detail, and once callers can read it they will construct it.

Do not let a caller page an unbounded collection with no ceiling on page size. The page size is the part of the request that decides how much work one call can ask for.

Do not use offset for anything an agent walks. Automated callers page deep, retry on failure and resume, and every one of those behaviors is where offset loses rows.

Related how-tos

• Advertise next and previous pages with Link headers

• Encode and sign opaque pagination cursors

Last verified

Verified 2026-09-06 against Node 22.22.2. Both output blocks are what the preceding command printed, against an in-memory table rather than a database.