Offset pagination with SUBSELECT versus cursor-based pagination for deep Hibernate queries

Direct answer

For bounded heap on deep pages under Hibernate 6.x, cursor-based pagination on a root-only query is the appropriate approach. Offset pagination tuned with @Fetch(FetchMode.SUBSELECT) does not bound heap when the query fetch-joins a child collection. A cursor keyed on a mutable @Version column is unsafe unless rows are immutable; prefer an indexed, non-null, monotonic key such as a sequence id or creation timestamp with a stable tiebreaker.

Why offset plus SUBSELECT does not fit

When a collection join fetch is present, Hibernate hydrates every joined row, so setMaxResults limits SQL rows rather than distinct parents. FetchMode.SUBSELECT governs separate-select collection initialization after a limited root query; it does not neutralize an explicit join fetch in HQL. Deep offsets also continue to scan skipped rows.

Recommended pattern

// 1. Root-only keyset page
List<Parent> roots = em.createQuery(
    "select p from Parent p where p.id > :lastId order by p.id", Parent.class)
    .setParameter("lastId", lastSeenId)
    .setMaxResults(50)
    .getResultList();

// 2. Initialize children for exactly those roots
em.createQuery(
    "select distinct p from Parent p left join fetch p.children where p.id in :ids", Parent.class)
    .setParameter("ids", roots.stream().map(Parent::getId).toList())
    .getResultList();

Because Hibernate collection fetch semantics are version-sensitive, verify this on your exact 6.x build.

What guarantees the page stays at 50 parents

The guarantee is structural: the paginated SQL must not contain a collection join fetch. With a root-only keyset query, the database returns exactly the limited parent rows and Hibernate hydrates exactly those roots. Child collections are then populated by a separate statement scoped to the page's parent ids. If a join fetch remains in the paginated query, nothing guarantees the SQL row count or hydrated root count stays at 50.

Remaining heap risk

Even with 50 roots, heap remains proportional to children per root. If each parent has many children, materializing every collection can still exhaust heap; consider a root-only page or DTO projection and load children on demand.

Verification

Enable SQL logging and inspect one deep page: the paginated statement should select only parent rows with a keyset predicate and limit, with no child collection join. Confirm child initialization issues a second subselect or IN query using only that page's parent ids. Compare EXPLAIN output for offset versus keyset on a deep page and confirm the cursor key has an index matching the order by.

One diagnostic that changes the recommendation: are rows updated between page requests? If yes, a @Version cursor will move rows across pages; switch to an immutable key such as a creation timestamp or sequence id.