Let's say we have a user table with sequential id and want to use cursor based pagination(not sql cursor) while ordering by not unique updated_at column.

We do a query:

SELECT * FROM user
LIMIT 3
ORDER BY updated_at asc;

For table (ordered by updated_at asc, id asc):

And get first 3 rows (2,3,5), our cursor is last row 5 | 14:00, now we need to get (6,4,1).

Is it correct to say that the best option is sort by unique combination of updated_at and id and use where like in example?

SELECT * FROM user
WHERE updated_at > cursor.updated_at(14:00) 
      OR (updated_at = cursor.updated_at(14:00) AND id > cursor.id(5))
LIMIT 3
ORDER BY updated_at asc, id asc;

If this query is correct, what to do with indexes? Should I always create two indexes updated_at asc, id asc and updated_at desc, id asc or single updated_at is enough?

And what do with cursor in graphql? It should be a String type, so should I stringify it as ${id}_${updated_at} to pass around (parsing it for sql query back to id and updated_at) or should I save only id and prefetch updated_at for this id to use in query?

CodePudding user response：

Your query is correct other than the placement of the LIMIT and the use of an unquoted reserved word as a table name, but it could be written better with a tuple comparison.

SELECT * FROM "user"
WHERE (updated_at,id) > (cursor.updated_at, cursor.id)
ORDER BY updated_at asc, id asc
LIMIT 3;

Not only is that cleaner, it will efficiently use an index on (updated_at asc, id asc). A quirk of the tuple comparison though is that there is no way to describe the ordering in discordant directions, but you don't seem to need that anyway. (If "id" is an int, you could just negate it to achieve that)

I can't answer the graphql-specific part of your question.