Which pagination strategy prevents in-memory filtering when using JOIN FETCH in Hibernate?

When implementing pagination in a Java application using Hibernate ORM, the standard approach involves utilizing setFirstResult() and setMaxResults(). While this typically translates to efficient LIMIT or OFFSET clauses in the database dialect, the behavior changes when JOIN FETCH is used to retrieve associated entities in a single query.

The primary constraint is that Hibernate may trigger in-memory pagination if it cannot safely translate the join and the limit into a single SQL statement without duplicating root entities. This results in the application loading the entire result set into the JVM heap before filtering, which risks OutOfMemoryError for large datasets.

What is the recommended architectural pattern to maintain database-level pagination while still avoiding the N+1 select problem? Does the use of ScrollableResults resolve the in-memory filtering issue, or is a two-step query approach required?