JPA Offset Pagination: When to Use setFirstResult and When to Switch to Keyset

The short answer

Use JPA offset pagination—setFirstResult() plus setMaxResults(), or Spring Data's PageRequest—only for bounded pages such as admin tables or search results where users rarely go past the first few pages. For deep feeds, exports, or infinite scroll, switch to keyset (cursor) pagination on a stable, unique sort key. Offset pagination asks the database to produce and discard every skipped row, so deep pages get slower as the offset grows, and concurrent writes can shift rows between pages.

How offset pagination works

In JPA, setFirstResult(n) and setMaxResults(m) are translated by the JPA provider into the dialect's row-limiting SQL. Hibernate, for example, converts these into LIMIT/OFFSET on PostgreSQL and MySQL, FETCH FIRST/OFFSET on newer databases, or vendor-specific equivalents. A JPQL ORDER BY or Spring Data Sort becomes the SQL ORDER BY clause.

Consider an invoice table where you need the newest records first. The following JPA query fetches page 3 with a page size of 20:

TypedQuery<Invoice> q = em.createQuery(
    "select i from Invoice i where i.status = :s order by i.createdAt desc, i.id desc",
    Invoice.class);
q.setParameter("s", status);
q.setFirstResult(page * size);  // e.g., 3 * 20 = 60
q.setMaxResults(size);          // e.g., 20
List<Invoice> rows = q.getResultList();

Two details matter here. First, the ORDER BY includes a unique tiebreaker (i.id) in addition to the timestamp. Second, the offset is calculated as page * size, which assumes zero-based page numbering.

Spring Data equivalent

Spring Data JPA expresses the same intent through a repository method:

Page<Invoice> result = repo.findByStatus(
    status,
    PageRequest.of(page, size, Sort.by(Sort.Direction.DESC, "createdAt", "id"))
);

Returning Page triggers a separate COUNT query to populate total-page metadata. If you only need "next page" navigation and do not need exact totals, return Slice or List instead; those avoid the count query entirely.

Limits of OFFSET pagination

The database must generate and discard the skipped rows before returning your page. On a large table, page 500 is not simply 500 times slower than page 1—the cost depends on how many rows the engine scans and whether an index can satisfy the sort. Deep offsets can cause timeouts, memory pressure, or excessive I/O.

Concurrency introduces a second problem. If rows are inserted or deleted between page requests, the offset shifts: a row that was at position 60 may move to position 59 or 61, causing it to appear on two pages or none. A unique tiebreaker makes the order deterministic, but it does not prevent the window from sliding when the underlying data changes.

Non-unique sort keys are a third trap. Sorting only by createdAt when many rows share the same timestamp can produce duplicate or missing records across pages, because the database may return rows with identical sort values in a different physical order on each query.

Sorting by association paths can introduce implicit joins and unstable ordering; prefer scalar indexed columns with an explicit tiebreaker. Pagination SQL, count-query optimization, and LIMIT/OFFSET support are also dialect- and version-sensitive, so verify behavior against your exact Hibernate, Spring Data, and database versions rather than assuming identical SQL across environments.

Common mistakes

• Paginating without ORDER BY. Without an explicit sort, the database may return rows in any order, and pagination becomes meaningless.
• Sorting only by a non-unique timestamp. Always add a unique column such as the primary key as a tiebreaker.
• Allowing very large page sizes. A client requesting size=100000 can exhaust memory. Enforce a server-side maximum.
• Using Page for infinite scroll. The count query can be expensive on large tables. Use Slice, List, or cursor metadata instead.
• Ignoring N+1 loads. Rendering lazy associations from page content can trigger one query per row. Use fetch joins or entity graphs deliberately.

Verifying the generated SQL

Never assume the provider emits the SQL you expect. Enable Hibernate's SQL logger in your application configuration (for example, set hibernate.show_sql=true or use a datasource proxy such as P6Spy) and observe the queries emitted during a page request. Confirm the page query contains the expected ORDER BY and row-limiting clause, and that a separate COUNT query appears only when the repository method returns Page.

For execution plans, run the captured page query in a database client or SQL console with read-only permissions, prefixed by your database's explain command:

-- PostgreSQL example; use the equivalent for your database
EXPLAIN ANALYZE
SELECT ... FROM invoice
WHERE status = 'PAID'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 60;

Expected check: the plan should use an index on the sort columns. Risk: on very large tables, a deep offset may show a high number of rows scanned and discarded. If so, offset pagination is unsuitable for that access pattern.

Add an integration test that requests page 0, a middle page, and the last page while inserting and deleting rows. Assert that the order is stable and that no record appears twice or is skipped when a unique tiebreaker is present. If you support multiple databases, compare the emitted SQL for each supported dialect rather than assuming identical behavior.

When to choose keyset pagination

Keyset pagination replaces OFFSET with a predicate on the last-seen sort key. Instead of skipping 60 rows, you ask for rows after a known boundary:

select i from Invoice i
where i.status = :s
  and (i.createdAt < :lastCreatedAt
       or (i.createdAt = :lastCreatedAt and i.id < :lastId))
order by i.createdAt desc, i.id desc

This approach scales to deep pages because the database seeks directly to the boundary rather than discarding rows. It requires a stable, usually unique, sort key and a defined restart behavior if the key is missing, null, or duplicated. For mostly read-only data with large result sets, keyset pagination is the more reliable default.

Practical decision checklist

Scenario	Recommended approach	Why
Admin table, first 10 pages	Offset with unique tiebreaker and max page size	Simple, random access, low depth
Infinite scroll or feed	Keyset/cursor pagination	Deep navigation, no count query, stable window
Full export	Keyset pagination or streaming	Offset cost grows with depth
Need exact total pages	Offset with Page and monitored count query	Totals require a separate count

Offset pagination is not wrong; it is simply bounded. Use it where the page number stays small, the sort key is unique, and the page size is capped. Move to keyset pagination when users can reach deep pages or when the result set is large and mostly read-only.