Question
Native SQL Pagination Limits with setMaxResults
Neel Byte
0 reputation · 22 Mar 2021, 04:08 UTC
52.5K views
Loading…
0 reputation · 22 Mar 2021, 04:08 UTC
Investigate how setMaxResults interacts with Hibernate query execution when native SQL is employed, given that the first-level cache does not support pagination parameters. The documented behavior specifies that setFirstResult defines a zero-based index and setMaxResults limits result count, but exact SQL generation varies by dialect. A precise goal is to determine whether omitting an explicit orderBy clause with setFirstResult produces non-deterministic ordering when entities are cached across Hibernate 5.x and 6.x. Constraints include the requirement for ordered result sets to ensure deterministic pagination and the performance impact of large offset values due to database row scanning. Uncertainty remains about how dialect-specific LIMIT/OFFSET syntax affects result boundaries when the cache bypasses first/max results entirely.
Does the absence of cache support for setFirstResult/setMaxResults introduce ordering inconsistencies when native SQL pagination is combined with a default entity identifier order? Can dialect-dependent LIMIT/OFFSET generation cause silent result set truncation at high offset values? Is there a documented strategy to enforce deterministic pagination without explicit orderBy when using native SQL queries?
34775 reputation · 22 Mar 2021, 05:43 UTC
When a native SQL query is executed in Hibernate 5.x or 6.x, the setFirstResult() and setMaxResults() methods are translated by the configured Dialect into database‑specific pagination syntax. For example, PostgreSQL and MySQL use LIMIT and OFFSET, Oracle uses FETCH FIRST, and SQL Server uses OFFSET/FETCH NEXT. The pagination is applied at the database level, so only the requested slice of rows is transferred to the application.
Hibernate’s first‑level cache stores entities by identifier but does not preserve the order of a result set. If a native query omits an ORDER BY clause, the database is free to return rows in any order that satisfies the query plan. Consequently:
npssORDER BY is required to guarantee deterministic pagination.Dialect translation does not silently truncate results; the generated SQL will include the appropriate LIMIT/OFFSET or FETCH clause. However, very large offset values force the database to scan and discard that many rows before returning the page, which can lead to noticeable latency or timeouts that may appear as a failure to retrieve data.
There is no documented strategy that removes the need for an explicit ORDER BY when using native SQL. The recommended practice is to always include a deterministic sort, typically:
ORDER BY created_at DESC, id ASC).Query query = session.createNativeQuery(
"SELECT * FROM users ORDER BY created_at DESC, id ASC"
);
query.setFirstResult(0);
query.setMaxResults(20);
List results = query.list();
Diagnostic Detail Needed: To refine this advice for your environment, please specify the database vendor and version you are using (e.g., PostgreSQL 15, Oracle 19c).
Use comments to ask for clarification. Post a solution as an answer.
34,775 reputation · 22 Mar 2021, 15:22 UTC
One clarification worth adding: the in-memory pagination pitfall that affects HQL queries with collection fetch joins does not apply to native SQL. Hibernate logs a warning and applies setMaxResults in memory for joined entity fetches because it must deduplicate parent rows; with createNativeQuery, it never rewrites your SQL, so the limit applies to raw result rows exactly as the dialect emits it. That also means a native query joining a one-to-many collection can return fewer distinct parents than maxResults suggests — the limit counts joined rows, not entities.
On the deep-offset concern, a practical escape hatch is keyset (seek) pagination: WHERE id > :lastSeenId ORDER BY id with setMaxResults. It avoids the scan-and-discard cost entirely and works identically for native and HQL. Verify with hibernate.show_sql that the expected LIMIT/FETCH FIRST clause appears, and test paging against the real database — dialect output differs between Hibernate 5.x and 6.x.