API Pagination Patterns (Cursor vs Offset)

Key facts, with sources

• Postman's 2025 State of the API report, based on more than 5,700 developers and API professionals, found 83.2% of respondents adopting some level of an API-first approach. (Postman State of the API 2025)
• The same Postman 2025 research found 65% of organizations now generate revenue directly from their API programs. (Postman State of the API 2025)
• One in four developers (24%) now design APIs specifically for consumption by AI agents, while 89% use generative AI tools in their daily work. (Business Wire)
• APIs make up 57% of the dynamic (non-cacheable) internet traffic processed by Cloudflare, and that share continues to grow. (Cloudflare)
• Salt Security's 2024 State of API Security report found 95% of respondents experienced API security problems in production, with security incidents more than doubling year over year from 17% to 37% of organizations. (Salt Security)

Where offset pagination breaks

Offset pagination has two independent failure modes. The correctness one: pages are defined by position, and positions shift. If a new row is inserted at the top while a user scrolls, everything slides down by one — the last item of the previous page reappears at the top of the next page, or a row silently falls into the gap between requests. In feeds and lists with active writes, users literally see duplicates.

The performance one: OFFSET does not skip rows cheaply. The database walks the index, produces every skipped row, and throws it away, so page one thousand costs a thousand pages of work. Deep pagination on a large table is a classic slow-query source that only appears once data grows — which is why it never shows up in development.

How keyset cursors work

Cursor pagination replaces position with value. Instead of asking for rows starting at position two hundred, the client sends back an opaque token encoding the sort-column values of the last row it saw — say, a created-at timestamp and an ID — and the server asks for rows strictly after that point in sort order. The database resolves this with a direct index seek, so the millionth row costs the same as the first.

Stability falls out naturally: the page boundary is anchored to a real row's values, not a shifting position, so concurrent inserts and deletes cannot cause duplicates or gaps within the traversal. The one requirement is a deterministic total order, which is why the cursor includes a unique tiebreaker column — timestamps alone collide.

A production implementation in SQLAlchemy

The pattern: order by a composite key with a unique tiebreaker, use a row-value comparison for the seek, and fetch one row beyond the page size to learn whether a next page exists without a second query. The cursor itself is just the last row's key values, base64-encoded so clients treat it as opaque.

import base64
import json
from datetime import datetime

from sqlalchemy import select, tuple_

PAGE_SIZE = 20


async def list_orders(session, cursor: str | None):
    stmt = select(Order).order_by(Order.created_at.desc(), Order.id.desc())

    if cursor:
        created_str, last_id = json.loads(base64.urlsafe_b64decode(cursor))
        stmt = stmt.where(
            tuple_(Order.created_at, Order.id)
            < (datetime.fromisoformat(created_str), last_id)
        )

    rows = (await session.execute(stmt.limit(PAGE_SIZE + 1))).scalars().all()

    next_cursor = None
    if len(rows) > PAGE_SIZE:
        rows = rows[:PAGE_SIZE]
        last = rows[-1]
        next_cursor = base64.urlsafe_b64encode(
            json.dumps([last.created_at.isoformat(), last.id]).encode()
        ).decode()

    return rows, next_cursor

Cursor design details that bite later

Keep cursors opaque. The moment clients parse or construct cursor contents, the encoding becomes a public contract you can never change; base64-encoding a JSON payload signals hands-off while staying debuggable server-side. If you want tamper-resistance — preventing clients from forging a cursor into data they should not traverse — sign it or encrypt it, though your queries should enforce authorization regardless.

Match the index to the sort exactly: descending created-at with descending ID needs a composite index in that order, or the seek advantage evaporates. And decide cursor lifetime semantics up front. A cursor references values, not a snapshot, so rows inserted behind the reader's position are simply never seen in that traversal — usually correct for feeds, but worth stating in the API docs before an integrator files it as a bug.

When offset is still the right call

Cursor pagination cannot jump: there is no page seven, only next-after-here. Admin tables, internal dashboards, and search results where users genuinely navigate to arbitrary pages are legitimately offset territory — the datasets are typically small, writes are infrequent, and numbered page controls are the expected UI.

My rule of thumb: infinite scroll, mobile feeds, public APIs, and anything with concurrent writes get cursors; numbered-page UIs over modest, mostly-static datasets keep offset. If a table needs both — numbered pages and stability — that is usually a sign the UI wants search and filtering rather than deep pagination, because no human actually browses to page forty; they are looking for something a query should find.

When to hire senior help

Bring in senior backend help when you are defining the public contract of your API (auth model, versioning, rate limits), because those decisions are nearly impossible to change once partners integrate. It is also warranted when incidents like timeout cascades, N+1 query storms, or authorization bugs start appearing, since these are pattern problems a senior engineer has usually fixed many times before. If your stack includes React Native + Python + AI, a senior engineer who owns the full product beats coordinating multiple juniors.

Bottom line

Dhairya Senjaliya ships Python — Backend APIs projects worldwide — book a scoping call to discuss your specific situation.

Common pitfalls to avoid

• ✕Shipping list endpoints without pagination or rate limiting, then having one integration partner's bulk pull take down the database
• ✕Launching with no versioning strategy, so the first breaking schema change strands mobile apps that cannot be force-updated
• ✕Missing per-object authorization checks (broken object-level authorization), letting any authenticated user read other tenants' records by iterating IDs
• ✕Treating internal APIs as trusted and undocumented, then exposing them to partners or frontends later without adding auth, quotas, or contracts

Frequently asked questions

Sources

• Postman State of the API 2025
• Business Wire
• Cloudflare
• Salt Security