Hibernate pagination and JOIN FETCH in-memory warning

Pagination Behavior with Collection Joins

When implementing pagination using setFirstResult() and setMaxResults() in Hibernate, a known interaction arises when JOIN FETCH is applied to collection associations. The ORM must ensure the result set contains the correct number of root entities despite the row duplication introduced by the join.

If the dialect cannot translate the pagination request into a database-level limit for the joined result set, Hibernate may retrieve all matching records and paginate in memory. That behavior can cause significant performance degradation or OutOfMemoryError on large datasets.

Technical Constraints

• Version assumption: Hibernate 5.x and 6.x
• Goal: constant-time deep paging without loading the full result set into the JVM

Which conditions trigger the in-memory pagination warning when JOIN FETCH is combined with setMaxResults()? How can the query be restructured so the database applies the limit and offset while still fetching the associated collections efficiently?