Pagination looks like a solved problem in Spring Boot: add a Pageable parameter, return a Page, done. Most of the trouble shows up later, when the table has millions of rows, rows are being inserted while users page through them, or a mobile client asks for size=100000. Interviewers like this topic because it connects an innocent API choice to database behaviour.
The Java below targets Spring Boot 3.x with Spring Data JPA 3.1 or later on Java 17+. It’s illustrative and wasn’t compiled here. The SQL timings come from SQLite 3.53 on a laptop with a 2-million-row table, run from Python; absolute numbers will differ on your database, but the shape won’t.
The standard setup and what it binds
With Spring Data on the classpath, Boot registers argument resolvers so a controller can take a Pageable directly:
@RestController
@RequestMapping("/orders")
public class OrderController {
private final OrderRepository orders;
public OrderController(OrderRepository orders) { this.orders = orders; }
@GetMapping
public PagedModel<OrderView> list(
@PageableDefault(size = 20, sort = {"createdAt", "id"}, direction = Sort.Direction.DESC)
Pageable pageable) {
return new PagedModel<>(orders.findAll(pageable).map(OrderView::from));
}
}A request like GET /orders?page=2&size=50&sort=createdAt,desc&sort=id,desc becomes a PageRequest for the third page (pages are zero-based by default), 50 rows, sorted by createdAt then id. The repository method generates ORDER BY ... LIMIT 50 OFFSET 100 and, because it returns a Page, a second SELECT count(*) query to fill in totalElements and totalPages.
Two defaults matter. Size falls back to 20 when not given, and is capped at 2,000 (spring.data.web.pageable.max-page-size). Two thousand rows of a rich DTO is far too much for most APIs, so lower it:
spring.data.web.pageable.max-page-size=100Also be careful what clients can sort on. sort accepts any property name; sorting by an unindexed column on a big table turns every page into a full scan. Validate the sort properties against an allow-list, or map public sort names to columns yourself.
What the JSON looks like
Returning Page<T> straight from a controller used to serialize Spring’s internal PageImpl, including fields like pageable and sort that change between versions. Since Spring Data 3.3 that logs a warning. Wrapping the page in PagedModel, as above, gives a stable shape:
{
"content": [ { "id": 1999960, "createdAt": "2026-10-18T09:14:03Z", "total": 4999 } ],
"page": { "size": 20, "number": 2, "totalElements": 2000000, "totalPages": 100000 }
}You can make that the default with @EnableSpringDataWebSupport(pageSerializationMode = VIA_DTO). Either way, it’s your API contract, so pick it deliberately and document it.
A sort that isn’t unique will lose rows
Sorting by createdAt alone looks fine until two orders share a timestamp, which at any real volume they will. SQL doesn’t guarantee the order of rows with equal sort keys, and the database can legitimately return ties in a different order on the next query (a different plan, a parallel scan, a page of the index that was rewritten). If rows A and B tie across the boundary between page 3 and page 4, a client can see A twice and B never.
The fix is cheap: always end the sort with a unique column. ORDER BY created_at DESC, id DESC is fully deterministic. That’s why @PageableDefault above sorts by both. Back it with a composite index on (created_at, id), so the database can read rows in order without sorting.
There’s a second way offset pagination drops or repeats rows, even with a perfect sort: the data changes between requests. The test table has 2 million orders. Read page 1 (five rows), insert one new order, then read page 2:
page 1 [2000000, 1999999, 1999998, 1999997, 1999996]
page 2 after one insert [1999996, 1999995, 1999994, 1999993, 1999992] repeated: [1999996]The new order pushed everything down one position, so order 1999996 appears on both pages. A deletion does the opposite and a row is skipped. For an admin table that’s tolerable. For a feed or an export that must see every row exactly once, it’s a bug.
Why deep pages get slow
OFFSET 1980000 doesn’t jump to row 1,980,000. The database walks the index (or the table) and throws away the first 1,980,000 rows before returning 20. The cost grows linearly with page number. On the test table, with the (created_at, id) index in place:
SELECT * OFFSET 0: 0.02 ms
SELECT * OFFSET 20000: 0.43 ms
SELECT * OFFSET 1000000: 22.82 ms
SELECT * OFFSET 1980000: 45.47 ms
SELECT * keyset : 0.02 msSQLite’s query plan confirms the difference: the offset query is SCAN orders USING COVERING INDEX ix_orders_created, the keyset query is SEARCH orders USING COVERING INDEX ix_orders_created (created_at<?). A scan versus a seek. On a server database with rows that don’t fit in memory, the deep-offset case is usually worse, since skipped rows may mean disk reads, and many concurrent deep-page requests (a crawler walking ?page= links, say) add up quickly.
The count query has a cost too: a count(*) over 2 million rows took about 8 ms here, on every page request, and with joins and filters it can dominate the response time. If the client only needs “is there a next page?”, return a Slice<T> instead of a Page<T>. Spring Data then fetches size + 1 rows to answer hasNext() and skips the count. For complex @Query methods that do need a total, supply a cheaper countQuery.
Keyset scrolling with Window and ScrollPosition
Keyset (or “seek”) pagination remembers where the last page ended and asks for rows after it: WHERE (created_at, id) < (:lastCreatedAt, :lastId) ORDER BY created_at DESC, id DESC LIMIT 20. It’s constant time regardless of depth, and inserts or deletes elsewhere don’t shift it, so no repeats or gaps.
Spring Data 3.1 added first-class support. A derived query returning Window<T> takes a ScrollPosition:
public interface OrderRepository extends JpaRepository<Order, Long> {
Window<Order> findFirst20ByCustomerIdOrderByCreatedAtDescIdDesc(
String customerId, ScrollPosition position);
}
// first page
Window<Order> window = orders.findFirst20ByCustomerIdOrderByCreatedAtDescIdDesc(
"c-42", ScrollPosition.keyset());
// next page: continue from the last row of this one
if (window.hasNext()) {
ScrollPosition next = window.positionAt(window.size() - 1);
Window<Order> page2 = orders.findFirst20ByCustomerIdOrderByCreatedAtDescIdDesc("c-42", next);
}Over HTTP you encode the position as an opaque cursor: serialize the last row’s createdAt and id, Base64 them, and return "next": "eyJjIjoiMjAyNi0xMC0xOFQwOToxNDowM1oiLCJpIjoxOTk5OTYwfQ". The client sends it back as ?cursor=... and never sees or builds keys itself. Keep the cursor opaque so you can change its contents later, and validate it on the way in, since it’s user input.
The trade-off: keyset can’t jump to page 437. It only goes forward (or backward, with the comparison reversed). For infinite scroll, feeds, sync APIs and exports, that’s exactly what you want. For a back-office table where someone types a page number, offset with a capped maximum page is still reasonable.
Interview exercise: the export that misses orders
A partner integration exports all orders by calling GET /orders?page=N&size=500&sort=createdAt in a loop until a page comes back empty. The table has 30 million rows and grows by about 50 orders a second. The partner reports occasional missing and duplicated orders, and the last pages of each run take several seconds. What’s wrong, and how do you redesign the endpoint?
Answer. Three problems compound. The sort is on createdAt alone, which isn’t unique, so ties at page boundaries can repeat or skip rows. New orders arrive during the run, and with offset pagination each insert ahead of the current position shifts every later row down by one, so the partner sees duplicates (or gaps if rows are deleted). And at page 60,000 the database is discarding 30 million rows per request, plus running a count(*) over the whole table each time, which explains the slowdown.
Redesign the export as a cursor-based endpoint. Sort by (created_at, id) with a matching index, return a Window of 500 rows plus an opaque next cursor, and don’t compute a total. Each request is then an index seek, and inserts during the run don’t affect positions already passed. If the partner needs a consistent snapshot, sort ascending and have them stop at a createdBefore timestamp captured at the start, so the run has a fixed end; if they need ongoing sync, the same cursor can be resumed tomorrow to fetch only newer orders. Keep the old offset endpoint for interactive use, with max-page-size capped and a maximum page depth.