OutOfMemoryError during in-memory pagination in Hibernate ORM

The Solution: Decoupling Pagination from Collection Fetching

To prevent OutOfMemoryError caused by in-memory pagination, you must separate the retrieval of the paginated root entity IDs from the retrieval of their associated collections. Hibernate cannot apply LIMIT or OFFSET at the database level when a JOIN FETCH on a collection is present because the resulting Cartesian product would truncate the parent entities incorrectly.

Recommended Architectural Patterns

The following two strategies ensure that pagination occurs at the database level while still efficiently loading associated data.

1. Two-Step ID Fetching (The ID-First Pattern)

Instead of fetching the entire entity graph in one query, split the operation into two distinct steps:

1. Fetch IDs: Execute a paginated query that selects only the primary keys of the root entity. Since no collections are joined, Hibernate will delegate setFirstResult() and setMaxResults() to the database.
2. Fetch Data: Use the retrieved list of IDs to fetch the full entities and their collections using a WHERE id IN (...) clause with a JOIN FETCH.

2. Batch Fetching with @BatchSize

If the two-step process is too verbose for your use case, replace JOIN FETCH with @BatchSize on the collection relationship. This avoids the Cartesian product entirely:

• Annotate the collection with @BatchSize(size = 20).
• Perform a standard paginated query on the root entity without the join fetch.
• When you access the collection for the first time, Hibernate will load the collections for up to 20 root entities in a single subsequent query, preventing the N+1 problem without risking heap exhaustion.

Verification and Diagnostics

To confirm if your application is still performing in-memory pagination, check the following:

• Log Analysis: Look for the specific Hibernate warning: "firstResult/maxResults specified with collection fetch; applying in memory!"
• SQL Inspection: Enable hibernate.show_sql=true. If the generated SQL for a paginated request lacks LIMIT, OFFSET, or TOP clauses, the pagination is happening in the JVM.
• Heap Monitoring: Use JVisualVM or JConsole to observe memory spikes during the execution of the paginated request.

Diagnostic Requirement

To refine this recommendation: Are you using a OneToMany relationship with a List or a Set? If using List, ensure you are not using JOIN FETCH on multiple collections simultaneously, as this creates a multiple-bag exception or further complicates the Cartesian product.