Deterministic Pagination in Spring Boot: a Tie-Breaker for Pageable Sorting

Rigas PapazisisSpring Boot

Overview

In Spring Boot applications that use Spring Data JPA, Pageable is the standard way to page through large result sets. There is a subtle trap, though: when the client sorts by a column that is not unique (a type, a status, a date), the same row can appear on two consecutive pages while another row never appears at all.

This post explains why that happens, why it matters, and the small, reusable mechanism we use to make every paged query deterministic: an @AdditionalSort annotation that appends a unique tie-breaker to the requested sort.

Tested with: Java 21, Spring Boot 2.7 and Spring Boot 4.1 (Spring Data JPA). The code below is the same on both.

The problem

Imagine an Order entity with:

  • id (primary key)
  • status (NEW, PAID or SHIPPED)
  • createdAt

There are 1,000 orders: 500 NEW, 300 PAID, 200 SHIPPED. The client asks for the second page, sorted by status:

GET /orders?sort=status,asc&page=1&size=20

(page is zero-based, so page=1 is the second page.)

The result is sorted by status, but 500 rows share the value NEW. SQL guarantees nothing about the relative order of rows with equal sort keys, so the database is free to return them in a different order on each execution — after a statistics refresh, a different execution plan, a parallel query, or simply because the data moved. Each page request is a separate query, so page 1 and page 2 may be cut from two different orderings of the same 500 rows.

The result: a row shows up on two pages, and another row is skipped.

Why it matters

  • Front ends that page through results show duplicates and “lose” records.
  • Exports and synchronisation jobs that walk all pages silently miss data.
  • The bug is intermittent and environment-dependent, so it is hard to reproduce and easy to dismiss.

The fix is well known: always end the ORDER BY with a unique tie-breaker, typically the primary key. The challenge is applying it everywhere, without boilerplate, and for entities whose key is not called id.

The solution: append the tie-breaker in the argument resolver

We wanted one global mechanism that:

  • adds the tie-breaker to the sort the client requested, only if it is not already there;
  • lets each endpoint name its own key field (id, code, taskId…);
  • needs no change in repositories or services;
  • keeps working with @PageableDefault and @SortDefault.

Step 1: the @AdditionalSort annotation

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.PARAMETER)
public @interface AdditionalSort {
    String value() default "id";
}

It marks a Pageable parameter and names the field to append.

Step 2: a Pageable resolver that honours it

@Configuration
public class PageableConfig implements WebMvcConfigurer {

    @Override
    public void addArgumentResolvers(List<HandlerMethodArgumentResolver> resolvers) {
        resolvers.add(new PageableHandlerMethodArgumentResolver(new AdditionalSortResolver()));
    }

    private static class AdditionalSortResolver extends SortHandlerMethodArgumentResolver {

        @Override
        public Sort resolveArgument(MethodParameter parameter, ModelAndViewContainer mavContainer,
                                    NativeWebRequest webRequest, WebDataBinderFactory binderFactory) {
            Sort sort = super.resolveArgument(parameter, mavContainer, webRequest, binderFactory);
            AdditionalSort annotation = parameter.getParameterAnnotation(AdditionalSort.class);
            return annotation == null ? sort : appendTieBreaker(sort, annotation.value());
        }
    }

    /** Kept separate from the resolver so it can be unit-tested without a servlet request. */
    static Sort appendTieBreaker(Sort sort, String field) {
        if (sort.isUnsorted() || field == null) {
            return sort;                      // no ORDER BY requested: leave the query alone
        }
        return sort.getOrderFor(field) != null
                ? sort                        // already part of the sort: do not add it twice
                : sort.and(Sort.by(field));   // append as the last, ascending, sort key
    }
}

The resolver lets Spring Data parse the sort parameter (and apply @SortDefault) exactly as it normally would, then appends the tie-breaker when the annotation is present and the field is not already in the sort.

Step 3: use it in controllers

@GetMapping(path = "/orders", produces = MediaType.APPLICATION_JSON_VALUE)
public Page<OrderSummary> findOrders(
        @RequestParam(value = "status", required = false) String status,
        @PageableDefault(size = 20)
        @SortDefault(sort = "createdAt", direction = Sort.Direction.DESC)
        @AdditionalSort("id") Pageable pageable) {
    return orderService.findOrders(status, pageable);
}

@GetMapping(path = "/countries", produces = MediaType.APPLICATION_JSON_VALUE)
public Page<CountryDto> findCountries(
        @PageableDefault(size = 200)
        @SortDefault(sort = "name", direction = Sort.Direction.ASC)
        @AdditionalSort("code") Pageable pageable) {
    return countryService.findCountries(pageable);
}

In the first endpoint the primary key is id; in the second the key field is code, so we name it.

A request for ?sort=status,asc on /orders now reaches the repository as ORDER BY status ASC, id ASC, and every page is cut from the same, total ordering.

Pitfalls

  • The tie-breaker must be unique. Appending createdAt or any other non-unique column only makes collisions rarer. Use the primary key, or a column with a unique constraint. For a view, use the view’s own key.
  • An unsorted request is left alone. If the client sends no sort and the endpoint has no @SortDefault, there is no ORDER BY at all, and paging is not deterministic either. Give every paged endpoint a @SortDefault; the tie-breaker is then appended to it.
  • Your resolver does not get Spring Boot’s paging properties. Spring Boot applies spring.data.web.pageable.* (maximum page size, one-indexed pages, parameter names) to the resolver Spring Data registers, not to an instance you create yourself. If you rely on those properties, set the same values on your PageableHandlerMethodArgumentResolver (for example setMaxPageSize(...)).
  • Make sure your resolver is the one that runs. Spring Data registers its own Pageable resolver too, and the first resolver that supports the parameter wins. Which one comes first depends on registration order, so add one @WebMvcTest that calls an endpoint with ?sort=status,asc and asserts that the Pageable reaching the service ends with the tie-breaker.
  • Offset paging still moves under concurrent writes. A tie-breaker makes the order deterministic; it cannot stop a row inserted between two page requests from shifting everything after it. For feeds and long exports, consider keyset scrolling (ScrollPosition / Window, available since Spring Data 3.1).

A note on security

Keep letting Spring Data turn the sort parameter into a Sort. Spring Data resolves every sort property against the entity and rejects anything that is not a real property, so a crafted sort value fails as a bad request instead of reaching the SQL. If you ever build an ORDER BY yourself from request input (for example a hand-built QueryDSL orderBy), that protection is gone. The tie-breaker above is safe because it is a compile-time constant in an annotation, never request input.

Advantages of this approach

  • Deterministic pagination, with no duplicated or missing rows.
  • No change in repositories or services.
  • One annotation per endpoint, and each endpoint names its own key.
  • Works with @PageableDefault and @SortDefault.

Conclusion

Paging is only correct when the order is total. Appending a unique tie-breaker in the Pageable resolver gives every paged endpoint that guarantee with one annotation, and keeps the sort parsing — and its validation — where Spring Data already does it well.

Cheers!

Related sources

Rigas Papazisis
Latest posts by Rigas Papazisis (see all)