Preventing Memory Exhaustion with Spring Data JPA Pagination

The Problem: Unbounded Result Sets

Retrieving all records from a database table into application memory is a common cause of OutOfMemoryError in Spring applications. As a dataset grows, a simple findAll() call that worked during development will eventually crash the JVM in production. To ensure scalability, APIs must enforce strict limits on the amount of data retrieved in a single request.

Minimum Viable Design for Pagination

The most straightforward way to implement data limiting in Spring Data JPA is through the Pageable interface. This allows the application to pass pagination parameters (page number and size) directly from the REST controller to the database query.

Repository Implementation

Instead of returning a List<T>, the repository method should return a Page<T> or Slice<T>. This signals to Spring Data JPA to append LIMIT and OFFSET clauses to the generated SQL.

public interface ProductRepository extends JpaRepository<Product, Long> {
    // Returns total count and content
    Page<Product> findByCategory(String category, Pageable pageable);

    // Returns content and whether a next page exists (no count query)
    Slice<Product> findByNameContaining(String name, Pageable pageable);
}

Controller Integration

Spring Boot automatically resolves Pageable parameters from query strings (e.g., ?page=0&size=20&sort=price,desc). However, accepting these parameters blindly creates a security risk.

Trust and Data Boundaries

Allowing a client to specify an arbitrary size parameter can lead to a Denial of Service (DoS) attack. A request for size=1000000 could force the database to allocate massive amounts of memory and the JVM to crash while attempting to serialize the response.

Constraint Enforcement: Always define a maximum page size. This can be done via application properties or a custom interceptor.

// application.properties
spring.data.web.pageable.max-page-size=100

If you need dynamic limits based on user roles, implement a validation check in the service layer to cap the Pageable.getPageSize() before passing it to the repository.

Operational Checks and Performance

To verify that pagination is working at the database level rather than in-memory, enable SQL logging in your development environment:

spring.jpa.show-sql=true
logging.level.org.hibernate.SQL=DEBUG

Check the logs for the following patterns:

• LIMIT/OFFSET: Ensure the SQL contains clauses like LIMIT 20 OFFSET 40. If these are missing, the application is fetching all rows and filtering them in Java.
• Count Queries: When using Page<T>, Spring executes a second query: SELECT count(*) FROM .... On tables with millions of rows, this count query can become slower than the data retrieval itself.

Page vs. Slice Comparison

Feature	Page<T>	Slice<T>
Count Query	Yes (Automatic)	No
Total Pages Info	Available	Unavailable
Performance	Slower on large sets	Faster
Use Case	Numbered pagination UI	Infinite scroll / "Load More"

Failure Modes and Design Pivots

Offset-based pagination (the default for Pageable) suffers from Linear Performance Degradation. As the offset increases (e.g., page 10,000), the database must still scan all preceding rows before discarding them to reach the requested offset.

When to Pivot to Keyset Pagination

You should move away from Pageable and toward Keyset Pagination (also known as Cursor-based pagination) when:

1. The dataset exceeds several million records.
2. Users frequently access deep pages of data.
3. Data is highly volatile (new records inserted frequently cause items to shift between pages, leading to duplicate results for the user).

In a keyset design, instead of OFFSET 1000, you query for records where the ID is greater than the last ID seen on the previous page: WHERE id > :lastSeenId LIMIT 20. This allows the database to utilize the primary key index directly, maintaining constant performance regardless of depth.