Pagination — Cursor Beats Offset for Reasons You'll Hit

"Offset pagination is the natural first pattern. It works fine until your dataset grows past a few thousand rows, or anyone writes to the table between page requests. Then it breaks in two specific ways that cursor pagination doesn't."

Why You Have to Paginate

Any API that returns a list eventually returns too much of it. GET /users with 50,000 users is a hostile response — the client downloads megabytes, the server scans the whole table, the UI freezes rendering. Pagination is the protocol for "give me some, then I'll ask for more."

Three patterns dominate, with very different tradeoffs.

Pattern 1: Offset/Limit (or Page/Page-Size)

The natural first design: GET /users?offset=100&limit=20 means "skip the first 100, give me 20." Pure SQL: SELECT ... LIMIT 20 OFFSET 100.

Pros: trivial to implement, intuitive for UI "jump to page 5" features, plays well with total-count display.

Cons (both serious):

Unstable under writes. If a new row is inserted at position 50 between page 1 (offset=0) and page 2 (offset=20), page 2 starts at what USED to be row 20 but is now row 21 — you skip row 21's old content (now at index 22), or you see row 20's old content (now at index 21) twice. For active datasets this happens constantly.
O(N) at large offsets. SELECT ... LIMIT 20 OFFSET 1000000 requires the DB to scan and skip 1,000,000 rows. There's no index trick; offset is computational. Past a few thousand offset, performance degrades sharply.

Pattern 2: Cursor Pagination

The client passes a cursor identifying "the last item I saw," and the server returns the next N items strictly after that cursor. GET /users?cursor=usr_8x3kPq&limit=20 means "give me the 20 users sorted right after this one."

Pros (both fix the offset problems):

Stable under writes. The cursor encodes a specific position in the sort order, not an index. Inserts and deletes don't shift the cursor's meaning.
O(log N) regardless of position. The server uses an indexed predicate (WHERE id > 'usr_8x3kPq' ORDER BY id LIMIT 20), which is an index range scan. Same speed at cursor=position-1 as at cursor=position-1,000,000.

Cons: can't jump to an arbitrary page (no "page 5 of 20" UI); cursor must be opaque (clients shouldn't construct them); needs a stable sort order (usually by created_at + id tiebreaker).

Pattern 3: Page Tokens (Google / AWS Style)

Like cursors but explicitly opaque — the server returns a nextPageToken, and the client passes it back on the next request. Server can encode anything it wants (sort position, filter snapshot, anti-tampering signature). Google Cloud APIs and AWS use this.

Pros: same as cursor + server can change the encoding without breaking clients.

Cons: same as cursor + opacity makes debugging harder (you can't read the token to see where you are).

Default to cursor pagination for new APIs. Offset feels simpler but breaks in both common cases — active datasets (insert/delete instability) and large datasets (performance). Cursor handles both. Reserve offset for cases you control end-to-end (admin tools on small tables, etc.) where the costs don't bite.

Returning Pagination Metadata

Three places the next-page info can live:

Response body. {"items": [...], "next_cursor": "abc", "has_more": true}. Most common; explicit; clients always see it.
Link header (RFC 8288). Link: </users?cursor=abc>; rel="next". The HATEOAS-flavored approach. GitHub does this.
Both. Belt and suspenders — body for SDK convenience, Link header for tooling that parses standard headers.

Total count is a separate question. If you need it ("showing 1-20 of 4,500"), return it as {"total": 4500} in the body or X-Total-Count: 4500. But computing total count on every request is expensive on large tables — many APIs return only "more available" booleans and reserve totals for explicit count endpoints.

cwkPippa's Pagination Reality

cwkPippa's session list endpoint currently uses offset (?offset=0&limit=50) because the dataset is small (Dad has maybe 500 conversations total across all four brains). When that count grows past a few thousand, cursor pagination becomes attractive — particularly for the council list, which accumulates round-by-round. Switching is a known future task; the current offset pagination ships fine until then. This is the right place to defer: low cost now, clear migration path when the cost flips.

Code

Three pagination patterns side by side·bash

# Offset pagination — works for small datasets, breaks at scale
curl 'https://api.example.com/users?offset=0&limit=20'
# {
#   "items": [...],
#   "total": 4500,
#   "offset": 0,
#   "limit": 20
# }

# Cursor pagination — stable + fast at any scale
curl 'https://api.example.com/users?limit=20'
# {
#   "items": [...],
#   "next_cursor": "usr_8x3kPq",
#   "has_more": true
# }

# Follow next page
curl 'https://api.example.com/users?cursor=usr_8x3kPq&limit=20'
# {
#   "items": [...],
#   "next_cursor": "usr_LmN9Op",
#   "has_more": true
# }

# Link header alternative (RFC 8288, GitHub-style)
curl -i 'https://api.example.com/users?limit=20'
# HTTP/1.1 200 OK
# Link: </users?cursor=usr_8x3kPq&limit=20>; rel="next"
# Content-Type: application/json

Cursor pagination — fetch limit+1 to detect 'has more'·python

# FastAPI — cursor pagination implementation
from fastapi import FastAPI, Query
from sqlalchemy import select  # imaginary ORM

app = FastAPI()

@app.get('/users')
async def list_users(
    limit: int = Query(20, ge=1, le=100),
    cursor: str | None = Query(None, description='opaque cursor from previous page'),
):
    # Stable sort order: created_at + id tiebreaker
    query = select(User).order_by(User.created_at, User.id)

    if cursor:
        # cursor encodes (created_at, id) of the last seen row
        last_created_at, last_id = decode_cursor(cursor)
        query = query.where(
            (User.created_at, User.id) > (last_created_at, last_id)
        )

    # Fetch one extra to detect if there's a next page
    rows = await db.execute(query.limit(limit + 1))
    items = list(rows.scalars())
    has_more = len(items) > limit
    items = items[:limit]  # trim the probe row

    next_cursor = None
    if has_more and items:
        last = items[-1]
        next_cursor = encode_cursor((last.created_at, last.id))

    return {
        'items': [u.to_dict() for u in items],
        'next_cursor': next_cursor,
        'has_more': has_more,
    }

Client — paginate_all generator, never loads everything·python

# Client — walk all pages
import httpx

def paginate_all(url: str, params: dict | None = None):
    params = params or {}
    while True:
        resp = httpx.get(url, params=params)
        resp.raise_for_status()
        data = resp.json()
        for item in data['items']:
            yield item
        if not data.get('has_more'):
            break
        params['cursor'] = data['next_cursor']

# Walk every user, never holding the full list in memory
for user in paginate_all('https://api.example.com/users', {'limit': 100}):
    print(user['id'])

Exercise

Build a FastAPI endpoint that lists 10,000 rows from a database (use SQLite + a quick faker), with both offset and cursor pagination side by side: GET /items-offset?offset=N&limit=20 and GET /items-cursor?cursor=X&limit=20. Benchmark each at offsets 0, 100, 1000, 10000 with time curl .... Then, while a paginated walk is happening, insert 100 new rows in the middle of the sort order and observe which paginator skips/repeats items (offset will; cursor won't).

Hint

For cursor encoding, base64(json.dumps([created_at, id])) is enough for a demo. Offset gets dramatically slower past offset=1000; cursor stays flat. The instability demo is the more dramatic one — start a paginator (limit=10, iterate slowly with sleep between requests), then INSERT 100 rows in another shell. Watch offset miscount; watch cursor stay coherent.

Pagination — Cursor Beats Offset for Reasons You'll Hit

Why You Have to Paginate

Pattern 1: Offset/Limit (or Page/Page-Size)

Pattern 2: Cursor Pagination

Pattern 3: Page Tokens (Google / AWS Style)

Returning Pagination Metadata

cwkPippa's Pagination Reality

Code

External links

Exercise

Progress

Comments 0