Offset pagination lies to your users

Here is a bug report I have now received on three separate products, phrased three different ways:

"Sometimes the same order shows up twice when I scroll."

"I know there was a request from this morning and I cannot find it in the list."

"The list gets slower the further down I go."

All three are the same bug, and it is not in the app. It is LIMIT ... OFFSET ....

What offset actually means

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 40;

You read that as "give me the third page". The database reads it as: sort the whole matching set, walk forward 40 rows, discard them, then return the next 20.

That definition contains the bug. The offset is a position in a result set that is recomputed on every request. If the set changes between requests, position 40 is no longer the same row.

Concretely. A user loads page one — the twenty newest orders. While they read it, two new orders come in. They scroll, and the app asks for OFFSET 20. But those two new rows have pushed everything down by two, so rows 19 and 20 of the original page one are now at positions 21 and 22 — and they come back again on page two. Duplicates.

Delete two rows instead of inserting them and the same mechanism skips two orders that the user never sees. That one is worse, because nobody reports a bug about something they did not notice was missing. It just quietly erodes trust.

And it gets slower

The second complaint is real too. OFFSET 40 makes the database produce and throw away 40 rows. OFFSET 10000 makes it produce and throw away 10,000. The work is linear in the offset, so the deeper the user scrolls, the slower every page gets — even though each page returns the same 20 rows.

An index does not save you. It makes finding the sorted order cheap; it does not let the database skip ahead to the n-th entry for free.

Use the last row you saw as the cursor

Instead of "start at position 40", say "start after this specific row". The row is a stable anchor; the position is not.

SELECT * FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Two things make this work:

The tuple comparison. (created_at, id) < (a, b) is row-value syntax: it compares created_at first and falls back to id only on a tie. Postgres can satisfy it directly from an index on (created_at DESC, id DESC), so the query jumps to the right place instead of counting.

The id tiebreaker. created_at is not unique — two rows inserted in the same millisecond are common under load. Without a unique tiebreaker in both the ORDER BY and the WHERE, rows tied on timestamp can be returned twice or skipped, and you have reinvented the original bug in a more confusing form.

Now inserts and deletes elsewhere in the table cannot move your anchor. New rows appear above the window, which is exactly what a user expects, and the cost of every page is the same.

In Django REST

You mostly do not have to write this yourself:

class OrderCursorPagination(CursorPagination):
    page_size = 20
    ordering = ("-created_at", "-id")
    cursor_query_param = "cursor"

The one thing to get right is ordering. Include the unique tiebreaker, and make sure a matching index exists:

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

The response carries opaque next and previous URLs. Treat them as opaque — do not parse the cursor to compute a page number. That is the whole point.

In the Flutter client

The client-side change is mostly a deletion. You stop tracking a page number:

class OrderFeed {
  final _items = <Order>[];
  String? _nextCursor;
  bool _exhausted = false;
 
  Future<void> loadMore() async {
    if (_exhausted) return;
 
    final page = await api.orders(cursor: _nextCursor);
    _items.addAll(page.results);
    _nextCursor = page.next;
    _exhausted = page.next == null;
  }
 
  /// Pull-to-refresh starts a new window from the top.
  Future<void> refresh() async {
    _nextCursor = null;
    _exhausted = false;
    _items.clear();
    await loadMore();
  }
}

Note that refresh() throws the cursor away. That is correct: a refresh means "show me the current top of the list", which is a new window, not a continuation of the old one.

When offset is still fine

This is not a rule against OFFSET. It is fine when:

• the data does not change while the user is paging (a finished report, an archive);
• you genuinely need to jump to page 7 of 12, which cursors cannot do;
• the table is small enough that the linear cost never matters.

But for anything that behaves like a feed — orders, messages, notifications, products, anything with an infinite scroll attached — the set is moving under your users. Anchor on a row, not on a position.

The reason this bug survives so long is that it is invisible in development. Your seed data does not change while you scroll. It only appears once real people are producing rows at the same time other people are reading them, and by then it arrives as "the list feels buggy" rather than as a stack trace.