Deep-offset latency versus lost random access: choosing between Offset and Keyset pagination in Hibernate

The Concrete Constraint

A Hibernate-based application must paginate a table expected to grow into the millions of rows. Two documented approaches are available, and the unresolved decision is which one fits a workload where users routinely navigate deep into the result set while concurrent inserts continue.

Option A: Offset-based pagination

Using Query.setFirstResult() with setMaxResults() maps to SQL OFFSET/LIMIT. It preserves the ability to jump directly to any page number, but the database must still read and discard all rows before the offset, so latency grows roughly linearly with page depth. Concurrent inserts can also shift rows between pages, causing items to be skipped or shown twice.

Option B: Keyset (seek) pagination

Filtering with a WHERE clause on a unique, non-nullable, strictly ordered column (for example, id > :lastSeenId ORDER BY id) gives near-constant lookup cost and is stable under concurrent inserts. The trade-offs: navigation becomes sequential (no direct jump to page 4,000), and Hibernate offers no high-level built-in API for it, so it must be written manually in HQL or the Criteria API. Behavior details such as fetch-size handling can vary between Hibernate versions, so any choice should be verified against the specific version in use.

Assuming Hibernate 6.x and a table with an indexed monotonically increasing primary key:

1. For offsets reaching into the millions, is the linear degradation of setFirstResult() severe enough in practice to justify abandoning random page access entirely?
2. Is a hybrid (Offset for shallow pages, Keyset beyond a threshold) a sound documented pattern, or does it introduce correctness problems at the boundary?
3. What is the most reliable way to verify the degradation — comparing execution plans for a high OFFSET against a keyset WHERE filter?