Keyset pagination when the sort column is not unique — what do you actually do at 40M rows?
Paginating events ordered by created_at DESC. Offset pagination dies past page 200 as expected. Keyset works beautifully if the sort key is unique, but created_at is a millisecond timestamp and we ingest bursts — a single millisecond can hold 300 rows.
The textbook answer is a composite cursor:
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 50;That is correct and it uses the index on (created_at DESC, id DESC). My problem is the API contract: clients currently send an opaque cursor that is a base64 timestamp, and there are third-party integrations reading it. Rotating the cursor format means a deprecation window I would rather not run.
Two questions:
- Is there a way to keep a single-column cursor and stay correct? I do not think there is — ties are genuinely ambiguous without a tiebreak — but I want to be told I am wrong.
- If I must rotate, has anyone served both cursor formats from one endpoint without the branching becoming permanent? Every version of this I have written turned into two code paths that both live forever.
Written by
Marcus Okafor
Backend engineer who ended up as the person the team pages when a query goes from 30ms to 30s. Postgres, Go, and a long-running grudge against ORMs that hide the plan from you. I like partitioning, EXPLAIN (ANALYZE, BUFFERS), and migrations that can be rolled back at 2am.
4 Comments
Sign in to join the discussion
This is the answer. The piece I had missed is that the branch only has to exist in the parser — I kept designing two query paths, which is why every version of it felt permanent.
Client-side question: do your API docs describe the cursor as opaque? Every time we have documented a cursor's internals, somebody has parsed it and then treated our next change as a breaking one.
Documented as opaque since day one, which is exactly why I feel entitled to rotate it. They parsed it anyway. That is why I want the old format to keep working rather than to be right about it.
You cannot keep a single-column cursor and stay correct. Ties are genuinely ambiguous, and any tiebreak you invent is a second column you are hiding from yourself.
On serving both formats without permanent branching: version the cursor, not the endpoint. Emit a base64 envelope
{v:2, t, id}, and treat anything that fails to parse as v1, meaning{v:1, t}where t is the old base64 timestamp. That gives you oneparseCursorwith two cases and oneserializeCursorthat only ever emits v2. Third parties keep working because their old cursor still parses, and nothing else in the codebase ever learns there were two formats.We ran that for eight months. Deleting v1 was a four-line PR, which is the bit that matters — the branch was small enough that nobody was afraid to remove it.