You own a JPQL-backed listing over a table of tens of millions of rows, where each listed row also shows a few of its child records. How do you decide the pagination strategy, and what guardrails do you put on it?

## Framing At this size, pagination is an API contract decision, not a query detail. Three separate questions have to be answered, and teams usually conflate them: *how do I select the page*, *how do I load the children*, and *what do I promise about totals and stability*. ## 1. Selecting the page **Offset (`setFirstResult`/`setMaxResults`)** is the default because it is simple and supports numbered pages. Its two costs are structural: the database must produce and discard everything before the offset, and the window shifts when rows are inserted or deleted between clicks, so users see duplicates or misses. **Keyset/seek** replaces the offset with a range predicate on the last row's sort key. Cost becomes independent of depth and pages are stable under concurrent writes, at the price of no random page access, no meaningful page numbers, and one cursor shape per sort order. My decision rule: - Highly filtered admin/search screens where the filtered set is small and users want page numbers → offset, with a depth cap. - Feeds, timelines, infinite scroll, notification lists → keyset. - Machine consumers, exports, reconciliation jobs, anything that must not duplicate or skip a record → keyset, always. - Mixed reality: offset for the first *k* pages so the numbered pager works, and a cursor beyond that. It is more code, so I only do it when the product insists on both. ## 2. Loading the children — where paging usually dies The instinct is `left join fetch parent.children` plus a page size. That is precisely the combination Hibernate cannot push into SQL: the join produces one row per child, a SQL `limit` would truncate a parent's collection, so Hibernate removes the limit, materialises **every** matching parent and child, and slices in memory. On tens of millions of rows that is an OutOfMemoryError waiting for traffic. It also stays *correct*, so it will never fail a test with seed data. Options, roughly in the order I reach for them: - **Do not return entity graphs from a list endpoint at all.** Project a DTO with the columns the list renders. Lists are read-only views; they do not need managed entities. - **Aggregate instead of enumerate.** "3 items" and "latest status" are `count`/`max` in the same query. Most list screens want a summary, not the collection. - **Two-query id-then-fetch.** Page the ids with a SQL-limited query, then load the graph for exactly those ids. Two round trips, both bounded. - **`@BatchSize` on the association.** Keep it lazy; Hibernate initialises collections in groups of *n* by owner id. Zero query rewriting and it protects every code path, which makes it the best default for a codebase rather than a single screen. - **Top-N per parent** ("show the 3 most recent lines") is not expressible in portable JPQL; that is a native query with a lateral join or a window function, run once for the page's ids. I avoid `FetchMode.SUBSELECT` on paged queries, because the subselect re-runs the original query *without* the limit. ## 3. Totals An exact count over a filtered set of tens of millions of rows is a second heavy read on every page click, and the number is stale the moment it is computed. I treat an exact total as a feature with a price: - Default: request `pageSize + 1` rows and return `hasNext`. Zero extra queries. - If a total is required: cap it ("10,000+"), compute it once per filter and carry it in the cursor, or accept an approximate figure from a native query against planner statistics. - With keyset paging, page numbers are meaningless anyway, which is often the honest answer to give the product team. ## 4. Guardrails I insist on - **Hard maximum page size** enforced server-side; clamp rather than trust the caller. An unbounded `maxResults` is a denial-of-service parameter. - **Maximum offset depth.** Past it, the API returns an error telling the client to use a cursor. This stops one crawler from pinning the database. - **A total ordering** on every paged query — business key plus primary key. Without it pages overlap non-deterministically, and that bug is nearly impossible to reproduce. - **Fail fast on the fetch-join trap.** Set Hibernate's `fail_on_pagination_over_collection_fetch` in test and staging so the in-memory fallback throws instead of warning. - **Assert query counts and SQL shape in tests** for the list endpoints — one statement for the page, a bounded number for children, and the page SQL must contain a limit. - **Bound cursors are opaque and validated.** They are query parameters; they get bound, never concatenated, and encode which sort they belong to so a stale cursor cannot silently change the ordering. ## 5. Then measure Compare p50/p99 for page 1 and for a deep page, count statements per request, and watch heap during a load test that walks the list. The failure modes here are quiet at low volume and catastrophic at high volume, so the evidence has to come from a realistic dataset, not a fixture.