API Pagination Patterns — Offset, Cursor, Keyset, and Relay-Style Connections

Why pagination matters#

Returning an entire dataset in a single response is impractical at scale. A table with millions of rows would produce a multi-gigabyte JSON payload, exhaust server memory, and time out the client. Pagination breaks results into manageable pages so both client and server stay performant.

Offset-based pagination#

The simplest approach. The client sends offset and limit (or page and page_size) parameters.

GET /api/posts?offset=20&limit=10

SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 20;

Pros#

• Easy to implement
• Clients can jump to any page directly
• Works well with traditional page-number UIs

Cons#

• Shifting window problem: if a row is inserted or deleted between requests, items can be duplicated or skipped
• Performance degrades at high offsets: the database still scans and discards offset rows before returning results
• OFFSET 1000000 on a large table is expensive even with an index

Cursor-based pagination#

Instead of a numeric offset, the server returns an opaque cursor that encodes the position of the last item. The client passes it back to fetch the next page.

GET /api/posts?after=eyJpZCI6MTAwfQ&limit=10

The cursor is typically a Base64-encoded value of the sort key (e.g., {"id": 100} or {"created_at": "2026-03-28T12:00:00Z"}).

SELECT * FROM posts
WHERE created_at < '2026-03-28T12:00:00Z'
ORDER BY created_at DESC
LIMIT 10;

Pros#

• Stable results even when data changes between pages
• Constant-time performance regardless of page depth
• No skipped or duplicated items

Cons#

• Cannot jump to an arbitrary page
• Sorting must be deterministic (add a tiebreaker like id if the sort column has duplicates)

Keyset pagination#

Keyset pagination is the database-level technique behind cursor-based APIs. The query uses a WHERE clause on the sort key instead of OFFSET.

-- Page 1:
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 10;

-- Page 2 (using last row's values as the keyset):
SELECT * FROM posts
WHERE (created_at, id) < ('2026-03-28T12:00:00Z', 500)
ORDER BY created_at DESC, id DESC
LIMIT 10;

The composite (created_at, id) comparison ensures deterministic ordering even when timestamps collide.

Requirement: the sort columns must be indexed together.

CREATE INDEX idx_posts_cursor ON posts (created_at DESC, id DESC);

Page tokens#

Page tokens are opaque strings returned by the server that encode everything needed to fetch the next page — sort key values, filter state, and direction.

{
  "data": [ ... ],
  "next_page_token": "dXNlcl9pZD00Miwgc3RhcnQ9MjAyNi0wMy0yOA=="
}

Google Cloud APIs, Stripe, and many SaaS platforms use this pattern. The client treats the token as a black box and passes it back verbatim.

Benefits#

• The server can change its internal pagination strategy without breaking clients
• Tokens can embed encrypted filter state for security
• Natural fit for server-driven pagination

The total count problem#

Clients often want a total_count to display "Page 3 of 12". But counting all matching rows is expensive.

-- This can be slow on large tables:
SELECT count(*) FROM posts WHERE author_id = 42;

Strategies#

1. Estimate the count: use pg_class.reltuples or EXPLAIN output for an approximate total
2. Cache the count: compute periodically and store in a counter table or cache
3. Omit the count: return has_next_page instead of a total — this is what cursor-based APIs do naturally
4. Cap the count: return "total": "1000+" if the real count exceeds a threshold

Infinite scroll#

Infinite scroll on the frontend maps naturally to cursor-based pagination.

1. Client loads initial page: GET /api/posts?limit=20
2. User scrolls near bottom
3. Client requests next page: GET /api/posts?after=CURSOR&limit=20
4. Append results to the list
5. Repeat until has_next_page is false

Implementation tips:

• Use an intersection observer to detect when the user approaches the bottom
• Show a loading skeleton while fetching
• Deduplicate items client-side as a safety net
• Provide a "Back to top" affordance for long lists

Relay-style connections#

The GraphQL Relay specification defines a standard pagination format using connections, edges, and pageInfo.

query {
  posts(first: 10, after: "cursor123") {
    edges {
      cursor
      node {
        id
        title
        createdAt
      }
    }
    pageInfo {
      hasNextPage
      hasPreviousPage
      startCursor
      endCursor
    }
  }
}

Key concepts#

• Connection: the paginated field itself (posts)
• Edge: a wrapper containing the node (actual data) and a cursor
• pageInfo: metadata about pagination state
• first / after for forward pagination
• last / before for backward pagination

Why Relay connections?#

• Standardized structure across every paginated field
• Every item has its own cursor, enabling fine-grained fetching
• pageInfo makes UI state management straightforward
• Well-supported by client libraries (Relay, Apollo, urql)

Comparison table#

Best practices#

1. Default to cursor-based pagination for APIs consumed by mobile or infinite-scroll UIs
2. Use offset pagination only when users need to jump to arbitrary pages (admin dashboards, search results)
3. Always include a deterministic tiebreaker in your sort key (typically id)
4. Index your sort columns — pagination without an index is just a slow sequential scan
5. Return has_next_page instead of total_count when possible
6. Make cursors opaque — encode them so clients cannot reverse-engineer or tamper with sort values
7. Set a maximum page size — never let clients request limit=1000000
8. Document your pagination model — tell consumers which pattern you use, what the default and max page sizes are, and how to handle the last page

Wrapping up#

There is no single best pagination strategy. Offset pagination is simple and familiar. Cursor and keyset pagination handle scale and real-time data gracefully. Relay connections standardize the pattern for GraphQL. Pick the approach that matches your data characteristics, client needs, and performance requirements.

---

This is article #242 on Codelit. If this guide helped you think through API pagination patterns, explore the rest of our library for deep dives on API design, system architecture, and backend engineering.