What fetch size minimizes memory pressure while keeping round‑trips acceptable for Hibernate offset‑based pagination on large datasets?

The goal is to identify a fetch size for Hibernate’s setMaxResults that balances reduced database round‑trips against memory consumption when paginating large tables with offset‑based queries.

Constraints include the known degradation of LIMIT/OFFSET performance as the offset grows, the risk of OutOfMemoryError when the fetch size is too large, and the interaction with lazy loading that can trigger N+1 selects if fetch joins are not used. Additionally, Hibernate 5 and Hibernate 6 handle setMaxResults differently in native queries, which may shift the optimal value.

• What fetch size range yields the best trade‑off for a table with one million rows given a typical 2 GB JVM heap?
• How does the optimal fetch size differ between Hibernate 5 and Hibernate 6 when using native queries with setMaxResults?
• Should the fetch size be configured per query or can a global setting be safely applied across varied workloads?