skip to content

A DRF list endpoint using LimitOffsetPagination on a very large table is slow and answers ?limit=1000000 — what causes both problems, and how do you harden it?

level: seniorimportance: should knowfreq 40%

answer

  1. no ceiling unless you set one
  2. clamped, not rejected
  3. a count on every page
  4. get_count or the paginator class

basics

~10 s

LimitOffsetPagination's max_limit defaults to None, so any limit is honoured, and every page runs queryset.count(). Set max_limit, override get_count() or switch to CursorPagination for deep or unbounded lists.

solid answer

~40 s

`LimitOffsetPagination.max_limit` is `None` by default, so `?limit=1000000` is honoured and one request serialises a million rows; even with a maximum set, DRF **clamps** larger values to it rather than rejecting them. Separately, every request calls `get_count()`, which runs `queryset.count()` — a full count on a large, filtered table — and deep `?offset=` values make the database walk and discard rows before returning any. `PageNumberPagination` has the same count, through Django's `Paginator.count`, and the same exposure if `page_size_query_param` is enabled without `max_page_size`. Hardening: set `max_limit` or `max_page_size`; override `get_count()` or `django_paginator_class` to cap or approximate the total; add a unique, indexed ordering; and move feeds and exports to `CursorPagination`, which issues no count and filters by position instead of offset.

code

python · 10 lines
python
from rest_framework.pagination import LimitOffsetPagination


class CappedCountPagination(LimitOffsetPagination):
    default_limit = 50
    max_limit = 200
    count_ceiling = 10_000

    def get_count(self, queryset):
        return queryset[: self.count_ceiling + 1].count()

go deeper

for a junior

Know that LimitOffsetPagination takes limit and offset from the query string and returns a total count.

for a middle

Explain that max_limit defaults to None, that oversized values are clamped, and that each request runs a count query.

for a senior

Diagnose a slow list endpoint by separating count, offset and serialisation cost, then cap sizes, tame the count and move deep paging to cursors.

for a principal

Set a pagination policy for the API — default caps, which endpoints may expose totals, and when cursors are mandatory — and enforce it with a shared base class.

## Two separate problems on one endpoint A list endpoint in Django REST Framework (DRF) that uses `LimitOffsetPagination` over tens of millions of rows tends to show two symptoms: some requests are enormous, and all requests are slower than the page size suggests. They have different causes in DRF's code. ## Problem 1: the client chooses the page size `LimitOffsetPagination` reads `?limit=` and `?offset=`: - `default_limit` comes from `PAGE_SIZE` and applies when `limit` is absent; - `max_limit` is **`None` by default**, so nothing bounds `limit`; - values are parsed by a helper that rejects zero and negatives (falling back to the default) and **clamps** anything above the maximum with `min()`. So `?limit=1000000` returns a million serialised rows unless you set `max_limit`, and with `max_limit = 200` it quietly returns 200 — no error, which clients should know from the documentation. The same pattern exists elsewhere: | Class | Client size parameter | Default cap | |---|---|---| | `LimitOffsetPagination` | `limit` (always on) | `max_limit = None` | | `PageNumberPagination` | off unless `page_size_query_param` is set | `max_page_size = None` | | `CursorPagination` | off unless `page_size_query_param` is set | `max_page_size = None` | A side effect worth knowing: because `get_limit()` checks `?limit=` before the default, a `LimitOffsetPagination` view paginates any request that sends `limit`, even when `PAGE_SIZE` is unset. ## Problem 2: work that grows with the table Two costs are paid on every request: 1. **The count.** `LimitOffsetPagination.paginate_queryset()` calls `get_count()`, which runs `queryset.count()` to fill the `count` key. `PageNumberPagination` gets the same number from Django's `Paginator.count`. On a large filtered table this can dominate the request. 2. **The offset.** The page is fetched as `queryset[offset:offset + limit]`, which becomes `LIMIT … OFFSET …`. The database still produces and discards the skipped rows, so page 5,000 costs far more than page 1. An offset beyond the end is not an error: DRF returns 200 with an empty `results` list, so crawlers can probe freely. ## Hardening steps - **Cap the size.** Set `max_limit` (or `max_page_size` when a size parameter is enabled) in a project-wide subclass. - **Tame the count.** Override `get_count()` on `LimitOffsetPagination`, or point `PageNumberPagination.django_paginator_class` at a `Paginator` subclass whose `count` is capped or estimated. Keep the `count` key's meaning documented if it becomes approximate. - **Order deterministically and index it.** Without a unique ordering, offset pages can repeat or skip rows; without an index, each page sorts the table. - **Switch style where depth is normal.** Feeds, sync jobs and exports belong on `CursorPagination`, which issues no count query, filters by a position instead of skipping rows, and caps its own internal tie offset at `offset_cutoff = 1000`. - **Bound filtering too.** A cheap page over an expensive filter is still expensive; the pagination class cannot fix the query it is handed. ## A project-wide default ```python from rest_framework.pagination import LimitOffsetPagination class BoundedLimitOffsetPagination(LimitOffsetPagination): default_limit = 50 max_limit = 200 ``` Register it as `DEFAULT_PAGINATION_CLASS` so a new endpoint cannot ship unbounded by accident, and keep `CursorPagination` subclasses for the few endpoints where clients page deeply.

  • What does a DRF LimitOffsetPagination endpoint return for an offset beyond the total count?
    `paginate_queryset()` returns an empty list when the offset exceeds the count, so the client gets 200 with `"results": []`, the real `count`, and a `previous` link. Unlike `PageNumberPagination`, which raises `NotFound` for an out-of-range page, nothing signals an error.
  • Why doesn't DRF's CursorPagination suffer from the deep-offset problem?
    It never skips rows by number. Each page filters the ordering field with `__lt` or `__gt` against the stored position and reads `page_size + 1` rows, so with an index the cost is the same on page one and page ten thousand. Its only offset handles ties and is capped by `offset_cutoff`.

saying these in an interview costs you the question

  • LimitOffsetPagination caps limit at PAGE_SIZE by default.
  • DRF rejects a limit above max_limit with a 400 error.
  • The count key is free because Django caches it between requests.
  • An offset past the end of the list returns 404.
  • Adding an index removes the cost of large offsets entirely.