Paginating a REST API, and Why OFFSET Stops Working

Every list endpoint needs pagination, Spring Data gives you Pageable for free, and that is where most teams stop. It works well until the table is large or the data changes while a client is reading it. Both problems have the same root cause and the same fix.

The default: offset pagination

@GetMapping("/orders")
public Page<OrderSummary> list(Pageable pageable) {
    return orderService.list(pageable);
}

Spring binds ?page=2&size=20&sort=createdAt,desc and issues limit 20 offset 40. It is easy, it gives you a total count and a page number, and for a few thousand rows it is the right answer.

Cap the page size before you ship it, because a client that asks for size=1000000 will get it:

spring.data.web.pageable.max-page-size=100
spring.data.web.pageable.default-page-size=20

Failure one: OFFSET gets slower the deeper you go

offset 100000 does not skip to row 100,000. The database produces the first 100,020 rows in sorted order and discards 100,000 of them. Page one is instant, page five thousand times out, and the cost grows linearly with page number. Any crawler or export script that walks all pages will spend most of its time re-reading rows it already has.

Failure two: shifting results

Offsets address positions in a result set, not rows. If a new order arrives between the client's request for page one and page two, everything shifts down by one, and the row that was last on page one is now first on page two. The client sees it twice. A deletion does the opposite and the client silently misses a row. For a UI this is cosmetic. For a synchronisation job it is a correctness bug that is nearly impossible to reproduce.

The fix: keyset pagination

Instead of "skip 100,000 rows", say "give me the rows after this one". The client passes back the sort value of the last row it saw.

@Query("""
    select new com.example.OrderSummary(o.id, o.createdAt, o.total)
    from Order o
    where o.createdAt < :after
       or (o.createdAt = :after and o.id < :afterId)
    order by o.createdAt desc, o.id desc
""")
List<OrderSummary> page(@Param("after") Instant after,
                        @Param("afterId") Long afterId,
                        Limit limit);

Two things make this work. The sort must end in a unique column, here the id, or rows with identical timestamps will be skipped or repeated at the boundary. And the comparison must be a tuple comparison, hence the or clause. On PostgreSQL you can write it directly as (o.created_at, o.id) < (:after, :afterId) in native SQL, which the planner handles well with a matching composite index.

With an index on (created_at desc, id desc), every page costs the same regardless of depth, and rows inserted after the client started are simply not in its window.

The cursor

Do not expose the raw column values. Encode them into an opaque string so you can change the sort key later without breaking clients:

public record Cursor(Instant createdAt, Long id) {

    public String encode() {
        return Base64.getUrlEncoder().withoutPadding()
            .encodeToString((createdAt + ":" + id).getBytes(UTF_8));
    }
}

The response carries the cursor for the next page and nothing else:

{
  "items": [ ... ],
  "nextCursor": "MjAyNi0wOS0wNFQxMDoxNTowMFo6ODgxMg"
}

Base64 is encoding, not security. Anyone can decode it. The point is that it is not a contract.

What you give up

Keyset pagination cannot jump to page 47, and it has no total count. Both are real losses, and both are usually acceptable: numbered pages past the first few are rarely used, and select count(*) on a large filtered table is often more expensive than the page query itself. If the UI needs an approximate total, get it from table statistics rather than a live count.

A reasonable compromise is to keep offset pagination for the human-facing UI, where people look at page one and then filter, and offer a cursor-based endpoint for exports and integrations, where every row matters and depth is unbounded.

Either way

Return a DTO rather than the entity, for the usual reasons, project the fields you need so the list query does not trigger N+1 loading, validate the sort field against an allowlist so a client cannot sort by an unindexed column, and make sure the index matches the sort order exactly. Pagination problems are almost always index problems wearing a different hat.