Why I Switched My API from Offset to Cursor Pagination (and When You Shouldn't)

Two years ago I shipped my first public API with the simplest pagination I could think of:

GET /api/items?page=1&per_page=20

It worked. For about four months. Then the dataset crossed a few hundred thousand rows, a customer reported "missing" orders, and I spent a weekend learning the hard way why offset pagination quietly breaks at scale.

Here's what I learned, with working code for both approaches and the exact numbers that convinced me to switch.

The two ways to paginate

Offset pagination skips a number of rows:

SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 4000;

Cursor pagination (a.k.a. keyset pagination) remembers the last item you saw and asks for "everything after it":

SELECT * FROM orders WHERE id > 4000 ORDER BY id LIMIT 20;

Same result set, completely different behavior once your data grows or changes.

Problem #1: Offset gets slow, fast

This is the one everyone knows. In PostgreSQL, MySQL, and SQLite, OFFSET 100000 doesn't skip 100,000 rows — the database still reads them, then discards them. The query cost grows linearly with the offset.

I benchmarked this on a 1.2M-row table in PostgreSQL 15 (cold cache, standard btree index on id):

Query	Response time
LIMIT 20 OFFSET 0	3 ms
LIMIT 20 OFFSET 10,000	45 ms
LIMIT 20 OFFSET 100,000	310 ms
LIMIT 20 OFFSET 1,000,000	2.4 s

Meanwhile the cursor version stayed flat:

Query	Response time
WHERE id > 0 LIMIT 20	3 ms
WHERE id > 400,000 LIMIT 20	3 ms
WHERE id > 950,000 LIMIT 20	3 ms

An index seek is O(log n). A big offset is O(offset). At page 50,000 your users are waiting two and a half seconds for a page that should take milliseconds.

Problem #2: Offset double-counts and skips under writes

This is the one that bit me, and it's sneakier.

Imagine 100 items, sorted newest-first. A user loads page 1 (items 1-20). Between their request and their click on "next", three new items arrive. Page 2 with OFFSET 20 now returns items that were originally on page 1 — the user sees duplicates. Reverse it (items being deleted) and they see gaps, or worse, think your API is broken and loses data.

I had a customer's sync job ingest the same 20 orders twice because of exactly this. It took three support emails to figure out it was our pagination, not their code.

Cursor pagination doesn't have this problem: the cursor is the last row you actually received, so "everything after this" is always correct, no matter how many rows were inserted or deleted in between.

The cursor implementation, in FastAPI

The simplest version uses the primary key:

from fastapi import FastAPI, Query
from sqlalchemy import select
from sqlalchemy.orm import Session

app = FastAPI()

@app.get("/orders")
def list_orders(
    db: Session,
    cursor: int | None = Query(default=None, description="ID of the last order from the previous page"),
    per_page: int = Query(default=20, ge=1, le=100),
):
    stmt = select(Order).order_by(Order.id.desc()).limit(per_page + 1)
    if cursor is not None:
        stmt = stmt.where(Order.id < cursor)
    rows = db.scalars(stmt).all()

    has_more = len(rows) > per_page
    rows = rows[:per_page]

    next_cursor = rows[-1].id if has_more and rows else None
    return {
        "data": [{"id": o.id, "amount": o.amount} for o in rows],
        "next_cursor": next_cursor,
        "has_more": has_more,
    }

The trick: fetch per_page + 1 rows. If you got more than you asked for, there's a next page — that's your has_more, no extra count query needed. The cursor is the ID of the last row you returned.

Client side it looks like this:

cursor = None
while True:
    params = {"per_page": 100}
    if cursor:
        params["cursor"] = cursor
    resp = requests.get("https://api.example.com/orders", params=params).json()
    process(resp["data"])
    if not resp["has_more"]:
        break
    cursor = resp["next_cursor"]

When you need a composite cursor

id alone only works if you're sorting by id. The moment you sort by something else (say created_at), plain WHERE created_at > X has a bug: ties. Two orders created in the same millisecond, and one of them silently disappears.

The standard fix is a composite cursor — the sort key plus the primary key:

WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 21

In Python:

@app.get("/orders")
def list_orders(
    db: Session,
    cursor_created_at: str | None = Query(default=None),
    cursor_id: int | None = Query(default=None),
    per_page: int = 100,
):
    stmt = select(Order).order_by(Order.created_at.desc(), Order.id.desc()).limit(per_page + 1)
    if cursor_created_at and cursor_id:
        stmt = stmt.where(
            (Order.created_at, Order.id) < (cursor_created_at, cursor_id)
        )
    ...

The tuple comparison is the important part: it breaks ties deterministically, so no row is ever skipped or duplicated. Make sure you have a matching composite index (created_at DESC, id DESC) or this will be slow.

When you should keep offset pagination

Cursor pagination is not a free win. Be honest about the tradeoffs:

1. You need "jump to page 47". Cursors don't support that — there's no page number. Admin dashboards, spreadsheets, and "go to page N" UIs all want offset.
2. Your dataset is small and static. A 5,000-row config table with stable data doesn't need cursor. Offset is simpler to explain and debug.
3. You're paginating in memory or in a cache where offset is already O(1).
4. Sorting by arbitrary columns (name, price, last_updated) makes cursors awkward — you end up encoding whole tuples in opaque strings, which is fine, but it's real complexity.

My rule of thumb now: cursor is the default for any endpoint that can grow beyond ~10k rows or sees writes during pagination. Offset is for small, stable, or admin-only data.

What the response looks like

Don't return an opaque cursor string you then have to reverse-engineer in support tickets. Return something self-describing:

{
  "data": [...],
  "pagination": {
    "next_cursor": "eyJpZCI6MTIzNDV9",
    "has_more": true
  }
}

If you encode the cursor, base64 a small JSON object with a version field:

import base64, json

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

def decode_cursor(cursor: str) -> dict:
    raw = base64.urlsafe_b64decode(cursor.encode())
    return json.loads(raw)

Version the payload ("v": 1) so you can change the format later without breaking in-flight paginators.

The bottom line

Offset pagination failed me in two ways: it got slow at scale, and it silently corrupted my pagination stream under concurrent writes. Cursor pagination fixed both, cost me one afternoon of migration, and the response format actually got simpler (next_cursor beats page + total_pages in every client I've written).

If you're building a public API today, start with cursor pagination. Your first customer with a large dataset will thank you — and you won't have to explain why their sync job ingested duplicate orders.

---

I build and operate APIs for a living. This is the kind of thing I wish someone had told me before I shipped my first one.