Cursor Pagination in Spring Data JPA: A Stable Page for Codeac Search Results

Users scrolling through a large Codeac search result set hit the same problem: page 2 contains items they already saw on page 1, or items disappear entirely when new findings are indexed between requests. Offset pagination is the cause. The useful takeaway is to replace OFFSET with a stable cursor on a monotonic column, and accept the added controller complexity for consistent pages and index‑friendly queries.

Where offset pagination breaks for live code data

Spring Data JPA ships with Page<T> and offset‑based paging. It is simple for CRUD endpoints. For a live dataset like code findings, commits, or analysis runs, offset has two costs.

First, performance degrades. OFFSET 100000 forces the database to scan and discard rows, even if an index exists on the sort column. Second, stability is lost. If a new row is inserted before the current page, the next offset request returns a duplicate and skips a row. For an API that consumers page through repeatedly, that drift is unacceptable.

Cursor‑based pagination replaces offset with a position marker, typically the last seen value of a monotonically increasing column such as id or created_at. The client sends the cursor, the server returns the next slice after that point.

Designing a cursor with Spring Data JPA

Spring Data REST does not provide cursor pagination out of the box. You must write the repository method and controller yourself.

Choose a cursor that is monotonic and, ideally, unique. A composite cursor of (created_at, id) avoids gaps when timestamps collide. The query shape is:

WHERE (created_at > :cursorTime) OR (created_at = :cursorTime AND id > :cursorId)
ORDER BY created_at ASC, id ASC
LIMIT :size

This allows the database to seek using an index on (created_at, id) instead of scanning offset rows.

Spring Data JPA can express the first part with a derived query. A minimal repository signature is:

public interface FindingRepository extends JpaRepository<Finding, Long> {
    List<Finding> findByCreatedAtAfterOrderByCreatedAtAscIdAsc(Instant after, Pageable pageable);
}

For a composite cursor you will need a @Query with explicit parameters. Use Slice rather than Page when you do not need a total count, which saves an extra count query.

Worked example for a Codeac findings endpoint

Controller shape, to be run in a Spring Boot application with JPA enabled. No elevated permissions are required beyond normal application startup.

@RestController
@RequestMapping("/api/findings")
public class FindingController {
    private final FindingRepository repo;
    public FindingController(FindingRepository repo){ this.repo = repo; }

    @GetMapping
    public FindingsPage getFindings(@RequestParam(required = false) Instant cursor,
                                    @RequestParam(defaultValue = "20") int size){
        Instant after = cursor != null ? cursor : Instant.EPOCH;
        Pageable pageable = PageRequest.of(0, size, Sort.by("createdAt").ascending().and(Sort.by("id").ascending()));
        List<Finding> items = repo.findByCreatedAtAfterOrderByCreatedAtAscIdAsc(after, pageable);
        Instant nextCursor = items.isEmpty() ? null : items.get(items.size()-1).getCreatedAt();
        return new FindingsPage(items, nextCursor);
    }
}

Client request example to be run from a terminal where the service is reachable:

curl -G http://localhost:8080/api/findings \
  --data-urlencode "cursor=2023-01-01T00:00:00Z" \
  --data-urlencode "size=5"

Expected checks: the response contains an items array and a nextCursor value. The next request should use that nextCursor as the cursor parameter to continue forward. Risk: exposing raw timestamps can leak internal timing; consider encoding the cursor.

Trade‑offs and limits

Cursor pagination adds complexity. You cannot jump to an arbitrary page number, and sorting must be limited to columns covered by the cursor index. Using a timestamp alone can cause missed or duplicated records if the column is not unique, because multiple rows share the same value.

Spring Data REST will not generate cursor links automatically, so you must document the contract and handle encoding/decoding of the cursor in the controller.

A practical way to verify the implementation is to enable SQL logging for the datasource and inspect the generated statement. You should see a WHERE clause on the cursor columns with ORDER BY and LIMIT, and no OFFSET. You can also send a request with a known cursor and size and confirm the returned subset is correct and the next cursor advances monotonically.

For Codeac‑style search APIs where result sets are large and continuously updated, the stability and index‑seek performance of cursor pagination outweigh the loss of random access.