Replacing Slow Hibernate Offset Pagination with Keyset Pagination

The Cost of Skipping Rows

When building a data-heavy Java application, the default approach to pagination is usually offset-based. You tell Hibernate to skip the first 10,000 records and give you the next 20. While this works for the first few pages, it creates a performance cliff as users navigate deeper into the dataset.

The problem is that databases cannot simply jump to row 10,001. They must scan and discard every preceding row to ensure the result set is accurate. This leads to increased CPU usage and disk I/O, often resulting in timeouts for deep-page requests. To solve this, you need to move from Offset Pagination to Keyset Pagination (also known as the Seek Method).

Offset-Based Pagination: The Simple but Slow Path

Offset pagination relies on setFirstResult() and setMaxResults(). In HQL or Criteria API, this translates to OFFSET and LIMIT clauses in SQL.

• Pros: Easy to implement; allows users to jump to a specific page (e.g., Page 50).
• Cons: Performance degrades linearly with the offset; results can drift (duplicate or skip items) if records are inserted or deleted while a user is paging.

Keyset Pagination: The High-Performance Alternative

Instead of telling the database how many rows to skip, keyset pagination tells the database exactly where the last page ended. You filter the query using a unique, sorted column—usually the Primary Key (ID) or a timestamp.

By using a WHERE clause to find records greater than the last seen ID, the database can use an index to jump directly to the starting point of the next page, maintaining constant performance regardless of whether you are on page 1 or page 1,000.

Implementation Example: Offset vs. Keyset

Consider a scenario where you are fetching a list of Order entities sorted by ID. These examples assume Hibernate 6.x and a standard JPA environment.

The Offset Approach (Avoid for large sets)

// Run this in your Service layer
// Risk: High latency as firstResult increases
Query query = session.createQuery("FROM Order o ORDER BY o.id ASC", Order.class);
query.setFirstResult(10000); // Skip 10k rows
query.setMaxResults(20);    // Fetch 20
List results = query.getResultList();

The Keyset Approach (Recommended)

To implement this, the client must send the ID of the last record from the previous page back to the server.

// Run this in your Service layer
// Required: lastSeenId passed from the client request
// Permission: Standard DB read permissions
Long lastSeenId = 10000L;

Query query = session.createQuery(
    "FROM Order o WHERE o.id > :lastId ORDER BY o.id ASC",
    Order.class
);
query.setParameter("lastId", lastSeenId);
query.setMaxResults(20); // Fetch only the next page size
List results = query.getResultList();

Comparing the Trade-offs

Feature	Offset-Based	Keyset-Based
Performance	Degrades as page increases	Constant (O(log N))
Random Access	Can jump to Page X	Sequential only (Next/Prev)
Data Stability	Prone to drifting/duplicates	Stable and consistent
Implementation	Trivial (Built-in)	Requires client-side state

Limitations and Verification

Keyset pagination is not a silver bullet. Its primary limitation is the loss of arbitrary page jumping. You cannot provide a Page 500 button because the application does not know the starting key for that page without scanning all previous keys.

To verify the performance gain in your environment, run the following diagnostic checks:

• Execution Plan: Use EXPLAIN ANALYZE on the generated SQL. Offset pagination will often show a Parallel Seq Scan or a high cost for skipping rows, while Keyset pagination should show an Index Seek or Index Scan.
• Latency Test: Compare the response time of setFirstResult(100) versus setFirstResult(100000). If the latter is significantly slower, your dataset has reached the threshold where keyset pagination is mandatory.

Actionable Summary

If your dataset is small (a few thousand rows) or users rarely go past the second page, stick with setFirstResult() for simplicity. However, for any production-scale table where users scroll through thousands of records, implement keyset pagination by tracking the last seen ID. Ensure the column used for the seek is indexed, or you will replace the offset problem with a full table scan problem.