Preventing Heap Exhaustion: Implementing Efficient Pagination in Hibernate

The Danger of the Unbounded Result Set

A common failure point in Java applications using Hibernate is the "all-or-nothing" retrieval pattern. When a developer calls list() or getResultList() on a query targeting a table with millions of rows, Hibernate attempts to load every entity into the persistence context (the first-level cache). This quickly leads to OutOfMemoryError as the JVM heap is exhausted by thousands of managed objects.

The solution is to shift the burden of data filtering from the application memory to the database engine using pagination. By limiting the result set at the SQL level, you ensure the application only handles a manageable slice of data at any given time.

Offset-Based Pagination with Query API

Hibernate provides a standardized way to handle pagination via the setFirstResult() and setMaxResults() methods. These methods are dialect-aware, meaning Hibernate translates them into the specific syntax required by your database (e.g., LIMIT/OFFSET for PostgreSQL or OFFSET/FETCH NEXT for SQL Server).

• setFirstResult(int startPosition): Defines the index of the first result to be retrieved. This is the "offset."
• setMaxResults(int maxResults): Defines the maximum number of entities to be returned in the list. This is the "page size."

Crucially, pagination must be paired with an OrderBy clause. Without a deterministic sort order, the database may return records in a different sequence between requests, causing some records to appear on multiple pages while others are skipped entirely.

Practical Implementation: Spring Data JPA

While the raw Hibernate API works, Spring Data JPA abstracts this into a Pageable object, reducing boilerplate code in the service layer.

Example Configuration

In this example, we retrieve a paginated list of Product entities from a repository. This code should be executed within a service class with appropriate database permissions to read the target table.

// Repository Interface
public interface ProductRepository extends JpaRepository<Product, Long> {
    // Spring Data JPA automatically handles the pagination logic
    Page<Product> findByCategory(String category, Pageable pageable);
}

// Service Layer Implementation
public Page<Product> getProductsByCategory(String category, int page, int size) {
    // Create a PageRequest with sorting to ensure deterministic results
    Pageable pageable = PageRequest.of(page, size, Sort.by("createdAt").descending());
    
    // The returned Page object contains the content and metadata (total elements, total pages)
    return productRepository.findByCategory(category, pageable);
}

Verifying the SQL Output

To ensure Hibernate is not fetching all records and filtering them in memory, enable SQL logging in your application.properties:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Check the logs for the presence of LIMIT and OFFSET (or equivalent) clauses. If these are missing, Hibernate may be performing "in-memory pagination," which offers no memory protection.

The "Join Fetch" Performance Trap

A critical limitation occurs when using JOIN FETCH on a OneToMany relationship while paginating. Because a join fetch creates a Cartesian product (multiple rows for one parent entity), Hibernate cannot safely apply a LIMIT clause to the SQL without potentially cutting off child records.

When this happens, Hibernate logs a warning: "firstResult/maxResults specified with collection fetch; applying in memory!". In this scenario, Hibernate pulls the entire dataset into memory and then discards the unwanted rows. This completely negates the purpose of pagination and will trigger the heap exhaustion you were trying to avoid.

Decision Matrix: Offset vs. Keyset Pagination

Approach	Mechanism	Pros	Cons
Offset-based	OFFSET X LIMIT Y	Easy to implement; allows jumping to specific pages.	Performance degrades as offset increases (DB must scan skipped rows).
Keyset-based	WHERE id > last_id LIMIT Y	Constant time performance regardless of depth.	Cannot jump to a specific page (e.g., "Page 500").

Actionable Summary

To protect your application from memory exhaustion, always apply setMaxResults() to queries targeting large tables. Ensure every paginated query has a explicit sort order to prevent data duplication. If you encounter the "in-memory pagination" warning during a join fetch, switch to two separate queries: one to fetch the paginated IDs of the parents, and a second to fetch the children for those specific IDs.