Question
Hibernate ORM offset pagination and JDBC collection-fetch results: where does the limit boundary belong?
Lax River
0 reputation · 07 Feb 2021, 08:54 UTC
86.3K views1
Loading…
0 reputation · 07 Feb 2021, 08:54 UTC
When a paginated Hibernate query uses JOIN FETCH on a collection, the ORM cannot safely push the offset and limit down to SQL without risking incorrect results. Instead, it reads the full result set and applies the boundary in memory, holding the JDBC connection longer and buffering more rows. Under concurrent requests, this boundary between Hibernate ORM pagination and JDBC collection-fetch results can turn a query that looks fast in isolation into a source of connection-pool wait time and heap pressure.
The unresolved decision is whether to keep the paginated root query free of collection fetches and load associations separately so the database can apply native LIMIT/OFFSET, or to accept the in-memory boundary and mitigate the concurrency cost. The exact behavior and log wording vary across Hibernate major versions and dialects, so the trade-off is a fetch-plan choice rather than a single fixed defect.
Where should the offset and limit boundary sit for this query shape? What concurrency profile makes the in-memory approach acceptable? Which Hibernate versions treat this as a documented caveat rather than a regression?
35275 reputation · 07 Feb 2021, 16:41 UTC
When a Hibernate query paginates a root entity and uses JOIN FETCH to eagerly load a collection, the LIMIT/OFFSET clause is applied only to the root SELECT. The collection fetch is performed without pagination; Hibernate buffers the full result set and then slices the page in memory. In other words, the offset boundary belongs at the root query, not on the collection fetch.
SQL engines cannot guarantee correct pagination when a collection is fetched via JOIN FETCH because the join duplicates rows for the root entity. Pushing the limit down would truncate rows that belong to a single root, breaking the entity graph. Therefore Hibernate chooses to fetch all collection rows and apply the page slice after materialisation.
javaJOIN FETCH with paginationHigh concurrency, large page sizes, or a tight connection pool make this pattern problematic. In those cases, split the query: paginate the root, then load collections lazily or via batch fetching (@BatchSize, @Fetch(FetchMode.SUBSELECT)), or switch to cursor‑based (seek) pagination.
hibernate.jdbc.fetch_size can reduce the amount of data buffered per batch.FETCH FIRST n ROWS ONLY subqueries, but Hibernate disables them when a collection is fetched.show_sql or format_sql) and verify that LIMIT/OFFSET appears only on the root select.FETCH FIRST and whether Hibernate is overriding it.What is the typical page size you use, and how many concurrent requests hit the paginated endpoint? Knowing these numbers will help decide whether the in‑memory boundary is tolerable or if a fetch‑plan change is required.
Use comments to ask for clarification. Post a solution as an answer.
No question comments on this page.