All notes
Programming

OFFSET 200,000: why page 10,000 is slow and how keyset pagination fixes it

OFFSET still reads every row it skips and shifts your pages whenever new rows arrive. Keyset pagination on (created_at, id), base64 cursors, the real cost of COUNT(*), and a complete Django implementation.

There's an orders list in the admin panel: newest first, 20 per page. The first pages open instantly. Then someone hunting for an old order types ?page=10000 into the URL, and the page noticeably stalls. Meanwhile, in the mobile app, a user taps "Load more" and sees an order they just scrolled past: while they were reading, a new order arrived and everything shifted down by one.

Both problems have the same root: OFFSET. Let's look at why it degrades, how keyset (cursor) pagination works, how to encode the cursor, what COUNT(*) really costs, and how to build all of it in Django.

How OFFSET works and why it slows down

Django's Paginator and slices like qs[200000:200020] end up as SQL like this:

SELECT id, customer_id, amount, created_at
FROM shop_order
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 200000;

It looks as if PostgreSQL "jumps" straight to row 200,001. It doesn't. A B-tree index has no notion of "the N-th entry": it's an ordered tree you can search by key, not by position. So the executor pulls rows in order, throws away the first 200,000 and returns the next 20. The PostgreSQL docs say it plainly: rows skipped by OFFSET still have to be computed inside the server, so a large OFFSET can be inefficient.

You can see it yourself with EXPLAIN ANALYZE. Timing depends on your hardware, so I've left it out; the number to look at is actual rows:

psql -c "EXPLAIN ANALYZE SELECT * FROM shop_order ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 200000;"

 Limit  (... rows=20 ...) (actual time=... rows=20 loops=1)
   ->  Index Scan using order_created_id_desc on shop_order
         (... rows=302000 ...) (actual time=... rows=200020 loops=1)

Limit returned 20 rows, but the Index Scan beneath it read 200,020. Work grows linearly with the page number: page 10 is cheap, page 10,000 is not. And if there is no index matching the ORDER BY, it gets worse: PostgreSQL has to read the table and sort it.

The second problem: pages drift

Speed aside, OFFSET results aren't stable. A user views page 1 (rows 1–20). Three new orders arrive. They request page 2: OFFSET 20. Positions 21–40 now hold what used to be rows 18–37, so three orders show up twice. If a row is deleted instead, one row is never shown at all.

In an admin panel that's an annoyance. In an infinite-scroll feed, or worse, a background job that exports or processes data page by page, it means duplicated and skipped records.

Keyset pagination: "continue from where I stopped"

The idea is simple: instead of "skip N rows", say "give me the rows after the last one I saw". The last row is identified by its sort key value. The cleanest way to pass it is PostgreSQL's row comparison:

-- first page
SELECT id, customer_id, amount, created_at
FROM shop_order
ORDER BY created_at DESC, id DESC
LIMIT 21;

-- next page: last row of the previous page was
-- (created_at = '2026-11-03 18:42:07.512301+05', id = 918273)
SELECT id, customer_id, amount, created_at
FROM shop_order
WHERE (created_at, id) < ('2026-11-03 18:42:07.512301+05', 918273)
ORDER BY created_at DESC, id DESC
LIMIT 21;

(created_at, id) < (a, b) compares lexicographically: first created_at < a, and on a tie, id < b. That is exactly the order of ORDER BY created_at DESC, id DESC.

Now the important part. With a B-tree index on (created_at, id), PostgreSQL uses that predicate as an Index Cond: it descends the tree to the right spot and reads 21 rows from there. Nothing is thrown away:

 Limit  (... rows=21 ...) (actual time=... rows=21 loops=1)
   ->  Index Scan using order_created_id_desc on shop_order
         (actual time=... rows=21 loops=1)
         Index Cond: (ROW(created_at, id) < ROW('2026-11-03 18:42:07.512301+05'::timestamptz, 918273))

Page 1 and "page 10,000" now cost the same: one descent of the tree plus reading LIMIT rows. New rows don't shift anything, because the condition says "after this row", not "after position 20".

OFFSET remembers how many rows to skip; keyset remembers where it stopped. The first breaks when the data changes, the second doesn't.

The one extra row (LIMIT 21) tells you whether there is a next page: if row 21 comes back, there is one, and you don't show that row to the user.

Why you need a tiebreaker

Paginating on created_at alone is tempting, and wrong. created_at isn't unique: bulk imports, several rows created in one transaction (now() returns the transaction start time), or just colliding timestamps. If a page boundary falls between two rows with the same created_at:

  • WHERE created_at < :last loses the second one;
  • WHERE created_at <= :last repeats the first one.

The fix is to make the order total by appending a unique column, usually the primary key: ORDER BY created_at DESC, id DESC. Every row then has exactly one position, and the cursor points to exactly one spot.

Implementing it in Django

The model and index. Index columns must follow the ORDER BY order. PostgreSQL can scan a B-tree backwards, so a plain (created_at, id) index also works; what matters is that both columns go in the same direction.

# shop/models.py
from django.db import models


class Order(models.Model):
    customer_id = models.BigIntegerField()
    amount = models.DecimalField(max_digits=12, decimal_places=2)
    created_at = models.DateTimeField()  # NOT NULL by default

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

Now the cursor. There's no reason to expose internals to the client: put created_at and id into JSON and encode it with URL-safe base64. To the client it's just an opaque string to send back with the next request.

# shop/cursors.py
import base64
import json
from datetime import datetime


class InvalidCursor(ValueError):
    pass


def encode_cursor(created_at: datetime, pk: int) -> str:
    raw = json.dumps({"c": created_at.isoformat(), "i": pk}, separators=(",", ":"))
    return base64.urlsafe_b64encode(raw.encode()).decode().rstrip("=")


def decode_cursor(cursor: str) -> tuple[datetime, int]:
    try:
        padded = cursor + "=" * (-len(cursor) % 4)
        data = json.loads(base64.urlsafe_b64decode(padded))
        return datetime.fromisoformat(data["c"]), int(data["i"])
    except (ValueError, KeyError, TypeError) as exc:
        raise InvalidCursor("bad cursor") from exc

isoformat() keeps microseconds. That matters: PostgreSQL stores timestamptz with microsecond precision, and if the cursor carries a timestamp rounded to milliseconds, rows at the boundary get duplicated or lost. It's also why clients must not build cursors themselves: a JavaScript Date only knows milliseconds.

The page function. Django's ORM has no public API for row comparison, so we write the equivalent condition with Q:

# shop/pagination.py
from django.db.models import Q

from .cursors import decode_cursor, encode_cursor
from .models import Order


def orders_page(cursor: str | None = None, limit: int = 20):
    qs = Order.objects.order_by("-created_at", "-id")
    if cursor:
        created_at, pk = decode_cursor(cursor)
        qs = qs.filter(
            Q(created_at__lt=created_at) | Q(created_at=created_at, id__lt=pk),
            created_at__lte=created_at,  # redundant, but gives the planner an index condition
        )
    rows = list(qs[: limit + 1])  # one extra row tells us whether a next page exists
    has_next = len(rows) > limit
    rows = rows[:limit]
    next_cursor = encode_cursor(rows[-1].created_at, rows[-1].pk) if has_next else None
    return rows, next_cursor

created_at__lte=created_at is logically redundant, since both OR branches imply it. But it hands the planner a plain range condition that PostgreSQL uses as the Index Cond, applying the OR as a filter on the few rows it reads. With just the OR, the planner may not find a good plan, so check EXPLAIN on your own data.

If you want the actual row comparison, you can pass RawSQL to filter() as a boolean expression. You have to spell out the table name by hand here, so keep that in mind:

from django.db.models import BooleanField
from django.db.models.expressions import RawSQL

qs = qs.filter(
    RawSQL(
        '("shop_order"."created_at", "shop_order"."id") < (%s, %s)',
        (created_at, pk),
        output_field=BooleanField(),
    )
)

In the view, an invalid cursor should be a 400, not a 500, and limit must be capped:

# shop/views.py
from django.http import JsonResponse

from .cursors import InvalidCursor
from .pagination import orders_page


def order_list(request):
    try:
        limit = max(1, min(int(request.GET.get("limit", 20)), 100))
    except ValueError:
        limit = 20
    try:
        rows, next_cursor = orders_page(request.GET.get("cursor"), limit)
    except InvalidCursor:
        return JsonResponse({"error": "invalid cursor"}, status=400)
    return JsonResponse({
        "results": [
            {"id": o.pk, "amount": str(o.amount), "created_at": o.created_at.isoformat()}
            for o in rows
        ],
        "next_cursor": next_cursor,
    })

If you use DRF, it ships a CursorPagination class built on the same idea, with the same requirement: the ordering field should be immutable and as close to unique as possible.

Total count: what "Total: 4,812,337" costs

Django's Paginator calls count() to work out the number of pages, so every request runs SELECT COUNT(*). PostgreSQL doesn't keep a ready-made row count for a table. The reason is MVCC: each transaction sees its own snapshot, and at the same instant two transactions can disagree on how many rows exist. So an exact COUNT(*) counts every visible row. An index-only scan can help, but it still walks every index entry. If you speed up the page with keyset and keep COUNT(*) next to it, you've just moved the slow part.

The options:

Approach Accuracy Cost Good for
COUNT(*) exact reads every matching row small tables or selective filters
pg_class.reltuples estimate almost free whole table, no filters
EXPLAIN estimate estimate, sometimes far off almost free filtered queries, showing "~12k"
No count at all — free infinite scroll, APIs
-- exact: reads every visible row
SELECT count(*) FROM shop_order;

-- estimate for the whole table: updated by VACUUM / ANALYZE
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE oid = 'shop_order'::regclass;

reltuples is updated by VACUUM, ANALYZE (including autovacuum) and CREATE INDEX. If the table has never been analyzed, PostgreSQL 14 and later report -1, so handle that in code. For a filtered query, you can get the planner's estimate straight from Django:

import json


def estimate_count(qs) -> int:
    plan = json.loads(qs.order_by().explain(format="json"))
    return int(plan[0]["Plan"]["Plan Rows"])


# usage
qs = Order.objects.filter(customer_id=42)
approx = estimate_count(qs)

This estimate comes from statistics and can be off by a large factor on complex conditions. Show it in the UI as "about 12,000", never as an exact number. Most of the time the right answer is to not count at all: "there is a next page" comes free with LIMIT + 1.

Production pitfalls

  • No jumping to page N. Keyset gives you next/previous only. When the business asks for "go to page 47", what they usually need is a date or ID filter: "jump to March 2025" is just WHERE created_at < '2025-04-01'.
  • Previous page. Flip the comparison (>), flip the ordering (ORDER BY created_at, id), fetch LIMIT + 1, then reverse the result in Python. Storing the direction in the cursor makes this easy.
  • Mixed directions. For ORDER BY created_at DESC, id ASC, (created_at, id) < (...) doesn't work: row comparison goes in one direction for both columns. Write the OR condition by hand and create the index as (created_at DESC, id ASC).
  • NULLs. A comparison with NULL yields NULL, so those rows fail the condition and silently drop out of pagination. Make sort columns NOT NULL, or build an expression index with COALESCE.
  • Sorting by a mutable field. Paginate by updated_at or score and a row can move while the user is scrolling: it shows up on two pages or on none. Keyset still beats OFFSET here, but it isn't perfect.
  • Filters and indexes. Add WHERE customer_id = 42 and the index needs to be (customer_id, created_at, id). Otherwise PostgreSQL reads other customers' rows and discards them in a filter, and you're back to the OFFSET problem.
  • Don't trust the cursor. Base64 isn't encryption; anyone can decode and tamper with it. That's usually fine, because the cursor is only a position and permission checks belong in the queryset. If you put filters into the cursor, sign it with django.core.signing.
  • Background jobs. To process a big table in chunks, don't use OFFSET; use WHERE id > :last_id ORDER BY id LIMIT 1000, the simplest form of keyset.

Quick checklist

  • Does the ORDER BY end with a unique column (usually id)?
  • Is there an index on the ORDER BY columns in the same order and direction? Are filter columns at the front of the index?
  • In EXPLAIN ANALYZE, does the condition show up as Index Cond, with actual rows close to LIMIT?
  • The cursor is opaque (base64), keeps microseconds, and an invalid one returns 400.
  • limit has an upper bound; the next page is detected with LIMIT + 1.
  • Sort columns are NOT NULL.
  • On big tables, an estimate or no count instead of an exact COUNT(*).

Takeaway

OFFSET is fine for small tables and the first few pages, but it pays for every skipped row and shifts pages whenever the data changes. Keyset pagination, with a tiebreaker that makes the order total and a matching index, serves any page at the same cost. Keep the cursor opaque, and use COUNT(*) only where you truly need it, ideally as an estimate.

// comments

Loading comments...

    // next step

    Let's build something together.

    Message me on Telegram
    aslbek@asldev: ~
    ❯