Pagination

Every list endpoint starts the same way:

SELECT * FROM posts ORDER BY created_at DESC;

With 200 rows this is fine. With two million it is a catastrophe: the database reads the entire table, your server buffers it all in memory, and the client waits thirty seconds for a response it will render 20 items of.

So you paginate. The obvious approach has two problems that only appear at scale, and most engineers never learn about either until production teaches them.

The obvious approach: offset

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

"Skip 40 rows, give me the next 20." It maps perfectly onto page numbers: page 3 with a page size of 20 means OFFSET 40. Every ORM supports it, and it powers the numbered page links users recognise.

Problem one: it gets slower the deeper you go

Here is the part that surprises people. OFFSET 40 does not let the database jump to row 41. There is no way to know where row 41 is without counting.

So the database reads and discards rows 1 to 40, then returns the next 20.

Now consider OFFSET 1000000. The database reads a million rows, throws every one away, and returns twenty. The work grows linearly with page depth:

Page one is instant. Page 50,000 may time out. And this is not hypothetical — crawlers and scrapers walk deep into paginated lists routinely, so your slowest queries get requested constantly by the traffic you care least about.

Problem two: it silently skips rows

This one is worse, because it corrupts results rather than merely being slow.

A user reads page 1 (posts 1–20). While they read, three new posts are created. Every existing post shifts three positions down. The user clicks "next page" and gets OFFSET 20.

Posts that were at positions 18, 19 and 20 have moved to 21, 22 and 23 — so they appear again on page 2. Meanwhile, on a list sorted the other way, rows can be skipped entirely and the user never learns they existed.

On a changing dataset, offset pagination does not give a stable view. For a blog archive that is a curiosity. For a payments export or an infinite-scroll feed, it is a bug that produces duplicated or missing records.

The better approach: cursor pagination

Instead of "skip N rows", say "give me what comes after this specific row".

SELECT * FROM posts
WHERE created_at < '2024-06-10 12:00:00'
ORDER BY created_at DESC
LIMIT 20;

The client sends back the last value it saw — the cursor — and the database jumps straight there using the index. No counting, no discarding.

Both problems vanish:

• Constant time. With an index on created_at, finding the starting point is a seek, not a scan. Page 50,000 costs the same as page 1.
• Stable results. New posts inserted at the top do not shift the position of the row you anchored to, so nothing duplicates or disappears.

Making the cursor correct

A naive cursor breaks when two rows share the same timestamp — you either skip or repeat the tied rows. Make the cursor unique by adding a tiebreaker:

SELECT * FROM posts
WHERE (created_at, id) < ('2024-06-10 12:00:00', 8842)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The sort key must be unique and match the index, otherwise ordering is ambiguous and pagination is unreliable.

In the API, encode the cursor as an opaque string so clients cannot construct their own and do not depend on its internals:

{
  "data": [ ... ],
  "nextCursor": "eyJjcmVhdGVkQXQiOiIyMDI0LTA2LTEwVDEyOjAwOjAwWiIsImlkIjo4ODQyfQ=="
}

Choosing between them

Use offset for admin tables and back-office screens where users genuinely want numbered pages, datasets are small, and data changes slowly.

Use cursors for infinite scroll, feeds, public APIs, exports, and anything where the dataset is large or actively changing.

Details that matter in practice

Always cap the page size. If a client sends ?limit=1000000, you are back to the original problem. Clamp it: const limit = Math.min(Number(req.query.limit) || 20, 100).

Always define a stable sort. Without ORDER BY, databases may return rows in any order, and pagination becomes meaningless. Include a unique tiebreaker.

Total counts are expensive. SELECT COUNT(*) on a large table scans it. If you need "340 pages", consider caching the count, showing an approximation, or simply returning hasMore: true — which is all infinite scroll actually needs.

Index the sort column. Cursor pagination without an index on the ordering column still scans the table, and you have gained nothing.

Check your understanding

Common mistakes

Returning everything and paginating in application code. Fetching a million rows to slice twenty in JavaScript moves the cost to your server. Paginate in the query.

Assuming offset is fine because the table is small today. Tables grow. The endpoint that scans 800 rows becomes the one scanning 8 million, usually on a Friday.

Cursors over a mutable sort key. If you paginate by updated_at and rows are being updated, the anchor moves under you. Cursor keys should be immutable — creation time plus id is a safe default.

Forgetting the last page. Decide what nextCursor is when there are no more results — null is clearer than an empty string, and clients must handle it.

Practice

Key takeaways

• OFFSET reads and discards every row it skips, so query cost grows linearly with page depth. Deep pages get slow, and crawlers request them constantly.
• On changing data, offset pagination duplicates or skips rows, because it counts positions rather than identities.
• Cursor pagination anchors to a specific row, giving constant-time seeks and a stable view regardless of inserts.
• Cursor keys must be unique, immutable and indexed — typically a timestamp plus a tiebreaking id.
• Use offset for numbered admin tables, cursors for feeds, exports and public APIs — and consider capping offset depth.
• Always clamp the page size and define an explicit ORDER BY, or pagination is meaningless.

Further reading

• Markus Winand, SQL Performance Explained (2012) — keyset pagination and why OFFSET degrades.
• Markus Winand, "We need tool support for keyset pagination" (2013).
• RFC 8288, Web Linking — the Link header for next and previous page relations.
• Facebook, "Cursor-based Pagination" (Graph API documentation) — opaque cursors in a public API.
• Joe Celko, SQL for Smarties, fifth edition (Morgan Kaufmann, 2014) — ordering, ties and stable sorts.