Stopping the Heap Crash: Implementing Pagination in Hibernate

The Memory Exhaustion Trap

A common failure pattern in Java applications occurs when a developer calls .getResultList() on a Hibernate query that targets a table with millions of rows. Because Hibernate attempts to load the entire result set into the JVM heap as managed entities, the application quickly hits an OutOfMemoryError. Even if the heap is large enough to hold the data, the resulting garbage collection pressure creates massive latency spikes, effectively freezing the application.

The solution is to shift the data-filtering responsibility from the application layer to the database engine using pagination. By limiting the number of records retrieved per request, you maintain a constant memory footprint regardless of the total dataset size.

Offset-Based Pagination with Query Interface

Hibernate provides a standardized way to handle pagination through the setFirstResult() and setMaxResults() methods. These methods abstract the underlying SQL dialect, meaning the same Java code works whether your backend is PostgreSQL (which uses LIMIT/OFFSET) or Oracle (which uses OFFSET/FETCH).

• setFirstResult(int startPosition): Defines the index of the first result to be retrieved. This is a zero-based index.
• setMaxResults(int maxResults): Defines the maximum number of entities the query should return.

Implementation Example

To implement this, you typically wrap the logic in a service layer that accepts a page number and a page size. The following example assumes Hibernate 5.x or 6.x using a standard Session or EntityManager.

// Run this in your Service/DAO layer with standard DB permissions
public List<User> fetchUsersPage(int pageNumber, int pageSize) {
    // Calculate the offset based on page number
    int offset = (pageNumber - 1) * pageSize;

    return session.createQuery("FROM User u ORDER BY u.id ASC", User.class)
                  .setFirstResult(offset)
                  .setMaxResults(pageSize)
                  .getResultList();
}

Verification and Diagnostics

To verify that Hibernate is actually delegating the work to the database rather than filtering in memory, enable SQL logging in your application.properties or hibernate.cfg.xml:

hibernate.show_sql=true
hibernate.format_sql=true

When running the code above, check the logs for a clause similar to LIMIT 10 OFFSET 20. If you see a query without these clauses, Hibernate may be performing "in-memory pagination," which does not solve the memory exhaustion problem.

The Performance Wall: Offset vs. Keyset

While setFirstResult() is easy to implement, it has a critical limitation: performance degradation at scale. In offset-based pagination, the database must still scan through all the preceding rows to reach the requested offset. If you request page 10,000, the database reads 100,000 rows only to discard the first 99,990.

Comparison Table: Pagination Strategies

Strategy	Mechanism	Pros	Cons
Offset-Based	OFFSET X LIMIT Y	Easy to jump to specific pages.	Slows down as offset increases.
Keyset (Seek)	WHERE id > last_id LIMIT Y	Constant performance.	Cannot jump to a random page.

Critical Limitations

Be cautious when using pagination with complex JOIN FETCH queries. If you fetch a collection (e.g., User and their Orders) using a join, the database returns a Cartesian product. Hibernate will attempt to deduplicate these results in memory to provide the correct number of User entities, which can lead to the very OutOfMemoryError you are trying to avoid. For these cases, it is safer to paginate the root entity first and then fetch associated collections in a separate query.

Actionable Summary

To prevent memory exhaustion, never call getResultList() on unbounded queries. Use setMaxResults() to cap the response size and setFirstResult() for basic paging. For datasets exceeding 100,000 rows where users only scroll forward, replace offset-based pagination with a Keyset (Seek) method by filtering on a unique, sorted ID.