How to Paginate an API: Offset, Keyset, and Cursor Pagination Compared

Every list endpoint eventually returns too much. The fix is pagination — and the answer almost everyone reaches for first, LIMIT and OFFSET, is the one that works beautifully in development and then quietly rots in production. As the table grows it gets slow, and as rows shift underneath a scrolling user it starts to skip and duplicate records. There are two better approaches that fix both problems, and the difference between them is mostly about how much you want to hide from the client. This is a practical, vendor-neutral guide to all three — offset, keyset, and cursor — what each costs and when each is right. Code is in FastAPI and SQLAlchemy; the ideas are framework-agnostic.

First: The Two Jobs Pagination Has to Do

A paging scheme has to get two things right, and it's worth naming them before any code, because they're exactly where the easy approach falls down:

• Performance — returning page 5,000 should cost about the same as returning page 1. The database should not have to read and discard everything before your slice.
• Stability — if rows are inserted or deleted while a user pages through, they shouldn't silently miss a record or see the same one twice. The window should track data, not a position.

Offset pagination, the default everyone starts with, quietly fails both at scale. The other two are built to pass them.

Strategy 1: Offset / Limit (and Why It Betrays You)

The familiar one: LIMIT rows, skip OFFSET of them. Page three of twenty is offset=40, limit=20. It's trivial to write and it can jump to any page by number, which is its one real superpower:

from fastapi import FastAPI, Query
from sqlalchemy import select

@app.get("/posts")
async def list_posts(
    limit: int = Query(20, le=100),
    offset: int = Query(0, ge=0),
):
    stmt = (
        select(Post)
        .order_by(Post.created_at.desc(), Post.id.desc())
        .limit(limit)
        .offset(offset)          # the DB still scans every skipped row
    )
    rows = (await session.execute(stmt)).scalars().all()
    return {"data": rows}

Two problems show up as soon as the table is real. First, performance degrades with depth: OFFSET 100000 doesn't teleport — the database generates and throws away all 100,000 rows before it reaches your 20, so deep pages get linearly slower. Second, the window drifts: because a page is defined by position, any insert or delete near the front shifts every later row by one — so a user paging through a busy feed skips records that slid past the boundary, or sees one twice. For a small, bounded dataset behind an admin screen where you genuinely want clickable page numbers, offset is fine. For anything large or actively changing, it's a slow, leaky default.

Strategy 2: Keyset (Seek) Pagination

Keyset pagination — also called seek pagination — replaces “skip N rows” with “give me the next N rows after this value.” You order by an indexed column and ask for everything past the last row the client already has. The database uses the index to seek straight to that spot, so the cost is the same whether you're on page one or page one million, and there's no position to drift:

from fastapi import FastAPI, Query
from sqlalchemy import select, tuple_

@app.get("/posts")
async def list_posts(
    limit: int = Query(20, le=100),
    after_created: datetime | None = None,
    after_id: int | None = None,
):
    stmt = (
        select(Post)
        .order_by(Post.created_at.desc(), Post.id.desc())
        .limit(limit)
    )
    if after_created is not None and after_id is not None:
        # row-value comparison: start strictly after the last row seen
        stmt = stmt.where(tuple_(Post.created_at, Post.id) < (after_created, after_id))
    rows = (await session.execute(stmt)).scalars().all()
    return {"data": rows}

With an index on (created_at DESC, id DESC), that WHERE is an index seek, not a scan — constant cost at any depth, and immune to inserts and deletes because you anchor to a value, not an ordinal position. The trade-off is honest: you get next (and, by reversing the comparison, previous), but not arbitrary “jump to page 50.” For feeds, infinite scroll, exports, and syncing — anything consumed in order — that's exactly the shape you want.

Strategy 3: Cursor Pagination (Keyset, Wrapped)

Cursor pagination is keyset pagination with the anchor hidden behind an opaque token. Instead of exposing after_created and after_id as raw query params, you hand the client a single next_cursor string — a base64 blob of the last row's sort values — and they pass it back to get the next page. It's the model the big public APIs expose, and the wrapping buys you something real: because the cursor is opaque, clients can't build logic on its internals, which leaves you free to change the sort key or encoding later without breaking them.

import base64, json
from datetime import datetime
from sqlalchemy import select, tuple_

def encode_cursor(row) -> str:
    payload = {"created_at": row.created_at.isoformat(), "id": row.id}
    return base64.urlsafe_b64encode(json.dumps(payload).encode()).decode()

def decode_cursor(cursor: str):
    payload = json.loads(base64.urlsafe_b64decode(cursor))
    return datetime.fromisoformat(payload["created_at"]), payload["id"]

@app.get("/posts")
async def list_posts(limit: int = Query(20, le=100), cursor: str | None = None):
    stmt = select(Post).order_by(Post.created_at.desc(), Post.id.desc()).limit(limit + 1)
    if cursor:
        last_created, last_id = decode_cursor(cursor)
        stmt = stmt.where(tuple_(Post.created_at, Post.id) < (last_created, last_id))
    rows = (await session.execute(stmt)).scalars().all()
    has_more = len(rows) > limit                 # fetched one extra to peek ahead
    rows = rows[:limit]
    next_cursor = encode_cursor(rows[-1]) if has_more and rows else None
    return {"data": rows, "next_cursor": next_cursor}

Two small details earn their keep. Fetching limit + 1 rows lets you know whether a next page exists without a second query or an expensive count — if you got the extra row, there's more. And returning next_cursor: null when you didn't is the unambiguous “you've reached the end” signal clients can loop on.

Shaping the Response

However you generate the page, wrap it in an envelope that's honest about what comes next:

• The data, plus a forward pointer. { "data": [...], "next_cursor": "..." } — with next_cursor set to null on the last page. That's the entire contract a scrolling client needs.
• Think hard before promising a total count. total: 48213 looks helpful, but on a large, filtered table a COUNT can cost as much as the query itself — which is why many cursor APIs omit it, make it opt-in, or return an approximate. Don't pay for a number nobody reads.
• Keep the ordering stable and documented. The sort key is the pagination contract; if it isn't deterministic (down to that unique tiebreaker), neither is your paging.

The Bottom Line

Offset pagination is the right tool for a narrow case — small data, page numbers — and the wrong default for everything else, because it pays more the deeper you go and leaks rows when the data moves. Keyset pagination fixes both by seeking to a value instead of counting to a position, and cursor pagination wraps that same idea in an opaque token so you can keep evolving the API underneath your clients. Pick by how the data is consumed, anchor every scheme on a total ordering that ends in a unique column, and reserve the total count for when someone will actually use it. Get those right and your list endpoints stay fast on page one and page one million alike — which is the only promise pagination was ever supposed to make.