Offset vs Cursor Pagination in Hibernate: Making the Right Choice

Offset vs Cursor Pagination in Hibernate: Making the Right Choice

Fetching the nth page of results from a growing table is a classic backend headache. In Hibernate, the default approach is offset-based pagination: setFirstResult(N) and setMaxResults(M) generate a LIMIT M OFFSET N clause. While simple, this strategy has a hidden cost.

Every additional offset row the database must process and discard. On page 50 of 100, that is 490 rows scanned. On page 1000, it is nearly the entire table. Under concurrency, this inflates I/O latency and can trigger query timeouts.

How Offset Pagination Works in Hibernate

Hibernate setFirstResult and setMaxResults map directly to SQL LIMIT and OFFSET. The ORM translates a JPQL or Criteria query into a count query and a data query. The data query appends the offset clause after any ordering. Here is the kind of SQL you will see on PostgreSQL:

SELECT * FROM orders ORDER BY created_at OFFSET 99 LIMIT 10;

If created_at is not indexed, the database does a sequential scan. Even with an index, every row before the offset is touched.

Cursor Pagination: A Stable Alternative

Cursor pagination replaces the numeric offset with a cursor typically the value of a unique monotonically increasing identifier from the last row on the previous page. The query then seeks rows greater than that cursor. This turns an OFFSET N scan into an index seek regardless of page depth.

SELECT * FROM orders WHERE id > :last_id ORDER BY id LIMIT 10;

Because the id column is typically the primary key with a b-tree index, the database jumps straight to the starting point. No row discard.

Implementing Cursor Pagination with Spring Data JPA

Spring Data JPA abstracts the native query letting you define a custom @Query that uses the cursor pattern. Suppose you have an Order entity with a primary key id. A repository method can look like this:

select o from Order o where o.id > :lastId order by o.id asc

The method returns a Page so you still get metadata total elements total pages if needed but the underlying query uses an index seek rather than an offset scan.

To fetch the first page pass null or a sentinel value for lastId; subsequent pages pass the last id from the previous result set.

Trade-offs and Limitations

Cursor pagination is not a silver bullet. It requires a stable unique monotonically increasing identifier. If your primary key is a surrogate key that never reorders you are fine. If it is a business key that can be updated or deleted gaps may appear leading to skipped or duplicated rows. Also cursor-based pages do not support arbitrary jump to page N you always move forward from the current cursor.

Offset pagination still has its place: shallow pages 1-3 are negligible in cost and the API is familiar to developers and consumers of your endpoint.

Verifying the Difference

To see the impact yourself run the same query with increasing offset values and capture execution time. In PostgreSQL you can use EXPLAIN ANALYZE to inspect the plan:

EXPLAIN ANALYZE SELECT * FROM orders ORDER BY created_at OFFSET 99 LIMIT 10;

Look for Seq Scan on orders versus an index scan. For the cursor variant:

EXPLAIN ANALYZE SELECT * FROM orders WHERE id > 12345 ORDER BY id LIMIT 10;

You should see Index Scan using orders_pkey on orders with startup cost near zero. The risk of running these commands is minimal they are read only diagnostic queries but always execute them on a staging or read-replica instance under moderate load to avoid skewing results.

Choosing Your Strategy

If your API typically returns the first few pages offset pagination may be sufficient and easier to reason about. For APIs where users can navigate to deep pages or where table size grows beyond a few hundred thousand rows cursor pagination offers a predictable performance profile. Many production systems hybrid approach: use offset for the first page then switch to cursor for deeper navigation.

The key is to measure. Benchmark your deepest supported page examine the execution plan and pick the strategy that keeps latency in your SLA.