Page an API With a Cursor, Not an Offset

A client asks for page 3, with 50 rows per page, while new rows arrive at the top. The database skips 100 rows from a list that no longer matches the list the client already displayed. The client misses a row and sees another twice. The endpoint did what it was asked. The question was the wrong question.

Ask for the next rows after a position the client already holds. That position is the cursor. The query is "where the sort key is past this value," with a limit, not "skip this many." The database can use an index on the sort key. It does not walk and discard the prefix of the table.

What has to be true for the cursor to be stable

• The sort is a total order. If two rows share a timestamp, add the primary key so the order does not flip between requests.
• The cursor is opaque to the client. You may encode the sort values. The client sends them back. It does not edit them.
• Filters stay the same for the life of the walk. Changing the filter starts a new walk.
• A deleted row disappears. The next page continues after the cursor. You do not renumber earlier pages.
• A row inserted behind the cursor does not shove later rows onto a different page. The client will see it only if it starts again, which is the honest behavior.

When an offset is still reasonable

A small admin table that a person clicks through, and that does not change while they click, can use an offset. Say so, and cap the offset so a client cannot ask you to skip a million rows. An export, a mobile feed, or any list that grows while it is read should use a cursor. Jumping to an arbitrary page number is the feature you give up. Most API clients never needed it. They needed the next page.

Illustrative scenario

This scenario is illustrative. An activity feed is sorted by created_at and id. The first response ends with a cursor holding those two values for the last item. The next request uses them in the WHERE clause and returns the following limit. A new activity inserted at the top does not shift the page the client is reading. The client refreshes from the start when it wants the newest item. Nothing in this design depends on a particular web framework.

Checklist

• Choose a unique sort. Add a tie-breaker.
• Return a cursor only when another page exists.
• Document that cursors are opaque and expire if you need them to.
• Reject a huge offset if you still offer one.
• Test a walk while rows are inserted and deleted. The walk should not repeat a row it already returned.

Questions that usually come next

Can the client decode the cursor?

It should not need to. If you publish the encoding, clients will build cursors by hand and break when you add a tie-breaker. Treat the cursor as a token.

How does this relate to a slow query?

An offset of 100000 still sorts or scans those rows. A cursor seeks the index. If the list is slow even for the first page, the access path is a separate problem, covered in A Slow Query Is an Access-Path Problem.