Pagination Strategies

Pagination is the API problem that looks easy until it isn't. The naive offset and limit work for small datasets; they break in specific ways at scale. Cursor-based pagination is more robust but adds complexity. The right choice depends on the use case.

This page is about the patterns and the edge cases.

Offset/limit (the obvious approach)

GET /api/orders?offset=100&limit=50

The server skips the first 100 results and returns the next 50.

When it works

When it breaks

Performance

For deep pages, the database must skip many rows:

SELECT * FROM orders ORDER BY created_at LIMIT 50 OFFSET 100000;

The database scans 100,050 rows to return 50. Cost is linear in offset.

Skipped or duplicated items

If items are inserted/deleted between page fetches, items shift:

For mutable data, offset pagination is unstable.

Cursor-based pagination

GET /api/orders?after=eyJjcmVhdGVkX2F0Ijp...&limit=50

The cursor encodes "where you left off" — typically the sort key of the last item plus tie-breakers.

Mechanics

SELECT * FROM orders
WHERE (created_at, id) > (?, ?)
ORDER BY created_at, id
LIMIT 50;

The condition is "after" the cursor. No skip; database uses index directly. O(log n) regardless of page depth.

Cursor encoding

Typical: opaque base64-encoded JSON with the sort key:

eyJjcmVhdGVkX2F0IjoiMjAyNi0wNC0yNlQxMjowMDowMFoiLCJpZCI6ImFiYyJ9

Decoded: { "created_at": "2026-04-26T12:00:00Z", "id": "abc" }

Opaque to the client; server defines the format.

When it works

When it has costs

Page-based (the API-friendly form)

GET /api/orders?page=5&per_page=50

Equivalent to offset/limit (offset = page * per_page) but more familiar to clients. Same performance issues at depth.

For UI that shows "page 1, 2, 3..." this is what most users expect.

The ordering problem

Pagination requires a stable order. Ambiguous order produces nondeterministic results:

-- Bad: ties are resolved nondeterministically
SELECT * FROM orders ORDER BY created_at LIMIT 50 OFFSET 100;

Two orders with the same created_at may appear in different orders across calls. Pagination breaks.

Always include a tie-breaker:

SELECT * FROM orders ORDER BY created_at, id LIMIT 50 OFFSET 100;

id (assumed unique) breaks ties. Pagination is now deterministic.

Total counts

Including total count in the response is expensive:

SELECT COUNT(*) FROM orders WHERE ...;

For large tables, this is slow. Common compromises:

For UIs that show "Showing 50 of 1,234,567 results," consider whether the count is actually useful at scale.

Specific patterns

Relay-style cursor pagination

Used by GitHub, Shopify, others. Standard structure:

type OrderConnection {
    edges: [OrderEdge!]!
    pageInfo: PageInfo!
    totalCount: Int  # optional
}

type OrderEdge {
    node: Order!
    cursor: String!
}

type PageInfo {
    hasNextPage: Boolean!
    hasPreviousPage: Boolean!
    startCursor: String
    endCursor: String
}

Bidirectional, cursor-based, with explicit page info. The standard for GraphQL pagination.

Keyset pagination (SQL idiom)

Same idea as cursor; "keyset" is the term in SQL contexts:

WHERE (created_at, id) > (?, ?)

Uses index efficiently; no offset.

Hybrid

Some APIs offer both: cursor for default, page-based as opt-in. Lets clients choose based on their UI.

Common failure patterns

A reasonable default

For new APIs:

For legacy / migration:

Further Reading