Offset-Based vs Page-Based Pagination in Spring Boot – Complete Guide

When building REST APIs, dealing with large datasets is a common challenge. Pagination comes to the rescue, helping manage and display data efficiently. However, developers often encounter confusion between offset-based and page-based pagination in frameworks like Spring Boot. Both approaches are widely used but differ in implementation, performance, and suitability for various use cases.

This guide clarifies the differences, explores their pros and cons, and demonstrates how to implement each in Spring Boot.

Definitions: What is Offset? What is Page?

Offset-Based Pagination

Offset-based pagination retrieves data by skipping a specific number of rows (defined as the offset) and limiting the number of rows returned per request (limit). It operates at the database level and is commonly used in SQL queries like:

SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

• LIMIT 10 fetches up to 10 rows.
• OFFSET 20 skips the first 20 rows before fetching the data.

Offset-based pagination is ideal for precise result positioning, regardless of where the dataset starts.

Page-Based Pagination

Page-based pagination relies on the concept of pages instead of absolute row positions. Each page corresponds to a set of results defined by the page number and page size. For example:

• Page number 0 with size=10 retrieves rows 0–9.
• Page number 1 fetches rows 10–19.

Spring Boot’s PageRequest uses page-based pagination, making it intuitive for developers to fetch results in terms of “pages.”

Pros and Cons of Each

Offset-Based Pagination

Pros:

1. Simple to Understand
   With SQL LIMIT and OFFSET, the logic is clear and familiar to developers working with databases.
2. Precise Result Control
   Offset allows flexible retrieval of any specific range of rows within a dataset.
3. Independent of Frameworks
   Since offset works directly with SQL queries, it works uniformly across various programming languages and frameworks.

Cons:

1. Performance Challenges on Large Datasets
   Skipping a large number of rows can lead to database performance degradation. For example, OFFSET 100000 requires scanning 100,000 rows to retrieve the desired set.
2. Data Consistency Issues
   If the dataset changes (e.g., inserts or deletions) between requests, the offset position might shift, leading to missing or duplicate data.

Page-Based Pagination

Pros:

1. Developer-Friendly
   The concept of pages aligns with how users interact with data, making it simpler to explain in an API context.
2. Integrated with Frameworks
   Spring Data JPA and other frameworks natively support page-based pagination, reducing boilerplate code.
3. Reduced Consistency Issues
   Since pages are calculated based on page numbers, the results are less likely to shift than with offsets in static datasets.

Cons:

1. Cannot Skip Arbitrary Rows
   Compared to offset, page-based pagination is less flexible in retrieving large or specific datasets directly.
2. Still Prone to Data Changes
   Dynamic datasets can cause inconsistencies, though the impact is less pronounced than with offsets.

How Spring Data JPA Handles It

Spring Data JPA provides built-in support for page-based pagination, simplifying the process for developers.

The Pageable Interface

Pageable is the key abstraction used in Spring Data JPA to represent pagination and sorting information. You can create a Pageable object using PageRequest:

Pageable pageable = PageRequest.of(page, pageSize, Sort.by("name").ascending());

Repository Support

When you extend the JpaRepository interface, your repository inherits pagination capabilities. For example:

public interface UserRepository extends JpaRepository<User, Long> {
}

You can pass a Pageable object to the repository’s findAll method:

Page<User> users = userRepository.findAll(pageable);

The result is a Page object that includes both data and metadata (e.g., total elements, total pages).

Native Query for Offset

If you require offset-based pagination, Spring Data JPA supports native queries:

@Query(value = "SELECT * FROM users LIMIT ?1 OFFSET ?2", nativeQuery = true)
List<User> findWithOffset(int limit, int offset);

This ensures flexibility when dealing with custom queries.

Example Output

Default Spring JPA pagination returns metadata such as total elements and current page:

{
  "content": [
    { "id": 1, "name": "Alice" },
    { "id": 2, "name": "Bob" }
  ],
  "totalElements": 100,
  "totalPages": 10,
  "size": 10,
  "number": 0
}

Example: PageRequest vs Native Offset

Page-Based Pagination Example

Controller code using PageRequest:

@GetMapping("/users")
public Page<User> getUsers(@RequestParam int page, @RequestParam int size) {
    Pageable pageable = PageRequest.of(page, size);
    return userRepository.findAll(pageable);
}

Sample request:

GET /users?page=1&size=10

Response:

{
  "content": [...],
  "currentPage": 1,
  "totalPages": 10,
  "totalItems": 100
}

Offset-Based Pagination Example

Controller code using native queries:

@GetMapping("/users/offset")
public List<User> getUsersWithOffset(@RequestParam int offset, @RequestParam int limit) {
    return userRepository.findWithOffset(limit, offset);
}

Sample request:

GET /users/offset?offset=20&limit=10

Response:

[
  { "id": 21, "name": "User21" },
  { "id": 22, "name": "User22" }
]

Performance Impact

Offset-Based Pagination

• Issue: Skipping rows with OFFSET can cause performance bottlenecks in large datasets, as the database still scans the skipped rows.
• Optimization: Use indexed columns in ORDER BY clauses and avoid large offsets when possible.

Page-Based Pagination

• Issue: Relies on calculating row positions, which is efficient for small data but can degrade with larger datasets.
• Optimization:
  • Index the columns used in ORDER BY.
  • Use database-specific optimizations, such as keyset pagination.

Recommendation

• For static datasets or when accessing sequential data, page-based pagination is the better choice.
• For dynamic datasets with frequent changes, offset-based pagination may require additional strategies like cursor-based pagination for consistency.

Best Practices for Large Datasets

1. Limit Page Size
   Introduce a maximum page size (e.g., size=50) to avoid excessive resource consumption.
2. Use Caching
   Cache frequently accessed data to reduce database queries and improve response times.
3. Optimize Queries
   Ensure queries are efficient with proper indexing and use EXPLAIN to analyze performance.
4. Cursor-Based Pagination
   Consider using cursor-based pagination (using a unique key) for high-performance data handling with dynamic or large datasets.
5. Educate Clients
   Clearly document pagination parameters (page, size, offset, limit) in your API documentation for client understanding.

Final Thoughts

Understanding and choosing between offset-based and page-based pagination is crucial for building efficient APIs. While page-based pagination integrates seamlessly with Spring Data JPA, offset-based pagination provides flexibility for precise use cases. By adopting best practices, you can create scalable APIs optimized for large-scale datasets. Evaluate your requirements and pick the approach that best aligns with your application’s needs.