Avoiding the Offset Pitfall: Practical Hibernate Pagination with setFirstResult and setMaxResults

Offset Pagination in Hibernate: The Core Problem

When you need to display a slice of a large result set—say, page 5 of 1000 rows—Hibernate offers two simple methods on a Query or Criteria object: setFirstResult() and setMaxResults(). Together they form the classic offset‑based pagination pattern.

In practice, you typically write:

Query q = session.createQuery("from Order o order by o.id asc");
q.setFirstResult(pageNumber * pageSize); // skip N rows
q.setMaxResults(pageSize);                 // limit returned rows
List<Order> page = q.list();

While this works for small tables, the database must still scan and discard all rows before the offset. On a table with millions of rows, asking for page 5000 means the engine processes 5 million rows before returning the desired 20. The result is high CPU, I/O, and memory usage, and the response time can spike dramatically.

Why setFirstResult and setMaxResults Can Be Hazardous

• Full table scans on large offsets: The database reads every row up to the offset to satisfy the query, even if the final result set is tiny.
• Session state interference: If you don’t flush or clear the Session before executing a paginated query, stale entities or duplicates may appear because Hibernate still holds pending changes.
• Second‑level cache effects: Cached entities can hide recently inserted rows, leading to inconsistent page boundaries across requests.
• Lazy associations: After pagination, accessing a lazily loaded collection may trigger a separate query per entity, potentially causing a LazyInitializationException if the session is closed.
• Non‑deterministic ordering: Concurrent inserts or deletes between paginated requests can shift rows, causing duplicates or missing rows when using offset pagination.

When Offset Pagination Is Acceptable

Offset pagination remains useful in the following scenarios:

1. Small result sets: Tables with tens of thousands of rows where page numbers stay below a few hundred.
2. Static data: Read‑only datasets that rarely change.
3. Prototype or admin tools: Quick, low‑traffic interfaces where performance is not critical.

In these cases, the simplicity of setFirstResult() outweighs the cost. Just remember to keep the page size reasonable (e.g., 20–50 rows) to avoid large offsets.

Key‑Set Pagination: The Scalable Alternative

For large tables, key‑set pagination (also called cursor pagination) is the recommended pattern. Instead of skipping rows, you filter by a key that comes after the last row of the previous page. The query looks like:

Query q = session.createQuery(
    "from Order o where o.id > :lastId order by o.id asc");
q.setParameter("lastId", lastOrderId); // id of the last row on the previous page
q.setMaxResults(pageSize);
List<Order> page = q.list();

Because the database can use an index on id, it jumps directly to the first row after lastId and reads only pageSize rows. The cost is linear in the number of rows returned, not the offset.

Key‑set pagination requires:

• Stable, unique ordering columns (e.g., primary key).
• Client‑side state to remember the last key (often stored in a hidden form field or cookie).
• Handling of inserts/deletes between pages to avoid missing or duplicated rows (e.g., using a snapshot timestamp or database versioning).

Concrete Example: Paginating Orders with Hibernate 6.x

Assume we have an Order entity with a primary key id and a createdAt timestamp. We want 20 orders per page, sorted by creation time.

Offset Pagination (for comparison)

// page 3 (zero‑based) → skip 40 rows
int pageSize = 20;
int pageNumber = 3;
Query q = session.createQuery(
    "from Order o order by o.createdAt asc", Order.class);
q.setFirstResult(pageNumber * pageSize);
q.setMaxResults(pageSize);
List<Order> orders = q.list();

Key‑Set Pagination

// lastCreatedAt holds the timestamp of the last order from the previous page
LocalDateTime lastCreatedAt = ...; // null for the first page
int pageSize = 20;
Query q = session.createQuery(
    "from Order o where o.createdAt > :lastCreatedAt order by o.createdAt asc", Order.class);
q.setParameter("lastCreatedAt", lastCreatedAt);
q.setMaxResults(pageSize);
List<Order> orders = q.list();

For the first page, set lastCreatedAt to null and adjust the HQL to remove the where clause when it’s null.

Trade‑Offs and Limitations

• Complexity: Key‑set pagination requires more client logic (storing the last key, handling edge cases).
• Non‑deterministic ordering: If two rows share the same createdAt, you must add a secondary sort (e.g., id) to guarantee uniqueness.
• Missing rows after deletes: If an order is deleted between pages, the next page may skip a row. Acceptable in many UI contexts but not for strict data export.
• Index requirement: The ordering column must be indexed; otherwise, the performance advantage disappears.

Actionable Checklist Before Choosing a Pagination Strategy

1. Measure the size of the result set and typical page numbers.
2. Check for an index on the ordering column(s).
3. Determine if the dataset changes frequently during user sessions.
4. For read‑only or small tables, keep offset pagination; for large or dynamic tables, switch to key‑set.
5. Implement a simple test: run both queries with setMaxResults=10 and compare execution plans via EXPLAIN.
6. Monitor latency and I/O in production; if page 10+ takes >200 ms, consider key‑set.

By following this checklist, you can avoid the hidden cost of large offsets and deliver a responsive user experience even on very large tables.

Final Takeaway

Hibernate’s setFirstResult and setMaxResults are quick to implement but can become a performance bottleneck on large offsets. When scaling beyond a few thousand rows, adopt key‑set pagination, which leverages indexed keys to jump directly to the desired slice. Keep an eye on session state, caching, and lazy associations to avoid stale data or exceptions. With a clear strategy and a small amount of client logic, you can maintain fast, predictable pagination in any Hibernate‑powered application.