How do you design pagination in a REST API and why does offset pagination break at deep pages

• Offset pagination – uses query parameters like ?page=2&size=20 or ?offset=40&limit=20; the database uses LIMIT and OFFSET in SQL.
• Metadata – the response includes totalElements, totalPages, hasNext, hasPrevious so the client can build navigation.
• Deep-page problem – OFFSET 49980 forces the database to scan and discard 49,980 rows before returning 20, so cost grows linearly with depth.
• Cursor (keyset) pagination – remembers the last seen key and queries WHERE id > :lastId ORDER BY id LIMIT 20, using an index seek that is fast at any depth.
• Trade-off – cursor pagination cannot jump to an arbitrary page number; offset pagination can but degrades at depth.

What is offset pagination?

Offset-based pagination accepts either page and size (where offset = page * size) or offset and limit directly. The server translates this into SQL with LIMIT and OFFSET. Alongside the data, you return metadata so the client can build navigation: total elements, total pages, current page, page size, and boolean flags like hasNext and hasPrevious.

package com.bank.controller;

import org.springframework.data.domain.*;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/api/v1/accounts/{accountId}/transactions")
public class TransactionController {

    @GetMapping
    public Page<TransactionResponse> list(
            @PathVariable String accountId,
            @RequestParam(defaultValue = "0") int page,
            @RequestParam(defaultValue = "20") int size) {
        Pageable pageable = PageRequest.of(page, size,
                Sort.by("createdAt").descending());
        return transactionService.findByAccount(accountId, pageable);
        // Returns: content, totalElements, totalPages, hasNext, etc.
    }
}

Offset pagination response:
{
  "content": [ /* 20 transactions */ ],
  "page": 2,
  "size": 20,
  "totalElements": 50000,
  "totalPages": 2500,
  "hasNext": true,
  "hasPrevious": true
}

Why does offset pagination break at deep pages?

Offset pagination has three problems at depth. First, performance: OFFSET 49980 LIMIT 20 forces the database to read and throw away 49,980 rows before returning the 20 you want, so cost grows linearly. Second, the COUNT(*) for total elements requires a separate full scan on large tables. Third, correctness: if rows are inserted or deleted while the user pages, offsets shift, causing duplicates or skipped rows.

// SQL for page 2500 of 50,000 transactions:
// SELECT * FROM transactions WHERE account_id = 'SAV2001'
//   ORDER BY created_at LIMIT 20 OFFSET 49980
// → Database reads 50,000 rows, discards 49,980, returns 20.
// → Page 1 is instant; page 2500 is slow.

What is cursor (keyset) pagination?

Cursor pagination remembers the last key the client saw and queries forward from it. The query becomes WHERE id > :lastId ORDER BY id LIMIT 20, which uses the index on id to seek directly to the right spot in constant time regardless of depth.

The response carries a nextCursor that the client passes back on the next request. The cursor is usually base64-encoded so clients treat it as opaque.

package com.bank.service;

import java.util.List;

public class TransactionService {

    public CursorPage<TransactionResponse> findAfter(
            String accountId, String cursor, int limit) {
        String lastId = cursor != null ? decodeCursor(cursor) : null;
        List<TransactionResponse> items = lastId != null
                ? repo.findAfter(accountId, lastId, limit + 1)
                : repo.findFirst(accountId, limit + 1);
        boolean hasNext = items.size() > limit;
        if (hasNext) items = items.subList(0, limit);
        String nextCursor = hasNext
                ? encodeCursor(items.get(items.size() - 1).id()) : null;
        return new CursorPage<>(items, nextCursor, hasNext);
    }
}

Cursor pagination response:
{
  "content": [ /* 20 transactions */ ],
  "nextCursor": "eyJpZCI6IlRYTi0zOTk4MCJ9",
  "limit": 20,
  "hasNext": true
}

How do offset and cursor compare?

| Aspect | Offset Pagination | Cursor Pagination |
|---|---|---|
| Deep pages | Slow: scans and discards all preceding rows | Fast: index seek, constant time |
| Jump to page N | Yes | No: forward/backward only |
| Stability | Unstable: inserts/deletes shift offsets | Stable: anchored on a value |
| Total count | Expensive COUNT(*) query | Not provided (or estimated) |
| Sort column | Any | Must be unique and indexed |
| Complexity | Simple to implement | Slightly more complex |

// Offset: simple but degrades.
// SELECT * FROM txns ORDER BY id LIMIT 20 OFFSET 49980  ← slow

// Cursor: always fast.
// SELECT * FROM txns WHERE id > 'TXN-49980' ORDER BY id LIMIT 20  ← index seek

How do you detect hasNext without a COUNT?

With cursor pagination, you fetch one extra row beyond the limit. If you get limit + 1 rows, there is a next page; return only the first limit rows and use the last one's key as the cursor. This avoids the expensive total count.

// Fetch limit + 1 to detect hasNext.
List<Transaction> items = repo.findAfter(accountId, lastId, limit + 1);
boolean hasNext = items.size() > limit;
if (hasNext) items = items.subList(0, limit);

How do you handle composite sort keys?

When the sort column is not unique (like createdAt), ties can cause rows to appear on multiple pages. The solution is a composite cursor: sort by (createdAt, id) where id breaks ties. The cursor encodes both values.

// Composite cursor for non-unique sort:
// SELECT * FROM txns
// WHERE (created_at, id) > (:lastCreatedAt, :lastId)
// ORDER BY created_at, id
// LIMIT 20
// Cursor: base64({"createdAt": "2026-03-15", "id": "TXN-500"})

When should you use offset vs cursor?

Use offset pagination for small admin screens where users genuinely jump to an arbitrary page and need total pages. Use cursor pagination for high-volume feeds like transaction history and audit logs where correctness under concurrent writes and constant-time deep access matter. Always cap the maximum page size to protect the server.

// Rule of thumb:
// Small table, jump-to-page needed → offset
// Large table, append-heavy, deep scrolling → cursor
// Both: always cap maximum page size.
@RequestParam(defaultValue = "20") @Max(100) int size // Never more than 100

What are common mistakes with pagination?

Not paginating at all and returning unbounded results is the biggest mistake. Another is using offset pagination for large, append-heavy tables. A third is not capping the page size, allowing a client to request a million rows.

// Wrong: no pagination – returns all 50,000 transactions.
@GetMapping("/transactions")
public List<TransactionResponse> listAll(@PathVariable String accountId) {
    return transactionService.findAll(accountId); // Unbounded!
}

// Correct: always paginate with a capped size.
@GetMapping("/transactions")
public CursorPage<TransactionResponse> list(
        @PathVariable String accountId,
        @RequestParam(required = false) String cursor,
        @RequestParam(defaultValue = "20") @Max(100) int limit) {
    return transactionService.findAfter(accountId, cursor, limit);
}

What should a senior engineer know about pagination?

A senior engineer knows that offset pagination is O(n) at depth while cursor is O(1) via index seek. They know that cursor pagination requires a unique, indexed sort column and cannot support jump-to-page. They cap page sizes, use composite cursors for non-unique sort columns, and choose the strategy based on data volume and access pattern.