Offset vs Cursor Pagination: Which Should Your API Use?

Quick answer

• Offset asks for a position: LIMIT 20 OFFSET 10000. The database counts and discards 10,000 rows to find your page.
• Cursor asks for a position by value: “give me rows after ID 4821.” The database seeks there directly.
• Offset gets slow with depth and produces duplicates and gaps when rows are inserted or deleted. Cursor is fast and consistent.
• Use cursor for feeds, infinite scroll, and large tables. Use offset only when users need to jump to a numbered page on a small, stable dataset.

What is the difference between offset and cursor pagination?

Pagination is how an API returns a long list without returning all of it. The two common approaches differ in what they send to ask for the next slice.

Offset pagination sends a number: how many rows to skip. The database must still walk those rows to know where to start, so the cost grows with depth. Page 1 skips nothing and is instant; page 500 skips 9,980 rows and gets progressively slower until the database gives up and stops using the index at all.

Where this bites vibecoders

The specific way this bites vibecoders is a generated list endpoint that is correct at first. An assistant writes ?page=1&limit=20 with LIMIT 20 OFFSET 0 — which is the right thing, and completely fine for the fifty rows in the seed data. The offset pattern is not a bug anyone can see, so nothing prompts a change as the table grows, and by the time the list is slow the code is old enough that nobody remembers choosing it. The symptom surfaces as “the app got slow” rather than as anything recognisable.

Cursor pagination sends a marker — the unique value of the last row on the previous page, usually its primary key or the timestamp you are sorting by. The database seeks directly to that value and reads forward, so page 500 costs the same as page 1. The marker is opaque to the client and usually base64-encoded so the client cannot construct arbitrary ones.

The performance difference is not subtle. On a table with a few hundred thousand rows, a deep offset can scan the entire table and take seconds; the equivalent cursor query uses the index and returns in milliseconds. This is the same underlying mechanism as What Is Database Indexing — the offset query cannot use the index for ordering, so it sorts or scans.

How do offset and cursor pagination compare?

The rows that decide most projects are consistency and cost at depth. Offset pagination’s real defect is not slowness on page 1 — it is that the list is unstable when rows change underneath it.

Why does offset pagination skip and repeat rows?

Because an offset counts rows in the order they exist at the moment of the query, and that order shifts when data changes.

Imagine a list ordered by newest first, and you are on page 2 (OFFSET 20). If someone posts two new items, everything shifts down by two positions — so you see two items you already saw on page 1, and two items you will never see at all. If someone deletes an item, the shift goes the other way and you silently skip one.

This is why “the feed duplicated an item” and “an item vanished when I scrolled” are such common reports. Neither is a rendering bug; both are the arithmetic of counting positions in a changing list.

Cursor pagination is immune because it anchors on a value rather than a count. “Rows where id < 4821” describes the same set of rows no matter what has been added since.

How do I implement cursor pagination?

Anchor on an indexed, unique, monotonically increasing column — a primary key or a creation timestamp plus the key to break ties.

Offset, for comparison:

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

Cursor, using keyset pagination:

SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($cursor_created_at, $cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The composite comparison handles the tie case: two posts with an identical created_at would otherwise be ambiguous, and the row comparison uses the unique id as the tiebreaker. The columns in the WHERE clause and the ORDER BY clause must match, or the database cannot use the index and you lose the benefit.

The token you return to the client is the last row’s values, encoded:

{
  "data": [ /* ...20 posts... */ ],
  "next_cursor": "MjAyNi0wOS0xNFQxMDozMDowMFo=",
  "has_more": true
}

has_more is a useful optimisation: fetch limit + 1 rows and return the extra one as the flag, so you avoid a second COUNT(*) query on every page.

When should you use which?

Use cursor pagination when:

• The list is large, or grows large over time.
• The data changes while people are paging through it — feeds, activity logs, comments, order history.
• The UI is infinite scroll or a “load more” button, which only ever moves forward.
• Deep pages are reachable and you care about latency.

Use offset pagination when:

• Users need to jump to a specific numbered page — an admin table, a print view, a public archive.
• The result set is small and mostly static.
• You need a total count, which cursor makes expensive.
• A link to “page 12” has to work when shared with someone else.

A pragmatic hybrid is common and worth considering: cursor for the browsing path, plus a small set of numbered links that use offset and are capped at the first few pages. Deep jumps then degrade gracefully instead of falling over.

Where AI coding assistants get this wrong

• Defaulting to LIMIT/OFFSET on every list endpoint, which is the correct default for a prototype and a performance bug at scale.
• Ordering by a non-unique column in the cursor query, so rows with equal sort values get skipped or repeated.
• Using WHERE id < cursor_id while ordering by created_at, which cannot use the index and is slower than the offset it replaced.
• Adding ORDER BY id DESC and LIMIT to a list that needs a JOIN, producing the N+1 problem one endpoint at a time.
• Running a COUNT(*) on every page for the cursor path, which reintroduces a full table scan per request.
• No index on the sort column, so the cursor query degrades to the same scan the cursor was meant to avoid.
• Cursor and offset mixed in one endpoint without a documented contract, so clients cannot tell which parameter takes precedence.

Checklist

• Default to cursor for large or frequently-changing lists.
• Use offset only where users must jump to a numbered page.
• Anchor cursors on an indexed, unique, monotonically increasing column.
• Make the WHERE clause and the ORDER BY clause use the same columns, in the same order.
• Add a composite index covering the sort columns, e.g. (created_at DESC, id DESC).
• Break sort ties explicitly with a unique key.
• Return an opaque encoded token, not raw internal values.
• Fetch limit + 1 rows to derive has_more instead of counting on every page.
• Use keyset pagination for forward-only flows such as infinite scroll.
• If you expose numbered pages, cap the depth so deep offsets cannot be requested.

FAQ

What is the difference between offset and cursor pagination?

Offset pagination asks for rows starting at a numeric position — skip 10,000 rows, give me the next 20 — so the database must count and discard everything before that point. Cursor pagination passes the sort key of the last row you saw and asks for rows after it, so the database seeks directly to that position. One is positional and one is positional-by-value, and the difference shows up in both speed and correctness.

Why does offset pagination skip or repeat rows?

Because the offset counts rows as they are at query time. If someone inserts or deletes a row before your offset, every row shifts by one, so you see a duplicate or skip an item. Cursor pagination does not have this problem because it anchors on a value — the last row’s ID or timestamp — rather than a position that moves.

When is offset pagination actually fine?

On small, mostly-static result sets where users genuinely need to jump to a specific page — an admin table, a paginated report, a small public archive. It is the right tool for “page 47” because a cursor cannot express that. The problem is not offset pagination itself; it is offset pagination on large, frequently-changing data.

Is cursor pagination always faster?

At small offsets, the difference is negligible and offset may even be simpler. The gap opens as the offset grows: cost rises roughly linearly for offset and stays flat for cursor, because the cursor query is an index seek. On a large table, a deep offset can be orders of magnitude slower.

Can I use cursor pagination with any database?

Most modern databases support keyset pagination directly. Older MySQL versions lack row-value comparison, and some databases need a slightly different formulation, but the pattern is portable enough that most ORMs now support it. If your ORM only emits OFFSET for pagination, write the query by hand.

Should I return a total count with cursor pagination?

Not on every request — a COUNT(*) over a large, filtered table is expensive and defeats the purpose. Fetch limit + 1 rows and derive a has_more boolean instead. If you truly need an exact total, compute it separately, cache it, and do not do it per page.

Related topics

• What Is Database Indexing (and Why Is My Query Slow)?
• How to Add an Index to a Slow SQL Query
• What Is the N+1 Query Problem?
• What Is an ORM (and When Should You Use Raw SQL)?
• What Is Caching (and the Most Common Ways to Get It Wrong)?

Sources

• PostgreSQL documentation — LIMIT and OFFSET
• PostgreSQL documentation — Using Indexes Effectively
• MySQL 8.0 Reference Manual — LIMIT clause
• Use The Index, Luke — Pagination