REST API Pagination Design: Offset vs. Cursor

Pagination may be one of the most "deceptively simple" features in a REST API. Add ?page=3&size=20, get it working, pass integration testing, and ship it. Then one day, a user reports, "Why am I seeing content I just saw when I scroll down?" A DBA shows up and says, "This slow query is bringing down the entire database." Only then do you realize that pagination is not so simple.

At its core, pagination answers one question: how do you retrieve a "segment" from a large, constantly changing collection in a stable and efficient way? There are two key words here: stable—records are being written while they are read, so results cannot be duplicated or skipped—and efficient—the ten-thousandth page cannot be slow. These two requirements divide pagination designs into two main camps: offset and cursor. The following sections explain their underlying logic, pitfalls, use cases, and engineering implementations.

1. Offset Pagination: Simple, but Trapped by Performance and Consistency

Offset pagination is the form most developers know:

GET /api/articles?page=3&size=20

The backend translates it into SQL:

SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

The meaning is straightforward: sort by time in descending order, skip the first 40 records, and return 20. The logic is clear, it supports jumping directly to a page, and frontend table components support it naturally. These are its greatest advantages, which is why it is the default choice for most admin panels and reporting systems.

But offset pagination has two unavoidable pitfalls.

First pitfall: deep pagination causes performance to deteriorate rapidly.

To a database, OFFSET 40 does not mean "jump directly to row 40." The actual behavior of most database engines, including Mysql and Postgres, is to scan the first 40 rows, discard them, and then return the next 20 rows. In other words, the larger the OFFSET, the more work the database performs.

At ?page=5000&size=20, the database actually scans 100,000 rows just to return 20. Query cost increases linearly with the page number, making this pattern capable of overwhelming a database with millions of records. Even with an index on created_at, deep pagination still produces large numbers of random IO operations through index scans and table lookups. It is a frequent entry in slow-query logs.

Second pitfall: Data Drift.

Suppose a user is on page 2, which displays IDs 21–40. Someone publishes a new article, inserting it at the top of the list and shifting every record's "logical position" back by one. The user clicks "next page," changing the offset from 20 to 40 and retrieving the current records 41–60. But the current record 41 is actually the record that appeared as number 40 on the previous page. The result is that one record is displayed twice.

Conversely, if a record is deleted while the user is paging, every position moves forward, causing one record to be skipped.

This kind of drift is fatal for feeds and message lists where reads and writes happen concurrently. Users become confused: I never saw this record, so why is it marked as read? Or: this record appeared earlier, so why is it appearing again?

Both offset pitfalls have the same root cause: offset uses an "absolute position" to describe a record's location, but absolute positions can change.

2. Cursor Pagination: Anchor the "Position" with a Cursor for Stable, Efficient Retrieval

Cursor pagination takes a different approach. Instead of describing "which page," it describes "where the previous request stopped."

GET /api/articles?limit=20&after=eyJpZCI6MTIzNDV9

after is an opaque cursor, usually a base64-encoded JSON value containing the sorting-field values of the last record. The backend SQL looks like this:

SELECT id, title, created_at
FROM articles
WHERE created_at < :cursor_time
ORDER BY created_at DESC
LIMIT 21;   -- Fetch one extra record to determine hasMore

Notice that there is no OFFSET. The query uses a WHERE condition to retrieve records after the cursor position, allowing the database to locate the starting point at very low cost through the index on created_at. The first page and the ten-thousandth page have the same cost: each requires a single index range scan, so performance remains stable.

More importantly, this approach is naturally immune to data drift. The cursor anchors the "value of the last record," not its "ordinal position." Even if new data is inserted at the top in the meantime, as long as the cursor value remains valid, the next query continues after that value without duplicating or skipping records. This is a decisive advantage for write-heavy workloads such as feeds.

But cursor pagination is not free. It has several costs:

You cannot jump directly to a page. Users cannot go straight to "page 50" because they do not have the cursor for page 50. This is a serious limitation for table UIs that require page navigation, which is why admin panels rarely use cursor pagination.

Cursor design requires care. A cursor must contain the complete values of the sorting fields, or boundaries will be incorrect. For example, when sorting by created_at DESC, multiple records may have the same created_at value because they were inserted within the same second. A timestamp alone cannot provide stable ordering, resulting in omitted or duplicated records. The correct approach is to add a unique tiebreaker, usually the primary key:

WHERE (created_at, id) < (:cursor_time, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 21;

This is known as keyset pagination, which is covered below.

Treat the cursor as an opaque token. Do not let clients construct cursors themselves, and do not trust the cursor format submitted by a client. The backend should generate and sign it—or at least base64-encode it—and the client should return it unchanged. This lets you modify the cursor's internal structure later without breaking compatibility.

3. Offset vs. Cursor: How to Choose

Neither approach is universally better. The right choice depends on the use case. My decision rule is simple: determine whether users need to jump between pages, whether writes are frequent, and whether the collection will become very large.

Use offset when:

• You are building an admin panel, data report, or audit-log query where users expect to "jump to a specific page."
• The data is relatively stable and writes are infrequent, so drift rarely occurs.
• Data volume is manageable—within a few hundred thousand records—and deep-pagination performance is not a major concern.
• The frontend uses a table component with a paginator, making offset inexpensive to integrate.

Use cursor when:

• You are building a feed, timeline, message list, or comment list with frequent writes and continuous consumption.
• The product uses infinite scroll and does not need page jumping at all.
• The dataset is large—millions of records or more—or is expected to reach that scale.
• Data consistency matters, and duplicated or skipped records are unacceptable.

A common compromise is to use offset for the initial page and cursor for subsequent pages. Many APIs support both page and after: the first page uses ?page=1 to retrieve the initial data and a cursor, while all subsequent requests use after. This preserves compatibility with older interfaces while avoiding the performance problems of deep pagination. However, it increases implementation complexity and is best used only during a transition. New APIs should adopt cursor pagination directly.

Another counterintuitive point: do not use offset to approximate total. To display "1,234 records in total" in the frontend, many teams run COUNT(*) on every cursor-pagination request. On a large table, COUNT(*) is extremely slow, especially with InnoDB, which gives back all the performance gains of cursor pagination. Either use an estimate or omit an exact total and return only hasMore.

4. Implementing Infinite Scroll: The Standard Cursor + hasMore Pattern

Infinite scroll is the classic use case for cursor pagination. The frontend interaction is "automatically load the next page when the user reaches the bottom," while the backend must reliably serve one segment of data at a time.

The backend response should look like this:

{
  "data": [
    { "id": 101, "title": "..." },
    { "id": 102, "title": "..." }
  ],
  "nextCursor": "eyJpZCI6MTAyLCJ0IjoiMjAyNi0wNy0xN1QwOTozMDowMFoifQ==",
  "hasMore": true
}

The frontend logic is straightforward:

async function loadMore() {
  if (loading || !hasMore) return;
  loading = true;
  const res = await fetch(`/api/articles?limit=20&after=${nextCursor ?? ""}`);
  const json = await res.json();
  appendToList(json.data);
  nextCursor = json.nextCursor;
  hasMore = json.hasMore;
  loading = false;
}

// Use IntersectionObserver to watch the sentinel element at the bottom
observer.observe(sentinelEl);

Several engineering details are easy to overlook:

Fetch one extra record to determine hasMore. The backend queries with LIMIT 21. If it actually receives 21 records, another page exists and hasMore = true, but it returns only the first 20 records to the client. This is much more efficient than running another COUNT query.

A null cursor represents the initial page. When nextCursor is null or absent, hasMore should be true if more data exists. The frontend omits the after parameter on its first request.

Optimistic updates and concurrency. What happens if the user publishes new content while loading, such as posting a comment? Should that content be inserted at the top of the list? The usual approach is to reload the initial page on refresh and avoid inserting new records at the top while the user is scrolling, which prevents visual jumps. This is a product decision, but the API must support it—for example, by providing ?before= for reverse pagination so new data can be retrieved.

Distinguish empty states explicitly. data: [] with hasMore: false means "there is no data at all." data: [] with hasMore: true should theoretically never occur and must be treated as a bug. Whether the frontend displays "Failed to load" or "No more results" depends entirely on the combination of these two fields.

5. Pagination Metadata: Do Not Return Only an Array

The antipattern I find least tolerable is an API that returns only a JSON array.

// Antipattern
[
  { "id": 1, "title": "..." },
  { "id": 2, "title": "..." }
]

This design gives the frontend no way to determine whether this is the complete dataset or whether another page exists. Did the query return zero records, or did the API fail? Inferring hasMore by checking whether the array length equals size creates numerous boundary bugs, including exactly full pages and empty tables.

A mature paginated response should return a structured envelope that separates data from metadata:

{
  "data": [...],
  "meta": {
    "total": 1234,
    "page": 3,
    "size": 20,
    "hasMore": true,
    "nextCursor": "eyJ..."
  }
}

My view on whether to return each field is:

• Always return hasMore. It is the only reliable signal the frontend can use to decide whether to continue loading, regardless of whether pagination uses offset or cursor.
• Cursor pagination must return nextCursor; offset pagination does not need it.
• Return total when the use case requires it. An admin panel needs it to display "X records in total," while feeds and infinite scroll should omit it because users do not care about the total and COUNT is expensive. If it must be returned but exact counting is too slow, return an estimate or a periodically refreshed cached value, and document that it is "not exact."
• Echo page / size for offset pagination; cursor pagination does not need them.

The design principle for metadata is: the frontend should never have to "guess." For every pagination state, the frontend should be able to reach a conclusion directly from response fields instead of relying on inference.

6. Advanced: Keyset Pagination and Reverse Pagination

When the sorting field is not unique, the keyset pagination described earlier is the standard solution. Its core idea is that the cursor consists of the complete tuple of sorting keys, ensuring a total ordering.

When sorting by (created_at DESC, id DESC), the cursor has this internal structure:

{ "t": "2026-07-17T09:30:00Z", "id": 102 }

The next-page query uses tuple comparison. Postgres, for example, natively supports row constructors:

WHERE (created_at, id) < ('2026-07-17T09:30:00Z', 102)
ORDER BY created_at DESC, id DESC
LIMIT 21;

For Mysql, use the equivalent form:

WHERE created_at <= '2026-07-17T09:30:00Z'
  AND NOT (created_at = '2026-07-17T09:30:00Z' AND id >= 102)
ORDER BY created_at DESC, id DESC
LIMIT 21;

One performance-critical detail: keyset pagination must use an index, so the sorting fields must be combined in the same index. In other words, (created_at, id) needs a composite index whose order matches the ORDER BY clause. Otherwise, the database must either scan the entire table or perform a file sort, eliminating every advantage. Index design determines whether keyset pagination is viable.

Reverse Pagination is another commonly overlooked requirement. Chat interfaces that let users "scroll up through message history" and comments with a "view earlier replies" action both need to retrieve records in the opposite direction. The solution is to provide a symmetric before parameter:

WHERE (created_at, id) > (:cursor_time, :cursor_id)
ORDER BY created_at ASC, id ASC   -- Note that the direction is reversed
LIMIT 21;

Before returning the results, reverse them to DESC in the application layer so the frontend receives a consistent ordering. Reverse-pagination cursors must be issued separately and cannot be mixed with forward cursors because their internal comparison directions are opposite. When designing the API, reserve both after—backward, newer—and before—forward, older. Adding them later is painful.

Summary

Pagination is not simply a matter of "adding limit/offset." It is an engineering tradeoff between performance and consistency. Several rules of thumb:

1. Offset is suitable for stable, small datasets that require page jumping. With frequent writes and large datasets, however, deep pagination becomes slow and data drifts, which will eventually cause problems.
2. Cursor pagination anchors a position with a cursor, providing constant performance without drift. It is the standard choice for feeds and infinite scroll, at the cost of not supporting page jumping and requiring careful cursor design.
3. When sorting fields are not unique, keyset pagination is mandatory: the cursor must contain the complete sorting-key tuple and be backed by a composite index, or records will be omitted at boundaries.
4. Always return structured metadata—hasMore, nextCursor, and total when needed—instead of giving the frontend only an array.
5. Plan for reverse pagination with before; chat and comment use cases will eventually need it.

One final sentence: pagination is not fundamentally about slicing data, but about anchoring a stable position amid change—offset describes "which page," while cursor describes "where you stopped last time." The former changes; the latter does not. Once you understand this, you can design pagination APIs that are both fast and reliable.