skip to content

A Django ListView paginating two million audit-log rows is slow even on page 1; how do you find the cost and reduce it?

level: seniorimportance: should knowfreq 40%

answer

  1. two queries per page
  2. the total is computed every time
  3. a cached_property you can override
  4. paginator_class on the view

basics

~20 s

Every paginated page runs Paginator.count, a SELECT COUNT(*) over all matching rows, before fetching the slice. On two million rows the count dominates; override count in a Paginator subclass (capped, cached or estimated) and set paginator_class, or drop page numbers.

solid answer

~40 s

Page 1 still needs the total: `paginate_queryset()` calls `paginator.page()`, which validates the number against `num_pages`, which reads `Paginator.count`, which calls `QuerySet.count()`. That is a `COUNT(*)` over every matching row, repeated on every request because a new paginator is built each time. The page itself is a cheap `LIMIT 50`. Confirm it with the SQL log or a query profiler: two queries, the count taking most of the time. Fixes, from least to most invasive: filter the list (by actor or date range) so fewer rows match; subclass `Paginator`, override the `count` `cached_property` with a capped count such as `object_list[:10001].count()` or a cached value, and set `paginator_class`; or stop showing page totals and paginate by "older/newer" links, which deep lists need anyway because large offsets are slow too.

code

python · 25 lines
python
from django.core.paginator import Paginator
from django.utils.functional import cached_property
from django.views.generic import ListView

from .models import AuditEntry


class CappedCountPaginator(Paginator):
    cap = 10_000

    @cached_property
    def count(self):
        # sliced count: the database stops after cap + 1 rows
        return self.object_list[: self.cap + 1].count()

    @property
    def is_capped(self):
        return self.count > self.cap


class AuditLogView(ListView):
    model = AuditEntry
    ordering = ['-created_at', '-id']
    paginate_by = 50
    paginator_class = CappedCountPaginator

go deeper

for a junior

Know that a paginated list runs a COUNT query as well as the page query.

for a middle

Trace the chain page() to num_pages to count to QuerySet.count(), and note that the cached_property lives only as long as one request's paginator.

for a senior

Measure the two queries, then pick default filters, a capped or cached count via paginator_class, or a count-free design, and explain what each gives up.

for a principal

Decide per screen whether an exact total is a real user need, and set a policy for large tables that combines default filters with keyset browsing.

## Where the time goes A paginated `ListView` issues two queries per request: 1. **The count.** `MultipleObjectMixin.paginate_queryset()` calls `Paginator.page(number)`, which validates `number` against `num_pages`, which reads `Paginator.count`. For a QuerySet that is `QuerySet.count()`, a `SELECT COUNT(*)` with the view's filters. 2. **The page.** `page.object_list` is the QuerySet sliced to `[offset:offset + 50]`, a `LIMIT`/`OFFSET` query run when the template iterates. For page 1 of an audit log the second query reads 50 rows. The first has to account for every matching row, so its cost grows with the table: on two million rows it is the slow one. `count` is a `cached_property`, but only per paginator instance, and a new paginator is built for each request, so every page view pays again. ## Confirming it - Log SQL in development, or use a query profiler, and look at the two statements and their timings. - Run the count on its own in a shell: `AuditEntry.objects.filter(...).count()`. - Check whether the template also calls things that read `count` (page totals, `get_elided_page_range()`); they reuse the cached value, so they add no queries. ## Fixes, from least to most invasive | Option | What changes | Trade-off | |---|---|---| | Filter by default (last 7 days, one actor) | fewer rows to count | users must widen filters explicitly | | Capped count | count at most N+1 rows, show "10,000+" | no exact total, last pages unreachable by number | | Cached count | store the total for a few minutes | total can be slightly stale | | No count at all | only "newer"/"older" links | no "page X of Y" | **Capped count.** Subclass `Paginator` and override `count` with a `cached_property` returning `self.object_list[: cap + 1].count()`. Django counts a sliced QuerySet through a subquery, so the database stops producing rows after the cap. Show "more than 10,000" when the result exceeds the cap. Set the subclass on the view with `paginator_class`. **Cached count.** Override `count` to read the total from Django's cache under a key derived from the filters, falling back to `QuerySet.count()` on a miss. Good for dashboards where a total that is a minute old is acceptable. **No count.** Fetch one row more than the page size to know whether an "older" link is needed, and link by position rather than page number. Django ships no count-free paginator, so this is custom code; at that point keyset (seek) pagination is usually the better design, because deep `OFFSET`s on two million rows are slow for the same reason the count is. The keyset technique itself is a database and API design topic. ## Things that do not help - Caching the `Paginator` object between requests: it holds a QuerySet and request state; cache the number, not the object. - Replacing the QuerySet with `list(queryset)`: `count` falls back to `len()`, but only after loading two million rows into memory. - Removing `paginate_by`: it renders every row, which is worse. ## The senior answer Measure first, then decide what the user actually needs. An audit screen is usually searched by actor and time window, not browsed to page 38,000. Default filters plus a capped count keep "page X of Y" for realistic result sets, and a keyset-based "older" link covers the rare full scroll.

  • Why does page 1 need the count at all?
    `Paginator.page()` validates the requested number against `num_pages` before slicing, and `num_pages` is derived from `count`. So even the first page triggers the `COUNT(*)`, and so does any template use of `num_pages` or `page_range`.
  • What changes on page 30,000 of the same list?
    The count costs the same, but the page query now uses a large `OFFSET`, which makes the database walk past 1.5 million rows before returning 50. Both costs point to the same fix for deep browsing: filter the list, or switch that path to keyset pagination.

saying these in an interview costs you the question

  • Page 1 is cheap because Paginator skips the count for the first page
  • Paginator.count is cached across requests
  • Converting the QuerySet to a list makes counting free
  • The slow part is always the LIMIT query, never the count
  • Removing paginate_by fixes the slowness