I want to paginate an User table with 10k+ registers, in the best way possible. I could use offset and limit, but it is clearly not good as I can see in many articles. Stumbled across keyset and cursor pagination, in which I though that keyset may be a better fit, since its easier to implement in APIs (im using golang). The problem is how the keyset works, from what I could see, the id (primary key) is indexed, then used to query across the database:
select * from users where id > ? order by id limit ?
However, it seems like this is only viable in integer auto-incremental IDs, and that's not my case, im using UUIDs (v4). How can I achieve this keyset pagination?
This will work just the same with UUIDs. Assuming that id is the UUID primary key column, you can write
SELECT ... FROM ...
WHERE (order_col1, order_col2, id) > (<previous values>)
ORDER BY order_col1, order_col2, id
LIMIT <page size>;
This query can be supported by an index on (order_col1, order_col2).