Solving the OFFSET Performance Trap with Cursor-Based Pagination in Hibernate

The Hidden Cost of Page 1,000

When building data-heavy applications, the standard OFFSET and LIMIT approach seems intuitive. You ask the database for 20 records, skipping the first 20,000. However, as your dataset grows into the millions, you will notice a significant slowdown. This happens because the database must scan and discard every single row preceding the offset before it can return the requested page.

The solution is cursor-based pagination (also known as the seek method). Instead of telling the database how many rows to skip, you tell it exactly where you left off using a unique, monotonically increasing identifier. This transforms a linear scan into an indexed lookup, ensuring that fetching page 1,000 is as fast as fetching page one.

How Cursor Pagination Works

In a traditional offset query, the SQL looks like this: SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 20000;. The database reads 20,020 rows and throws away the first 20,000.

Cursor pagination replaces the OFFSET with a WHERE clause. If the last ID on the previous page was 5021, the next query becomes: SELECT * FROM orders WHERE id > 5021 ORDER BY id LIMIT 20;. Because the id column is indexed, the database performs a B-tree seek to find 5021 and immediately reads the next 20 rows.

Implementing the Seek Method in Spring Data JPA

While Spring Data JPA's Pageable is excellent for small sets, it defaults to offset pagination. To implement a cursor strategy, you must define a custom query that accepts the cursor value from the previous request.

Example Configuration

Assume a Product entity with a primary key id. Here is how to implement a cursor-based repository method:

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query("SELECT p FROM Product p WHERE p.id > :lastSeenId ORDER BY p.id ASC")
    List<Product> findNextPage(@Param("lastSeenId") Long lastSeenId, Pageable pageable);
}

Execution Details:

• Where to run: This method is called within your Service layer.
• Permissions: Requires standard database read permissions for the Product table.
• Placeholders: lastSeenId is the ID of the last element of the current page; pageable should be initialized with PageRequest.of(0, size) since we are no longer using the page index.
• Expected Result: A list of entities starting immediately after the provided ID.

Performance Verification

To confirm that your application is actually using the index and not performing a full table scan, run an EXPLAIN plan on the generated SQL in your database console:

EXPLAIN SELECT * FROM product WHERE id > 5021 ORDER BY id ASC LIMIT 20;

Look for Index Scan or Index Seek in the output. If you see Full Table Scan or Parallel Seq Scan, the database is ignoring the index, and you may need to verify that your cursor column is properly indexed.

Trade-offs and Limitations

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

• No Random Access: You cannot jump directly to "Page 50." You can only move forward (or backward) sequentially. This makes it ideal for infinite scrolls but poor for traditional numbered pagination bars.
• Strict Ordering: The cursor must be based on a unique, stable column. If you use a non-unique column (like created_at), rows with the exact same timestamp may be skipped or duplicated across page boundaries. To solve this, use a composite cursor (e.g., WHERE (created_at, id) > (:lastTimestamp, :lastId)).
• State Management: The client must now track and send back the lastSeenId, increasing the complexity of the API contract.

Actionable Summary

If your tables are exceeding 100,000 rows and you are seeing latency spikes on deep pages, migrate your high-traffic endpoints to cursor-based pagination. Start by identifying a unique, indexed column to serve as the cursor, rewrite your @Query to use a WHERE seek instead of OFFSET, and verify the execution plan to ensure O(log n) lookup performance.