Cursor vs Offset Pagination: Designing Faster APIs

The first page of an API can be fast while the thousandth page is painfully slow. Another problem appears when data changes between requests: a reader sees the same article twice or misses an article entirely.

Pagination determines how an application moves through a dataset. The choice affects database work, navigation and the consistency users experience while browsing. An endpoint that returns only twenty rows can still do a surprising amount of work to find those twenty rows.

Introduction

Offset pagination describes a position: skip a number of rows, then return the next batch. Cursor pagination describes a continuation point: resume after a boundary or position represented by a token.

Many cursor APIs use keyset pagination underneath. They store the last returned sort values in the token and use those values to query the next range. Other cursor APIs can use server-side snapshots or provider-specific continuation state, so a cursor's existence alone does not prove the database uses a keyset seek.

This article uses a newest-first article feed as a worked example. We will begin with a stable order, compare the queries, design a cursor contract and examine changes that happen while a reader browses.

The aim is to choose deliberately. Offset remains useful for small administrative lists and numbered page navigation. Keyset pagination usually fits large feeds and sequential traversal, provided the ordering, indexes and consistency contract support it.

Start with what the reader needs to do. An activity feed may require next, previous and refresh. A moderation table may need direct access to page seventeen, an exact count and several user-selected sort orders.

These experiences impose different costs. Direct page jumps are natural with offsets because the client asks for a numerical position. A keyset boundary normally comes from a previously read page, so jumping to an arbitrary page requires additional state, an approximate anchor or a different query strategy.

Clarify whether the list changes frequently and how long a browsing session lasts. A ten-second scan through recent activity is different from a two-hour export of every financial record. The latter may need a consistent dataset rather than merely fast continuation.

Also define the unit being paged. If an article has several tags, the user expects twenty articles rather than twenty joined article-tag rows. The query shape needs to preserve that unit before applying the page limit.

Finally, cap page size. A client-controlled value of a million defeats the reason for pagination. The default and maximum should reflect payload size, query cost and user experience rather than an arbitrary framework setting.

Begin with a Stable, Unique Sort Order

A page boundary must uniquely position each row. Sorting by publication time alone is insufficient when several articles share the same timestamp. Add a unique identifier as a tie-breaker:

ORDER BY PublishedAt DESC, Id DESC

Now an article's position is defined by the pair of values. Two rows can share a publication time, but they cannot share both values if Id is unique within the query's scope.

Without a predictable order, repeated requests can return different subsets even when no business data changes. Query plans and storage layout are not a pagination contract. PostgreSQL explicitly documents the need for a constrained order when using limits and offsets, and notes that skipped rows still require work. LIMIT and OFFSET.

Choose boundary columns carefully

Prefer non-null columns with stable values. A publication time that editors regularly change can move an article across a reader's current boundary. That may be valid product behaviour, but it weakens the expectation of seeing each article exactly once.

Define null ordering if nulls are possible. Comparisons such as PublishedAt < @lastPublishedAt do not automatically include rows with a null publication time. A cursor crossing between non-null and null regions requires explicit predicates.

Text sorting adds collation rules, case handling and possibly locale dependence. A cursor over display names needs the same comparison semantics as the database order. Normalising strings in the API differently from the database can make the boundary inconsistent.

A simple immutable sequence or creation timestamp plus unique identifier is often easier to reason about. Use business sorting when the product needs it, but accept the additional complexity instead of hiding it in the token.

Understand Offset Pagination

For a page size of twenty, page three skips forty rows and returns the next twenty:

SELECT Id, Title, PublishedAt
FROM Articles
WHERE OrganisationId = @organisationId
ORDER BY PublishedAt DESC, Id DESC
LIMIT 20 OFFSET 40;

The offset is usually calculated as (pageNumber - 1) * pageSize. Validate positive page numbers and use arithmetic that cannot overflow when a malicious or mistaken client sends a very large value.

Numbered navigation is easy to expose:

GET /articles?page=3&pageSize=20

A user can bookmark page three without first obtaining page two. Administrative interfaces often benefit from that simplicity, especially when the dataset is small or the maximum browsing depth is naturally limited.

Why deep pages become expensive

An index matching the filter and order can avoid a large sort. It does not make a deep offset equivalent to reading twenty records. The database generally still has to find and step past the preceding qualifying rows.

For a hypothetical request using offset 100,000 and limit twenty, the engine may inspect roughly that large prefix before returning the requested batch. Actual work depends on filters, visibility checks and the plan, so measure rather than treating the offset as an exact row-read counter.

If the application reads the complete dataset using increasing offsets, it revisits prefixes repeatedly. The first page skips nothing, the second skips one page and the final page skips almost everything already read. That cumulative work is particularly unattractive for exports and background processing.

A deferred join can reduce the cost of retrieving wide rows by first selecting page identifiers, but it does not remove the need to traverse the offset. It is an optimisation of the chosen approach, not a conversion into keyset pagination.

How changes shift positions

Imagine the descending list is F, E, D, C, B, A, with a page size of three. The first request returns F, E, D.

Before the second request, a newer article G is inserted. The list becomes G, F, E, D, C, B, A. Skipping three now returns D, C, B, so D appears twice.

If F is deleted instead, the list becomes E, D, C, B, A. Skipping three returns B, A, so C is never shown in that traversal. Unique ordering makes the results deterministic for each query, but does not stop positions shifting between queries.

Understand Keyset Pagination

Keyset pagination uses the last returned article's sort values to find the next range. For a newest-first feed, the next page contains rows whose publication time is earlier, or whose time matches and identifier is smaller.

SELECT Id, Title, PublishedAt
FROM Articles
WHERE OrganisationId = @organisationId
  AND (
      PublishedAt < @lastPublishedAt
      OR (
          PublishedAt = @lastPublishedAt
          AND Id < @lastId
      )
  )
ORDER BY PublishedAt DESC, Id DESC
LIMIT 21;

The first page omits the boundary predicate. Subsequent pages use a boundary produced by the previous response. Query parameters must be bound normally rather than interpolated into SQL.

The extra row answers whether more data exists. Return twenty articles and set hasMore if a twenty-first was fetched. Create the next cursor from the twentieth returned article, not from the extra row, otherwise that extra row can be skipped on the next request.

Why the comparison works

Suppose the final displayed row has publication time 10:00 and identifier 500. A row at 09:59 belongs after it regardless of identifier. A row at 10:00 belongs after it only if its identifier is below 500.

A predicate requiring both an earlier timestamp and a smaller identifier would be wrong. It could exclude an older article whose identifier happens to be larger. The predicate must reproduce the lexicographic order of the entire sort tuple.

PostgreSQL supports row-value comparisons that can express compatible tuple ordering more compactly. For application frameworks and database providers, verify translation support before assuming the compact SQL form is available through the query API. Microsoft's EF Core guide shows the expanded multiple-key predicate and emphasises fully unique ordering. EF Core pagination guidance.

How insertions behave

Using the earlier list, the first page still returns F, E, D, and the continuation boundary is D. Inserting G ahead of D does not change which rows are after D. The next request returns C, B, A.

This behaviour fits feeds where users continue reading older items while new items accumulate at the top. The interface can separately indicate that newer content is available and let the user restart from the beginning.

The boundary row itself need not remain present. If D is deleted, its timestamp and identifier in the cursor still define a valid comparison. A design that stores only D's identifier and looks up its timestamp later would lose that useful property.

Design the Cursor as an API Contract

A cursor is usually an opaque continuation token. Clients should store and return it without constructing or editing its internal fields. That gives the server room to change query implementation and token formats.

An illustrative decoded payload might look like this:

{
  "version": 1,
  "sort": "published_desc",
  "lastPublishedAt": "2026-09-12T10:00:00.123456Z",
  "lastId": 500,
  "filterHash": "canonical-filter-digest",
  "expiresAt": "2026-09-12T12:00:00Z"
}

The exact structure is a design choice. Include enough information to reproduce the boundary and validate the request context, while avoiding unnecessary personal or confidential data.

Preserve types and precision

Serialise timestamps without losing database precision. If the database distinguishes microseconds but the cursor rounds to milliseconds, several rows near the boundary can move to the wrong side of the comparison.

Use a consistent time-zone representation and parse it explicitly. A timestamp without an offset can be interpreted differently by clients or server configurations. The token should preserve the actual ordering value, not a display-formatted date.

Large integer identifiers also need care in clients whose numeric type cannot represent every integer exactly. Opaque tokens avoid requiring clients to parse these values at all. Inside the server, retain the database-compatible type.

Version the format before the first public release. When an old format becomes unsupported, return a clear client error and a documented way to restart browsing. Silent reinterpretation can produce plausible but incorrect pages.

Protect integrity and enforce access

Base64 is a transport encoding. It does not provide confidentiality or tamper protection. If the server relies on cursor fields being unchanged, authenticate the token with a signature or use a protected token mechanism.

Integrity is still separate from authorisation. A valid signed cursor must not let a user continue reading a tenant or dataset they can no longer access. Derive the permitted organisation from current identity and apply access predicates on every page.

If the token contains a tenant identifier, compare it with the authenticated scope rather than allowing it to select that scope. A server-generated token can outlive a user's role change, account suspension or document access.

Bound token length before decoding, reject malformed values and avoid returning parser stack traces. Rotation of signing keys may require a short overlap so valid in-progress sessions continue, with an explicit expiry for older tokens.

Bind filters and ordering

A cursor from a feed filtered to published articles should not be reused for a draft-only query without a defined rule. The same applies to search terms, category, locale and sort direction.

Canonicalise filter values and bind them to the token, or include validated query context in a server-side cursor record. If request parameters disagree, return a clear error instead of silently continuing under a different dataset.

Whether page size can change mid-traversal is a product choice. Keyset boundaries do not inherently depend on size, so allowing a different bounded size can be reasonable. Snapshot systems or provider tokens may impose other constraints; document the contract actually supported.

Shape a Useful API Response

A response can expose the articles and explicit continuation information:

{
  "items": [
    { "id": 502, "title": "Example article" },
    { "id": 500, "title": "Another article" }
  ],
  "pageInfo": {
    "hasMore": true,
    "nextCursor": "opaque-token"
  }
}

This abbreviated example shows two items only to keep the contract readable. The real response follows the requested bounded page size.

Use a null or absent next cursor when no continuation exists, according to the documented schema. Empty results should remain a successful list response rather than becoming an error merely because a cursor has reached the end.

An endpoint can also return a complete next link. That reduces the chance of clients dropping filters or incorrectly escaping a token. Keep the link within the API's expected host and route construction rules.

Do not promise that following the same cursor always returns the identical rows. Without a snapshot, later deletions, access changes and edits can change the answer. A token represents a boundary, not necessarily a saved response.

Match Indexes to the Actual Query

For an organisation-specific feed, a candidate PostgreSQL index is:

CREATE INDEX ix_articles_organisation_publication
ON Articles (OrganisationId, PublishedAt DESC, Id DESC);

The organisation equality condition narrows the relevant range, while publication time and identifier match the requested order. This is a candidate to measure against the real schema, not a universal prescription.

PostgreSQL's multicolumn-index documentation explains how leading columns and constraints influence the range an index can scan. Index usefulness depends on the complete predicate and data distribution, including filters not represented in the leading keys. Multicolumn indexes.

Inspect plans beyond the first page

Compare first-page and deep-boundary plans with realistic organisation sizes. A tiny tenant can make almost any approach appear fast. A large tenant with selective filters can reveal substantial extra scanning.

Look at rows examined, rows discarded by filters, sort work, buffer reads and returned payload size. If only one in a thousand rows matches a category, a keyset seek may still scan many entries before collecting twenty results.

Consider a different index or a partial index for a stable common filter when the database supports it. Avoid building an index for every imaginable sort and filter combination: each index consumes storage and adds write maintenance.

Selected columns matter too. A covering strategy can reduce table lookups, but including large article bodies in a feed index may be wasteful. Return only what the list needs and fetch full content through the detail endpoint.

Preserve the paged entity

Joining articles to multiple tags before limiting can yield repeated articles or fewer unique articles than requested. First identify the bounded article set, then load associated data, or use a query shape that does not multiply the primary rows.

The same issue appears in object-relational mappers when loading collections. Inspect generated SQL and the materialised result, particularly when combining ordering, includes and page limits.

Pagination should reduce total work and payload. Fetching twenty articles followed by one query per article for author information can reintroduce latency through an N+1 query pattern even when the pagination query itself is efficient.

Implement the Boundary Without Loading the Whole Dataset

An application query should keep filtering, ordering and limiting in the database. Materialising every article and then applying a continuation predicate in memory loses the main benefit of keyset pagination.

For an EF Core model with a long identifier and non-null publication timestamp, the relevant query shape can be expressed as:

var query = db.Articles
    .AsNoTracking()
    .Where(article => article.OrganisationId == organisationId);

if (boundary is not null)
{
    var publishedAt = boundary.PublishedAt;
    var id = boundary.Id;

    query = query.Where(article =>
        article.PublishedAt < publishedAt ||
        (article.PublishedAt == publishedAt && article.Id < id));
}

var rows = await query
    .OrderByDescending(article => article.PublishedAt)
    .ThenByDescending(article => article.Id)
    .Select(article => new ArticleListItem(
        article.Id, article.Title, article.PublishedAt))
    .Take(pageSize + 1)
    .ToListAsync(cancellationToken);

Here, boundary is already validated server-side cursor data, and pageSize has already been constrained to the endpoint's supported range. ArticleListItem is a small response projection rather than a complete tracked entity graph.

After execution, determine whether the extra row exists, select the first pageSize records and create the continuation from the final displayed row. Handle an empty list before accessing that final element.

Keep the query composable until the final asynchronous materialisation call. Helpers that call ToList early can quietly move the remaining work into application memory. Review generated SQL when introducing abstractions around pagination, especially if they accept arbitrary expressions or custom comparison functions.

The sample's numeric identifier is intentional. A different identifier type may require a different comparison expression or database-specific translation. The cursor encoder, query predicate and database order must agree on the same comparison semantics.

Also propagate cancellation from the request so abandoned page loads do not continue consuming resources unnecessarily. A page-size cap limits returned rows, but an expensive filter or count can still run for a long time; give the database operation an appropriate deadline.

Finally, keep enrichment bounded. If the response needs an author label, include it through an efficient projection or one batch lookup for the page's author identifiers. Do not allow a reusable pagination helper to hide dozens of additional calls after the apparently fast page query has completed.

Previous-page navigation requires reversing the boundary. Starting from the first displayed row, find rows before it in the user-visible order, fetch them using the opposite database order, then reverse the resulting list for display.

For the descending publication order, the previous query uses greater-than comparisons and ascending order:

SELECT Id, Title, PublishedAt
FROM Articles
WHERE OrganisationId = @organisationId
  AND (
      PublishedAt > @firstPublishedAt
      OR (
          PublishedAt = @firstPublishedAt
          AND Id > @firstId
      )
  )
ORDER BY PublishedAt ASC, Id ASC
LIMIT 21;

Select the nearest twenty rows in that ascending result, use the extra row only as a continuation signal, then reverse the selected rows into descending display order. Derive previous and next boundaries from the displayed page's edges.

A browser can also keep a stack of cursors already visited. That supports back navigation without inventing arbitrary page numbers, though revisiting a boundary still reflects current data unless responses or snapshots are retained.

Some interfaces use offsets for an explicit page jump and keysets for next and previous navigation. That can be a reasonable compromise if the added contract remains understandable and deep jumps are measured or capped.

If direct random access is a core requirement over a huge changing dataset, consider search refinement or approximate anchors such as month boundaries. A page number is often less useful than a meaningful date or filter that narrows the user's task.

Be Explicit About Consistency

A cursor is not a snapshot

Keyset pagination handles insertions ahead of a stable boundary well. It does not freeze the dataset. An article whose publication time changes from old to new can cross the boundary while the user browses.

A previously unseen article moved ahead of the boundary may never appear in the remaining traversal. A previously shown article moved behind it may appear again. Deletions can remove records before the user reaches them.

Filtering changes create similar effects. An article can become published, change category or lose visibility. These are legitimate changes to membership rather than a defect in the comparison operator.

The API documentation should state whether the list is a live view or a consistent traversal. That distinction is more useful than claiming cursor pagination prevents every duplicate and omission.

Use snapshots for consistent exports

An export that must represent the dataset at a specific point needs an explicit snapshot strategy. Depending on the system, that can mean a database snapshot, a materialised set of identifiers and values, temporal queries or an asynchronous export job.

A high-water mark such as the largest identifier at the start can exclude newer insertions, but does not preserve the original values of rows updated later. It is a membership bound under particular assumptions, not a general historical snapshot.

PostgreSQL's default Read Committed isolation takes a new snapshot for each command, while Repeatable Read provides a stable transaction snapshot. Separate HTTP requests do not share that transaction automatically. Transaction isolation.

Holding a database transaction open throughout a long human browsing session has operational costs, including retained resources and old row versions. A durable export job can own a bounded snapshot lifetime, produce a file and let the user download it later.

Consider replicas and distributed stores

Reading successive pages from replicas with different lag can make membership change unexpectedly even when ordering columns are stable. If the product requires a stronger session experience, choose routing or consistency controls that support it.

For a dataset spread across shards, each shard may supply a sorted range that the API merges. The continuation token may need per-shard positions, and changing shard topology requires a migration contract for existing tokens.

That is still cursor pagination, but it is more than a single timestamp and identifier. The token should represent the actual continuation state required by the storage system, with size and lifetime limits.

Test Correctness and Measure Useful Work

Create records sharing the same timestamp and verify that all appear exactly once during an unchanged traversal. Include the smallest and largest supported identifiers, empty datasets and page sizes of one and the configured maximum.

Test insertion ahead of the boundary, deletion of the boundary row and modification of a sort value. Assert the documented live-view behaviour rather than expecting snapshot guarantees the design does not provide.

Change filters between requests and attempt to use a cursor from another organisation. Confirm that validation rejects inconsistent context and current authorisation remains effective even for a correctly signed token.

Measure deep offsets and deep keyset boundaries with the same filters and payload. Record database work as well as response time; a warm cache can conceal inefficient scanning until production traffic changes.

Finally, inspect exact-count queries. Counting all matching records on every page can cost more than fetching the bounded result. Use a separate count endpoint, a cached estimate or no count when the interface does not need an exact total. Label estimates honestly and define when they are refreshed.

Summary

Offset pagination offers simple page numbers and direct jumps, while keyset-backed cursors avoid repeatedly skipping large prefixes and cope better with new rows ahead of a stable boundary.

Both require a unique sort order, consistent access predicates and indexes matched to the query. A robust cursor also preserves precision, binds query context and remains subject to current authorisation.

Choose the navigation the product needs and state its consistency model explicitly. For live feeds, a stable boundary is often enough. For a consistent export, design a snapshot workflow. In either case, measure beyond the first page so a small response also means a reasonable amount of database work.

Cursor vs Offset Pagination: Designing Fast APIs for Large Datasets