Efficient Pagination — Offset vs Keyset with Spring Data

Offset pagination — LIMIT 20 OFFSET 200 — is the default in Spring Data and works well for the first few pages. Beyond page 10 or 20 in a large table, it becomes a performance problem: the database must read and discard all rows before the offset. At page 1000 of a 50-row page with a 100,000-row table, that means reading 50,000 rows to return 50. Keyset pagination solves this by navigating with a cursor rather than a row count.

The offset pagination problem

// Spring Data default — offset under the hood
Page<Order> page = orderRepository.findAll(PageRequest.of(pageNumber, 20, Sort.by("id")));

The SQL generated:

SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 200;

For OFFSET 200, the database scans 220 rows and discards the first 200. For OFFSET 200000, it scans 200020 rows. Performance degrades linearly with page number, and on a busy table with frequent inserts the offset also causes phantom rows (pages that skip or repeat rows because the dataset shifted between requests).

Keyset pagination

Instead of an offset, navigate using the value of the last seen row’s sort key:

-- Page 1
SELECT * FROM orders WHERE id > 0 ORDER BY id LIMIT 20;

-- Page 2: pass the last id from page 1 (say, 120)
SELECT * FROM orders WHERE id > 120 ORDER BY id LIMIT 20;

The WHERE id > 120 clause uses the index directly — no rows are scanned and discarded. Performance is O(log n) regardless of page depth.

Implementing with Spring Data JPA

Spring Data does not have built-in keyset support. Implement it with a derived query:

public interface OrderRepository extends JpaRepository<Order, String> {

    List<Order> findByIdGreaterThanOrderByIdAsc(String lastId, Pageable pageable);

    // For reverse direction
    List<Order> findByIdLessThanOrderByIdDesc(String lastId, Pageable pageable);
}

With a Pageable that sets only the size (not the page number — page number is meaningless here):

List<Order> page = orderRepository.findByIdGreaterThanOrderByIdAsc(
    lastSeenId,
    PageRequest.of(0, 20)
);

Multi-column cursor

For consistent ordering in a high-insert table, use a stable sort key. If created_at is not unique (multiple inserts per millisecond), combine it with id:

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

The OR clause handles the tie-breaking: rows with the same created_at are ordered by id within that timestamp.

Cursor serialisation

Expose the cursor as an opaque token rather than raw field values — prevents clients from constructing arbitrary cursors:

public record PageCursor(String lastId, Instant lastCreatedAt) {

    public String encode() {
        String raw = lastId + "|" + lastCreatedAt.toEpochMilli();
        return Base64.getEncoder().encodeToString(raw.getBytes(UTF_8));
    }

    public static PageCursor decode(String encoded) {
        String raw = new String(Base64.getDecoder().decode(encoded), UTF_8);
        String[] parts = raw.split("\\|", 2);
        return new PageCursor(parts[0], Instant.ofEpochMilli(Long.parseLong(parts[1])));
    }
}

REST API contract

public record OrderPageResponse(
    List<OrderDto> items,
    String nextCursor,    // null if no more pages
    boolean hasMore
) {}

@GetMapping("/orders")
public OrderPageResponse getOrders(
    @RequestParam(required = false) String cursor,
    @RequestParam(defaultValue = "20") int size
) {
    List<Order> orders;
    if (cursor == null) {
        orders = orderRepository.findFirstPage(PageRequest.ofSize(size + 1));
    } else {
        PageCursor pc = PageCursor.decode(cursor);
        orders = orderRepository.findNextPage(pc.lastCreatedAt(), pc.lastId(),
                                               PageRequest.ofSize(size + 1));
    }

    boolean hasMore = orders.size() > size;
    List<Order> page = hasMore ? orders.subList(0, size) : orders;

    String nextCursor = hasMore
        ? new PageCursor(page.getLast().id(), page.getLast().createdAt()).encode()
        : null;

    return new OrderPageResponse(page.stream().map(OrderDto::from).toList(),
                                  nextCursor, hasMore);
}

Fetch size + 1 rows to detect whether a next page exists without a separate count query.

When to use offset pagination

Keyset pagination cannot page backwards or jump to arbitrary page numbers. If your UI requires “go to page 47” or “previous page” navigation, offset is necessary. Use keyset for:

Infinite scroll / load-more interfaces
API consumers processing all records in sequence
Large datasets (> 10,000 rows with potential for deep pagination)

Use offset for:

Admin UIs with numbered pages
Small datasets (< 10,000 rows) where offset performance is acceptable
Sorting by non-indexed columns where a cursor would require full scans anyway

Index requirement

Keyset pagination is only fast if the sort column is indexed. For the (created_at, id) cursor:

CREATE INDEX idx_orders_created_at_id ON orders (created_at ASC, id ASC);

Without this index, the WHERE clause cannot use keyset efficiently and may be slower than offset.

If you’re optimising pagination for high-volume REST APIs in Spring Boot and want a review, get in touch.