Add support for keyset-based scrolling

[spring-projects-issues]

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]

Junho, Kim commented

Is there any update?

[terence-sk]

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]

@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]

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

[petromir]

@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]

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]

@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]

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

[petromir]

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

[germanflighel]

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