Sometimes the most important production warning is hidden in plain sight:
firstResult/maxResults specified with collection fetch; applying in memory!
If this appears in your logs, your query is probably paging in memory, not in SQL. It usually happens when we mix pagination and JOIN FETCH on a One-to-Many association.
Why this happens
When you fetch a parent with a collection, SQL returns multiple rows per parent. One Author with five Book rows becomes five result rows. In that shape, limiting rows is not the same as limiting distinct parent entities.
Hibernate cannot guarantee correct parent-level pagination directly in that query, so it loads a larger result set and trims in memory.
Typical anti-pattern
@Query("select a from Author a left join fetch a.books order by a.id")
fun findPageWithBooks(pageable: Pageable): List<Author>Looks convenient, but it breaks the pagination contract for larger datasets.
Safer pattern: two-step loading
Use two queries:
- Page only parent IDs.
- Fetch entities with collection for that small ID set.
@Query("select a.id from Author a order by a.id")
fun findPageOfIds(pageable: Pageable): Page<Long>
@Query("""
select a
from Author a
left join fetch a.books
where a.id in :ids
order by a.id
""")
fun fetchByIdsWithBooks(@Param("ids") ids: List<Long>): List<Author>In service code, combine both calls and preserve ordering.
Why this works in production
- SQL pagination stays deterministic.
- Memory profile is stable under load.
- You still return a rich object graph for the page.
Audit checklist for this class of issue
- Search logs for the warning above.
- Review repositories using
JOIN FETCH+Pageable. - Confirm query plans for large cardinalities.
- Add integration tests with realistic row counts.
For Java/Kotlin systems in active growth, this fix removes a common hidden bottleneck before it turns into latency incidents.
Need help identifying performance issues in your Java/Kotlin codebase? Learn about our code audit and modernization services.