Web development

Cursor pagination: why offset skips rows

On 200,000 rows offset costs 5.063 ms and keyset 0.010 ms, but the real problem is that offset skips a row when data changes.

Key points

  • On 200,000 indexed rows, a page at offset 180,000 costs 5.063 ms; the same page with keyset costs 0.010 ms.
  • Deleting the first row after reading page 1 makes offset page 2 skip a row; a cursor skips nothing.
  • The cursor must hold the last ordering key, with a tiebreaker that makes the order total.
  • The client must not infer the end from page length: serve next_cursor and has_more.

Cursor pagination reads the next page starting from the last row seen, instead of counting rows to skip. On 200,000 indexed rows, SQLite 3.51.3 takes 5.063 ms for a page at offset 180,000 and 0.010 ms for the same page with keyset: offset counts, a cursor seeks.

Why offset ages badly

OFFSET 180000 asks the database to produce and discard 180,000 rows: cost grows with depth, not with page size. Worse, the row number does not mark a stable point: if someone deletes a row before the current page, the next page starts one row later and skips one. If someone inserts one, the next page repeats a row already seen. Users notice this as missing content, not as slowness.

A cursor is a key, not a page number

The cursor holds the last ordering key, for example created_at plus id as a tiebreaker. The query becomes an index seek: WHERE (created_at, id) > (?, ?) with the same ORDER BY. For that to work, the query order and the index order must match and the key must be unique: without the tiebreaker two rows with the same timestamp produce an unstable order, with duplicates or gaps.

The numbers, and what they say

The measured ratio is about 500 times on the second-to-last page and over 600 on the last one, on a 14 MB database that fits entirely in memory. On a larger table and a real disk the gap widens, because offset reads pages it then discards. A cursor touches only the range it needs, and its cost stays almost flat with depth.

EXPLAIN QUERY PLAN SELECT id, created_at FROM events WHERE (created_at, id) > ('2026-01-03T04:00:00.000Z', 180000) ORDER BY created_at, id LIMIT 20;
SELECT id, created_at FROM events WHERE (created_at, id) > ('2026-01-03T04:00:00.000Z', 180000) ORDER BY created_at, id LIMIT 20;

What the API returns

The response carries the data, an opaque next_cursor and an explicit has_more. The client must not infer the end from page length: a full page can be the last one, and a short page may not be, if a filter removed rows. Validate the cursor: if it does not decode or does not match the requested ordering, reply 400 instead of returning an arbitrary page. Cap the limit parameter too, or the cursor becomes a way to request the whole table.

The limit: no jumping to page 57

With a cursor you cannot ask for page 57 directly, because there is no starting point you can compute without knowing the data. For an admin panel with a few thousand rows and visible page numbers, offset is still the simpler choice; for a feed, an order list or an export, a cursor is the only option that survives growth. A signed cursor stops the client from building arbitrary keys, and a cursor carrying a timestamp should be rejected when it lies in the future.

Where to start

    Frequently asked questions

    What does an opaque cursor contain?

    The last ordering key, usually base64 encoded and signed. The client must not interpret it, only send it back; the server decodes it, checks the signature and rebuilds the query.

    Can I also return the total row count?

    Yes, but it is a second query, and on large tables it costs as much as offset. If you need a counter, compute it approximately or cache it; the normal flow needs only next_cursor and has_more.

    How do I go back to the previous page?

    Keep the cursor of each visited page and reuse it, or run the same query with the order reversed and an initial cursor. Do not recompute an offset: that is the thing you were leaving behind.

    What happens if data changes while I paginate?

    The cursor stays fixed at the point already read; new rows appear only if they sort after that point. Deleted rows disappear and are not replaced by others, which is the predictable behaviour.

    Is offset fine for an admin panel?

    Yes when rows are few and page numbers matter. With a few thousand rows the 5 ms gap is irrelevant and direct jumps are worth more than stability; above that threshold the cursor wins on both counts.