Paginating Large Result Sets with Spring Data JPA: A Practical Guide

Problem: Loading Thousands of Rows Exhausts Memory

When a REST endpoint calls repository.findAll() on a table with tens of thousands of rows, Spring Data JPA materializes the entire result set as a list of entities. The JVM heap spikes, garbage collection becomes frequent, and response times climb. Users experience timeouts or out‑of‑memory errors.

Thesis: Use Spring Data JPA’s Pageable to fetch data in chunks

By accepting a Pageable parameter, Spring Data JPA adds LIMIT and OFFSET clauses to the SQL and returns a Page<T> that contains the current slice plus metadata (total elements, total pages). This keeps memory usage bounded to the page size.

1. Repository: Declare a Paginated Method

Extend JpaRepository (or PagingAndSortingRepository) and keep the default findAll(Pageable) method. No extra code is needed.

public interface UserRepository extends JpaRepository {
    // findAll(Pageable) is inherited
}

2. Service Layer: Accept Pageable and Return Page

Inject the repository, delegate the call, and return the Page to the controller. This keeps the service agnostic of web concerns.

@Service
@RequiredArgsConstructor
public class UserService {
    private final UserRepository userRepository;

    public Page getUsers(Pageable pageable) {
        return userRepository.findAll(pageable);
    }
}

3. Controller: Bind Request Parameters to Pageable

Spring MVC can automatically convert request parameters page, size, and sort into a Pageable object. Expose them as query parameters.

@RestController
@RequestMapping("/api/users")
@RequiredArgsConstructor
public class UserController {
    private final UserService userService;

    @GetMapping
    public ResponseEntity> listUsers(Pageable pageable) {
        Page page = userService.getUsers(pageable);
        return ResponseEntity.ok(page);
    }
}

4. Worked Example: From Entity to cURL

Assume a simple User entity:

@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;
    // getters and setters omitted
}

Start the application (e.g., ./mvnw spring-boot:run) and request the first page with 20 items, sorted by name ascending:

curl -s "http://localhost:8080/api/users?page=0&size=20&sort=name,asc" | jq .

The JSON response includes:

• content: array of up to 20 User objects
• pageable: pagination details (page number, size, sort)
• totalElements: total rows in the table
• totalPages: derived from totalElements and size

To verify the generated SQL, enable Hibernate logging (logging.level.org.hibernate.SQL=debug) or use a DataSource proxy. You will see statements similar to:

select user0_.id as id1_0_, user0_.name as name2_0_, user0_.email as email3_0_
from user user0_
order by user0_.name asc
limit ? offset ?

select count(*) as col_0_0_ from user user0_

The first query uses the supplied LIMIT and OFFSET; the second is the count query for totalElements.

5. Trade‑offs and Limitations

• Count query overhead: Each page request triggers an additional SELECT COUNT(*). On very large tables this can become expensive unless an appropriate index exists or you provide a custom count query via @Query with a countQuery attribute.
• Page size tuning: A size that is too large defeats pagination’s memory benefit; a size that is too small increases round‑trips and latency. Start with a size that matches your typical payload (e.g., 20‑50 items) and adjust based on observed latency and throughput.
• Offset pagination limits: Deep offsets (high page numbers) can degrade performance because the database must skip many rows. For keyset‑based scrolling consider using Slice or a custom WHERE id > :lastId approach.

6. Actionable Closing: Verify and Deploy

1. Add the Pageable parameter to your repository method (inherited is fine).
2. Expose page, size, and sort query parameters in your controller.
3. Run a @DataJpaTest to assert that the generated SQL contains LIMIT and OFFSET and that the returned Page has the expected content size.
4. Enable SQL logging in a staging environment to confirm the count query is issued and to monitor its execution time.
5. Adjust page size based on payload measurements; consider adding a covering index on the columns used in ORDER BY if the count query is slow.

By following these steps, you keep memory usage predictable, improve response times, and retain the ability to expose rich pagination metadata to API consumers.