8 ms·

Pasting from http://use-the-index-luke.com/no-offset http://use-the-index-luke.com/no-offset (2014); any updates on this list since then? -- The hall of fame

by j_s 9y ago

Pasting from http://use-the-index-luke.com/no-offset http://use-the-index-luke.com/no-offset (2014); any updates on this list since then? -- The hall of fame of frameworks that do support keyset pagination is rather short: jOOQ — Java Object Oriented Querying. Docs. Ruby order_query Django (Python) chunkator Django Infinite Scroll Pagination. SQL Alchemy sqlakeyset. blaze-persistence — a rich Criteria API for JPA providers Perl DBIx::Class::Wrapper

always_good 9y ago
Doesn't that strategy only work when you're paginating one page at a time, like Reddit? How would you implement a deep pagination like "go to page 107" unless you can derive page 107 from your order criteria?
- wutwutwutwut 9y ago
  This is true. It depends on application but I wonder how often users really need to go to Page 107 of something. Seems like maybe this use case should be solved with some kind of search instead. I have implemented offset pagination myself to several apps where being able to paginate by jumping to the page with records starting with "R" would make more sense.
  - flukus 9y ago
    In most cases this is probably better, but sometimes there is no logical filter that works on you hvae to rely on sort order. One example might be a gallery, I might decide to look though pages 1-10 tonight, 20-30 tomorrow, etc. An arbitrary primary key is the only discriminator that makes sense here. Another example would be classical forum posts, they are sorted by last active but sometimes I'll want to jump to page 134 because that's where I got up to last time and bookmarked my place. That said, if users were doing this in my software I'd definitely be looking at how I can improve their workflow.
- stubish 9y ago
  We have this on one of our web apps, and it is a problem. To me the problem is why do we allow jumping to page 107 of 736 in the first place? How does this help someone? The two use cases are 1) user is manually performing a binary search on the results, 2) a bot needs to index the site. Both of these use cases are better solved in other ways, with either search forms that do not suck or index pages generated specifically for crawlers. So the answer to 'how would you implement a deep pagination like "go to page 107"' is to not implement that, and instead improve your design.
  - kuschku 9y ago
    And that’s why Google’s pagination on search results is broken, and why so much usability was lost. Sometimes I want the lowest ranked results for a query, or the middle-of-the-field results. Let me have them.
    - gruez 9y ago
      >Sometimes I want the lowest ranked results for a query, or the middle-of-the-field results. What's the use case for that?
      - kuschku 9y ago
        Google results are gamed so much, that often personal blogs that don’t do SEO, but offer interesting content, appear beyond page 5 of Google search results, but are still relevant. Similar issues appear in many situations where search is used on third party results, but even on first-party results the first page is often gamed. I’ve been thinking about it, and ideally one would make a search engine that only indexes pages that have no analytics, tracking, ads, paywalls, etc. Then I’d find those same pages I’m searching for. To give an example: I found http://blog.deconinck.info/post/2016/12/19/A-Dirt-Cheap-F-Awesome-Led-Table http://blog.deconinck.info/post/2016/12/19/A-Dirt-Cheap-F-Aw... on page 3 of Google for "raspberry pi led", just below https://tech.scargill.net/home-control-2016/ https://tech.scargill.net/home-control-2016/ (both of which are interesting IMO)
- masklinn 9y ago
  First, you should consider whether you actually need that. Also note that it's not just reddit, HN uses the same system. Second, you can just perform an indirection through offset (+ limit = 1).
- grogers 9y ago
  When you are jumping large numbers of pages, you usually don't care about exact page boundaries. For example, you could have a background process that slowly paginates through the table and saves off the page boundaries, which you can then use for jumping into. They will always be a bit off especially near the middle of the table, but that's probably okay. There's lots of ways to do this fuzzy jumping that don't involve scanning the full table synchronously.
- awj 9y ago
  > How would you implement a deep pagination like "go to page 107" You wouldn't. Keyset pagination forces the user to do the exact same thing people complain about you doing to the database. Want page 10 of results? Too bad, enjoy running through pages 1-9 and throwing them away. Except now everything is even slower because it's users talking to the service, not the service talking to the db. There are plenty of use cases where that's a perfectly acceptable trade-off, or an annoying-but-necessary trade-off, but the people all up in a froth over the inefficiency of limit/offset seem to regularly fail to realize that they're advocating for a database performance solution with implications that reach all the way out to the UI/UX level. At a minimum, I see very little in the way of acknowledgement of this factor.
olalonde 9y ago
I recently implemented one for Node.js (as a Bookshelf.js plugin): https://github.com/binded/bookshelf-cursor-pagination https://github.com/binded/bookshelf-cursor-pagination. It supports multiple ordering as well.
- MarkusWinand 9y ago
  Thanks. I've added it to the Hall of Fame: http://use-the-index-luke.com/no-offset#frameworks http://use-the-index-luke.com/no-offset#frameworks
charlysisto 9y ago
I wrote nexter (https://github.com/charly/nexter https://github.com/charly/nexter) a few years ago without knowing I was applying the keyset principle (it's not pagination though but a next page library). It works well on a consistent data set but often falls short with Nulls, too many associations and whenever I aggregate stuff. Also it generates so many ANDs & ORs you do feel safer using Offset & Limit. I find it frustrating there's no easy solution provided by DBs for such a universal and simple need...
- MarkusWinand 9y ago
  Thanks. I've added it to the Hall of Fame: http://use-the-index-luke.com/no-offset#frameworks http://use-the-index-luke.com/no-offset#frameworks
bgentry 9y ago
Also Ruby: sequel-seek-pagination https://github.com/chanks/sequel-seek-pagination https://github.com/chanks/sequel-seek-pagination I used it just this week to write a Relay Connection paginator for graphql-ruby and Sequel/Postgres. It uses keyset pagination to reliably avoid skipping items and maintain consistent query times. Backwards pagination was a bit tricky since that’s not natively supported by sequel-seek-pagination. Basically I had to reverse the list order and then filter with the cursor values to get the last N items in a subquery, then reverse that list again to get back the desired sort order. https://github.com/rmosolgo/graphql-ruby/pull/1014 https://github.com/rmosolgo/graphql-ruby/pull/1014
- MarkusWinand 9y ago
  Thanks. I've added it to the Hall of Fame: http://use-the-index-luke.com/no-offset#frameworks http://use-the-index-luke.com/no-offset#frameworks
dom0 9y ago
I was unaware that this is called "keyset pagination". The name doesn't make much sense to me, could someone explain what it is supposed to mean and why it describes this pattern? I called this offset or key-offset pagination; it works well in any database with ordered indices (like B-trees), including ZODB.
MarkusWinand 9y ago
> any updates on this list since then? When I notice, I add/update this list. I'll check those mentioned in the reply's and add them if the seem to be right.
- j_s 9y ago
  Nice. I would recommend adding some sort of a 'last updated' type thing at the bottom of the article, along with a note that you update near that list.
  - MarkusWinand 9y ago
    I've added the other frameworks. As for the update marker, I've just added an "updated" date to next the the publishing date in the breadcrumb.

by j_s 9y ago

always_good 9y ago

Doesn't that strategy only work when you're paginating one page at a time, like Reddit? How would you implement a deep pagination like "go to page 107" unless you can derive page 107 from your order criteria?

wutwutwutwut 9y ago
This is true. It depends on application but I wonder how often users really need to go to Page 107 of something. Seems like maybe this use case should be solved with some kind of search instead. I have implemented offset pagination myself to several apps where being able to paginate by jumping to the page with records starting with "R" would make more sense.
- flukus 9y ago
  In most cases this is probably better, but sometimes there is no logical filter that works on you hvae to rely on sort order. One example might be a gallery, I might decide to look though pages 1-10 tonight, 20-30 tomorrow, etc. An arbitrary primary key is the only discriminator that makes sense here. Another example would be classical forum posts, they are sorted by last active but sometimes I'll want to jump to page 134 because that's where I got up to last time and bookmarked my place. That said, if users were doing this in my software I'd definitely be looking at how I can improve their workflow.
stubish 9y ago
We have this on one of our web apps, and it is a problem. To me the problem is why do we allow jumping to page 107 of 736 in the first place? How does this help someone? The two use cases are 1) user is manually performing a binary search on the results, 2) a bot needs to index the site. Both of these use cases are better solved in other ways, with either search forms that do not suck or index pages generated specifically for crawlers. So the answer to 'how would you implement a deep pagination like "go to page 107"' is to not implement that, and instead improve your design.
- kuschku 9y ago
  And that’s why Google’s pagination on search results is broken, and why so much usability was lost. Sometimes I want the lowest ranked results for a query, or the middle-of-the-field results. Let me have them.
  - gruez 9y ago
    >Sometimes I want the lowest ranked results for a query, or the middle-of-the-field results. What's the use case for that?
    - kuschku 9y ago
      Google results are gamed so much, that often personal blogs that don’t do SEO, but offer interesting content, appear beyond page 5 of Google search results, but are still relevant. Similar issues appear in many situations where search is used on third party results, but even on first-party results the first page is often gamed. I’ve been thinking about it, and ideally one would make a search engine that only indexes pages that have no analytics, tracking, ads, paywalls, etc. Then I’d find those same pages I’m searching for. To give an example: I found http://blog.deconinck.info/post/2016/12/19/A-Dirt-Cheap-F-Awesome-Led-Table http://blog.deconinck.info/post/2016/12/19/A-Dirt-Cheap-F-Aw... on page 3 of Google for "raspberry pi led", just below https://tech.scargill.net/home-control-2016/ https://tech.scargill.net/home-control-2016/ (both of which are interesting IMO)
masklinn 9y ago
First, you should consider whether you actually need that. Also note that it's not just reddit, HN uses the same system. Second, you can just perform an indirection through offset (+ limit = 1).
grogers 9y ago
When you are jumping large numbers of pages, you usually don't care about exact page boundaries. For example, you could have a background process that slowly paginates through the table and saves off the page boundaries, which you can then use for jumping into. They will always be a bit off especially near the middle of the table, but that's probably okay. There's lots of ways to do this fuzzy jumping that don't involve scanning the full table synchronously.
awj 9y ago
> How would you implement a deep pagination like "go to page 107" You wouldn't. Keyset pagination forces the user to do the exact same thing people complain about you doing to the database. Want page 10 of results? Too bad, enjoy running through pages 1-9 and throwing them away. Except now everything is even slower because it's users talking to the service, not the service talking to the db. There are plenty of use cases where that's a perfectly acceptable trade-off, or an annoying-but-necessary trade-off, but the people all up in a froth over the inefficiency of limit/offset seem to regularly fail to realize that they're advocating for a database performance solution with implications that reach all the way out to the UI/UX level. At a minimum, I see very little in the way of acknowledgement of this factor.

olalonde 9y ago

I recently implemented one for Node.js (as a Bookshelf.js plugin): https://github.com/binded/bookshelf-cursor-pagination https://github.com/binded/bookshelf-cursor-pagination. It supports multiple ordering as well.

MarkusWinand 9y ago
Thanks. I've added it to the Hall of Fame: http://use-the-index-luke.com/no-offset#frameworks http://use-the-index-luke.com/no-offset#frameworks

charlysisto 9y ago

I wrote nexter (https://github.com/charly/nexter https://github.com/charly/nexter) a few years ago without knowing I was applying the keyset principle (it's not pagination though but a next page library). It works well on a consistent data set but often falls short with Nulls, too many associations and whenever I aggregate stuff. Also it generates so many ANDs & ORs you do feel safer using Offset & Limit. I find it frustrating there's no easy solution provided by DBs for such a universal and simple need...

MarkusWinand 9y ago
Thanks. I've added it to the Hall of Fame: http://use-the-index-luke.com/no-offset#frameworks http://use-the-index-luke.com/no-offset#frameworks

bgentry 9y ago

Also Ruby: sequel-seek-pagination https://github.com/chanks/sequel-seek-pagination https://github.com/chanks/sequel-seek-pagination I used it just this week to write a Relay Connection paginator for graphql-ruby and Sequel/Postgres. It uses keyset pagination to reliably avoid skipping items and maintain consistent query times. Backwards pagination was a bit tricky since that’s not natively supported by sequel-seek-pagination. Basically I had to reverse the list order and then filter with the cursor values to get the last N items in a subquery, then reverse that list again to get back the desired sort order. https://github.com/rmosolgo/graphql-ruby/pull/1014 https://github.com/rmosolgo/graphql-ruby/pull/1014

MarkusWinand 9y ago
Thanks. I've added it to the Hall of Fame: http://use-the-index-luke.com/no-offset#frameworks http://use-the-index-luke.com/no-offset#frameworks

dom0 9y ago

I was unaware that this is called "keyset pagination". The name doesn't make much sense to me, could someone explain what it is supposed to mean and why it describes this pattern? I called this offset or key-offset pagination; it works well in any database with ordered indices (like B-trees), including ZODB.

MarkusWinand 9y ago

> any updates on this list since then? When I notice, I add/update this list. I'll check those mentioned in the reply's and add them if the seem to be right.

j_s 9y ago
Nice. I would recommend adding some sort of a 'last updated' type thing at the bottom of the article, along with a note that you update near that list.
- MarkusWinand 9y ago
  I've added the other frameworks. As for the update marker, I've just added an "updated" date to next the the publishing date in the breadcrumb.