Paginate and filter large API lists

Approach

Use CursorPagination ordered by an indexed, unique-ish column for stable, fast pages, and allow filtering with validated query parameters. Cap the page size.

Solution

from rest_framework import generics, serializers
from rest_framework.pagination import CursorPagination

from shop.models import Order


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


class OrderSerializer(serializers.ModelSerializer):
    class Meta:
        model = Order
        fields = ["id", "status", "total", "created_at"]


class OrderFilters(serializers.Serializer):
    status = serializers.ChoiceField(choices=["pending", "paid", "shipped"], required=False)
    created_after = serializers.DateTimeField(required=False)


class OrderList(generics.ListAPIView):
    serializer_class = OrderSerializer
    pagination_class = OrderCursorPagination

    def get_queryset(self):
        filters = OrderFilters(data=self.request.query_params)
        filters.is_valid(raise_exception=True)             # 400 for bad parameters
        qs = Order.objects.filter(customer=self.request.user)
        if status := filters.validated_data.get("status"):
            qs = qs.filter(status=status)
        if after := filters.validated_data.get("created_after"):
            qs = qs.filter(created_at__gte=after)
        return qs

Pitfalls

• Cursor pagination ordered by a non-unique field without a tiebreaker, skipping or repeating rows.
• Unbounded page_size letting clients request the whole table.
• Passing raw query parameters into filter(**request.GET).

Variations

• django-filter with DjangoFilterBackend declares filters declaratively.
• Add a composite index matching the ordering, e.g. (customer_id, created_at, id).

Further reading: CursorPagination