Hibernate Pagination: Why Offset Fails at Scale and How Keyset Fixes It

The Problem: Page 1 Is Fast, Page 1000 Times Out

You've built a product catalog with Hibernate. The first few pages load instantly. Then a user jumps to page 500 and the request hangs. The culprit is setFirstResult()/setMaxResults() — offset pagination. Most databases implement OFFSET n by scanning and discarding the first n rows. On a table with millions of rows, page 500 (20 rows per page) forces a scan of 10,000 rows just to return 20. The deeper the page, the worse it gets.

Takeaway: For large datasets where users navigate deep, replace offset pagination with keyset pagination (the seek method). It turns a linear scan into an index lookup, keeping latency flat regardless of page depth.

Offset Pagination: What Happens Under the Hood

When you call query.setFirstResult(10000).setMaxResults(20), Hibernate generates SQL roughly like:

SELECT * FROM product ORDER BY id ASC LIMIT 20 OFFSET 10000;

The database must locate row 10,001. Even with a B-tree index on id, it still walks through 10,000 entries to get there. Without a usable index, it degenerates into a sort plus scan. Two practical side effects:

• Growing cost: Page p costs roughly p × pageSize row visits, so latency climbs the deeper the user goes.
• Drifting results: Concurrent inserts or deletes shift row positions. A user on page 2 may see a duplicate or miss a row entirely if page 1 changed between requests.

Offset remains fine for small tables, admin UIs with numbered page links, or when you genuinely need a total row count for a pager component.

Keyset Pagination: Seek, Don't Scan

Keyset pagination uses the last-seen sort key as a cursor. Instead of "skip 10,000 rows", you ask for "rows where id > 10000 ordered by id, limit 20". The database seeks directly to the starting point via the index and reads only the rows it returns.

Requirements: the sort column(s) must be unique and indexed. If you sort by a non-unique column such as created_at, add a unique tie-breaker (typically the primary key) to both the ORDER BY and the WHERE predicate, or you will silently skip rows that share the same sort value.

Forward Pagination (First Page → Next → Next)

Run this in your service layer inside a read-only transaction. lastSeenId comes from the client — the id of the last item on the previous page. For page 1, omit the WHERE clause entirely: WHERE id > NULL evaluates to unknown and returns zero rows, and a placeholder like 0 breaks for UUID or string keys.

@Transactional(readOnly = true)
public List<Product> getProductsKeyset(Serializable lastSeenId, int size) {
    StringBuilder hql = new StringBuilder("FROM Product p");
    if (lastSeenId != null) {
        hql.append(" WHERE p.id > :lastSeenId");
    }
    hql.append(" ORDER BY p.id ASC");
    
    Query<Product> query = session.createQuery(hql.toString(), Product.class);
    if (lastSeenId != null) {
        query.setParameter("lastSeenId", lastSeenId);
    }
    query.setMaxResults(size);
    return query.list();
}

Assumption: Product.id is the primary key — unique, indexed, and immutable. The pattern works on Hibernate 5.x/6.x with any dialect that supports parameter binding; only the generated SQL limit syntax differs by dialect.

Backward Pagination (Previous Page)

Track the first item's key on the current page. Query in reverse order, then reverse the list in Java so the client always receives ascending order.

@Transactional(readOnly = true)
public List<Product> getPreviousPage(Serializable firstSeenId, int size) {
    StringBuilder hql = new StringBuilder("FROM Product p");
    if (firstSeenId != null) {
        hql.append(" WHERE p.id < :firstSeenId");
    }
    hql.append(" ORDER BY p.id DESC");
    
    Query<Product> query = session.createQuery(hql.toString(), Product.class);
    if (firstSeenId != null) {
        query.setParameter("firstSeenId", firstSeenId);
    }
    query.setMaxResults(size);
    List<Product> results = query.list();
    Collections.reverse(results);
    return results;
}

Composite Cursor: Sorting by created_at + id

Real lists usually sort by a business column, not the primary key. Use a composite cursor so duplicate created_at values don't cause skipped rows:

// Client sends the last row's values, e.g. lastSeenCreatedAt + lastSeenId
@Transactional(readOnly = true)
public List<Product> getProductsByDate(Instant lastSeenCreatedAt, Long lastSeenId, int size) {
    StringBuilder hql = new StringBuilder("FROM Product p");
    if (lastSeenCreatedAt != null && lastSeenId != null) {
        hql.append(" WHERE (p.createdAt > :createdAt OR (p.createdAt = :createdAt AND p.id > :id))");
    }
    hql.append(" ORDER BY p.createdAt ASC, p.id ASC");
    
    Query<Product> query = session.createQuery(hql.toString(), Product.class);
    if (lastSeenCreatedAt != null) {
        query.setParameter("createdAt", lastSeenCreatedAt);
        query.setParameter("id", lastSeenId);
    }
    query.setMaxResults(size);
    return query.list();
}

The expanded boolean form shown here is portable across PostgreSQL, MySQL, SQL Server, and Oracle. Some databases also support row-value syntax ((created_at, id) > (?, ?)), which the planner can often optimize better, but HQL support for that varies — the boolean form is the safe default.

Trade-offs You Must Accept

• No random page jumps: users can only move next/previous. If the UI needs "page 500 of 2000", offset is the only option, or a hybrid (offset for the first few pages, keyset beyond that).
• Immutable sort keys: if created_at can be updated after a row is read, that row may vanish from or appear twice in later pages. Prefer immutable cursor columns (primary key, insert timestamp).
• Client state: the frontend must store and send the cursor instead of a simple page number.
• No total count: keyset doesn't tell you how many rows exist. Run a separate COUNT(*) if the UI needs it, or drop the total-count display.

Lazy-loaded associations can still cause N+1 queries with either strategy. Use JOIN FETCH or entity graphs to control fetching explicitly — pagination choice doesn't solve that.

How to Verify the Difference Yourself

Don't trust benchmarks from other environments. Run this against a staging database with production-like data volume:

1. Create a test table with roughly a million rows and an index on the sort column.
2. Enable timing in your SQL client (for example \timing in psql) or use your APM/profiler.
3. Run offset queries with increasing offsets (0, 10k, 100k, 500k) and record execution times.
4. Run keyset queries with equivalent cursor values and record execution times.
5. Compare execution plans with EXPLAIN (ANALYZE, BUFFERS) on PostgreSQL (or your database's equivalent): the offset plan shows a limit node whose cost grows with the offset; the keyset plan should show an index scan with near-constant cost.

If the planner picks a sequential scan even for the keyset query — which can happen with stale statistics or a predicate matching most of the table — refresh statistics (for example ANALYZE in PostgreSQL) and re-check. The gain is real but depends on index health and data distribution, so measure on your own data.

When to Stick With Offset

• Small tables where deep-page latency is already acceptable.
• UIs that require numbered page links ("1 2 3 … 50").
• One-off admin exports where total count and random access matter more than latency.

For everything else — infinite scroll, "load more" buttons, deep navigation over large tables — keyset pagination is the engineering decision that keeps response times flat. Start with the single-column version, verify the plan with EXPLAIN, and only add composite cursors when your sort column actually needs them.