spring-data-commons
spring-data-commons copied to clipboard
spring-projects
Add support for keyset-based pagination [DATACMNS-1729]
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
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
@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.
@mp911de I know that you are pretty busy with other stuff, but any plans to implement that soon and integrated in spring-data-jdbc?
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.
@mp911de I would like to help with this, but:
- The implementation could take more than a month
- I could handle only spring-data-commons and spring-data-jdbc
Is this ok for you?
Any kind of progress is appreciated. You might want to explore existing keyset pagination implementations to collect some ideas.
@mp911de Could you please point me to specific code. Any other guidance will be highly appreciated.
Hi! I'd also be interested in collaborating, but have no real clue how to! Any kind of support would be really helpful!
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.
