Cursor Pagination: Why OFFSET Gets Slower and Skips Rows

OFFSET pagination degrades as tables grow and silently skips or repeats rows. Here's how cursor-based pagination fixes both problems.

Every paginated API starts the same way: LIMIT 20 OFFSET 40. It works in the demo, it works in staging, and it works in production — for a while. Then the table hits a few million rows, someone requests page 5,000, and your database spends hundreds of milliseconds scanning rows it's about to throw away. Worse, users quietly see duplicate or missing items and nobody can reproduce it.

The two problems with OFFSET

It doesn't scale. OFFSET 100000 doesn't jump to row 100,000 — the database still has to walk past every row before it, discard them, and then return your 20. The deeper the page, the more work per request. Cost grows linearly with page depth, which means your slowest queries are also your most wasteful ones.

It's not stable. OFFSET counts positions, not rows. If a new record is inserted while a user is paging through results, everything shifts: the last item of page 3 reappears at the top of page 4, or a row slides into a position the user already passed and is never seen. On an append-heavy feed — orders, messages, transactions — this isn't an edge case. It's constant.

For a dashboard with ten pages of data, OFFSET is fine. For an API that external systems sync against, it's a correctness bug waiting to be filed.

How cursor pagination works

Instead of asking for "page N," the client asks for "rows after this point." The cursor is a value from the last row of the previous page — typically the primary key, or a (created_at, id) pair when you sort by time:

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

With an index on (created_at, id), the database seeks directly to the cursor position and reads 20 rows. Page 1 and page 50,000 cost the same. And because the cursor anchors to an actual row rather than a position, inserts and deletes elsewhere in the table can't cause skips or duplicates.

Practical rules that keep it working

• Always include a unique tiebreaker. Sorting by created_at alone breaks when two rows share a timestamp. Append id to both the ORDER BY and the cursor comparison.
• Make the cursor opaque. Encode it (base64 of the key values is enough) so clients treat it as a token, not a contract. You can change the underlying columns later without breaking anyone.
• Return the cursor explicitly. Respond with next_cursor and a has_more flag instead of expecting clients to extract values from the last item.
• Match the index to the sort. The whole performance win depends on the composite index covering your exact ORDER BY. Verify with EXPLAIN, not hope.

What you give up

Cursor pagination can't jump to an arbitrary page number, and "page 7 of 42" style UIs don't map onto it. That trade is usually worth making: most real usage is "next, next, next" — infinite scroll, data exports, sync jobs — and those are exactly the workloads OFFSET handles worst. If you genuinely need numbered pages, keep OFFSET for the first few pages of a small, human-facing list and use cursors everywhere depth or correctness matters.

The takeaway: OFFSET is a prototype default, not an architecture. If an endpoint will ever be synced against, scrolled deeply, or hit by another machine, give it a cursor from day one — retrofitting one after clients depend on page numbers is far more painful.