Spring Data JPA: Keyset Pagination (Scroll API)
Traditionally, pagination in Spring Data JPA was done using Pageable and offset-based pagination. However, for large datasets, this approach can suffer from performance issues due to high offset values, as the database must scan and skip all rows preceding the offset.
Keyset pagination, introduced in Spring Data using the Scroll API (ScrollPosition), is a highly efficient alternative. It uses the keys (typically indexed columns) of the last retrieved row to query the next batch, resulting in constant time complexity regardless of the page number.
This tutorial demonstrates how to use the Spring Data Scroll API to implement high-performance keyset pagination with Spring Boot and PostgreSQL.
Entity Definition
We will use an Article entity that has attributes such as author and views. We will implement pagination to scroll through articles sorted by their views.
In Article:
@Entity
@Table(name = "articles")
class Article {
@Id
private UUID id;
private String title;
private String author;
private int views;
}Repository Interface
To use keyset pagination, we define a repository method that accepts a ScrollPosition parameter and returns a Window<T>. A Window provides access to the paginated elements and a method to determine the position of elements, enabling scrolling to the next page.
interface ArticleRepository extends Repository<Article, UUID> {
Window<Article> findTop2ByAuthorOrderByViewsDesc(String author, ScrollPosition position);
}The query restricts the result size (e.g., using Top2) to fetch data in manageable chunks.
Scrolling Through Pages
With keyset pagination, you retrieve the first page by passing an initial ScrollPosition.keyset(). To fetch the subsequent page, you extract the ScrollPosition of the last item in the current window and pass it to the next query.
We will verify this behavior using an integration test with @Testcontainers and PostgreSQL.
@Testcontainers
@DataJpaTest(properties = "spring.jpa.hibernate.ddl-auto=create-drop")
@Sql(
statements = {
"INSERT INTO articles (id, title, author, views) VALUES (gen_random_uuid(), 'Spring Boot Keyset Pagination', 'Rashidi Zin', 1000)",
"INSERT INTO articles (id, title, author, views) VALUES (gen_random_uuid(), 'Mastering Spring Data JPA', 'Rashidi Zin', 800)",
"INSERT INTO articles (id, title, author, views) VALUES (gen_random_uuid(), 'Java 25 Features', 'Rashidi Zin', 1200)",
"INSERT INTO articles (id, title, author, views) VALUES (gen_random_uuid(), 'Testcontainers with Spring Boot', 'Rashidi Zin', 900)",
"INSERT INTO articles (id, title, author, views) VALUES (gen_random_uuid(), 'Introduction to Docker', 'Jane Doe', 500)"
},
executionPhase = BEFORE_TEST_CLASS
)
class ArticleRepositoryTests {
@Container
@ServiceConnection
private static final PostgreSQLContainer postgres = new PostgreSQLContainer(DockerImageName.parse("postgres:latest"));
@Autowired
private ArticleRepository repository;
@Test
@DisplayName("Given articles exist When fetching by author using keyset pagination Then should return ordered window")
void fetchUsingKeysetPagination() {
var firstPage = repository.findTop2ByAuthorOrderByViewsDesc("Rashidi Zin", ScrollPosition.keyset());
assertThat(firstPage.toList()).hasSize(2)
.extracting("title").containsOnly("Java 25 Features", "Spring Boot Keyset Pagination");
var position = firstPage.positionAt(1);
var secondPage = repository.findTop2ByAuthorOrderByViewsDesc("Rashidi Zin", position);
assertThat(secondPage.toList()).hasSize(2)
.extracting("title").containsOnly("Testcontainers with Spring Boot", "Mastering Spring Data JPA");
}
}