Offset-based vs. Keyset pagination for high-volume Hibernate datasets

Choosing a Pagination Strategy

For datasets with millions of records where consistent response times are required regardless of page depth, keyset pagination (the seek method) is the only sustainable approach. Offset-based pagination suffers from linear performance degradation because the database must scan and discard all preceding rows to reach the requested offset.

The Impact of Arbitrary Page Jumping

The requirement for arbitrary page jumping (e.g., "Jump to Page 500") is incompatible with keyset pagination. Because the seek method relies on the value of the last seen record to find the next set, it cannot calculate the starting point of a distant page without first traversing all intermediate records.

If arbitrary jumping is a hard business requirement, you must either accept the performance penalty of setFirstResult() or implement a hybrid approach, such as caching page-to-key mappings for common intervals.

Implementation Logic

While offset pagination uses Hibernate's built-in setFirstResult(int) and setMaxResults(int), keyset pagination is implemented via a WHERE clause filtering on a unique, monotonic column (usually the Primary Key).

Example Keyset Logic (JPQL):

SELECT e FROM Entity e WHERE e.id > :lastSeenId ORDER BY e.id ASC

1. Identify the Sort Key: Select a column that is indexed, unique, and monotonically increasing (e.g., Long id).
2. Pass the Cursor: The client must send the ID of the last record from the previous page back to the server.
3. Apply the Filter: Use the cursor in the WHERE clause to seek directly to the next record.
4. Limit Results: Use setMaxResults() to define the page size.

Performance Verification

To confirm the behavior in your environment, you can use the following verification steps:

• Execution Plan: Run EXPLAIN ANALYZE on the generated SQL. Offset pagination will typically show an Index Scan or Seq Scan that reads up to the offset; keyset pagination should show an Index Seek.
• Hibernate Statistics: Enable hibernate.generate_statistics to monitor the time spent on query execution as you increase the page depth.

Missing Diagnostic: Does your dataset require sorting by a non-unique column (e.g., created_at)? If so, you must use a composite key (created_at, id) to prevent skipping records with identical timestamps.