Cursor‑Based Pagination in Java Data Access: Solving Memory Pressure and UI Latency

Cursor‑Based Pagination in Java Data Access: Solving Memory Pressure and UI Latency

Problem: Large Result Sets Hurt UI and Memory

When a query returns thousands of rows, loading them all at once exhausts heap and makes the UI sluggish. The typical symptom is a spike in memory usage followed by a frozen browser.

Why Offset Pagination Fails at Scale

Offset pagination issues grow linearly with the number of rows skipped. A query such as SELECT * FROM customer ORDER BY created_at DESC LIMIT 20 OFFSET 10000 must scan and discard the first 10 000 rows before returning the next 20. This leads to two problems:

Aspect	Offset pagination	Cursor pagination
Rows examined	All rows up to the offset	Only rows after the cursor
Memory consumption	Increases with offset size	Bounded by page size
Query performance	Degrades sharply for deep pages	Consistent latency

Because of these drawbacks, offset pagination is unsuitable for large tables or deep UI pages.

Thesis: Use Server‑Side Cursor Pagination

Instead of offset‑based fetching, request a slice using a stable ordering key. This keeps memory bounded and eliminates the costly OFFSET clause, resulting in predictable performance even for millions of rows.

Choosing the Model and Mapping

Consider a simple Customer entity with fields id (primary key) and created_at. The id is unique and monotonic, making it a reliable cursor. If you prefer a timestamp, ensure it is indexed and never decreases.

Implementing a Custom Pageable

A custom Pageable carries the last cursor value (e.g., the created_at of the previous page). Below is a minimal implementation:

public class CursorPageable implements Pageable {
    private final int size;
    private final Object cursor; // e.g., LocalDateTime

    public CursorPageable(int size, Object cursor) {
        this.size = size;
        this.cursor = cursor;
    }

    @Override
    public Page getPage(Pageable next) {
        // next is the same CursorPageable with updated cursor
        // delegate to repository method that adds a WHERE clause
        return repository.findPage(size, cursor);
    }

    @Override public int getPageSize() { return size; }
    @Override public int getPageNumber() { return 0; } // not used
    @Override public Sort getSort() { return null; }
}

Run the application with java -jar myapp.jar (requires read access to the customer table). The curl command example remains the same.

Worked Example: Spring Boot Controller

@RestController
@RequestMapping("/api/customers")
class CustomerController {

    private final CustomerRepository repo;

    public CustomerController(CustomerRepository repo) {
        this.repo = repo;
    }

    @GetMapping
    public Page list(
            @RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "20") int size) {

        // Build a Specification that orders by created_at descending
        Specification spec = (root, query, cb) -
                cb.desc(root.get("createdAt"));

        // Use a custom Pageable that carries the cursor value (last created_at from previous page)
        CursorPageable pageable = new CursorPageable(size, pageableCursor);
        return repo.findAll(spec, pageable);
    }
}

Execute the app (e.g., mvn spring-boot:run or gradle bootRun) and call the endpoint with curl: curl "http://localhost:8080/api/customers?page=0&size=20". Expected JSON includes content (up to 20 CustomerDto) and totalElements. Risk: if the cursor key is not unique, duplicate rows may appear or be omitted.

Trade‑off: Stability vs Implementation Complexity

Cursor pagination avoids the O(N) cost of OFFSET, but it requires a unique, ever‑increasing key. If the ordering column can contain duplicates, add a tie‑breaker such as the primary key. Also, the cursor must reflect a consistent snapshot; concurrent inserts or updates between pages can cause missed or duplicated rows.

Verification Steps

1. Run unit tests with varying page and size values and assert that the returned slice matches the expected rows.
2. Profile heap usage under load (e.g., with VisualVM or Java Flight Recorder); memory should stay roughly constant regardless of page size.
3. Compare query execution time with and without pagination; the cursor version should show lower latency for deep pages.
4. Execute the curl command above and verify that totalElements matches the count of all rows in the customer table.
5. Check that the created_at value in the cursor is monotonic across pages; a decrease indicates a data anomaly.

Actionable Closing

To adopt cursor pagination, start by adding a stable ordering column to your entity, ensure it is indexed, and implement a custom Pageable that accepts the cursor value. Replace offset calls with setFirstResult‑free queries, test edge cases—first page, last page, and pages requesting more rows than exist—to confirm correct total element counts. Deploy and monitor memory metrics; you’ll see predictable usage and faster UI responses.