Hibernate setMaxResults memory limits with join fetches

In-Memory Pagination Risk

Hibernate provides setFirstResult() and setMaxResults() to handle pagination at the database level. However, the behavior changes when a query utilizes a JOIN FETCH on a collection to avoid N+1 select problems.

When combining collection fetching with pagination limits, there is a documented risk that Hibernate cannot safely apply the limit clause in the generated SQL. This occurs because a single root entity may be associated with multiple child rows, making a database-level limit potentially truncate the entity's associated collection.

Under these constraints, the framework may shift the pagination logic from the database to the JVM, loading the entire result set into memory before applying the limit.

• How does Hibernate determine the threshold for triggering in-memory pagination versus database-level limiting?
• What are the specific indicators in the generated SQL that confirm pagination is being handled in memory rather than via LIMIT/OFFSET?