Cursor pagination is part of your API contract

Someone inserts a row while the client walks the pages, and the whole window shifts. Page two shows an item the client already saw on page one. Or an item falls between pages and the client never sees it. For a feed this is annoying. For an export or a sync endpoint this is a data loss bug that nobody can reproduce.

I used to think keyset pagination is a performance trick. Deep OFFSET is slow, keyset is fast, end of story. Now I think the performance part is the boring half. Offset pagination over a changing dataset lies to the client. That is the interesting half.

Keyset fixes it, but only if the sort is deterministic. ORDER BY created_at is not enough, timestamps collide. Add the primary key as a tiebreaker:

$sql = 'SELECT * FROM events
        WHERE (created_at, id) < (:ts, :id)
        ORDER BY created_at DESC, id DESC
        LIMIT 50';

The cursor is the (created_at, id) of the last row, base64-encoded into one opaque string. Opaque matters. The moment clients learn the cursor is a timestamp, they start building cursors by hand, and your internal sort order is a public API forever. Encode it, and put a version byte inside while you are there. Future you will want to change the sort key.

One contract question people skip: does the cursor survive? If the last row on the page gets deleted, the keyset query still works, it continues from where that row would have been. Offset cannot promise even that.

So my rule for new list endpoints this year: cursor by default, (created_at, id) with a matching composite index, opaque token in the response. Offset stays for admin panels, where a human clicks page numbers and nobody cares about a shifted row.

The endpoint that taught me this still runs on OFFSET. Every client would have to change at once. The bug is documented instead.