Question
Hibernate Pagination Limits and Join Fetch Behavior
Ash Atlas
0 reputation · 21 Nov 2021, 20:47 UTC
50.6K views
Loading…
0 reputation · 21 Nov 2021, 20:47 UTC
Hibernate utilizes setFirstResult() and setMaxResults() to implement pagination, relying on the database Dialect to translate these calls into native SQL clauses such as LIMIT or OFFSET.
A known architectural constraint occurs when pagination is combined with join fetches. In certain configurations, Hibernate may be unable to apply the limit constraints at the database level because the resulting Cartesian product would lead to incorrect row counts. This often forces the framework to retrieve the entire result set and perform pagination within the application memory.
When dealing with large datasets, this behavior introduces significant risks regarding heap memory consumption and query latency.
33775 reputation · 22 Nov 2021, 02:36 UTC
Hibernate switches to in-memory pagination whenever setFirstResult() or setMaxResults() is used in a query that includes a JOIN FETCH on a collection (a ToMany relationship). This is not a Dialect-specific failure, but a fundamental architectural constraint of how SQL joins interact with entity mapping.
When you fetch a parent entity and its children via a join, the database returns a Cartesian product. If one parent has ten children, the result set contains ten rows for that single parent. If the database applied a LIMIT 10, it might return only one parent and its children, or worse, a partial list of children for a single parent, rather than ten distinct parent entities.
Because the database cannot distinguish between a "row" and a "root entity" in a join result, Hibernate retrieves the entire unfiltered result set into the JVM heap and performs the pagination manually to ensure the correct number of root entities is returned.
You can confirm this behavior is occurring using the following methods:
hibernate
clarityHHH000104: firstResult/maxResults specified with collection fetch; applying in memory! in your application logs.org.hibernate.SQL=DEBUG). If the generated SQL lacks LIMIT, OFFSET, or TOP clauses despite your pagination settings, Hibernate is processing the results in memory.To achieve true database-level pagination while still loading collections, separate the identity retrieval from the data retrieval:
IN clause with the retrieved IDs, utilizing JOIN FETCH to load the collections for those specific entities.Note: This recommendation assumes you are using a standard OneToMany or ManyToMany relationship. If you are using a ManyToOne (ToOne) relationship, Hibernate can typically paginate at the database level because the join does not multiply the root entity rows.
Use comments to ask for clarification. Post a solution as an answer.
33,775 reputation · 22 Nov 2021, 02:58 UTC
One detail worth adding to the two-step fetch recommendation: when you switch from a join fetch to a separate collection query, watch out for Hibernate silently adding DISTINCT to your JPQL. In Hibernate 5.x/6.x, a query like SELECT DISTINCT p FROM Post p JOIN FETCH p.comments passes the DISTINCT keyword through to the SQL as well as applying entity-level deduplication, which can force an unnecessary sort or dedup step on the database.
Since Hibernate 5.2 (and configurable in 6.x), you can control this with hibernate.query.passDistinctThrough=false, which keeps the entity deduplication but omits the SQL DISTINCT. This is easy to verify: enable hibernate.show_sql, run the query with and without the setting, and compare the logged statements. If you adopt the ID-then-fetch pattern, the second query often doesn't need DISTINCT at all, since you're filtering by primary key.