Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for keyset-based scrolling #2151

Closed
spring-projects-issues opened this issue May 18, 2020 · 12 comments
Closed

Add support for keyset-based scrolling #2151

spring-projects-issues opened this issue May 18, 2020 · 12 comments
Assignees
Labels
theme: 3.1 type: enhancement A general enhancement

Comments

@spring-projects-issues
Copy link

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
Copy link
Author

Junho, Kim commented

Is there any update?

@terence-sk
Copy link

terence-sk commented Jan 5, 2021

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
Copy link

Kidlike commented Jan 27, 2021

@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 mp911de changed the title Add support for value bases pagination [DATACMNS-1729] Add support for keyset-based pagination [DATACMNS-1729] Sep 2, 2021
@yinchenlan
Copy link

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

@petromir
Copy link

petromir commented Jan 26, 2022

@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
Copy link
Member

mp911de commented Jan 27, 2022

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
Copy link

petromir commented Jan 28, 2022

@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
Copy link
Member

mp911de commented Jan 28, 2022

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

@petromir
Copy link

petromir commented Jan 28, 2022

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

@germanflighel
Copy link

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

@mp911de mp911de assigned mp911de and unassigned schauder Feb 27, 2023
@mp911de mp911de changed the title Add support for keyset-based pagination [DATACMNS-1729] Add support for keyset-based pagination Feb 27, 2023
@mp911de
Copy link
Member

mp911de commented Feb 27, 2023

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.

@mp911de mp911de linked a pull request Mar 6, 2023 that will close this issue
4 tasks
@mp911de mp911de changed the title Add support for keyset-based pagination Add support for keyset-based scrolling Mar 8, 2023
christophstrobl pushed a commit that referenced this issue Mar 16, 2023
See: #2151
Original Pull Request: #2787
christophstrobl added a commit that referenced this issue Mar 16, 2023
The intend of WindowIterator is to support users who need to iterate multiple windows. It keeps track of the position and loads the next window if needed so that the user does not have to interact with the position at all.

Also remove the Window methods to get the first/last position and enforce the index based variant.

Update the documentation to make use of the newly introduced API.

See: #2151
Original Pull Request: #2787
@christophstrobl
Copy link
Member

closed by: #2787

@christophstrobl christophstrobl added this to the 3.1 M3 (2023.0.0) milestone Mar 16, 2023
michael-simons added a commit to spring-projects/spring-data-neo4j that referenced this issue Mar 17, 2023
Discussed spring-projects/spring-data-commons#2151 and implemented in spring-projects/spring-data-commons#2787 we can build on top and provide basic support for both imperative and reactive repositories.

The support will be available only on the repository level in the first iteration, think

```java
import java.util.UUID;

import org.springframework.data.domain.ScrollPosition;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Window;
import org.springframework.data.neo4j.repository.Neo4jRepository;

public interface ScrollingRepository extends Neo4jRepository<ScrollingEntity, UUID> {
	Window<ScrollingEntity> findTop4By(Sort sort, ScrollPosition position);
}
```

and other derived finder methods that have a limit and a stable sort.

If requested, further support can be added to the templates, too.

Closes #2691.
michael-simons added a commit to spring-projects/spring-data-neo4j that referenced this issue Mar 20, 2023
Discussed in spring-projects/spring-data-commons#2151 and implemented in spring-projects/spring-data-commons#2787 we can build on top and provide basic support for both imperative and reactive repositories.

The support will be available only on the repository level in the first iteration, think

```java
import java.util.UUID;

import org.springframework.data.domain.ScrollPosition;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Window;
import org.springframework.data.neo4j.repository.Neo4jRepository;

public interface ScrollingRepository extends Neo4jRepository<ScrollingEntity, UUID> {
	Window<ScrollingEntity> findTop4By(Sort sort, ScrollPosition position);
}
```

and other derived finder methods that have a limit and a stable sort.

If requested, further support can be added to the templates, too.

Closes #2691.
schauder pushed a commit to spring-projects/spring-data-jpa that referenced this issue Mar 29, 2023
trayanus1026 pushed a commit to trayanus1026/spring-data-jpa-java that referenced this issue Aug 5, 2023
Closes #2878
Original pull request #2885
See spring-projects/spring-data-commons#2151
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
theme: 3.1 type: enhancement A general enhancement
Projects
None yet
9 participants