What you'll learn

  1. Quick answer
  2. The two approaches
  3. The gotcha, with a real table
  4. What each one actually costs you
  5. When to pick which
  6. FAQ

Quick Answer

Offset pagination (LIMIT x OFFSET y) is simple and lets you jump to any page number, but it recomputes position from scratch on every request, so a row inserted or deleted between two page fetches shifts everything and causes rows to repeat or vanish. Cursor pagination (WHERE id > last_seen_id LIMIT x) anchors each page to the last row actually seen, so it stays correct under concurrent writes but can't jump to an arbitrary page. Use offset for small, mostly-static tables where page numbers matter, like an admin list; use cursors for anything that changes while it's being read, like a public feed or infinite scroll.

The two approaches

Offset pagination asks the database for a slice by position: skip this many rows, then take this many.

SELECT id, title FROM articles
ORDER BY id DESC
LIMIT 10 OFFSET 20;  -- page 3, page size 10

It's simple to implement and it maps directly onto page numbers: page n is OFFSET (n-1) * pageSize. Any client can request page 7 directly without having fetched pages 1 through 6 first, which is exactly why it's the default choice in most tutorials and admin panels.

Cursor pagination asks instead for everything after a specific row you've already seen:

SELECT id, title FROM articles
WHERE id < 41   -- last id seen on the previous page
ORDER BY id DESC
LIMIT 10;

There's no page number — just "give me the next 10 after this point." The client stores the last row's sort key (here, its id) and sends it back as the cursor for the next request.

Mechanically these look similar. The difference only shows up once the underlying data changes between two page fetches — which, for anything with real traffic, is constantly.

The gotcha, with a real table

Fifty rows in SQLite, ordered newest-first — a typical "latest articles" feed. Fetch page 1, then simulate a new article getting published before fetching page 2:

Page 1 (OFFSET 0 LIMIT 10): [50, 49, 48, 47, 46, 45, 44, 43, 42, 41]

[EVENT] A new article (id=51) was published between the two page fetches.

Page 2 (OFFSET 10 LIMIT 10): [41, 40, 39, 38, 37, 36, 35, 34, 33, 32]

Id 41 appears on both pages. The new row pushed every existing row down one slot, so the OFFSET 10 window landed one row earlier than intended and re-captured the last row of page 1.

Same scenario, cursor pagination:

Page 1 (id DESC, LIMIT 10): [50, 49, 48, 47, 46, 45, 44, 43, 42, 41]

[EVENT] The same new article (id=51) was published.

Page 2 (WHERE id < 41 LIMIT 10): [40, 39, 38, 37, 36, 35, 34, 33, 32, 31]

No overlap. The cursor query is anchored to id 41 specifically, so the new row (id 51, sorting above it) has no effect on where page 2 starts.

What each one actually costs you

Offset gets slower on deep pages. OFFSET 100000 still requires the database to scan and discard the first 100,000 matching rows before it can return anything — the cost grows with how deep you page, even though you only ever see 10 rows at a time. A cursor query with an index on the sort column seeks directly to the right spot regardless of depth, because it's a WHERE condition, not a row count to skip.

Cursor gives up page numbers. There's no way to jump straight to "page 47" with a cursor — you can only move forward (or backward) from a known position. If your UI needs numbered pages or a "go to page" box, that's a real constraint, not a minor one.

Total counts have the same problem on both sides, but for different reasons: offset pagination can compute one cheaply enough for small tables, while a genuinely large or fast-changing table makes any "142 results total" number stale almost as soon as it's shown, whichever pagination style produced it.

Cursor needs a strict, indexed ordering. The sort column (or combination — commonly created_at plus id as a tiebreaker) has to be unique and stable, or rows can still be missed. The cursor itself is usually an opaque encoded token, not a raw id, to keep the sort key private and the API resilient to internal changes.

When to pick which

Pick offset when: it's an internal admin table or report, the data is small or changes rarely while someone is browsing it, and you genuinely need page numbers or a total count ("142 results, page 3 of 15").

Pick cursor when: it's a public feed, an infinite-scroll UI, a mobile app fetching the next batch, or any API where external clients are paging through data that keeps changing — new rows arriving is the normal case, not the edge case, and duplicate or missing rows in someone else's integration become a support ticket.

The honest tradeoff: cursor pagination is more work to build correctly — you need a stable sort key, an encoded cursor, and a different mental model than "page number" — and you lose total counts and arbitrary page jumps without adding a separate query for them. It's worth that cost exactly when correctness under concurrent writes matters more than UI convenience, which for anything at real scale, it usually does.

A reasonable default for a new API: start with cursor pagination on any endpoint that returns a list a client might page through more than once, and reserve offset for the handful of internal screens where someone genuinely needs to type a page number and jump straight there.

Frequently Asked Questions

Which is faster, offset or cursor pagination? For early pages they're about the same. For deep pages, cursor pagination is faster because it seeks via an indexed WHERE clause instead of scanning and discarding rows to satisfy an OFFSET.
Can I get a total page count with cursor pagination? Not from the same query. You'd need a separate COUNT(*) query, and on a large or fast-changing table that count can be stale by the time it's shown.
What column should the cursor be based on? A column, or combination of columns, that's unique and strictly ordered — a primary key, or a timestamp plus id as a tiebreaker when timestamps can collide.
Does GraphQL's Relay-style pagination use cursors? Yes. The Relay connection spec (edges, node, cursor, pageInfo) is a standardized cursor pagination pattern, for exactly the reasons covered here.
Can I combine both offset and cursor approaches? Some APIs offer offset for small, shallow browsing and switch to cursor-only past a certain depth, but running both against the same endpoint adds real complexity — most teams pick one based on the use case above.