OutOfMemoryError during pagination with Join Fetch in Spring Data JPA

Question

When implementing pagination for large datasets using Spring Data JPA with Hibernate, developers often use JOIN FETCH to optimize entity loading and avoid the N+1 select problem. However, combining fetch joins with Pageable parameters can lead to unexpected memory consumption.

The core issue arises when Hibernate cannot translate the fetch join and pagination request into a single SQL query with LIMIT and OFFSET clauses. In such cases, Hibernate may retrieve the entire result set into memory to perform the pagination manually.

Constraints and Uncertainty

The dataset size is large enough to exceed available heap space during in-memory processing.
  The requirement is to maintain the performance benefits of eager fetching while ensuring the database handles the bounding of the result set.

What are the specific conditions that trigger Hibernate to switch from database-level pagination to in-memory pagination when using fetch joins? Is there a documented configuration to prevent this behavior and force a database-level limit?

Siddharth · Answer

Answer
When a query contains a JOIN FETCH and is executed with a Pageable, Hibernate will *always* materialise the entire result set before applying the page boundaries. The JPA specification forbids applying LIMIT/OFFSET to a fetch join because it would break the integrity of the entity graph. Consequently, Hibernate loads every row that matches the join, stores the entities in the persistence context, and then slices the collection in memory. This is the source of the OutOfMemoryError you see.

Conditions that trigger in‑memory pagination

The query uses a JOIN FETCH (or an @EntityGraph that expands an association in the same statement).
The query is executed with a Pageable (or any JPA setFirstResult/setMaxResults).
The root entity is returned as the result type (not a DTO projection).
Hibernate cannot rewrite the query into a single SQL statement with LIMIT/OFFSET because the fetch join would produce duplicate root rows.

Is there a configuration flag to force database‑level limits?
No. Hibernate deliberately refuses to apply pagination to a fetch join. The spec states that a fetch join is a *fetch* operation, not a query operation, and therefore must not be subject to result‑set limits. The only knobs you have are:

hibernate.max_fetch_depth – limits how deep Hibernate will traverse associations, but does not affect pagination.
Second‑level cache – can spill entities to disk but does not change the in‑memory fetch.

In practice the fix is to redesign the query:

Remove the fetch join from the pageable query. Load the root entities with Pageable, then access associations lazily or via a separate JOIN FETCH query that is not paged.
Use an @EntityGraph with distinct and a separate SELECT for the associations. This keeps eager loading but respects pagination.
Project to a DTO. Select only the fields you need; Hibernate will not materialise the full entity graph.
If you must keep the fetch join, drastically reduce the page size or enable a second‑level cache. This only mitigates the symptom, not the root cause.

Verification steps

Enable statistics: hibernate.generate_statistics=true.
Run the pageable query and check statistics.getEntityLoadCount() – it should equal the total rows, not the page size.
Apply one of the fixes above and re‑run the query. The load count should now match the requested page size and the OOM should disappear.

In short, Hibernate’s behaviour is by design, not a bug. There is no switch to override it; you must refactor the query or projection to keep pagination at the database level.

OutOfMemoryError during pagination with Join Fetch in Spring Data JPA

Constraints and Uncertainty

1 answer

Answer

Conditions that trigger in‑memory pagination

Is there a configuration flag to force database‑level limits?

Verification steps

Your perspective belongs here.

1 question comment

Clarifying a Common Pitfall