What are the performance implications of using offset-based pagination with large result sets in Hibernate when combined with join fetch?

When retrieving large datasets with Hibernate, developers often rely on setFirstResult() and setMaxResults() for offset‑based pagination. This approach translates to LIMIT/OFFSET in most dialects, but the database must scan and discard preceding rows, which can degrade performance as the offset grows. Combining offset pagination with eager join fetches adds another layer of complexity because Hibernate may warn about performing pagination in memory, potentially leading to OutOfMemoryErrors.

Given these trade‑offs, an unresolved question remains about the practical impact of using offset‑based pagination alongside join fetch on both query execution time and memory consumption in a production‑scale environment.

Does the performance penalty become unacceptable beyond a certain offset threshold, and can keyset pagination reliably replace offset‑based queries when join fetch is required?