Custom pagination response in spring boot jpa with postgresql, Spring Data JPA pagination with custom query, Spring Boot pagination with custom query, Spring Boot pagination, sorting and filtering, Spring JPA pagination with native query example, Pageable Spring Boot, Pagination in Spring Boot REST API with JPA

Efficient data retrieval is paramount when your application is managing large-scale datasets like social feeds, logs, or audit trails. While offset-based pagination is widely used, it can cause performance issues for large datasets. Cursor-based pagination offers a more efficient alternative. By relying on a unique identifier (typically a column like id), cursor-based pagination avoids the downsides associated with offset calculations in large result sets.

This guide will take you step-by-step through implementing cursor-based pagination in Spring Boot with PostgreSQL, exploring its advantages and providing a comprehensive code walkthrough.

What Is Cursor-Based Pagination?
When to Use Cursor vs Offset
Custom Query Using WHERE id > :lastId
Accepting Limit and Cursor in Request
Return nextCursor in Response
Implement Backward Pagination (Optional)
Cursor with Sorting Logic
PostgreSQL Optimization Techniques
Use Case: Social Feeds, Logs, Audit Trails
Final Code Walkthrough

What Is Cursor-Based Pagination?

Cursor-based pagination is a method of retrieving paginated data using a cursor (an identifier unique to each record, such as id or createdAt). Instead of calculating offsets, data is fetched based on this cursor’s value from the last retrieved record. The key idea is to fetch the next “chunk” of data relative to the cursor, ensuring better performance in large datasets.

Advantages:

Performance: Skipping rows using OFFSET can cause PostgreSQL to scan unnecessary rows. Cursor-based pagination eliminates this issue.
Consistency: Cursor-based pagination is more stable for datasets that frequently change (e.g., real-time feeds).
Efficiency on Large Tables: Suitable for databases with millions of rows, as it avoids deep scans.

Example:

If the last record on page 1 has an id of 100, the query for page 2 fetches rows where id > 100.

When to Use Cursor vs Offset

While cursor-based pagination is powerful, it’s not always the ideal solution. Here’s when to use each approach:

Use Cursor-Based Pagination:

When datasets are large (hundreds of thousands to millions of records).
For time-sensitive or dynamic datasets, like social feeds or audit logs, where records change frequently.
When performance is critical, as it avoids the overhead of large offsets.

Use Offset-Based Pagination:

When simple and easy implementation is more important than performance.
For smaller datasets or tables where query efficiency isn’t a bottleneck.
When users need fast arbitrary access to any page (e.g., jump to page 50).

Custom Query Using WHERE id > :lastId

Cursor-based pagination relies on custom queries that fetch rows based on the cursor (lastId) and a limit.

Example Query:

SELECT * FROM products 
WHERE id > :lastId 
ORDER BY id ASC 
LIMIT :pageSize;

Explanation:

id > :lastId: Ensures you’re fetching rows after the last displayed record.
ORDER BY id ASC: Sorts results in ascending order to maintain a predictable sequence.
LIMIT :pageSize: Fetches only the specified number of rows for the page.

Accepting Limit and Cursor in Request

To implement cursor-based pagination, your API must accept cursor and limit as query parameters.

Example API Endpoint:

GET /api/products?cursor=100&pageSize=10

Controller Code:

@GetMapping("/products")
public List<Product> getProducts(
    @RequestParam(required = false) Long cursor,
    @RequestParam(defaultValue = "10") int pageSize
) {
    if (cursor == null) {
        return productRepository.findFirstPage(pageSize);
    }
    return productRepository.findNextPage(cursor, pageSize);
}

Return nextCursor in Response

The response should include a nextCursor field, allowing clients to fetch the subsequent page.

Example Response:

{
    "content": [
        {"id": 101, "name": "Product A"},
        {"id": 102, "name": "Product B"}
    ],
    "nextCursor": 103
}

Here’s how to implement it:

public PaginatedResponse<Product> fetchProducts(Page<Product> products) {
    List<Product> content = products.getContent();
    Long nextCursor = content.isEmpty() ? null : content.get(content.size() - 1).getId();
    return new PaginatedResponse<>(content, nextCursor);
}

Implement Backward Pagination (Optional)

Backward pagination allows users to fetch records prior to the existing set.

SQL Query for Backward Pagination:

SELECT * FROM products 
WHERE id < :cursor 
ORDER BY id DESC 
LIMIT :pageSize;

The query fetches rows preceding the given cursor (e.g., id < :cursor) and requires reversing the sort order for proper navigation.

Cursor with Sorting Logic

To ensure flexible sorting with cursor-based pagination, additional columns can act as secondary keys. For example, sorting by price while paginating by id.

Example SQL Query:

SELECT * FROM products 
WHERE (price = :lastPrice AND id > :lastId)
   OR (price > :lastPrice)
ORDER BY price ASC, id ASC 
LIMIT :pageSize;

This query ensures stable pagination for sorted datasets.

PostgreSQL Optimization Techniques

Cursor-based pagination benefits greatly from database tuning. Here are some tips for PostgreSQL optimization:

Indexes: Ensure columns like id or createdAt used in sorting/filtering have appropriate indexes.
Query Analysis: Use EXPLAIN and ANALYZE to identify bottlenecks.
Vacuuming: Run VACUUM regularly to improve query efficiency.
Batch Size: Use a reasonable LIMIT (e.g., 20–100) to avoid high memory usage.
Query Joins: Avoid complex joins; use denormalized views for better performance.

Use Case: Social Feeds, Logs, Audit Trails

Social Feeds:

A user views posts sorted by createdAt. The cursor tracks the last viewed createdAt timestamp and allows incremental loading.

Logs:

Paginating server or application logs ensures system administrators don’t load too many records at once.

Audit Trails:

Cursor-based pagination helps fetch audit records efficiently while preserving order and supporting backward navigation.

Final Code Walkthrough

Here’s a complete implementation of cursor-based pagination in Spring Boot:

Entity:

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private double price;
}

Repository:

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query("SELECT p FROM Product p WHERE p.id > :cursor ORDER BY p.id ASC")
    List<Product> findNextPage(@Param("cursor") Long cursor, @Param("pageSize") int pageSize);

    @Query("SELECT p FROM Product p ORDER BY p.id ASC")
    List<Product> findFirstPage(@Param("pageSize") int pageSize);
}

Controller:

@RestController
@RequestMapping("/api/products")
public class ProductController {
    private final ProductRepository repository;

    @GetMapping
    public PaginatedResponse<Product> getProducts(
            @RequestParam(required = false) Long cursor,
            @RequestParam(defaultValue = "10") int pageSize) {
        List<Product> products = cursor == null
                ? repository.findFirstPage(pageSize)
                : repository.findNextPage(cursor, pageSize);
        Long nextCursor = products.isEmpty() ? null : products.get(products.size() - 1).getId();
        return new PaginatedResponse<>(products, nextCursor);
    }
}

Response DTO:

public class PaginatedResponse<T> {
    private List<T> content;
    private Long nextCursor;

    public PaginatedResponse(List<T> content, Long nextCursor) {
        this.content = content;
        this.nextCursor = nextCursor;
    }
}

FAQs

Q1. How does cursor pagination improve performance?

By eliminating the need for OFFSET, it avoids scanning unnecessary rows, making queries faster for large datasets.

Q2. Can cursor-based pagination handle sorted datasets?

Yes, you can sort by multiple fields (e.g., price, id) without performance degradation using proper indices.

Q3. Is cursor-based pagination suitable for all applications?

No, it’s ideal for large, growing datasets but may not be necessary for small or static datasets.

Cursor-based pagination in Spring Boot ensures efficient data retrieval and a smoother user experience, making it a must-have for scalable applications. Start implementing it in your Spring Boot APIs today!

Table of Contents