Pagination, filtering and sorting

A collection endpoint that returns everything works until the collection is large, and then it stops working all at once: the query is slow, the JSON is enormous, and the client that was holding it in memory falls over. Pagination is the fix, and the first way everyone reaches for is the one that quietly returns wrong answers.

#Offset pagination, and the row that appears twice

limit and offset — in Spring Data, PageRequest.of(page, size) — is the obvious approach. Skip some rows, take some rows.

Here are ten users, newest first, three to a page. Page one:

### page 1 (offset 0): [user-10, user-09, user-08]

Between that request and the next, somebody signs up. Not a contrived event: on any real list this happens constantly. Page two:

### now somebody signs up, between the two requests
### page 2 (offset 3): [user-08, user-07, user-06]

user-08 is on both pages. The new row went in at position one, everything shifted down by one, and "skip 3" now skips a different three than it would have a moment earlier. The client shows a duplicate, and the user below the boundary — had the sort been the other way — would have been skipped entirely and never seen at all.

No error. No warning. The API did exactly what it was asked.

And there is a second cost that arrives later. offset 100000 does not jump — the database produces and discards a hundred thousand rows to reach the ones you want. Page 1 is instant, page 4,000 is not, and the endpoint gets slower the deeper anyone goes.

#Keyset pagination asks "after this one"

Instead of counting rows to skip, name where you stopped:

select * from customer where id < :lastSeenId order by id desc limit 3

Same interruption, same ten rows:

### page 1: [user-NEW, user-10, user-09]
### somebody signs up again
### page 2 (after id 9): [user-08, user-07, user-06]

No duplicate, no skip. The second page is defined by a row, not by a count, so inserting anything above it changes nothing. And the query uses the index directly — where id < ? with a limit is the same cost on page 4,000 as on page 1.

The trade is real and worth stating plainly:

So the choice follows the UI, not taste. A numbered pager over a small, stable admin table is fine on offsets. An infinite-scroll feed, an export, anything a machine walks — keyset, every time.

#Cursors: keyset with the details hidden

Exposing ?afterId=9 works and leaks a decision you may want back. An opaque cursor encodes the same thing:

{
  "items": [ ... ],
  "next": "eyJpZCI6OSwic29ydCI6ImNyZWF0ZWRfYXQifQ"
}

The client treats it as a token, sends it back, and never parses it. You keep the freedom to change what is inside — adding the sort field to the cursor when you add a second sort order, for instance — without a client noticing.

Two rules make cursors safe: encode the sort you used, so a client cannot send a cursor from one ordering into another; and do not sign secrets into it — base64 is not encryption, and a cursor is a URL people will look at.

When there is no next page, the convention that causes fewest bugs is to omit next entirely rather than return null or an empty string. "Is there a next key?" is one check, and clients get it right.

#Filtering that does not become a query language

The pressure on any list endpoint is to accept more filters, and the end state is an accidental query language that nobody designed:

GET /orders?filter=status:eq:SHIPPED,total:gt:5000,customer.name:like:ana

That is a parser you now maintain, an injection surface, and a shape no OpenAPI schema describes. The simpler version survives longer:

GET /orders?status=SHIPPED&minTotal=5000&createdAfter=2026-01-01

Named parameters, each with a type, each documented, each validated by the previous lesson's constraints. When the filters genuinely are dynamic — a search screen with twelve optional fields — that is where Specification from the JPA course belongs, built from an allow-list of field names rather than from whatever string arrived.

#Bound everything

Three limits that cost nothing to add and are awkward to retrofit:

• A maximum page size. ?size=1000000 should be capped, not honoured. Clamp silently, or reject with 422 — but decide, because the default of trusting it is a memory problem waiting for a bored client.
• A default size. Missing size should mean 20, not everything.
• A maximum depth, if you stay on offsets. Past a few thousand pages, nobody is browsing; something is crawling, and a cursor is what it should be using.