3 ms·

Both approaches use the underlying index in the database, but cursor approaches need to look at far fewer results in order to return the page. If you are deep i

by wespiser_2018 4y ago

Both approaches use the underlying index in the database, but cursor approaches need to look at far fewer results in order to return the page. If you are deep into the result, your offset could be high, like 1000 or so. The database would have to start at the beginning and go through 1000 results, especially if filters are in play, in order to get the the N items in the page.

kaba0 4y ago
OP talks about key set pagination which also operates by a WHERE clause, not about simple offset-based. According to other commenters cursor based is the same as key-set, but without exposing the internal format in the public API, so it can be implemented as easily as taking a reversible “hash” of a last seen index and the sort order.
- zikohh 4y ago
  But how does this make it the most efficient method of pagination Vs key set. To me it seems this is an overstatement.
  - kaba0 4y ago
    It is not more efficient than key sets as it is basically the same.
    - zikohh 4y ago
      Although https://news.ycombinator.com/item?id=31541822 https://news.ycombinator.com/item?id=31541822 Lazides comment suggests some DBs let you expose the cursor which I haven't heard of before. But I agree with you unless what lazide said exists.
- wespiser_2018 4y ago
  You are correct. I misread the article. We use different terms for this stuff internally, having both simple offset based, and continuation token based, which is essentially a mix between what the author describes as key-set and cursor based and using a different FE API. IMO, the performance would come down to the underlying database query: you could get faster key-set in some cases, and faster cursor based in others if the query was hitting a good index and using fewer joins. Both approaches, "key set" and "cursor based" let you search based off index, so I don't see how one could be inherently faster?

kaba0 4y ago

OP talks about key set pagination which also operates by a WHERE clause, not about simple offset-based. According to other commenters cursor based is the same as key-set, but without exposing the internal format in the public API, so it can be implemented as easily as taking a reversible “hash” of a last seen index and the sort order.

zikohh 4y ago
But how does this make it the most efficient method of pagination Vs key set. To me it seems this is an overstatement.
- kaba0 4y ago
  It is not more efficient than key sets as it is basically the same.
  - zikohh 4y ago
    Although https://news.ycombinator.com/item?id=31541822 https://news.ycombinator.com/item?id=31541822 Lazides comment suggests some DBs let you expose the cursor which I haven't heard of before. But I agree with you unless what lazide said exists.
wespiser_2018 4y ago
You are correct. I misread the article. We use different terms for this stuff internally, having both simple offset based, and continuation token based, which is essentially a mix between what the author describes as key-set and cursor based and using a different FE API. IMO, the performance would come down to the underlying database query: you could get faster key-set in some cases, and faster cursor based in others if the query was hitting a good index and using fewer joins. Both approaches, "key set" and "cursor based" let you search based off index, so I don't see how one could be inherently faster?