Avoiding Memory Exhaustion: Pagination Strategies in Hibernate

The Danger of the 'Fetch All' Pattern

When building a Java application with Hibernate, it is tempting to simply call findAll() or execute a HQL query that returns a List<Entity>. For a few hundred records, this works. For a production dataset of 500,000 records, this is a recipe for an OutOfMemoryError (OOME). Even if the JVM heap can hold the data, the latency involved in transferring that volume of data from the database to the application server will likely trigger request timeouts.

The goal is to ensure that the database—not the Java application—handles the filtering and limiting of the result set. This ensures the application only ever holds a small, manageable slice of data in memory.

Offset-Based Pagination

The most common way to implement pagination in Hibernate is using setFirstResult() and setMaxResults(). These methods instruct Hibernate to append the appropriate limit and offset clauses to the generated SQL based on the configured database dialect (e.g., LIMIT and OFFSET in PostgreSQL).

Example: Implementing a Page Request

// Run this in your Service or DAO layer
// Required permissions: Standard DB read access
public List<User> getUsersPage(int pageNumber, int pageSize) {
    int offset = pageNumber * pageSize;
    return session.createQuery("FROM User u ORDER BY u.id ASC", User.class)
                  .setFirstResult(offset)
                  .setMaxResults(pageSize)
                  .getResultList();
}

Expected Check: If you enable hibernate.show_sql=true in your configuration, you should see a SQL query ending in something like LIMIT 20 OFFSET 100. If you do not see these keywords, Hibernate may be attempting to paginate in memory.

The 'In-Memory Pagination' Trap

A critical risk occurs when combining pagination with JOIN FETCH on a one-to-many relationship. If you attempt to fetch a parent entity and its collection of children in a single query while applying setMaxResults(), Hibernate may be unable to translate the limit to SQL because the join creates a Cartesian product (multiple rows per parent).

In this scenario, Hibernate will log a warning: HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

This is dangerous. Hibernate will execute a query that retrieves every single record from the database and then discard the ones it doesn't need inside the JVM. This completely defeats the purpose of pagination and will lead to memory exhaustion as the table grows.

Scaling Further: Keyset Pagination

Offset-based pagination degrades in performance as the page number increases. To retrieve the 1,000th page, the database must still scan through the first 999 pages before discarding them. For massive datasets, Keyset Pagination (also known as the Seek Method) is the preferred engineering decision.

Instead of an offset, you filter by the unique identifier of the last record from the previous page.

Comparison: Offset vs. Keyset

Feature	Offset-Based	Keyset-Based
Implementation	Simple (setFirstResult)	Custom WHERE clause
Performance	Slows down on deep pages	Constant performance
Consistency	Items shift if rows are deleted	Stable anchor point

Example: Keyset Query

// Fetch the next page starting after the last ID seen
// Placeholder: lastSeenId is the ID of the last element of the previous page
public List getUsersNextPage(Long lastSeenId, int pageSize) {
    return session.createQuery("FROM User u WHERE u.id > :lastId ORDER BY u.id ASC", User.class)
                  .setParameter("lastId", lastSeenId)
                  .setMaxResults(pageSize)
                  .getResultList();
}

Verification and Limitations

To verify your pagination is working correctly, monitor the JVM heap usage using a tool like VisualVM or JConsole while requesting the first page versus the last page. If memory spikes significantly on the last page, you are likely experiencing in-memory pagination or deep-offset performance degradation.

Limitations: Keyset pagination requires a strictly ordered, unique column (like a primary key). It does not support "jumping" to a specific page number (e.g., Page 500) because it only knows how to find the next set of records relative to a known point.

Actionable Summary

• Use setFirstResult and setMaxResults for small-to-medium datasets.
• Never use JOIN FETCH on collections when paginating; use two separate queries or a BatchSize configuration instead.
• Switch to Keyset pagination for tables with millions of rows to avoid the "deep paging" performance cliff.
• Always verify the generated SQL to ensure LIMIT or OFFSET is present.