Keyset Pagination with Spring
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, I’m going to show you how you can use the Keyset Pagination technique with Spring or Spring Boot.
While the default offset-based pagination provided by Spring Data PagingAndSortingRepository
is useful in many situations, if you have a large result set that you have to iterate over, then the Keyset Pagination or Seek Method technique provides better performance.
What is Keyset Pagination
As explained in this article, Keyset Pagination or Seek Method allows us to use an index when seeking the first element of a given page that we want to load.
A Top-N Keyset Pagination query that loads the latest 25 Post
entities looks as follows:
SELECT id, title, created_on FROM post ORDER BY created_on DESC, id DESC FETCH FIRST 25 ROWS ONLY
And the Next-N query that loads the second, third, or nth page looks like this:
SELECT id, title, created_on FROM post WHERE (created_on, id) < (:previousCreatedOn, :previousId) ORDER BY created_on DESC, id DESC FETCH FIRST 25 ROWS ONLY
As you can see, the Keyset Pagination queries are DB-specific, so we need a framework that can provide us with the API that abstracts this functionality while generating the proper SQL queries for each supported relational database.
That framework is called Blaze Persistence, and it supports Keyset Pagination for JPA entity queries.
How to use Keyset Pagination with Spring
When using Spring, the data access logic is implemented using Spring Data Repositories. Therefore, the basic data access methods are defined by the JpaRepository
, and the custom logic can be abstracted in one or more custom Spring Data Repository classes.
The PostRepository
is the Post
entity Data Access Object, and it looks like this:
@Repository public interface PostRepository extends JpaRepository<Post, Long>, CustomPostRepository { }
As explained in this article, if we want to provide extra data access methods, we can make the PostRepository
extend a CustomPostRepository
where we will define the custom data access logic.
The CustomPostRepository
looks as follows:
public interface CustomPostRepository { PagedList<Post> findTopN( Sort sortBy, int pageSize ); PagedList<Post> findNextN( Sort orderBy, PagedList<Post> previousPage ); }
And the CustomPostRepositoryImpl
class that implements the CustomPostRepository
interface looks as follows:
public class CustomPostRepositoryImpl implements CustomPostRepository { @PersistenceContext private EntityManager entityManager; @Autowired private CriteriaBuilderFactory criteriaBuilderFactory; @Override public PagedList<Post> findTopN( Sort sortBy, int pageSize) { return sortedCriteriaBuilder(sortBy) .page(0, pageSize) .withKeysetExtraction(true) .getResultList(); } @Override public PagedList<Post> findNextN( Sort sortBy, PagedList<Post> previousPage) { return sortedCriteriaBuilder(sortBy) .page( previousPage.getKeysetPage(), previousPage.getPage() * previousPage.getMaxResults(), previousPage.getMaxResults() ) .getResultList(); } private CriteriaBuilder<Post> sortedCriteriaBuilder( Sort sortBy) { CriteriaBuilder<Post> criteriaBuilder = criteriaBuilderFactory .create(entityManager, Post.class); sortBy.forEach(order -> { criteriaBuilder.orderBy( order.getProperty(), order.isAscending() ); }); return criteriaBuilder; } }
The ForumService
uses the PostRepository
KeySet Pagination methods like this:
@Service @Transactional(readOnly = true) public class ForumService { @Autowired private PostRepository postRepository; public PagedList<Post> firstLatestPosts( int pageSize) { return postRepository.findTopN( Sort.by( Post_.CREATED_ON ).descending().and( Sort.by( Post_.ID ).descending() ), pageSize ); } public PagedList<Post> findNextLatestPosts( PagedList<Post> previousPage) { return postRepository.findNextN( Sort.by( Post_.CREATED_ON ).descending().and( Sort.by( Post_.ID ).descending() ), previousPage ); } }
Testing Time
Assuming we have created 50 Post
entities:
LocalDateTime timestamp = LocalDateTime.of( 2021, 12, 30, 12, 0, 0, 0 ); LongStream.rangeClosed(1, POST_COUNT).forEach(postId -> { Post post = new Post() .setId(postId) .setTitle( String.format( "High-Performance Java Persistence - Chapter %d", postId ) ) .setCreatedOn( Timestamp.valueOf(timestamp.plusMinutes(postId)) ); entityManager.persist(post); });
When loading the first page, we get the expected result:
PagedList<Post> topPage = forumService.firstLatestPosts(PAGE_SIZE); assertEquals(POST_COUNT, topPage.getTotalSize()); assertEquals(POST_COUNT / PAGE_SIZE, topPage.getTotalPages()); assertEquals(1, topPage.getPage()); List<Long> topIds = topPage.stream().map(Post::getId).toList(); assertEquals(Long.valueOf(50), topIds.get(0)); assertEquals(Long.valueOf(49), topIds.get(1));
And, the SQL query that was executed on PostgreSQL looks as follows:
SELECT p.id AS col_0_0_, p.created_on AS col_1_0_, p.id AS col_2_0_, ( SELECT count(*) FROM post post1_ ) AS col_3_0_, p.id AS id1_0_, p.created_on AS created_2_0_, p.title AS title3_0_ FROM post p ORDER BY p.created_on DESC, p.id DESC LIMIT 25
When loading the second page, we get the next latest 25 Post
entities:
PagedList<Post> nextPage = forumService.findNextLatestPosts(topPage); assertEquals(2, nextPage.getPage()); List<Long> nextIds = nextPage.stream().map(Post::getId).toList(); assertEquals(Long.valueOf(25), nextIds.get(0)); assertEquals(Long.valueOf(24), nextIds.get(1));
And the underlying SQL query looks as follows:
SELECT p.id AS col_0_0_, p.created_on AS col_1_0_, p.id AS col_2_0_, ( SELECT count(*) FROM post post1_ ) AS col_3_0_, p.id AS id1_0_, p.created_on AS created_2_0_, p.title AS title3_0_ FROM post p WHERE (p.created_on, p.id) < ('2021-12-30 12:26:00.0', 26) AND 0=0 ORDER BY p.created_on DESC, p.id DESC LIMIT 25
Cool, right?
I'm running an online workshop on the 11th of October about High-Performance SQL.If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
Keyset Pagination is very useful when implementing an infinite scrolling solution, and while there is no built-in support for it in Spring Data, we can easily implement it ourselves using Blaze Persistence and custom Spring Data Repositories.
