Solving the Deep Pagination Performance Gap in Hibernate

The 'Page 100' Performance Wall

When building a data-heavy application, the standard approach to pagination is intuitive: use a page number and a page size. In Hibernate, this typically means calling setFirstResult() and setMaxResults(). This works perfectly for the first few pages, but as a user navigates deeper into a dataset—say, to page 500 of a million-row table—the application slows down significantly.

The problem is that offset-based pagination requires the database to scan and discard all preceding rows before returning the requested slice. If you request an offset of 100,000, the database must still process those 100,000 records just to throw them away. The takeaway: for large datasets, you must move from Offset-based to Keyset-based (or "seek") pagination to maintain constant performance.

Offset vs. Keyset: The Technical Divide

Offset-based pagination is a request for a relative position. You are telling the database, "Skip X rows and give me the next Y." This is convenient for UI components that display specific page numbers, but it is computationally expensive at scale.

Keyset pagination is a request for a specific starting point. Instead of saying "skip 100,000 rows," you say, "give me the next 20 rows where the ID is greater than the last ID I saw." Because the ID is indexed, the database can jump directly to that record using a B-Tree index lookup, bypassing the need to scan previous pages entirely.

Comparison Summary

Feature	Offset-based	Keyset-based
DB Performance	Degrades as offset increases	Constant regardless of depth
UI Flexibility	Allows jumping to specific pages	Sequential navigation only (Next/Prev)
Data Consistency	Risk of "drifting" (skipped/duplicate rows)	Stable regardless of insertions/deletions

Implementing the Seek Method in Hibernate

Hibernate does not provide a built-in setKeyset() method; you must implement this logic via JPQL or the Criteria API. To make this work, you need a column that is unique, non-null, and ordered (usually the Primary Key or a created_at timestamp combined with an ID).

Assume we are using Hibernate 6.x with a PostgreSQL database. To fetch the next page of 20 users after the last seen user ID 12345, you would execute a query similar to this:

// Run this in your Service/Repository layer
String hql = "FROM User u WHERE u.id > :lastId ORDER BY u.id ASC";
List<User> results = session.createQuery(hql, User.class)
    .setParameter("lastId", 12345)
    .setMaxResults(20)
    .getResultList();

Execution Check: To verify this is working, run the query in your database console with EXPLAIN ANALYZE. For an offset query, you will likely see a "Sequential Scan" or a high cost for skipping rows. For the keyset query, you should see an "Index Scan," indicating the database jumped directly to the starting point.

The Trade-off: No More "Jump to Page 50"

The primary limitation of keyset pagination is the loss of arbitrary page access. Because the query depends on the value of the previous page's last element, you cannot calculate the starting point for page 50 without first fetching pages 1 through 49.

If your product requirements strictly demand a page-numbering system, you have two choices: limit the maximum page a user can navigate to (e.g., only allow the first 100 pages) or accept the performance hit for those rare users who navigate deep into the archives.

Verification and Rollback

To test the implementation, populate a table with at least 100,000 records. Compare the response time of setFirstResult(90000) against a keyset query using the ID of the 90,000th record. The keyset query should return results in milliseconds, while the offset query will show a linear increase in latency.

Since this change modifies the query logic rather than the database schema, rolling back simply requires reverting the HQL/JPQL string to use setFirstResult() and removing the lastId parameter from the API request.