Choosing Between Offset and Keyset Pagination in Hibernate ORM

The Performance Gap in Data Retrieval

When implementing pagination in Java applications using Hibernate, developers often default to setFirstResult() and setMaxResults(). While this is the simplest path, it introduces a critical performance bottleneck: as the page number increases, the database must scan and discard an increasing number of rows before returning the requested set. In large datasets, this leads to linear performance degradation and potential database timeouts.

The core decision is whether to use Offset-based pagination (skipping rows) or Keyset-based pagination (seeking from a known point). The right choice depends on your dataset size and whether your users need to jump to specific page numbers.

Comparison of Pagination Strategies

Feature	Offset-based (Limit/Offset)	Keyset-based (Seek Method)
Implementation	Simple (Built-in Hibernate methods)	Custom WHERE clauses
Performance	Degrades as offset increases	Constant regardless of depth
Random Access	Supports jumping to Page X	Sequential navigation only
Consistency	Items may shift/duplicate on inserts	Stable results between pages
Requirement	None	Indexed, unique, ordered column

Trade-offs and Decision Logic

Offset-based pagination is appropriate for small tables (under 10,000 rows) or administrative interfaces where users rarely navigate past the first few pages. Because Hibernate translates setFirstResult(n) into the SQL OFFSET n clause, the database engine must still read the first n rows from the disk, even if it doesn't return them to the application.

Keyset-based pagination is essential for high-volume datasets or infinite-scroll interfaces. Instead of telling the database how many rows to skip, you tell it exactly where the last page ended. This allows the database to perform an Index Seek, jumping directly to the starting row of the next page.

Implementation Examples

Assume a Product entity with a unique, indexed id and a createdAt timestamp. We are using Hibernate 6.x.

Option A: Offset-based (Standard)

Run this within a Service layer using a Session or EntityManager. This approach is high-risk for tables with millions of rows.

// Required Permissions: Read access to Product table
// Risk: High DB load for large 'firstResult' values
Query<Product> query = session.createQuery("from Product p order by p.id", Product.class);
query.setFirstResult(100000); // Skip 100k rows
query.setMaxResults(20);      // Fetch 20 rows
List<Product> results = query.getResultList();

Option B: Keyset-based (Optimized)

This method requires the client to pass the ID of the last item seen on the previous page.

// Required Permissions: Read access to Product table
// Requirement: 'id' must be indexed and sorted
Long lastSeenId = 100000L; // Provided by the client/frontend
int pageSize = 20;

String hql = "from Product p where p.id > :lastId order by p.id asc";
Query<Product> query = session.createQuery(hql, Product.class);
query.setParameter("lastId", lastSeenId);
query.setMaxResults(pageSize);

List<Product> results = query.getResultList();

Validating the Decision

To verify if your pagination strategy is performing efficiently, examine the database execution plan (e.g., using EXPLAIN ANALYZE in PostgreSQL or EXPLAIN in MySQL).

• Offset Warning: If you see a Parallel Seq Scan or a high number of rows removed by filter, the offset is causing a performance hit.
• Keyset Success: Look for an Index Seek or Index Scan that targets a specific range of the index, indicating the database jumped directly to the starting point.

Limitations

Keyset pagination cannot support "Jump to Page 500" functionality because the application does not know the ID of the first element of page 500 without scanning the preceding pages. If random access is a hard requirement, you must either use offset-based pagination and accept the performance penalty or implement a caching layer for page boundaries.