Deep Page Slowness: Why Hibernate's Offset Pagination Fails at Scale

The Hidden Cost of Deep Pagination in Hibernate

If your application uses Hibernate's firstResult and maxResults for pagination and experiences severe performance degradation on pages deeper than 100, you are suffering from the inherent inefficiency of SQL OFFSET-based limiting. The useful takeaway is this: for deep pagination, switch from offset-based limiting to keyset (or cursor-based) pagination.

Offset Pagination Mechanics

Hibernate translates firstResult (the starting index) and maxResults (the page size) into SQL using LIMIT and OFFSET. For example, requesting page 100 with a size of 10 requires LIMIT 10 OFFSET 1000 (assuming 0-indexing).

The Performance Trap of Large Offsets

Databases do not efficiently skip rows using OFFSET. They must still scan and discard the preceding 1000 rows before returning the next 10. This leads to linear performance degradation as the offset increases.

Keyset Pagination: The Scalable Alternative

Keyset pagination, also known as cursor-based pagination, avoids offsets entirely. Instead of saying "give me the 1001st row," you say, "give me the next 10 rows where the primary key (or a unique indexed column like a timestamp) is greater than the last ID I saw."

Worked Example: Transitioning the Query Logic

Assume you are paginating a Product entity ordered by id (a unique, indexed primary key).

// Old (Offset-based, slow for deep pages)
Query query = session.createQuery("FROM Product p ORDER BY p.id LIMIT :size OFFSET :offset");
query.setParameter("size", 20);
query.setParameter("offset", 2000); // Problematic offset

// New (Keyset-based, fast for deep pages)
// 'lastId' is the ID of the last item returned on the previous page
Query query = session.createQuery("FROM Product p WHERE p.id > :lastId ORDER BY p.id LIMIT :size");
query.setParameter("lastId", 2000L);
query.setParameter("size", 20);

Where to run: Application service layer using Hibernate Session/EntityManager.

Permissions: Standard application user permissions sufficient; no elevated DB rights needed.

Expected Check: The generated SQL should use a WHERE id > X clause instead of OFFSET Y.

Trade-Offs and Limitations

The primary limitation of keyset pagination is that it only works reliably when a strict, unique ordering column (like an auto-incrementing ID or a precise timestamp) is available and used in the WHERE clause. It cannot easily support arbitrary sorting (e.g., sorting by a non-indexed, non-unique field).

Practical Check: After implementing the keyset logic, verify the resulting page count is correct by comparing the returned list size against the expected maxResults (e.g., 20).

Limitation: If the underlying data changes (e.g., an item is deleted or inserted) between requests, keyset pagination might skip or duplicate items if the cursor relies solely on ID, although this is generally less problematic than the performance hit of offset pagination.

Actionable Step

Audit your service layer queries. If OFFSET values regularly exceed a few hundred, refactor the pagination logic to pass the last retrieved entity's primary key as a boundary condition for the next query.