Let Spring Data JPA Do the Paging: Pageable, Sort, and When to Stop Trusting Them

You have a users table with two million rows and a REST endpoint that currently returns all of them. The fix most teams reach for — writing LIMIT/OFFSET SQL by hand, plus a separate COUNT query, plus JSON metadata for the frontend — is exactly the boilerplate Spring Data JPA was built to eliminate. The thesis of this post: Pageable and Sort handle 90% of paging needs with almost no code, but you need to know where the remaining 10% bites: expensive count queries, unindexed sort columns, and databases where OFFSET performs badly.

What Pageable actually does for you

Add a Pageable parameter to a repository method and Spring Data translates it into the appropriate LIMIT/OFFSET clauses for your database dialect. Return a Page<T> instead of a List<T> and Spring also runs a count query, so the result carries totalElements, totalPages, and navigation helpers (hasNext(), isFirst()) that a frontend can render directly.

public interface UserRepository extends JpaRepository<User, Long> {
    Page<User> findByLastName(String lastName, Pageable pageable);
}

That's the whole repository. No count query, no row-mapping, no pagination math. If you only need the slice and not the totals, return Slice<T> instead — it skips the count query entirely and just tells you whether another page exists.

Sorting rides along for free

A Pageable can carry a Sort, which Spring turns into an ORDER BY over entity attribute names (not column names):

Pageable pageable = PageRequest.of(0, 20,
    Sort.by("lastName").ascending().and(Sort.by("firstName").ascending()));
Page<User> page = userRepository.findByActiveTrue(pageable);

Multi-column sorting that would otherwise mean string-concatenating SQL becomes a typed, refactor-safe API. One caveat: sort property names are resolved against the entity, so a typo surfaces as a runtime exception, not a compile error — worth a test per sortable field you expose.

Exposing it over HTTP

In a Spring MVC controller, declare a Pageable parameter and the PageableHandlerMethodArgumentResolver binds query parameters automatically:

@GetMapping("/users")
public Page<User> users(@RequestParam String lastName,
                        @PageableDefault(size = 20, sort = "lastName") Pageable pageable) {
    return userRepository.findByLastName(lastName, pageable);
}

A client can now call GET /users?lastName=Smith&page=0&size=10&sort=firstName,asc and receive a JSON body with content, totalElements, totalPages, and number. To verify the machinery rather than trust it, enable SQL logging (spring.jpa.show-sql=true in a dev profile, or a proxy like p6spy) and confirm the generated statements contain the expected ORDER BY and limit/offset clauses, plus one count query per request.

Custom queries need a countQuery

Derived queries get their count statement generated automatically. With a handwritten @Query, Spring cannot always infer it, so you supply one:

@Query(value = "SELECT u FROM User u WHERE u.department = :dept AND u.active = true",
       countQuery = "SELECT COUNT(u) FROM User u WHERE u.department = :dept AND u.active = true")
Page<User> findActiveByDepartment(@Param("dept") String dept, Pageable pageable);

Without countQuery, pagination metadata may be wrong or the query may fail at runtime depending on the query shape — check your logs on the first paged request.

Where the convenience ends

Three trade-offs deserve real attention:

• The count query can dominate cost. On very large tables with selective filters, COUNT(*) with joins can be slower than the page fetch itself. If the UI only needs "next/previous", use Slice<T>. If it needs approximate totals, consider a cached or estimated count.
• Sorting on unindexed columns scans. sort=createdAt,desc on a table with no index on created_at forces a sort of every matching row before the limit applies. Match indexes to the sort fields you expose, and consider whitelisting sortable properties instead of accepting arbitrary ones from clients.
• Deep OFFSET paging degrades. OFFSET 500000 still reads and discards half a million rows. For deep traversal (exports, infinite scroll), keyset pagination — filtering on WHERE id > :lastSeenId ORDER BY id — is the standard fix, and it maps cleanly onto a derived repository method.

Also note the defaults: Spring's web binding defaults to page size 20, and unbounded size parameters from clients are a memory and denial-of-service risk. Cap it with spring.data.web.pageable.max-page-size.

Actionable takeaways

1. Return Page when the UI needs totals, Slice when it doesn't.
2. Add countQuery to every custom @Query that pages.
3. Index every column you allow clients to sort by, and cap max-page-size.
4. Verify with SQL logging that limit, offset, and order clauses match what you intended — dialect and driver mismatches surface there first.
5. Switch to keyset pagination before deep-offset performance becomes an incident, not after.