Choosing the Right Pagination Strategy in Spring Data JPA: Pageable vs Cursor vs Full Fetch

Deciding on a Pagination Strategy

When a REST endpoint must expose a list of entities that can grow into the tens of thousands, the way you fetch and deliver that data determines performance, memory usage, and user experience. The decision hinges on three constraints:

• Dataset size and growth rate – small, static collections vs. large, frequently updated tables.
• Client expectations – whether the UI requires random access to any page or only forward navigation.
• Infrastructure limits – database connection pool size, network latency, and memory footprint on the application server.

Spring Data JPA offers a built‑in Pageable interface that abstracts most of the heavy lifting. However, it is not the only option. Below is a compact comparison of the three common approaches.

Comparison Table

Approach	Database Interaction	Performance	Memory Footprint	State Management	Typical Use‑Case
Pageable (LIMIT/OFFSET)	Single query with LIMIT and OFFSET	Fast for small to medium offsets; slows as OFFSET grows because the DB must skip rows.	Only the requested page; minimal.	Standard CRUD APIs, admin dashboards.
Cursor‑based (last ID, keyset)	Query uses a WHERE id > :lastId or similar; no OFFSET.	Constant time regardless of dataset size; excellent for very large tables.	Only the page; minimal.	Infinite scroll UIs, feeds.
Full fetch + slice	Retrieve entire result set (or large chunk) then slice in memory.	High network and DB load; only suitable for <1k rows.	Potentially large; can exhaust heap.	Reporting tools, small look‑ups.

Why Pageable Is Often the Right Choice

The Pageable interface is the default pagination mechanism in Spring Data. It maps directly to the SQL LIMIT and OFFSET clauses, giving you declarative control over page size and number. The framework handles:

• Sorting: Sort.by("name").descending()
• Page request: PageRequest.of(page, size, sort)
• Result wrapping: Page<T> contains content, total elements, total pages, etc.

Because the query is generated automatically, you avoid boilerplate and reduce the risk of SQL injection. The only trade‑off is the potential performance hit with large OFFSET values, which is mitigated by keeping page sizes moderate (e.g., 50–200).

Concrete Implementation

1. Repository Method

Define a repository that extends JpaRepository and declare a method that accepts a Pageable parameter.

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

Spring Data automatically generates the query: SELECT * FROM users WHERE last_name = ? ORDER BY ... LIMIT ? OFFSET ?.

2. Service Layer

Use the repository to fetch a page. The service can add business logic or transform entities.

@Service
public class UserService {
    private final UserRepository repo;

    public UserService(UserRepository repo) { this.repo = repo; }

    public Page<UserDto> getUsersByLastName(String lastName, int page, int size) {
        Pageable pageable = PageRequest.of(page, size, Sort.by("firstName").ascending());
        Page<User> users = repo.findByLastName(lastName, pageable);
        return users.map(UserDto::fromEntity);
    }
}

3. REST Controller

Expose the endpoint. Spring MVC automatically binds query parameters page and size to the Pageable argument if you use @PageableDefault or a custom argument resolver.

@RestController
@RequestMapping("/api/users")
public class UserController {
    private final UserService service;

    public UserController(UserService service) { this.service = service; }

    @GetMapping
    public Page<UserDto> list(@RequestParam String lastName,
                               @PageableDefault(size = 50) Pageable pageable) {
        return service.getUsersByLastName(lastName, pageable.getPageNumber(), pageable.getPageSize());
    }
}

4. Configuration to Verify SQL Generation

To confirm that Spring Data uses LIMIT and OFFSET, enable SQL logging in application.yml:

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        use_sql_comments: true

Run the application and hit /api/users?lastName=Smith&page=2&size=20. In the console you should see a statement similar to:

select ... from users where last_name=? order by first_name asc limit 20 offset 40

5. Validation Test

Write a simple integration test to ensure the page size matches the request and that the total element count is accurate.

@SpringBootTest
@AutoConfigureTestDatabase
class UserPaginationTest {

    @Autowired
    private UserRepository repo;

    @Test
    void testPageSize() {
        // Arrange: seed 105 users with lastName "Smith"
        IntStream.range(0, 105).forEach(i -> repo.save(new User("User"+i, "Smith")));

        Pageable pageable = PageRequest.of(1, 20, Sort.by("firstName"));
        Page<User> page = repo.findByLastName("Smith", pageable);

        // Assert
        assertEquals(20, page.getContent().size());
        assertEquals(105, page.getTotalElements());
        assertEquals(6, page.getTotalPages()); // 105/20 = 5.25 → 6 pages
    }
}

When to Consider Alternatives

• Cursor‑based pagination – If you anticipate page numbers beyond a few thousand or need to support infinite scroll, replace the OFFSET with a keyset query. Implement a repository method like findByIdGreaterThanOrderByIdAsc(Long lastId, Pageable pageable) and store the last seen ID on the client side.
• Full fetch + slice – For reporting or export features where the dataset is guaranteed to be small, you can fetch all rows and then use List.subList() to create a page. Avoid this for production endpoints exposed to arbitrary users.

Limitations and Practical Checks

• Large OFFSET values can still degrade performance. Monitor query execution plans and consider adding an index on the sorting column.
• Pageable counts the total number of rows, which triggers a COUNT(*) query. For very large tables, this can be expensive. Use Pageable.unpaged() for read‑only streams where total count is unnecessary.
• When the underlying data changes between paginated requests, the page contents may shift. If consistent snapshots are required, use a transaction with repeatable read isolation or materialized views.

Conclusion

For most CRUD APIs, Spring Data’s Pageable delivers a balanced mix of simplicity, performance, and feature set. By configuring SQL logging, writing a small integration test, and keeping page sizes moderate, you can confidently expose paginated data. Reserve cursor‑based or full‑fetch strategies for specialized scenarios where the default LIMIT/OFFSET approach falls short.