Hibernate Offset Pagination Limits with setFirstResult and setMaxResults

Where offset-based pagination stops scaling

Hibernate ORM exposes pagination through setFirstResult() and setMaxResults() on Query and TypedQuery. The API is uniform, but the behavior behind it is not: whether the offset becomes SQL LIMIT/OFFSET or the dialect equivalent, or is resolved by skipping rows after retrieval, depends on the dialect and on how the query can be expressed.

Two limits worth separating

The first is offset cost. Many databases scan and discard rows before returning a page, so work grows with the offset value rather than the page size. The second is join handling. Queries that cannot be reduced to a single offset-capable statement may be processed in memory, and eager associations can add extra selects unless fetched explicitly.

Assuming a Hibernate ORM release targeting Jakarta Persistence 3.x, which documented conditions decide whether setFirstResult() is pushed to SQL or applied in memory? How can the practical offset ceiling for a given dialect be established before deep pages are relied upon, and when does ScrollableResults or keyset filtering become the more appropriate mechanism?