Codeac: Optimizing Large Dataset Retrieval with Offset vs. Keyset Pagination

The Performance Wall of Deep Pagination

When building Java applications with Hibernate, the standard approach to pagination involves setFirstResult() and setMaxResults(). While this works for the first few pages of a result set, it creates a performance bottleneck known as "deep pagination." As the offset increases, the database must scan and discard thousands of rows before returning the requested slice, leading to linear performance degradation, increased disk I/O, and CPU spikes.

Requirements for Efficient Data Retrieval

To maintain a responsive application as datasets grow into the millions of rows, the retrieval system must meet these criteria:

• Constant-time access: Retrieving page 1,000 should be nearly as fast as retrieving page 1.
• Consistency: Users should not see duplicate items or skip records if data is inserted or deleted while they are browsing.
• Memory Safety: The application must prevent OutOfMemoryErrors caused by excessively large page size requests.

Design Comparison: Offset vs. Keyset

The smallest suitable design depends on the expected dataset size and the requirement for random access (jumping to a specific page number).

Feature	Offset-Based (Standard)	Keyset-Based (Seek Method)
Mechanism	LIMIT 10 OFFSET 10000	WHERE id > 10000 LIMIT 10
Complexity	O(n) - Linear scan	O(log n) - Index seek
Stability	Unstable (shifts on inserts)	Stable (anchored to a value)
Random Access	Supported (Page 5 \rightarrow Page 50)	Unsupported (Next/Previous only)

Implementing the Keyset Pattern

Keyset pagination replaces the page index with a "cursor"—the unique identifier of the last record from the previous page. This requires a strictly ordered, non-nullable column, typically the Primary Key.

Example Implementation:

// Run this in your Service layer with @Transactional permissions
// Assuming 'lastSeenId' is passed from the client request
public List fetchNextPage(Long lastSeenId, int pageSize) {
    return entityManager.createQuery(
        "SELECT e FROM Entity e WHERE e.id > :lastId ORDER BY e.id ASC", Entity.class)
        .setParameter("lastId", lastSeenId != null ? lastSeenId : 0L)
        .setMaxResults(pageSize)
        .getResultList();
}

Trust Boundaries and Data Validation

The API layer serves as the trust boundary. Unvalidated pagination parameters can be used to trigger Denial of Service (DoS) attacks via resource exhaustion.

• Page Size Caps: Hard-code a maximum pageSize (e.g., 100). Do not trust the client-provided limit.
• Offset Validation: If using offset pagination, reject negative values and set a maximum allowable offset to prevent the database from hanging on deep scans.
• Type Checking: Ensure the cursor/ID passed for keyset pagination matches the expected data type to prevent SQL injection or casting errors.

Operational Checks and Failure Modes

To verify the health of your pagination strategy, monitor the following:

• Slow Query Logs: Identify queries with high OFFSET values. If the execution time increases proportionally with the page number, the system is hitting the offset wall.
• Execution Plans: Run EXPLAIN on the generated SQL. An offset query will show a "Full Index Scan" or "Filter" on a large number of rows, whereas a keyset query will show an "Index Range Scan."
• Memory Pressure: Monitor JVM heap usage during large requests. Some JPA providers may fetch all results into memory if the SQL dialect doesn't natively support LIMIT.

Failure Mode: The "Missing Item" Problem
In offset pagination, if a record is deleted from page 1 while a user is moving to page 2, the first item of page 2 shifts to page 1. The user will skip that record entirely. Keyset pagination avoids this because the query is anchored to a specific ID, not a relative position.

When to Change the Design

You should migrate from offset to keyset pagination when:

1. The dataset exceeds the threshold where OFFSET queries consistently take longer than 500ms.
2. The business requirement shifts from "Jump to Page X" to "Infinite Scroll" or "Load More."
3. Data volatility (high frequency of inserts/deletes) causes visible inconsistencies in paginated views.

Rollback Strategy: Since switching to keyset pagination changes the API contract (replacing pageNumber with lastId), maintain a versioned API endpoint (e.g., /v1/items for offset and /v2/items for keyset) to allow clients to migrate without breaking existing integrations.