Pagination, Filtering & Sorting

Every collection endpoint needs pagination, and the naive implementation works perfectly until the table is large and someone asks for page 500. The fixes are well understood; the trick is choosing the right one before the data grows.

Offset pagination#

The default, and the right choice for small or shallow datasets:

@GetMapping("/api/v1/orders")
public Page<OrderResponse> list(
        @PageableDefault(size = 20, sort = "createdAt", direction = Sort.Direction.DESC)
        Pageable pageable) {
    return orderService.findAll(pageable);
}

Cap the page size so a client cannot request everything at once:

@Configuration
public class PageableConfig implements WebMvcConfigurer {
    @Override
    public void addArgumentResolvers(List<HandlerMethodArgumentResolver> resolvers) {
        var resolver = new PageableHandlerMethodArgumentResolver();
        resolver.setMaxPageSize(100);              // silently clamps larger requests
        resolver.setFallbackPageable(PageRequest.of(0, 20));
        resolvers.add(resolver);
    }
}

Offset pagination has a second problem beyond cost, and it is the one that produces bug reports: inserts and deletes shift the window. If a row is inserted at the top while a user is reading page one, the last item of page one reappears as the first item of page two. The user sees a duplicate and misses a different row entirely.

Keyset pagination#

public record CursorPage<T>(List<T> items, String nextCursor, boolean hasMore) { }
 
@Service
public class OrderQueryService {
 
    private static final int MAX = 100;
 
    public CursorPage<OrderResponse> page(String cursor, int size) {
        int limit = Math.min(size, MAX);
        Instant after = cursor == null ? Instant.now() : Cursor.decode(cursor).createdAt();
        Long afterId  = cursor == null ? Long.MAX_VALUE : Cursor.decode(cursor).id();
 
        // Fetch one extra row to decide hasMore without a COUNT query.
        var rows = repository.findPage(after, afterId, PageRequest.ofSize(limit + 1));
 
        boolean hasMore = rows.size() > limit;
        var page = hasMore ? rows.subList(0, limit) : rows;
 
        String next = hasMore
                ? Cursor.encode(page.get(page.size() - 1).createdAt(), page.get(page.size() - 1).id())
                : null;
 
        return new CursorPage<>(page.stream().map(OrderResponse::from).toList(), next, hasMore);
    }
}

@Query("""
       select o from Order o
       where (o.createdAt < :after)
          or (o.createdAt = :after and o.id < :afterId)
       order by o.createdAt desc, o.id desc
       """)
List<Order> findPage(@Param("after") Instant after,
                     @Param("afterId") Long afterId,
                     Pageable pageable);

The composite condition is the part people leave out. Sorting on createdAt alone is not deterministic when two rows share a timestamp — and they will. Adding id as a tiebreaker, both in the ORDER BY and in the WHERE, makes the sequence total and the pagination stable.

Make the cursor opaque. Base64-encoding the composite key signals that it is not a client-constructed value and lets you change the internal representation later without breaking anyone:

public record Cursor(Instant createdAt, Long id) {
 
    public static String encode(Instant createdAt, Long id) {
        String raw = createdAt.toEpochMilli() + ":" + id;
        return Base64.getUrlEncoder().withoutPadding()
                     .encodeToString(raw.getBytes(StandardCharsets.UTF_8));
    }
 
    public static Cursor decode(String cursor) {
        try {
            String raw = new String(Base64.getUrlDecoder().decode(cursor), StandardCharsets.UTF_8);
            String[] parts = raw.split(":", 2);
            return new Cursor(Instant.ofEpochMilli(Long.parseLong(parts[0])),
                              Long.parseLong(parts[1]));
        } catch (RuntimeException ex) {
            throw new BadRequestException("invalid cursor");
        }
    }
}

The trade-off is what you give up: no jumping to an arbitrary page, and no total count. For feeds, timelines, exports and anything with more than a few thousand rows, that is a good trade. For an admin table with page numbers over ten thousand records, offset is fine.

Filtering#

For a handful of optional filters, bind a command object and translate it into a Specification:

public record OrderFilter(
        OrderStatus status,
        @PastOrPresent LocalDate createdAfter,
        @Size(max = 64) String customerReference,
        @DecimalMin("0") BigDecimal minTotal) {
 
    public Specification<Order> toSpecification() {
        return Specification.allOf(
                eq("status", status),
                gt("createdAt", createdAfter == null ? null : createdAfter.atStartOfDay(UTC).toInstant()),
                eq("customerReference", customerReference),
                gte("total.amount", minTotal));
    }
}

Do not accept arbitrary field names from the query string and reflect them into the query. It looks flexible and it is an injection surface: a client can filter on columns you never meant to expose, and error messages leak your schema. An explicit filter object is a contract you can document, validate and index for.

For genuinely complex search, a small query language such as RSQL is a reasonable option — but treat it like any parser: validate the field names against an allowlist before building the predicate.

Sorting safely#

private static final Set<String> SORTABLE = Set.of("createdAt", "total", "status", "reference");
 
private Sort validated(Sort requested) {
    for (Sort.Order order : requested) {
        if (!SORTABLE.contains(order.getProperty())) {
            throw new BadRequestException(
                "cannot sort by '%s'; allowed: %s".formatted(order.getProperty(), SORTABLE));
        }
    }
    return requested;
}

Every sortable column needs an index that matches the sort direction and includes the tiebreaker. Without one, sorting a large table is a full scan plus a sort, and it will be the slowest query in your application.

Choosing a strategy#

The decision comes down to three questions about how the collection is actually used.

Can a user jump to an arbitrary page? If the interface has numbered page links, you need offset, because a cursor cannot express "page 47". If it has a next button or infinite scroll, a cursor is strictly better. In practice, numbered pagination is far less used than product teams assume — usage data almost always shows traffic collapsing after page three.

How large will the collection get? Under a few thousand rows, offset is fine forever and the extra machinery of cursors is not worth it. Above a hundred thousand, deep offsets will eventually be slow enough to notice, and the fix is much cheaper to apply before clients depend on page numbers.

How fast does the data change? A collection with frequent inserts at the sort position — a feed, an event log, a notifications list — produces visible duplicates and gaps under offset pagination. Cursors are immune to this, because the cursor names a position in the data rather than a count of rows.

A reasonable default: offset for admin tables and reference data, cursors for anything user-facing and time-ordered. Whichever you choose, decide before publishing, because changing the pagination contract later is a breaking change for every consumer.

Documenting the contract#

Pagination is one of the parts of an API consumers get wrong most often, usually because the defaults and limits were never written down. State four things explicitly: the default page size, the maximum page size and what happens when it is exceeded (clamped, or rejected with a 400), whether a total count is returned, and whether results are stable across pages.

That last point deserves a sentence in the documentation even when the answer is uncomfortable. If your offset pagination can show a duplicate when data is inserted concurrently, saying so lets a consumer deduplicate by id rather than discovering the behaviour as a bug report from their users.

Telling the client how to navigate#

@GetMapping("/api/v1/orders")
public ResponseEntity<List<OrderResponse>> list(Pageable pageable, UriComponentsBuilder uri) {
    Page<OrderResponse> page = service.findAll(pageable);
 
    var links = new ArrayList<String>();
    if (page.hasNext())     links.add(link(uri, page.nextPageable(), "next"));
    if (page.hasPrevious()) links.add(link(uri, page.previousPageable(), "prev"));
    links.add(link(uri, page.getPageable().first(), "first"));
 
    return ResponseEntity.ok()
            .header(HttpHeaders.LINK, String.join(", ", links))
            .header("X-Total-Count", String.valueOf(page.getTotalElements()))
            .body(page.getContent());
}

RFC 8288 Link headers are understood by generic HTTP tooling, so a consumer can follow pagination without knowing anything about your body format. Providing both header links and body metadata costs nothing and suits both kinds of client.

What to take away#

Start with offset pagination and a capped page size. Move to keyset the moment depth or insert rate makes it necessary — the composite sort key is what makes it correct. Return a Slice when the total is not worth its query. Keep filters explicit and sorts allowlisted, and index every column you let people sort by.