Offset-based vs. Keyset pagination for deep-page Hibernate queries

Answer to the Core Question

For datasets that mutate often during a user session, offset‑based pagination is the more sustainable choice if you need a consistent snapshot or random page jumps. Keyset pagination is preferable when the ordering key is immutable, inserts happen only at the tail, and the UI never requires jumping to a specific page number.

Why Offset Is Safer on Volatile Data

• Each request re‑evaluates the full query, so the result set reflects the current state of the table.
• Snapshot isolation (e.g., SET TRANSACTION ISOLATION LEVEL REPEATABLE READ in PostgreSQL) can be used to lock the view for the duration of the user session, preventing phantom rows.
• Random navigation is trivial: the client can request any page number without extra server logic.

When Keyset’s Lack of Random Jump Is Acceptable

• The UI only ever shows a “next”/“previous” interface, so forward navigation is sufficient.
• The ordering column is a monotonically increasing, never‑updated key (e.g., surrogate primary key id).
• New rows are appended at the end; deletions are rare or handled via soft‑delete flags that do not affect ordering.
• Session length is short enough that a few inserts between pages do not materially alter the user’s view.

When Keyset Is Not Worth It

• Users can jump to page 25 or any arbitrary page; keyset would require iterating through all preceding keys, negating its performance advantage.
• The ordering key can change (e.g., business‑logic updates to a date field) or is not unique, leading to unstable ordering and duplicate or missing rows.
• The dataset is only moderately sized (tens of thousands of rows) and the database can handle offset scans quickly; the constant overhead of keyset logic outweighs the benefit.

Practical Steps for a Hibernate Implementation

1. Assess the ordering key: Ensure it is unique, immutable, and indexed. If not, consider adding a surrogate key solely for pagination.
2. Choose the strategy:
   • Offset: query.setFirstResult(offset).setMaxResults(pageSize) with optional transaction isolation.
   • Keyset: query.setParameter("lastId", lastSeenId).setMaxResults(pageSize) and order by id.
3. Handle data volatility: For offset, wrap the sequence of page requests in a repeatable‑read transaction if the database supports it. For keyset, document that the view may shift if new rows are inserted before the current cursor.
4. Cache the last seen ID: Store it in the session or client to enable the next keyset query. In offset, remember the page number.
5. Measure performance: Benchmark both approaches on the target dataset size and typical page depth. Use the database’s explain plan to confirm that keyset uses an index seek while offset uses a seek plus skip.
6. Provide fallback UI: If the user tries to jump to an arbitrary page in a keyset session, offer a warning that the request will be slow or redirect them to a search/filter page.

Diagnostic Question That Could Shift the Recommendation

Do you need to allow users to jump directly to any page number (e.g., “Go to page 42”) without sequential navigation? If the answer is yes, offset pagination becomes mandatory regardless of performance concerns. If no, keyset can be considered for its constant‑time benefit.