API Pagination Filtering and Sorting Design Done Right
Design API pagination, filtering and sorting: query parameters, offset versus cursor, allowed sort fields, response envelopes, links and limits in Spring.

Listing endpoints are where APIs meet real data volumes. The design choices made on day one are hard to change once clients depend on them.
The request
GET /orders?status=OPEN&customer=ada&createdAfter=2026-01-01&sort=createdAt,desc&page=0&size=20| Concern | Parameters | Notes |
|---|---|---|
| Pagination | page, size or cursor, limit | Cap size, for example at 100 |
| Filtering | One parameter per field, with a clear operator convention | status=OPEN, createdAfter=... |
| Sorting | sort=field,direction, repeatable | Whitelisted fields |
Choose naming once, and apply it to every list endpoint. The naming parallels the URL rules in resource URL design.
In Spring
Spring Data supplies Pageable binding:
@GetMapping("/orders")
Page<OrderView> list(OrderFilter filter,
@PageableDefault(size = 20, sort = "createdAt", direction = Sort.Direction.DESC) Pageable pageable) {
return orders.search(filter, clamp(pageable)).map(OrderView::from);
}
private Pageable clamp(Pageable p) {
int size = Math.min(p.getPageSize(), 100);
Sort safe = Sort.by(p.getSort().stream()
.filter(o -> ALLOWED_SORTS.contains(o.getProperty()))
.toList());
return PageRequest.of(p.getPageNumber(), size, safe.and(Sort.by("id"))); // unique tie-breaker
}OrderFilter is a record bound from query parameters. Spring MVC's binding of parameters is in PathVariable, RequestParam and RequestBody. Spring Boot also has spring.data.web.pageable.max-page-size, which caps it globally.
Whitelist what clients may ask for
private static final Set<String> ALLOWED_SORTS = Set.of("createdAt", "total", "status");An open-ended sort=anything becomes a query on an unindexed column, or an injection path if the field name is concatenated into SQL. Reject unknown fields with a 400 or ignore them, and document the supported ones. The same applies to filters: expose the ones that have indexes.
Stable ordering
Sorting by createdAt alone is unstable when many rows share a timestamp: the database may return them in any order, and rows can appear twice or never as the client pages. Add a unique column last: order by created_at desc, id desc.
The response
{
"items": [ ... ],
"page": 0,
"size": 20,
"total": 1342,
"links": {
"self": "/orders?page=0&size=20",
"next": "/orders?page=1&size=20"
}
}Including the total needs a count(*), which is expensive on large tables. Offer it, or leave it out and give a hasNext flag. Links remove the need for clients to build URLs. Spring Data's raw Page serialization is verbose and unstable across versions, so return your own envelope or PagedModel.
Offset versus cursor
select * from orders order by created_at desc, id desc limit 20 offset 100000; -- scans and discards 100000 rows
select * from orders where (created_at, id) < (:lastCreatedAt, :lastId)
order by created_at desc, id desc limit 20; -- uses the indexOffset pagination is easy and allows jumping to page 50, and its cost grows with the offset. It also shifts when rows are inserted. Cursor (keyset) pagination keeps constant cost and consistent results, but you cannot jump. Use it for feeds, exports and big tables, see cursor pagination in Spring Boot.
Filtering design
- Equality filters are plain parameters. Ranges use suffixes (
createdAfter,createdBefore) or bracket syntax. - Multiple values:
status=OPEN&status=PAIDor a comma list, pick one. - Free text search is a separate
qparameter with its own index (full text). - Avoid a query language inside a string unless you intend to maintain a parser.
Errors
Invalid input gets a 400 with a body that names the parameter, in the format from RFC 9457. A page beyond the end returns an empty list, not a 404.
Measure why deep offsets hurt
The offset-versus-cursor argument is usually stated as a rule. Here it is as a measurement. The table is the one-million-row orders table from the indexes post, in PostgreSQL 17, with a primary key on id.
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, status FROM orders ORDER BY id LIMIT 20 OFFSET 900000;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, status FROM orders WHERE id > 900000 ORDER BY id LIMIT 20;OFFSET 0 (first page) Execution Time: 0.145 ms Index Scan, 20 rows
OFFSET 900000 Execution Time: 237.880 ms Index Scan, actual rows=900020, Buffers: 9541 pages
WHERE id > 900000 (keyset) Execution Time: 0.134 ms Index Scan, actual rows=20, Buffers: 7 pagesAsking for the page after row 900,000 with OFFSET made the database walk 900,020 index entries and throw 900,000 of them away, touching 9,541 pages and taking 238 ms. The keyset form seeks straight to the first row after the last id the client saw, reads 20 rows and 7 pages, and takes 0.13 ms, the same as the first page. That is a factor of nearly 1,800 on this data. Offset cost grows linearly with page number; keyset cost does not grow at all. The first page and the keyset query cost the same because the index lets the database start where it wants.
The same holds when the ordering is not the primary key, provided an index matches the sort. With an index on (created_at DESC, id DESC), page 40,000 by offset (OFFSET 800000) took 37 ms and the keyset equivalent took 0.05 ms:
CREATE INDEX orders_created_id_idx ON orders (created_at DESC, id DESC);
SELECT id, created_at FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;The row-value comparison (created_at, id) < (...) handles ties on the timestamp. Always add the unique id as the last sort key; without it two rows with the same timestamp can appear on both pages or on neither.
Build the cursor, and keep it opaque
The client should not need to know what the cursor contains. Encode the last row's sort keys into a single string and decode it on the next request.
record Cursor(Instant createdAt, long id) {
String encode() {
String raw = createdAt.toString() + "|" + id;
return Base64.getUrlEncoder().withoutPadding().encodeToString(raw.getBytes(StandardCharsets.UTF_8));
}
static Cursor decode(String token) {
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]));
}
}@GetMapping("/orders")
Page<OrderView> list(@RequestParam(required = false) String after,
@RequestParam(defaultValue = "20") @Max(100) int limit) {
Cursor c = after == null ? null : Cursor.decode(after);
List<Order> rows = orders.page(c, limit + 1); // fetch one extra to know if there is a next page
boolean more = rows.size() > limit;
List<Order> page = more ? rows.subList(0, limit) : rows;
String next = more ? new Cursor(page.getLast().createdAt(), page.getLast().id()).encode() : null;
return new Page<>(page.stream().map(OrderView::of).toList(), next);
}Fetching limit + 1 rows is the trick that avoids a count(*) query: if an extra row came back, there is a next page. A total count over a million rows costs a full scan, so offer it only when a product screen needs it, and then as an estimate. Treat a malformed cursor as a 400, never as a server error. If you sign the cursor with an HMAC, clients cannot craft their own, which prevents someone from probing for rows by guessing positions.
Pagination and filters must agree
A cursor is valid only for the query that produced it. If the client changes the filter or the sort order, the old cursor points into a different result set, so return an error or start from the beginning. Include a hash of the filter and sort in the cursor and compare it on decode. A filter on a status with an index (status, created_at, id) still paginates in constant time; a filter on a column with no index scans the table for every page, and no pagination style hides that.
Reading filters from the request without opening an injection hole
Filters arrive as query parameters, and they are the part of a list endpoint that most often turns into a security bug. Never concatenate a field name from the request into SQL. Map each public filter name to a fixed column or a typed predicate on the server, and ignore or reject names you do not know.
private static final Map<String, String> SORTABLE = Map.of("created", "created_at", "total", "total", "status", "status");
String column = SORTABLE.get(requestedSort);
if (column == null) throw new BadRequest("sort must be one of " + SORTABLE.keySet());The same table drives documentation: publish the list of sortable and filterable fields in the OpenAPI description, with the maximum page size, so clients learn the limits before they hit them. A request for limit=100000 should be clamped or rejected, not honoured, because the cap is what keeps one client from making the database read the whole table.
Pin and test the paging contract
Pagination bugs hide until data changes: a row inserted between two page requests, a deletion, two rows with equal timestamps. A test that creates 45 rows including ties, then walks the pages and asserts that every row appears exactly once, covers them. Write it once for the cursor code and keep it.
@Test
void walkingAllPagesVisitsEachRowOnce() {
insertOrders(45, /* withTiedTimestamps */ true);
List<Long> seen = new ArrayList<>();
String cursor = null;
do {
Page<OrderView> page = api.list(cursor, 10);
page.items().forEach(o -> seen.add(o.id()));
cursor = page.next();
} while (cursor != null);
assertThat(seen).hasSize(45).doesNotHaveDuplicates();
}The test fails if the sort omits the unique tiebreaker, which is the common mistake. Insert a row in the middle of the walk to see the cursor's main advantage: nothing is skipped or repeated, unlike offset paging, where an insert shifts every later row by one.
Where this does not apply
GraphQL has its own connection model. For small, fixed lists, return all of them. Principles for the whole API are in REST API design rules. The reference is Spring Data web support, and Use The Index, Luke explains why offset slows down.


