How to do cursor-based pagination

Direct answer: Use a stable unique sort (for example, created_at plus a tie-breaker id), return an opaque cursor token that encodes the last item’s position, and let clients request the next slice with that token (for REST via a Link rel="next" header or a next_cursor field). Keep repeating until no next link/token is provided. This avoids OFFSET performance issues and remains stable when rows are inserted or deleted between requests (GitLab pagination guidelines) (PingCAP overview).

What cursor pagination is and why it helps

Cursor (keyset) pagination fetches results relative to a known position instead of skipping rows with OFFSET. Rather than “page 5,” the client asks for “items after this cursor.” This is typically faster for large tables and avoids duplicates/gaps when data changes during paging (GitLab) (PingCAP).

Design a deterministic order and cursor

• Always sort by a deterministic, unique keyset. If created_at is not unique, add a unique tiebreaker like id: ORDER BY created_at, id.
• Index the columns used in ORDER BY and WHERE so the database can do range scans efficiently (GitLab).

SQL pattern (keyset range)

Forward (next page):

-- Use a composite key for stability
SELECT *
FROM entries
WHERE (created_at, id) > ($1, $2)
ORDER BY created_at, id
LIMIT $3;

Backward (previous page):

SELECT *
FROM entries
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT $3; -- then reverse in-memory for ascending display

These clauses follow standard SQL WHERE and ORDER BY semantics (PostgreSQL reference: SELECT/WHERE/ORDER BY/LIMIT).

REST patterns: next token and Link headers

• Link header: Include a rel="next" URL with the cursor for the next slice. Many APIs also provide rel="prev" and rel="first"/"last" when meaningful (GitHub REST pagination) (MDN HTTP headers: Link).
• Opaque cursor field: You can also include a next_cursor (and prev_cursor) in the JSON body. Keep the token opaque so clients don’t parse internal fields (GitLab).

Example response (both header and body):

{
  "items": [
    {"id": 101, "created_at": "2026-10-30T09:00:00Z"},
    {"id": 102, "created_at": "2026-10-30T09:01:00Z"}
  ],
  "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0xMC0zMFQwOTowMTowMFoiLCJpZCI6MTAyfQ==",
  "prev_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0xMC0zMFQwOTowMDowMFoiLCJpZCI6MTAxfQ==",
  "has_more": true
}

Example Link header with next:

Link: <https://api.example.com/entries?after=eyJpZCI6MTAyfQ==&limit=2>; rel="next"

GitHub also documents that different endpoints may use page, before/after, or since, but the Link header remains the reliable way to navigate (GitHub).

Encoding the next token

To keep cursors opaque, encode the position (for example, the composite key) into a token such as Base64 JSON. The server must validate and decode it; clients should treat it as an opaque string (don’t parse or modify it) (GitLab).

{"created_at": "2026-10-30T09:01:00Z", "id": 102}

Filtering and stable ordering

• Apply the same filters on every page so the cursor remains valid across requests.
• Extend indexes to cover both filter and sort keys (for example, (status, created_at, id)) to avoid full scans (GitLab).

Client flow (forward and backward)

1. Request the first page with a limit (no cursor).
2. Save the returned next_cursor (and optionally prev_cursor).
3. Request the next page using after=next_cursor (or the Link rel="next").
4. Repeat until there is no next link/token (has_more=false or Link rel="next" absent).
5. For previous pages, use before=prev_cursor with the reverse query shape.

Common pitfalls (and fixes)

• Non-unique ordering: created_at alone can repeat. Always add a unique tiebreaker, commonly id, in both ORDER BY and cursor comparison.
• Missing indexes: Without indexes on the order and filter columns, the database will fall back to slow scans. Add composite indexes that match your WHERE and ORDER BY (GitLab).
• Exposing internal fields: Don’t make the cursor a plain offset or raw keys. Keep it opaque so you can change internals safely later (GitLab).
• Large OFFSET usage: Deep offsets get slower as row counts rise. Keyset/cursor pagination avoids skipping and remains stable on insert/delete churn (PingCAP).
• Building URLs on the client: Prefer the server-provided Link rel values instead of composing URLs yourself; it’s more robust to API changes (GitHub).

Practical examples

First page request

GET /entries?limit=25

Response

{
  "items": [{"id": 125, "created_at": "2026-10-31T09:00:00Z"}],
  "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyNi0xMC0zMVQwOTowMDowMFoiLCJpZCI6MTI1fQ==",
  "has_more": true
}

Next page

GET /entries?after=eyJjcmVhdGVkX2F0IjoiMjAyNi0xMC0zMVQwOTowMDowMFoiLCJpZCI6MTI1fQ==&limit=25

Related guidance

• Types of pagination in REST APIs (overview of offset, cursor/keyset and more)
• How to stop 429 Too Many Requests (useful with paged APIs and rate limits)

Put this into practice

Test the next-page cursor with tied sort values and an insert between requests. Record the unique tie-breaker and filter context so a successful first page does not hide duplicates or gaps later.

Ship a robust cursor-based pagination API

• Choose a stable unique order (for example, ORDER BY created_at, id) and add matching composite indexes
• Return an opaque next cursor (and prev cursor) or a Link rel="next" header; do not expose raw keys or offsets
• Validate and decode cursors server-side; reject malformed or out-of-range tokens
• Apply identical filters on every page request and honor the same sort order
• Stop when no Link rel="next" is present or has_more is false

FAQ

How do I expose the “next page” to clients?

Include a Link rel="next" header (widely used in REST) or a next_cursor field in the JSON body. GitHub documents Link-based pagination with rel values (GitHub) and MDN documents the Link header field (MDN).

Can clients jump to page N?

Cursor pagination is optimized for sequential access. If you must support random jumps, keep offset pagination for admin/reporting views and use cursor pagination for large feeds (GitLab).

Can I safely show a total count?

Totals can be expensive or inconsistent on changing datasets. Consider approximate or capped counts in UI (for example, 1000+) as suggested in GitLab’s guidance (GitLab).