Hibernate JOIN FETCH Queries Trigger In‑Memory Pagination When Using setFirstResult/setMaxResults

Direct answer

If the JOIN FETCH targets a collection-valued association (@OneToMany or @ManyToMany), Hibernate does not push LIMIT/OFFSET into the SQL. It falls back to in-memory pagination: every matching joined row is read from the database, the entity graph is assembled in the persistence context, and only then are setFirstResult()/setMaxResults() applied to the root entities. For a single-valued association (@ManyToOne or @OneToOne), Hibernate can usually push the limit down because the join does not multiply root rows.

ScrollableResults streams JDBC rows, but with a collection JOIN FETCH it does not avoid materializing the full joined result. The persistence context still accumulates managed entities unless you explicitly evict them or use a StatelessSession.

Why this happens

The root cause is row multiplication. A SQL LIMIT on the joined result would arbitrarily truncate child collections, producing incomplete object graphs. Because Hibernate cannot know which child rows belong to which root without reading them all, it defers pagination to the JVM heap.

Verify the behavior in your environment

Enable SQL logging and inspect the generated statement for a paginated query:

# application.properties (Spring Boot example)
logging.level.org.hibernate.SQL=DEBUG
spring.jpa.properties.hibernate.show_sql=true

Run the query and check whether the SQL contains a LIMIT/OFFSET clause (single-valued fetch) or returns all rows (collection fetch). Also confirm the exact Hibernate minor version, because query-planning and limit-pushdown rules have evolved across 5.x and 6.x releases.

Recommended approach for guaranteed database-level pagination

1. Avoid JOIN FETCH on collections when paginating. Use a JPQL or Criteria DTO projection so only the root rows are limited in SQL.
2. Or use a two-query pattern. Fetch the root page first with setFirstResult/setMaxResults, then load associations in a second query using the collected parent IDs (optionally with @BatchSize).
3. For true streaming without pagination, use ScrollableResults with a StatelessSession or periodically evict entities to keep heap usage bounded.

One diagnostic detail that changes the recommendation

Is the association you are fetching collection-valued or single-valued? If it is single-valued, the issue is likely dialect-specific limit support rather than in-memory pagination, and the fix is different.