Fast Pagination for Large Django Tables

Problem statement

Paginator with page numbers works beautifully on small tables. On a table with ten million rows, page 1 is fast but page 5,000 takes seconds, and every page also runs SELECT COUNT(*), which scans the table. Both costs come from SQL, not Django: OFFSET 250000 makes the database read and discard 250,000 rows, and PostgreSQL has no cheap exact count. Keyset (cursor) pagination asks for "the next 50 rows after this one" using an index, so every page is as fast as the first.

1. For APIs and infinite scroll, use keyset pagination: filter with created_at < last_seen (plus a tie-breaker) and ORDER BY the same columns, with a matching index.
2. In DRF, use CursorPagination with an indexed, unique-enough ordering.
3. Avoid exact counts on big tables: drop "page X of Y", or show an estimate from pg_class.reltuples.
4. If you need page numbers, cap the maximum page depth and keep pages shallow.
5. For the admin, set show_full_result_count = False and consider a fast estimated paginator.
6. Index the ordering columns; keyset pagination without an index is just a slow scan.

Step-by-step solution

1. See the cost of OFFSET

EXPLAIN ANALYZE SELECT * FROM orders_order ORDER BY created_at DESC LIMIT 50 OFFSET 500000;

The plan reads 500,050 rows to return 50. Execution time grows linearly with the offset.

2. Keyset pagination by hand

def orders_page(request):
    qs = Order.objects.order_by("-created_at", "-id")
    after = request.GET.get("after")        # "2026-10-02T10:15:00+00:00|81234"
    if after:
        ts, pk = after.split("|")
        qs = qs.filter(Q(created_at__lt=ts) | Q(created_at=ts, id__lt=int(pk)))
    page = list(qs[:51])
    has_next = len(page) > 50
    page = page[:50]
    next_cursor = f"{page[-1].created_at.isoformat()}|{page[-1].id}" if has_next else None
    return render(request, "orders/list.html", {"orders": page, "next": next_cursor})

class Meta:
    indexes = [models.Index(fields=["-created_at", "-id"], name="order_created_id_idx")]

The id tie-breaker makes the order total, so rows with identical timestamps are neither skipped nor repeated. The index lets PostgreSQL jump straight to the cursor position.

3. DRF CursorPagination

class OrderCursorPagination(CursorPagination):
    page_size = 50
    ordering = ("-created_at", "-id")

class OrderViewSet(viewsets.ReadOnlyModelViewSet):
    pagination_class = OrderCursorPagination

DRF encodes the position in an opaque cursor parameter, returns next and previous links, and never runs COUNT(*). The trade-off: clients cannot jump to arbitrary page numbers.

4. Counts without scanning

Exact counts on big tables are expensive in PostgreSQL because of MVCC. Options:

• Show "next/previous" only, which most feeds and APIs need anyway.
• Show an estimate for unfiltered tables:

from django.db import connection
def estimated_count(model):
    with connection.cursor() as c:
        c.execute("SELECT reltuples::bigint FROM pg_class WHERE oid = %s::regclass", [model._meta.db_table])
        return c.fetchone()[0]

• Cache filtered counts for a few minutes.

5. The admin

@admin.register(Order)
class OrderAdmin(admin.ModelAdmin):
    show_full_result_count = False
    list_per_page = 50

show_full_result_count = False removes the second unfiltered count query. A custom paginator returning an estimated count removes the remaining one for unfiltered changelists.

Verification check: EXPLAIN ANALYZE of a deep keyset page shows an Index Scan with a tiny row count and millisecond execution; the endpoint's latency is the same for the first page and a deep page.

Explanation

SQL OFFSET has no shortcut: the database must produce and skip all preceding rows in sort order, so cost grows with depth. Keyset pagination turns "page N" into a range condition on the sort key (WHERE (created_at, id) < (last_created_at, last_id)), which a B-tree index satisfies by seeking directly to the boundary and reading forward. Counting is separately expensive in PostgreSQL because visibility must be checked row by row. Removing both lets list endpoints stay fast as tables grow by orders of magnitude.

Edge cases / notes

• Sorting by non-unique columns needs a unique tie-breaker such as id in both the ordering and the cursor.
• Filters must be combined with the cursor condition and covered by a suitable composite index.
• Mutable sort keys (for example a score that changes) can make items move between pages; prefer immutable keys like creation time.
• MySQL behaves the same way with OFFSET; keyset pagination helps there too.

Next steps

• Apply it to APIs with Speed Up Django REST Framework APIs.
• Slow admin lists: Fix Django Admin Slow on Large Tables.

FAQ

Why are deep pages slow in Django?

OFFSET makes the database read and discard all earlier rows, and COUNT(*) scans the table.

What is keyset pagination?

Fetching the next rows after the last seen sort key using a WHERE condition and an index, instead of an offset.

Does DRF support keyset pagination?

Yes, with CursorPagination and an indexed ordering.

Can I keep page numbers?

On small tables, yes. On large ones, cap the depth and avoid exact counts.