Offset vs Keyset Pagination in Hibernate: A Decision Guide

Decision Context

When you expose a REST endpoint that returns a large table—think millions of rows—choosing the right pagination strategy is critical. A naive LIMIT/OFFSET approach can become a performance bottleneck, while a keyset (seek) method can keep queries fast but changes navigation semantics. This guide helps you decide which strategy fits your use case and shows how to implement and validate each in Hibernate.

Key Decision Factors

• Dataset size – How many rows will you query? Millions?
• Navigation pattern – Do clients need random page jumps or only forward/backward traversal?
• Consistency requirements – Is it acceptable to see duplicates or gaps if the underlying table changes?
• Indexing – Do you have a unique, monotonically increasing column (e.g., primary key) that can be used for keyset?
• Developer effort – Can you afford custom JPQL, or do you need a quick built‑in solution?

Option Comparison Table

Feature	Offset‑Based	Keyset (Seek)
Implementation complexity	Very low – setFirstResult(), setMaxResults()	Moderate – manual JPQL/HQL with WHERE id > :lastId
Random access	Supported – any page number	Not supported – only sequential next/prev
Performance at deep pages	Degrades linearly – DB scans OFFSET rows	Constant – index seek on id
Consistency under concurrent changes	High risk – duplicates/skips if rows inserted/deleted	Lower risk – deterministic if id is monotonic
Index requirement	None – full scan if no index on order column	Required – index on key column for seek
Developer effort	Minimal	Requires custom query building

Trade‑off Analysis

In practice, the choice often boils down to dataset size and navigation needs. For tables with under 10,000 rows, the difference is negligible, and offset pagination is usually fine. Once you cross that threshold, especially for pages beyond the first dozen, keyset pagination offers significant latency savings.

If your API must support page number requests (e.g., ?page=42), offset is the only viable option unless you implement a hybrid approach (e.g., keep a page cache). If you can expose a cursor or last‑seen ID token, keyset becomes attractive because it avoids the costly scan.

Another consideration is data volatility. In a heavily written table, offset pagination can return duplicate or missing rows when the offset changes between requests. Keyset pagination, by filtering on a stable primary key, will always return the next distinct set of rows.

Concrete Implementation

Offset‑Based Example (Hibernate 6)

// Run on the server side, e.g., in a service method
int page = 0; // zero‑based
int pageSize = 50;
List<User> users = entityManager.createQuery(
    "SELECT u FROM User u ORDER BY u.id ASC", User.class)
    .setFirstResult(page * pageSize)
    .setMaxResults(pageSize)
    .getResultList();

Replace page with the desired page number. Ensure the ORDER BY column is indexed to mitigate scan costs.

Keyset Pagination Example

// Assuming the client sends the last seen ID
Long lastId = 12345L; // null for the first page
int pageSize = 50;
String jpql = "SELECT u FROM User u WHERE u.id > :lastId ORDER BY u.id ASC";
List<User> users = entityManager.createQuery(jpql, User.class)
    .setParameter("lastId", lastId)
    .setMaxResults(pageSize)
    .getResultList();

For the first page, set lastId to the minimum value (e.g., Long.MIN_VALUE) or omit the WHERE clause entirely. The client should store the highest id from the current page and pass it as lastId for the next request.

Hybrid Approach (Optional)

Some projects use a hybrid: offset for the first few pages, keyset thereafter. This requires tracking both the page number and the last seen ID. It can be complex, so weigh the benefits carefully.

Validation & Diagnostics

After implementing, validate performance and correctness:

1. Execution plan check
   • Run the offset query with a large offset: setFirstResult(1_000_000).
   • Inspect the database plan for Full Index Scan or high logical reads.
2. Index seek verification
   • Execute the keyset query and confirm the plan shows an Index Seek on the id column.
3. Latency comparison
   • Measure round‑trip time for page 1, 10, 100 using both strategies on a 1M‑row table.
   • Expect near‑constant latency for keyset and linear growth for offset.
4. Data consistency test
   • Insert a new row between two offset page requests and observe if a row is duplicated or skipped.
   • Repeat with keyset and confirm no duplication.

When to Roll Back

Only keyset pagination changes application logic (e.g., the API contract). If you decide to switch back to offset, simply revert the query and remove the cursor token. No schema changes are required.

Conclusion

For small to medium tables or when random page access is mandatory, offset pagination is the simplest and sufficient choice. For large, read‑heavy tables where performance matters and navigation can be sequential, keyset pagination delivers consistent, low‑latency responses and better resilience to concurrent writes.

Implement the strategy that aligns with your dataset size, navigation pattern, and consistency needs, then use the diagnostics above to confirm the expected behavior.