API Design

Pagination Strategies: Offset vs Cursor-Based Pagination

OFFSET pagination is the first thing everyone reaches for, and it works fine right up until page 400, or until a row gets inserted between two requests and quietly duplicates or skips a result.

Published July 6, 2026

Both approaches solve the same problem — return results in manageable chunks instead of one giant response — but they behave very differently once the dataset gets large or changes between requests.

Offset pagination

This is the familiar page-number approach: page 1 is rows 0–19, page 2 is rows 20–39, and so on.

SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;  -- page 3, 20 per page

It's trivial to implement and lets users jump to an arbitrary page number, which is genuinely useful for things like an admin table with a page-number widget. The problem is what OFFSET actually does under the hood: the database still has to scan and discard the first 40 rows before it can return rows 41–60. OFFSET 40 is cheap. OFFSET 400,000 means scanning and throwing away 400,000 rows on every single request, and that cost grows linearly with page number — page 10,000 is dramatically slower than page 10, even though both return the same 20 rows.

The consistency bug nobody notices until it happens

OFFSET pagination assumes the underlying result set doesn't change between page requests. If a new row is inserted at the top of the ordering (a new article published, sorted by created_at DESC) between a user loading page 1 and page 2, everything shifts by one position. Page 2 now starts at what used to be row 39, and the row that used to be last on page 1 shows up again as the first row of page 2 — a duplicate the user will notice, especially on a feed that updates frequently. Delete a row instead of inserting one, and you get the opposite bug: a row silently skipped, never shown on any page.

Cursor-based (keyset) pagination

Instead of counting rows to skip, cursor pagination remembers a value from the last row you saw and asks the database for rows strictly after it:

-- first page
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- next page: cursor = (created_at, id) of the last row from the previous page
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ('2026-06-30 14:02:00', 8841)
ORDER BY created_at DESC, id DESC
LIMIT 20;

This query can use an index on (created_at, id) to jump directly to the right starting point rather than scanning and discarding prior rows, so performance stays flat regardless of how deep into the dataset you page. It's also immune to the insert/delete bug above: since the query is "give me rows after this specific point," a new row inserted at the top simply doesn't affect where the cursor picks back up. Notice the tie-breaker column (id) alongside created_at — without it, two rows with the identical timestamp would make the ordering ambiguous and the cursor unreliable.

What you give up with cursors

You can't jump to "page 47" directly — you can only go forward or backward from a known cursor, one page at a time (or by keeping a list of cursors you've already seen, if backward navigation matters). Total result count also becomes expensive to compute for the same reason OFFSET is expensive at depth, so UIs built around cursor pagination typically show "load more" instead of numbered pages with a total count. This is exactly the trade-off GraphQL's connection pattern formalizes, with cursors encoded as opaque strings for the client to pass back; the full shape is defined in the GraphQL Cursor Connections specification.

Which one to use

Cursor pagination is the right default for infinite-scroll feeds, API endpoints serving large or frequently-changing datasets, and anywhere performance at depth matters. Offset pagination is still fine for small, mostly-static tables, or admin UIs where jumping to an arbitrary page number by index is a feature users actually rely on and the table size never gets large enough for the scan cost to matter.