For a large dataset, paginating with an
OFFSET is known to be slow and not the best way to paginate. A much better way to paginate is with a cursor, which is just a unique identifier on the row so we know where to continue paginating from where we last left off from the last cursor position.
When it comes to a cursor where it is an auto incrementing
id value, it's fairly easily to implement:
SELECT * FROM users WHERE id <= %cursor // cursor is the auto incrementing id, ex. 100000 ORDER BY id DESC LIMIT %limit
What we're not certain about, is if instead of an auto incrementing
id cursor, the only unique sequential identifiers for the cursor are
created_at on the table rows.
We can certainly query based on the
uuid to get the
created_at, and then select all
users that are
<= created_at but the issue is what if there are multiple instances of the same
created_at timestamp in the
users table? Any idea how to query the
userstable based on
uuid/created_at cursor combination to ensure we get the correct datasets (just as if we were using auto incrementing
id)? Again, the only unique field is
created_at may be duplicate, but their combination would be unique per row.