Area 08 · Backend · Concept 18 of 20

Pagination

Return long lists in pages (limit/offset or cursor).

11–20 · Production levelPhase 0 + 5 · Weeks 1–2, 18–20≈ 30 min

The idea

Endpoints that list things (documents, chats, messages, traces) must not return everything at once. Pagination returns a page plus a way to get the next one.

Limit/offset (?limit=20&offset=40) is simple but gets slow on deep pages and can skip or repeat items when data changes. Cursor (keyset) pagination returns an opaque cursor encoding the last item’s sort key (created_at, id); the next query asks for items after it. It stays fast and stable.

Always cap the page size, return a next_cursor (or null at the end), and index the sort columns.

Why it matters

Unpaginated lists become slow, expensive and eventually break as data grows.

Words to know

  • CursorA token pointing at a position in a sorted list.
  • Keyset paginationPaging by “after this sort key” instead of offset.

Try it without code

Read a real API

  1. Open the GitHub API docs for listing issues.
  2. Find how it paginates (Link header).

Use it in code

Cursor pagination for chat messages.

python
import base64, json

def encode(created_at, id_): return base64.urlsafe_b64encode(json.dumps([created_at, id_]).encode()).decode()
def decode(c): return json.loads(base64.urlsafe_b64decode(c))

def list_messages(conn, chat_id, limit=20, cursor=None):
    limit = min(limit, 100)
    params = {"chat": chat_id, "limit": limit + 1}
    where = "chat_id = %(chat)s"
    if cursor:
        params["ts"], params["id"] = decode(cursor)
        where += " AND (created_at, id) < (%(ts)s, %(id)s)"
    rows = conn.execute(f"SELECT id, created_at, content FROM messages WHERE {where} "
                        "ORDER BY created_at DESC, id DESC LIMIT %(limit)s", params).fetchall()
    page, more = rows[:limit], len(rows) > limit
    nxt = encode(str(page[-1][1]), page[-1][0]) if more else None
    return {"items": page, "next_cursor": nxt}
PRACTICE · TICK IT AFTER THIS

Add cursor pagination to your documents and chat history endpoints.

Common mistakes

  • No maximum page size.
  • Sorting by a non-unique column without a tie-breaker.

Go deeper