Spring Boot REST API: How to efficiently paginate through large datasets using Querydsl?
I'm converting an old project and I'm currently building a Spring Boot REST API that retrieves large datasets from a PostgreSQL database, and I'm trying to implement pagination. I've decided to use Querydsl for type-safe queries, but I'm facing issues with performance when handling requests for pages with a large number of entries. My `Pageable` implementation seems to be working, but the response time for the last few pages is significantly slower, and I'm getting `SQLTimeoutException` when the page size is large. Here’s a snippet of my repository method: ```java public interface UserRepository extends JpaRepository<User, Long>, QuerydslPredicateExecutor<User> { Page<User> findAll(Predicate predicate, Pageable pageable); } ``` And here’s how I call it in my service: ```java public Page<User> getUsers(Predicate predicate, Pageable pageable) { return userRepository.findAll(predicate, pageable); } ``` I’m currently using Querydsl 5.0.0 and Spring Data JPA 2.5.0. The dataset is quite large, with millions of entries, and I noticed that when I request pages towards the end, it takes much longer to retrieve results (e.g., the last page often takes over 10 seconds). I've tried optimizing the database indexes used in these queries, but that hasn't seemed to help. Could this be a result of how Querydsl generates queries, or is there a better way to handle pagination for a large dataset in Spring Boot? Any suggestions on improving performance, or maybe alternative approaches I could consider would be greatly appreciated. Could this be a known issue?