Diagnosing Inconsistent Pagination in Hibernate: setFirstResult and setMaxResults

The Problem: Unstable Result Sets and Performance Degradation

When implementing pagination in Java applications using Hibernate, developers often encounter two critical failures: unstable result sets (where records appear on multiple pages or vanish entirely) and exponential latency as the user navigates to deeper pages. These issues typically stem from a mismatch between the Java-level pagination API and the underlying database's execution plan.

Diagnostic Matrix: Common Pagination Failures

Symptom	Likely Cause	Diagnostic Indicator
Duplicate records across pages	Non-unique Sort Column	ORDER BY uses a column with duplicate values (e.g., created_date).
Query time increases with page number	Offset Scanning	DB Execution plan shows a full index scan up to the offset.
Incorrect record counts	Calculation Logic Error	setFirstResult is calculated as page * size instead of (page - 1) * size.
SQL Syntax Errors	Dialect Mismatch	Generated SQL uses LIMIT/OFFSET on a database requiring OFFSET FETCH.

Step-by-Step Verification Process

Follow these checks to isolate the root cause of pagination instability.

1. Validate Result Determinism

Hibernate relies on the database to maintain order. If your ORDER BY clause targets a column that is not unique, the database does not guarantee the order of rows sharing the same value. This leads to "shuffling" between page requests.

• Check: Identify the columns used in the orderBy() method of your Query object.
• Fix: Always append a unique identifier (usually the Primary Key) as the final sort criteria.

// Unstable: Order by date only
query.orderBy(Order.asc("createdDate")); 

// Stable: Order by date, then unique ID
query.orderBy(Order.asc("createdDate"), Order.asc("id"));

2. Inspect Generated SQL Dialects

Different databases handle pagination differently. For example, SQL Server 2012+ requires an ORDER BY clause to use OFFSET FETCH, while MySQL uses LIMIT. If the Hibernate Dialect is misconfigured, the query may fail or behave unexpectedly.

• Action: Enable SQL logging in application.properties: spring.jpa.show-sql=true.
• Verification: Run the query and copy the generated SQL into your database console. Check if the OFFSET and LIMIT (or equivalent) clauses match your database vendor's requirements.

3. Analyze Offset Performance

Using setFirstResult(10000) forces the database to read 10,000 rows and discard them before returning the next set. This is an O(N) operation that degrades as the offset increases.

• Action: Run an EXPLAIN plan on the generated SQL for page 1 versus page 100.
• Risk: If the execution plan shows a "Full Table Scan" or "Index Scan" that grows linearly with the offset, your application will experience timeouts as the dataset grows.

Implementation Example: Stable Pagination

This example demonstrates a robust implementation for Hibernate 5.4+ using a unique sort key and correct offset calculation.

public List<Product> getProductsPaged(int pageNumber, int pageSize) {
    // Ensure pageNumber starts at 1 to avoid negative offsets
    int firstResult = (Math.max(1, pageNumber) - 1) * pageSize;

    return session.createQuery("FROM Product p WHERE p.status = :status", Product.class)
        .setParameter("status", Status.ACTIVE)
        // Critical: Sort by a non-unique field AND the primary key
        .orderBy(Order.desc("lastModified"), Order.asc("id")) 
        .setFirstResult(firstResult)
        .setMaxResults(pageSize)
        .getResultList();
}

Comparison: Offset vs. Keyset Pagination

When setFirstResult becomes a performance bottleneck, consider switching to Keyset Pagination (also known as the "Seek Method").

Feature	Offset Pagination (setFirstResult)	Keyset Pagination (Seek)
Implementation	Simple API call	Manual WHERE clause filtering
Performance	Slows down on deep pages	Constant time regardless of page
Use Case	Small datasets, random page access	Large datasets, infinite scroll
Data Drift	Items shift if new rows are inserted	Consistent view of the sequence

Escalation Criteria

If the following conditions persist after applying the fixes above, escalate to a Database Administrator (DBA):

• The EXPLAIN plan shows that the database is ignoring indexes despite the ORDER BY and WHERE clauses being correctly indexed.
• Memory leaks occur in the JVM specifically during large getResultList() calls (indicating potential issues with the Hibernate Session cache or fetch size).
• The database is returning different result counts for the same query within milliseconds, suggesting an isolation level issue (e.g., Read Committed vs. Repeatable Read).

Verification of Result

To verify the fix, execute a "Boundary Test": request the last page of the dataset and the first page. Ensure the total count of records across all pages matches the result of a SELECT COUNT(*) query. If the sum of paginated records differs from the total count, check for concurrent data modifications during the pagination sequence.