search

spring-projects / spring-data-commons

Spring Data Commons. Interfaces and code shared between the various datastore specific implementations.
https://spring.io/projects/spring-data
Apache License 2.0
778 stars 675 forks source link

Add support for keyset-based scrolling #2151

Closed spring-projects-issues closed 1 year ago

spring-projects-issues commented 4 years ago

Jens Schauder opened DATACMNS-1729 and commented

Value based pagination basically uses queries of the form: select n rows after the one with id value :id

On relational databases these kind of queries show better performance than the typical: select n rows after the first m especially when m is large.

It is likely that other stores benefit as well

No further details from DATACMNS-1729

spring-projects-issues commented 3 years ago

Junho, Kim commented

Is there any update?

terence-sk commented 3 years ago

wow, I found this 6 years old article asking for this kind of feature and stating that it is not very well known in the field, guess it is true

https://use-the-index-luke.com/no-offset

Kidlike commented 3 years ago

@terence-sk same here... this link was referenced today in a hackernews thread :)

It would be amazing to have this in spring-data.

About a year ago in our company project, we imported a lot of data into our database, and then all paginated queries were suffering. Going through pages was extremely slow, and after some page, requests were timing-out at 30sec.

We had to move paginated queries to elasticsearch in order to avoid offset paging that is used in spring-data (we duplicate most of our sql data in elasticsearch anyway for full-text-search support). It's a bit ugly code and architecture, but it runs in constant time (~150ms) regardless of which page you're on or the sorting criteria.

yinchenlan commented 3 years ago

Any thoughts on when keyset-based pagination will become available?

petromir commented 2 years ago

@mp911de I know that you are pretty busy with other stuff, but any plans to implement that soon and integrated in spring-data-jdbc?

mp911de commented 2 years ago

I have no idea. We're currently busy with other topics that are required for Spring Data 3.0. If someone is interested in picking up this theme and crafting a pull request along with an implementation for JPA, Mongo, or Spring Data JDBC, we'd be happy to provide additional guidance.

petromir commented 2 years ago

@mp911de I would like to help with this, but:

  1. The implementation could take more than a month
  2. I could handle only spring-data-commons and spring-data-jdbc

Is this ok for you?

mp911de commented 2 years ago

Any kind of progress is appreciated. You might want to explore existing keyset pagination implementations to collect some ideas.

petromir commented 2 years ago

@mp911de Could you please point me to specific code. Any other guidance will be highly appreciated.

germanflighel commented 2 years ago

Hi! I'd also be interested in collaborating, but have no real clue how to! Any kind of support would be really helpful!

mp911de commented 1 year ago

Keyset pagination is not pagination in the sense of page number and page size but rather an approach to resume cursor consumption where we've left off the last time.

Taking a step back, keyset cursoring introduces the concept of consumption windows of data. We should make this concept explicit instead of squeezing cursoring into pagination. Another aspect of this is that we can provide means for index/offset-based cursoring and we can retrofit Pageable to become usable with the new Cursor API as page number, page size and a stable sort are ideal to compute a cursor offset.

christophstrobl commented 1 year ago

closed by: #2787