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 uuid
and 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 users
table 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 uuid
since created_at
may be duplicate, but their combination would be unique per row.
OFFSET
. However it leaves out the bugs of skipping or duplicating rows as the user moves to the next page while rows are being inserted/deleted. (That also argues againstOFFSET
.)