Choosing Between PageNumberPagination and CursorPagination in Django REST Framework

The Pagination Problem in DRF

When an API endpoint returns thousands of records, you need pagination. Django REST Framework ships with two built-in strategies: PageNumberPagination (offset/limit) and CursorPagination (keyset). They solve the same problem but behave very differently under load. Picking the wrong one can turn a responsive feed into a database bottleneck.

PageNumberPagination: Simple but Costly at Depth

PageNumberPagination uses OFFSET and LIMIT in SQL. The database must scan and discard OFFSET rows before returning the page. That work grows linearly with the page number. Page 1 is fast; page 5,000 on a 100k-row table forces a full index scan of 500k rows.

It supports random page jumps (?page=42) and renders numbered controls in the browsable API. The response includes count, next, previous, and results. The count query (SELECT COUNT(*)) can also be expensive on large tables unless you override get_count() with a cached or estimated value.

CursorPagination: Constant-Time Seeks with Constraints

CursorPagination encodes a cursor (base64 URL-safe) that holds the last-seen ordering value and direction. The next request translates to WHERE ordering_field > last_value ORDER BY ordering_field LIMIT page_size. No offset, so the 5,000th page costs the same as the first.

Trade-offs: the ordering must be stable and unique (DRF 3.14+ defaults to ['-created', '-id']), the cursor is opaque, and clients can only follow next/previous links—no jumping to page 42. The browsable API shows only relational links. Filtering combined with cursors requires the filter fields to be part of the ordering or a custom get_cursor_queryset(); otherwise the cursor becomes invalid.

Worked Example: Activity Feed with 100k Rows

Model:

class Activity(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    verb = models.CharField(max_length=50)
    target = models.GenericForeignKey('target_content_type', 'target_object_id')
    created = models.DateTimeField(auto_now_add=True, db_index=True)
    id = models.BigAutoField(primary_key=True)

    class Meta:
        indexes = [models.Index(fields=['-created', '-id'])]
        ordering = ['-created', '-id']

Two viewsets, same queryset, different paginators:

class PageNumberActivityViewSet(viewsets.ReadOnlyModelViewSet):
    queryset = Activity.objects.all()
    serializer_class = ActivitySerializer
    pagination_class = PageNumberPagination
    # page_size controlled by ?page_size=20 or PAGE_SIZE setting

class CursorActivityViewSet(viewsets.ReadOnlyModelViewSet):
    queryset = Activity.objects.all()
    serializer_class = ActivitySerializer
    pagination_class = CursorPagination
    # cursor pagination ignores ?page=; uses ?cursor= opaque token

SQL for page 5,000 (page_size=20):

• PageNumberPagination: SELECT ... FROM activity ORDER BY -created, -id LIMIT 20 OFFSET 100000 — scans 100k index entries.
• CursorPagination: SELECT ... FROM activity WHERE (created, id) < (last_created, last_id) ORDER BY -created, -id LIMIT 20 — seeks directly to the cursor position.

In a write-heavy feed where new activities appear at the top, PageNumberPagination shifts results between requests (items appear/disappear). CursorPagination avoids this because the cursor anchors to a specific row.

Trade-offs and Limitations

• Index requirement: Cursor pagination fails silently if the ordering columns lack a composite index. Always define Meta.indexes matching the ordering.
• Filter + cursor interaction: Adding ?search=term to a cursor-paginated view breaks the next link unless you override get_cursor_queryset() to include the filter in the cursor payload.
• Count query cost: PageNumberPagination runs SELECT COUNT(*) on every request. Override get_count() to use a cached total or EXPLAIN estimate for large tables.
• API contract change: Switching paginators alters response shape (count disappears, page param ignored) and query parameters. Version the endpoint or communicate the breaking change.
• Non-deterministic ordering: Window functions, annotations, or unindexed fields can produce unstable ordering, breaking cursor assumptions.

Practical Hybrid Strategy

Use PageNumberPagination for admin dashboards, internal tools, and datasets under ~10k rows where random access and visible page numbers matter. Use CursorPagination for infinite-scroll mobile feeds, sync endpoints, and any list that grows beyond 10k rows or experiences frequent inserts at the top.

To verify in your project: create a test table with 100k rows, enable django-debug-toolbar, and compare the SQL panel for page 5,000 of each paginator. Decode a cursor with base64.urlsafe_b64decode(cursor + '==').decode() to inspect the ordering payload. Confirm the browsable API renders numbered links for page-number and only rel="next"/rel="prev" links for cursor.

Check the DRF changelog for pagination updates (releases page) before upgrading, as cursor encoding and default ordering have evolved.