Stop Offset-Paginating Your Big DRF Endpoints: A Practical Case for Cursor Pagination

Your API listing endpoint was fine at 10,000 rows. At two million, page 400 takes four seconds, and your mobile client shows the same item twice while skipping three others. Both symptoms come from the same decision: offset-based pagination on a large, frequently-changing table. The fix is built into Django REST Framework already — CursorPagination — and switching is usually a dozen lines of configuration.

Why offset pagination falls apart at scale

DRF's default PageNumberPagination (and LimitOffsetPagination) translate "page 400, size 50" into SQL with OFFSET 19950 LIMIT 50. The database still has to locate those 19,950 rows — scanning and discarding them — before it can return the 50 you asked for. Cost grows linearly with page depth, so your deepest pages are your slowest queries.

The second problem is correctness. Offset paging assumes the dataset is frozen. If a client is walking pages 1, 2, 3… and someone inserts a row that sorts into page 1, every subsequent page shifts by one: the client sees a duplicate. Deletes cause skips. On a busy feed or event log, this isn't an edge case — it's constant.

How cursor pagination fixes both

Instead of "skip N rows," a cursor encodes a position: "give me rows where created < <cursor value>, ordered by created descending, limit 50." With an index on the ordering column, that's an index range scan whose cost is roughly constant whether you're on the first page or the ten-thousandth.

Because the cursor points at an actual row's value rather than a row count, inserts and deletes elsewhere in the table don't shift anything. New rows simply appear ahead of the cursor on the next forward pass; nothing gets duplicated or skipped mid-iteration.

DRF embeds the cursor as an opaque, signed, base64-encoded token in the next and previous URLs it returns. Clients treat it as an opaque string — they follow links rather than constructing offsets, which also means they can't hand-craft a request for an arbitrary deep offset and hammer your database.

Configuring it

Subclass CursorPagination and set an ordering on an indexed column, then register it globally or per-view. Run this in your Django project's settings module; no extra permissions or packages are needed beyond DRF itself:

# pagination.py
from rest_framework.pagination import CursorPagination

class FeedPagination(CursorPagination):
    page_size = 50
    ordering = "-created"  # must be indexed; see caveat below

# settings.py
REST_FRAMEWORK = {
    "DEFAULT_PAGINATION_CLASS": "myapp.pagination.FeedPagination",
}

Or apply it to one view only:

class EventListView(generics.ListAPIView):
    queryset = Event.objects.all()
    serializer_class = EventSerializer
    pagination_class = FeedPagination

Response shape is the familiar envelope — next, previous, results — so most serializers and clients need no changes beyond following the next URL instead of incrementing a page number.

The non-unique-ordering trap

This is the caveat that bites people in production. If your ordering column isn't unique — say, a created timestamp where bulk imports produce identical values — rows sharing the boundary value can be skipped or repeated between pages. The cursor filter created < X has no way to distinguish the five rows that all have timestamp X.

The cleanest fix is to order by a unique, indexed, monotonic column — an auto-increment primary key works well (ordering = "-id") if insertion order is acceptable. If you genuinely need timestamp ordering, check whether your DRF version supports multiple ordering fields as a tiebreaker, or denormalize a unique sort key. Whatever you choose, verify it: seed a few hundred rows with deliberately duplicated ordering values, page through the entire set, and assert the union of all pages equals the full table with no duplicates.

Also watch the interaction with ordering backends. If you expose OrderingFilter and let clients re-sort the queryset, the final ordering must match the cursor's ordering or page boundaries become meaningless. A common pattern is to fix the cursor ordering and disable client-controlled ordering on that endpoint.

What you give up

Cursor pagination is a forward/backward stream, not a page picker. There's no "jump to page 37," no "showing page 3 of 812" UI, and no total page count (DRF doesn't run a COUNT for cursor pages, which is part of why it's fast). For admin tables and search results where users expect numbered pages, offset pagination remains the right tool — those datasets are usually smaller and less volatile anyway. The decision rule: infinite scroll, feeds, activity logs, and sync-style clients get cursors; bounded admin grids get page numbers.

Verifying the win

Don't take the performance claim on faith — measure it on your own schema. In a Django shell connected to your production-like database, compare query plans: print(Event.objects.all().order_by('-created')[19950:20000].explain()) versus the query DRF generates for a deep cursor page (enable query logging or capture it with django.db.connection.queries in a test). You're looking for the cursor query to use an index range scan on the ordering column instead of scanning and discarding tens of thousands of rows. If the plan shows a sequential scan, the ordering column is missing an index — add one before shipping, because cursor pagination without a supporting index buys you nothing.

Setting names and defaults do shift between DRF versions, so confirm against the docs for the version pinned in your project before deploying.