Stopping the Offset Slide: Implementing Keyset Pagination in Hibernate

The Performance Wall of Offset Pagination

When building a data-heavy application, the default approach to pagination is usually setFirstResult() and setMaxResults(). This creates a standard SQL OFFSET and LIMIT query. While this works for the first few pages, it creates a critical performance bottleneck as the user navigates deeper into the dataset.

The problem is that the database does not magically jump to the 100,000th row. It must scan through all 100,000 preceding records, load them into memory, and then discard them just to return the next 20. This leads to linear performance degradation: the deeper the page, the slower the response, and the higher the CPU load on your database server.

The Keyset Alternative (The Seek Method)

Keyset pagination, often called the "Seek Method," avoids the scan entirely. Instead of telling the database how many rows to skip, you tell it exactly where you left off. You do this by filtering the results based on the unique identifier (the key) of the last record from the previous page.

Because the query uses a WHERE clause on an indexed column, the database can perform an index seek to jump directly to the starting point of the next page. This results in constant-time performance regardless of whether you are on page one or page one thousand.

Implementation Example: Offset vs. Keyset

Consider a scenario where you are fetching a list of Order entities sorted by their id. Assume you are using Hibernate 6.x with a standard relational database.

The Traditional Offset Approach

// Run this in your Service layer
// Page 5000 (assuming 20 items per page)
List<Order> orders = session.createQuery("from Order o order by o.id", Order.class)
    .setFirstResult(100000)
    .setMaxResults(20)
    .getResultList();

Risk: The database executes OFFSET 100000 LIMIT 20. As the offset grows, query latency increases significantly.

The Keyset Approach

To implement this, your API must return the ID of the last item on the current page to the client. The client then sends that ID back to request the next page.

// Run this in your Service layer
// lastSeenId is the ID of the 20th item from the previous page
Long lastSeenId = 100450L;

List<Order> orders = session.createQuery(
    "from Order o where o.id > :lastId order by o.id asc", Order.class)
    .setParameter("lastId", lastSeenId)
    .setMaxResults(20)
    .getResultList();

Result: The database executes a WHERE id > 100450 LIMIT 20. This is a direct index lookup, maintaining high speed even for massive datasets.

Trade-offs and Constraints

Keyset pagination is not a universal replacement for offset pagination. It introduces specific constraints:

• No Random Access: Users cannot jump directly to "Page 45." They can only move forward (or backward) sequentially.
• Strict Ordering Requirements: You must sort by a column (or combination of columns) that is unique and non-null. If you sort by a non-unique column (like createdAt), you must append the primary key to the sort order to ensure a deterministic sequence.
• Complexity: The client-side logic must change to track the "last seen" identifier rather than a simple page number.

Verifying the Performance Gain

To confirm that your implementation is actually avoiding the offset scan, you should analyze the database execution plan. Run the following steps in your database console:

1. Run an EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN (MySQL) on a query with a high offset. Look for "Index Scan" or "Seq Scan" that processes a high number of rows before returning the limit.
2. Run the same EXPLAIN on your Keyset query. You should see an "Index Seek" or "Index Range Scan" that targets a small number of rows.
3. Compare the execution time of setFirstResult(0) versus setFirstResult(100000). If the latter is significantly slower, your application is vulnerable to the offset slide.

Actionable Summary

If your application supports datasets that grow indefinitely and you provide "infinite scroll" or "next page" functionality, migrate from setFirstResult() to Keyset pagination. Ensure your sorting columns are indexed, and update your API contracts to accept a lastId parameter instead of a pageNumber.