Keyset Pagination in JPA: Replacing Slow Deep OFFSET Queries with Seek-Based Paging

Your events dashboard was fast when users looked at the first page. Fifty clicks later, the same “next” button takes seconds. The page size never changed—still twenty rows—but the database is doing dramatically more work. That gap is usually OFFSET pagination, and it gets worse exactly when users need it most.

The fix is not a bigger connection pool or a cache. It is changing how you ask for the next page. Instead of telling the database to skip N rows, you tell it where to start. That approach is called keyset pagination, or seek pagination, and in JPA it is a small change to your JPQL with a large effect on deep pages.

The hidden cost of OFFSET

When you call setFirstResult(1000) and setMaxResults(20), the database does not magically jump to row 1001. It produces the first 1000 rows that match your filters, throws them away, and returns the next twenty. The deeper the user pages, the more rows the database must generate and discard before it returns anything useful.

There is a second, less obvious problem: stability. If another transaction inserts a row with a sort key lower than your current window between requests, every subsequent row shifts forward. A row that belonged on page 3 can appear on page 2, and a row can be skipped entirely. Deletes have the opposite effect, pulling rows backward and causing duplicates. For small tables or admin screens this is harmless. For large, frequently written tables it corrupts the user’s view of the data.

How keyset pagination works

Keyset pagination removes the offset entirely. You remember the last row the user saw and ask for rows that come strictly after it. The database can use an index to seek close to that position, so the work is proportional to the page size, not the page number.

The requirement is a deterministic order. If you sort only by createdAt, two rows created in the same millisecond can appear in either order on different requests. You need a unique tie-breaker. The common pattern is to sort by a timestamp plus the primary key:

ORDER BY e.createdAt ASC, e.id ASC

Your WHERE clause then encodes the same ordering as a range predicate:

WHERE (e.createdAt > :lastCreatedAt
       OR (e.createdAt = :lastCreatedAt AND e.id > :lastId))

This is a lexicographic comparison. The database seeks to the first row that satisfies the tuple (createdAt, id) greater than the last returned tuple. With a composite index on (createdAt, id), the planner can perform an index range scan instead of a full scan plus discard.

A worked JPQL example

Assume an Event entity mapped to an events table. The repository method below fetches the next page after a known cursor. Run this in your Spring Data JPA repository or directly through an EntityManager. It requires only read permissions on the underlying table.

public interface EventRepository extends JpaRepository<Event, Long> {

  @Query("""
      SELECT e FROM Event e
      WHERE (e.createdAt > :lastCreatedAt
             OR (e.createdAt = :lastCreatedAt AND e.id > :lastId))
      ORDER BY e.createdAt ASC, e.id ASC
      """)
  List<Event> findNextPage(@Param("lastCreatedAt") Instant lastCreatedAt,
                            @Param("lastId") Long lastId,
                            Pageable pageable);
}

Call it with a page size but no offset:

Pageable limit = PageRequest.of(0, 20); // page number is ignored for keyset
List<Event> page = eventRepository.findNextPage(cursor.createdAt(), cursor.id(), limit);

Build the next cursor from the last row of the result. If the list is empty, you have reached the end. Notice that setFirstResult is absent; the WHERE clause does the seeking.

Expected check: the returned rows should all be strictly greater than the cursor tuple. If any row has a createdAt equal to the cursor and an id less than or equal to the cursor id, the predicate is wrong.

Trade-offs and limitations

Keyset pagination is not free. The biggest loss is random access. You cannot jump to page 50 without either walking from the beginning or storing intermediate cursors. If your UI requires “go to last page” or arbitrary page numbers, keyset pagination fights that design.

Backward paging is also harder. You can reverse the query with < and DESC ordering, but then you must reverse the results in memory to preserve display order. Alternatively, you can remember a stack of cursors as the user moves forward.

Total-count queries remain a separate problem. Keyset pagination makes page reads cheap, but a COUNT(*) over a huge filtered table is still expensive. If your UI shows “page 3 of 500,” that count will not improve.

Mutable sort columns break stability. If createdAt can be updated after insert, rows can move between pages. Prefer an immutable key such as a database-assigned creation timestamp or a monotonic sequence. If you must sort by a mutable column, accept that later updates can cause visible drift.

Finally, timestamp precision matters. If the column stores milliseconds but the application truncates to seconds, rows can collide on the tie-breaker. Use a consistent column type—Instant or TIMESTAMP with matching precision—and always include the unique id tie-breaker.

How to verify before you switch

Do not rewrite every endpoint blindly. Start with the queries that show deep paging in production. On a production-like dataset, run EXPLAIN ANALYZE for both the offset and keyset versions at shallow and deep pages. You are looking for the keyset plan to use an index range scan and examine roughly the page-size number of rows, while the offset plan examines and discards everything before the page. Generated SQL and index selection vary by database and Hibernate version, so confirm the plan on your actual stack.

Next, walk every page in a test and collect the union of primary keys. Compare that set against the expected full result set. Any duplicate or missing id means the cursor predicate or ordering is inconsistent.

Measure p50 and p95 latency under realistic filters and concurrent writes. Inspect your provider SQL logs to confirm that setMaxResults maps to a native LIMIT (or FETCH FIRST) clause and that no unexpected client-side paging is happening. Finally, treat cursors as untrusted input: validate, encode, and constrain both direction and page size before passing them to the database.

Bottom line

Deep OFFSET pagination asks the database to do work proportional to how far the user has scrolled. Keyset pagination asks it to seek to a position. For large tables with unique, immutable sort keys, that change keeps latency flat and eliminates the duplicate-row problem caused by concurrent writes. Measure the endpoints that already hurt, confirm the index plan, and migrate those first.