Hibernate Deep Pagination: Offset Costs and Keyset Alternatives

The Page 500 Problem

You ship a REST endpoint that returns 20 orders per page. The first few pages respond in milliseconds. Then someone clicks "Page 500" and the request hangs, the database CPU spikes, and the connection pool backs up. The code is simple—just setFirstResult() and setMaxResults()—but the cost grows with the page number.

This is the deep pagination trap. Offset-based pagination asks the database to skip a number of rows before returning the window you want. When that offset is small, the overhead is invisible. When it is large, the database may have to process many more rows than it returns.

Why Large Offsets Hurt

Hibernate translates HQL/JPQL offset pagination into dialect-specific SQL. For PostgreSQL, this typically becomes a LIMIT/OFFSET clause. The database engine must locate the starting row for the offset and then return the requested number of rows. Depending on the indexes and execution plan, the engine may scan and discard preceding rows, which increases CPU and I/O load as the offset grows.

Legacy dialects add another risk: some older Hibernate dialects may fetch the entire result set into memory and paginate in Java. That not only slows the query but can also pressure the heap, because the persistence context caches every entity it loads.

Keyset Pagination: Seek, Don't Skip

Keyset pagination—also called the seek method—replaces the numeric offset with a filter on the last row you saw. Instead of asking for "the next 20 rows after skipping 10,000," you ask for "the next 20 rows where the ID is greater than the last ID I saw."

With a proper index on the sort column, the database can seek directly to the starting position rather than counting through discarded rows. This usually makes deep pages cheaper than large offsets, though the exact gain depends on index selectivity, data distribution, and the cost of the index seek itself.

Worked Example: Orders by ID

Assume Hibernate 6.x and PostgreSQL. Verify the exact API signatures against your Hibernate version before deploying.

Offset-Based

// Service layer
int pageNumber = 500;
int pageSize = 20;

Query<Order> query = session.createQuery(
    "from Order o order by o.id", Order.class);
query.setFirstResult(pageNumber * pageSize);
query.setMaxResults(pageSize);
List<Order> results = query.getResultList();

Hibernate generates SQL resembling ... ORDER BY id LIMIT ? OFFSET ? for PostgreSQL, though the exact form depends on the dialect. The database must resolve the offset before returning rows.

Keyset-Based

// Service layer
Long lastSeenId = 10500L; // supplied by the client from the previous page

Query<Order> query = session.createQuery(
    "from Order o where o.id > :lastId order by o.id", Order.class);
query.setParameter("lastId", lastSeenId);
query.setMaxResults(pageSize);
List<Order> results = query.getResultList();

Here the WHERE clause constrains the scan. If an index exists on id, the engine can seek to the boundary and read forward. The client must remember the last ID rather than a page number.

Trade-offs and Limitations

• No random access: Keyset pagination is sequential. You cannot jump directly to "Page 500" without knowing the key at that boundary.
• Unique, stable sort: The sort column must be unique and ideally immutable. If you sort by a non-unique column such as createdAt, add the primary key as a tie-breaker so rows are not skipped or duplicated.
• Client contract changes: The caller sends back a cursor (the last seen key) instead of a page index. This is a breaking change for existing API consumers.
• Not always faster: For small offsets or unindexed sort columns, keyset pagination may not outperform offset. Measure with your data.

How to Verify the Difference

Enable SQL logging to see what Hibernate emits:

# Hibernate 6.x example; property names vary by version
org.hibernate.SQL=DEBUG
org.hibernate.orm.jdbc.bind=TRACE

DEBUG shows the SQL statement. TRACE on the bind category shows parameter values. Run both queries with a large dataset and compare response times.

For deeper analysis, copy the generated SQL into your database console and run EXPLAIN ANALYZE. Look for whether the plan uses an index scan or a sequential scan, and how many rows the engine processes before returning the page. This tells you whether the offset is being absorbed by an index or by brute-force scanning.

When to Choose What

Use offset pagination when users need arbitrary page jumps, the dataset is small, or the offset stays low. Use keyset pagination for infinite scroll, "Load more" buttons, or exports that walk through millions of rows sequentially. If you are unsure, benchmark both with a production-sized dataset before committing.