OutOfMemoryError during pagination with Join Fetch in Spring Data JPA

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?