Cursor Pagination Spring Boot API With Keyset Queries
Implement cursor (keyset) pagination in a Spring Boot API with an opaque cursor, a stable sort and a limit plus one query, using JdbcClient or Spring Data.

An offset of 100,000 makes the database read and throw away 100,000 rows before it returns the page you asked for, so page 5,000 of a large table is slow in proportion to how deep it is. It also gives wrong answers under change: if a new row is inserted while a client pages, every later page shifts and the client sees a row twice or misses one. Keyset pagination, also called cursor pagination, avoids both problems. It remembers where the last page stopped and continues from that position with an index, so every page costs about the same and rows do not move. This guide builds it on Spring Boot 4 with PostgreSQL: the query, an opaque cursor, the endpoint, the repository, the trade-offs and the cases where offset paging is still the right choice.
The query
Take orders sorted newest first, with a unique id to break ties between rows created at the same instant:
create index orders_created_id_idx on orders (created_at desc, id desc);
select id, customer, total, created_at
from orders
where (created_at, id) < (:createdAt, :id)
order by created_at desc, id desc
limit :limitPlusOne;The row-value comparison picks the rows strictly after the cursor in the sort order, and the index on the same two columns lets the database jump straight to that position instead of scanning from the start. PostgreSQL, MySQL 8 and several other databases support it. On a database without row values, write the expanded form: created_at < :c or (created_at = :c and id < :id). For the first page, omit the where. The tie-breaker on id is not optional. Without a unique second column, rows with the same timestamp can be skipped or repeated at a page boundary.
The cursor
record Cursor(Instant createdAt, long id) {
static String encode(Cursor c) {
String json = c.createdAt() + "|" + c.id();
return Base64.getUrlEncoder().withoutPadding().encodeToString(json.getBytes(StandardCharsets.UTF_8));
}
static Cursor decode(String token) {
try {
String raw = new String(Base64.getUrlDecoder().decode(token), StandardCharsets.UTF_8);
String[] parts = raw.split("\\|");
return new Cursor(Instant.parse(parts[0]), Long.parseLong(parts[1]));
} catch (RuntimeException e) {
throw new ResponseStatusException(HttpStatus.BAD_REQUEST, "invalid cursor");
}
}
}The cursor is opaque to clients: they pass it back unchanged and never build one. Base64 keeps it URL-safe and hides the structure, which gives you the freedom to change it later, for example to add a sort key, without breaking anyone. A malformed token returns a 400 and never reaches the query. If tampering matters, for example because filters are embedded in the cursor, sign it with an HMAC and verify the signature on decode, so a client cannot craft a cursor that skips the checks.
The endpoint
record Page<T>(List<T> items, String next) { }
@GetMapping("/orders")
Page<OrderView> list(@RequestParam(required = false) String cursor,
@RequestParam(defaultValue = "20") @Max(100) int limit) {
Cursor after = cursor == null ? null : Cursor.decode(cursor);
List<OrderView> rows = orders.after(after, limit + 1); // one extra row
boolean hasNext = rows.size() > limit;
List<OrderView> items = hasNext ? rows.subList(0, limit) : rows;
String next = hasNext ? Cursor.encode(new Cursor(items.getLast().createdAt(), items.getLast().id())) : null;
return new Page<>(items, next);
}Asking for limit + 1 rows tells you whether another page exists without a separate count(*), which on a large table would cost as much as the paging you are avoiding. The extra row is dropped from the response, and the cursor is built from the last item actually returned. The method getLast() is available on List since Java 21. The maximum limit of 100 protects the database from a client that asks for a million rows in one call, and the next value is null on the last page, which is the signal that the client is done.
The repository
List<OrderView> after(Cursor c, int n) {
var sql = "select id, customer, total, created_at from orders "
+ (c == null ? "" : "where (created_at, id) < (:createdAt, :id) ")
+ "order by created_at desc, id desc limit :n";
var spec = jdbc.sql(sql).param("n", n);
if (c != null) {
spec = spec.param("createdAt", Timestamp.from(c.createdAt())).param("id", c.id());
}
return spec.query(OrderView.class).list();
}Only the fixed text is concatenated, and every value is a bound parameter, as required by SQL injection prevention. Spring Data 3.1 and later offers ScrollPosition with Window<T> support for keyset scrolling in repositories, which wraps this idea in an API. Use it if you already use Spring Data repositories, and write the query by hand when you need control over the exact SQL and the index.
Trade-offs
| Offset | Cursor | |
|---|---|---|
| Jump to page N | Yes | No |
| Deep page cost | Grows | Constant |
| Stable under inserts | No | Yes |
| Total count | Possible | Costly, usually omitted |
| Complexity | Low | Medium |
Filters must be part of the cursor's context: a cursor produced with status=OPEN is meaningless with status=PAID, so either include the filter in the cursor and verify it on decode, or document that the client must send the same filters with every request. Keep the sort order fixed, or encode the sort in the cursor. Backward paging needs the reverse comparison and the reverse order, and then the result is reversed before returning. If clients need "previous", return a previous cursor as well.
Testing it
Write a test that inserts a hundred orders with some equal timestamps, pages through them twenty at a time and asserts that every id appears exactly once and in order. That test catches the missing tie-breaker. A second test inserts a row between two requests and asserts that the second page is unaffected. A third checks that a malformed cursor returns 400. Run these against the real database, because row-value behaviour differs between engines.
Multiple sort keys and nullable columns
A cursor works for any sort order as long as the sort columns plus a unique tie-breaker define a total order. For a user-selectable sort, include the sort field and direction in the cursor and build the where clause accordingly, and whitelist the allowed fields so a client cannot sort by an unindexed column. Nullable sort columns are the usual trap: a row-value comparison with null yields unknown and drops the row. Either make the column non-null with a default, or sort with nulls last and handle the null group as its own case in the cursor logic.
Index design
The index must match the sort exactly, in both column order and direction, or the database falls back to sorting the table. Check with explain that the query reads the index with an index condition and does not sort, and recheck after adding a filter, since a filter on another column can change the plan. For filtered lists, a composite index starting with the filter column, such as (status, created_at desc, id desc), keeps the same property. A cursor query that is fast on an empty development table and slow in production is almost always a missing or mismatched index.
Document the cursor as opaque in the API reference.
Where offset paging is fine
Admin screens that need page numbers can stay on offset with a cap on the depth, since nobody pages to ten thousand. The design of parameters, filters and sort names is covered in pagination, filtering and sorting, and reading huge exports with a driver cursor is in fetch size. The reference is Spring Data scrolling, and Slack's cursor-based pagination notes show a production design.


