Hibernate pagination with collection fetch join: choosing between in-memory pagination, batch fetching, and a two-step ID query

Paginating a fetch-joined collection without loading the full result set

I have a JPA query (Hibernate 6.x, Spring Data JPA) that fetches a parent entity together with a collection-valued association using join fetch, and the repository method also applies pagination via setFirstResult/setMaxResults (through Pageable). The goal is a page of parent entities with their collections eagerly loaded in as few round trips as possible.

The constraint I am unsure about: with a collection fetch join, Hibernate cannot push LIMIT/OFFSET to the database because each parent row is duplicated per child element, so it falls back to in-memory pagination (warning HHH000104). On a small development dataset this behaves fine, but I do not want production to silently fetch the entire table into memory.

The alternatives I see are: keep the fetch join and accept in-memory pagination, drop the fetch join and rely on @BatchSize with lazy loading, or run a two-step query — paginate parent IDs first, then fetch collections by ID (mindful of database IN-list limits).

Given this, my questions are:

1. Is there any Hibernate configuration or query hint that makes paginated collection fetch joins safe at the SQL level, or is the two-step ID query the only reliable option?
2. When using @BatchSize instead of a fetch join, how do I size the batch to balance N+1 risk against memory use?
3. How can I verify in a test whether pagination was applied in memory rather than in the generated SQL?