Efficient Pagination in Spring Data JPA REST APIs with Pageable and Sort

Problem: fetching large lists wastes bandwidth and slows down APIs

When a REST endpoint returns every row of a table, the payload grows linearly with data size. Consumers usually need only a slice—say, the first 20 items sorted by name—to display a page in a UI. Manually parsing request parameters, calculating offsets, and building queries is error‑prone and duplicates work that Spring Data JPA already does.

Thesis: let Spring Data JPA turn page, size, and sort query parameters into a Pageable object and return a Page<T> that already contains the needed slice and metadata.

By injecting Pageable as a method argument in a Spring MVC controller, the framework binds the incoming request to a pagination and sorting abstraction. The repository method that accepts this Pageable returns a Page whose content holds the current page of entities, while totalElements, totalPages, and sort give the client everything needed for UI pagination controls.

How Pageable works under the hood

Spring MVC registers a PageableHandlerMethodArgumentResolver that reads the request parameters:

• page – zero‑based index of the page (default 0)
• size – number of elements per page
• sort – one or more property,direction pairs, e.g. sort=name,asc&sort=createdDate,desc

The resolver creates a PageRequest (an implementation of Pageable) that holds these values. When you pass this object to a Spring Data JPA repository method such as findAll(Pageable), the JPA provider translates it into a SQL query with LIMIT and OFFSET (or a keyset‑based query if you have configured it). The returned Page encapsulates the query results and the pagination metadata.

Worked example: controller, repository, and entity

Assume a simple Person entity:

@Entity
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    // getters and setters omitted for brevity
}

Define a repository that extends JpaRepository:

public interface PersonRepository extends JpaRepository {}

Expose a GET endpoint that receives Pageable and returns the repository’s Page:

@RestController
@RequestMapping("/api/persons")
public class PersonController {
    private final PersonRepository repository;
    public PersonController(PersonRepository repository) {
        this.repository = repository;
    }

    @GetMapping
    public Page getAll(Pageable pageable) {
        return repository.findAll(pageable);
    }
}

When the application runs, a request like:

GET http://localhost:8080/api/persons?page=0&size=5&sort=name,asc

will cause Spring to:

1. Create a PageRequest with page=0, size=5, and a Sort ordering by name ascending.
2. Call repository.findAll(pageable).
3. Generate SQL similar to select * from person order by name asc limit 5 offset 0.
4. Return a JSON payload that includes:

{
  "content": [ { "id": 1, "name": "Alice" }, … ],
  "pageable": { "sort": { "sorted": true, "unsorted": false, "empty": false }, "pageNumber": 0, "pageSize": 5, "offset": 0 },
  "totalElements": 23,
  "totalPages": 5,
  "last": false,
  "size": 5,
  "number": 0,
  "sort": { "sorted": true, "unsorted": false, "empty": false },
  "first": true,
  "numberOfElements": 5
}

Increasing the page parameter (e.g., page=4) will increase the OFFSET in the logged SQL, demonstrating the pagination mechanism.

Trade‑off: offset‑based pagination degrades on deep pages

The default implementation uses LIMIT … OFFSET. As the page number grows, the database must skip more rows, which can become costly on large tables. Symptoms include higher query latency and increased CPU usage.

To mitigate this:

• Impose a maximum size (e.g., 100) via a @Max validation on a custom wrapper or a controller advice.
• Consider keyset pagination (also called seek pagination) for immutable or append‑only datasets; Spring Data offers Slice‑based methods that can be combined with a Sort on a unique column.
• Monitor slow‑query logs; if you see rising execution times for high page values, evaluate switching to keyset or caching the total count.

Practical way to verify the behavior

Enable SQL logging in application.properties:

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Start the application and hit the endpoint with different page values. Observe the console: the generated SQL will show the OFFSET changing linearly with the page number, confirming that Spring Data JPA is applying the pagination you requested.

Actionable closing

Start by adding Pageable to your controller method and letting Spring Data JPA return a Page. Verify the JSON shape and the SQL offset in your logs. If you notice performance drops for high page numbers, introduce a page‑size ceiling or explore keyset pagination for a more scalable solution. This approach gives you a clean, declarative way to paginate REST responses while keeping the data access layer focused on business logic.