Cursor pagination

Any endpoint that returns a list has to return it in pages. A list that works with 50 items will time out at 500,000. Cursor pagination (also called keyset pagination) returns one page, plus a marker that says where the next page starts. The marker is the sort key of the last row the client saw.

The big idea

Picture a list of files sorted newest first. There are two ways to say "give me the next page."

• Offset: "Skip the first 10,000 and give me 50." You count your way there.
• Cursor: "Give me 50 files older than 2026-10-01 12:00." You name the place.

The analogy breaks in one place. A bookmark stays where you left it. A cursor is a value you send back, so it has to be stable and sortable, which is why the tiebreaker below matters.

Walking through it

Offset pagination is one SQL line:

SELECT * FROM files WHERE folder_id = $1 ORDER BY created_at DESC LIMIT 50 OFFSET 10000;

It has two problems.

• It gets slow. The database reads and throws away 10,000 rows before it returns 50. Deep pages cost more than shallow ones.
• It shifts under the reader. Say the newest list is [F5, F4, F3, F2, F1] with page size 2. Page 1 shows F5, F4. A new file F6 arrives at the top. Now OFFSET 2 starts at F4 again, so the user sees F4 twice.

Cursor pagination fixes both:

SELECT id, name, created_at FROM files
WHERE folder_id = $1
  AND (created_at, id) < ($2, $3)   -- last row from the previous page
ORDER BY created_at DESC, id DESC
LIMIT 51;                           -- one extra row tells you a next page exists

The id in the sort is a tiebreaker. Two files can share a timestamp, and without a unique second key the boundary between pages is ambiguous. With an index on (folder_id, created_at DESC, id DESC), page 1 and page 10,000 each cost one index seek.

Hide the cursor's contents from clients, so you can change them later. Encode it as an opaque string:

const encodeCursor = (c) => Buffer.from(JSON.stringify(c)).toString("base64url")
const decodeCursor = (s) => JSON.parse(Buffer.from(s, "base64url").toString())

const token = encodeCursor({ createdAt: "2026-10-01T12:00:00Z", id: "fl_9" })
console.log(token)
console.log(decodeCursor(token))

Output:

eyJjcmVhdGVkQXQiOiIyMDI2LTEwLTAxVDEyOjAwOjAwWiIsImlkIjoiZmxfOSJ9
{ createdAt: '2026-10-01T12:00:00Z', id: 'fl_9' }

The response carries next_cursor and has_more. Use them, and never let a client pick a huge limit.

Common mistakes

• No tiebreaker. Rows with the same timestamp can be skipped or repeated across pages.
• No page cap. A client that sends limit=1000000 can take down your database. Set a default such as 50 and a maximum such as 200.
• Expecting to jump to page 200. Cursors only move forward and back one page at a time. Rarely does an API need arbitrary jumps.

Check yourself

1. Why does the sort include id as well as created_at? It makes the order total, so two rows with the same timestamp always sort in the same place.
2. Why fetch 51 rows when the page size is 50? The extra row tells you another page exists without running a separate count query.

Key takeaways

• Offset pagination gets slower and less stable as the page number grows.
• Cursor pagination asks for rows after the last one seen, so every page costs the same with the right index.
• Always add a unique tiebreaker to the sort.
• Encode cursors as opaque strings and cap the page size on the server.

Further learning

The Google Cloud API Design Guide covers pagination and other conventions.

Next, read about API versioning, which deals with changes to these responses over time.