Pagination stability and performance limits with setFirstResult

When managing large datasets in Hibernate, the setFirstResult and setMaxResults methods are used to bound queries and implement pagination by translating these calls into dialect-specific LIMIT and OFFSET clauses.

A primary concern arises when navigating deep into a result set. As the start position increases, the database must scan and discard a growing number of rows before returning the requested page, which can lead to significant latency.

Additionally, maintaining a consistent result set across multiple page requests requires a deterministic ordering to prevent records from shifting between boundaries.

• What is the recommended strategy to maintain pagination stability when the underlying data is frequently updated?
• At what threshold of offset values does the performance degradation of setFirstResult typically necessitate a transition to ScrollableResults or keyset pagination?