Pagination Done Right: Cursor vs Offset vs Keyset for Large, Mutable Datasets

TL;DR: Offset pagination costs O(n) per page and silently skips or duplicates rows when the underlying data changes between requests. Keyset pagination — seeking on an indexed, unique sort key — makes every page an index range scan and stays correct under concurrent writes. Expose it to clients as an opaque cursor so you can change the underlying implementation (sharding, new sort keys, encryption) without breaking anyone.

Pagination looks like a solved problem until an endpoint that was designed to return a few hundred rows starts returning a few million. Slack's engineering team described exactly this trajectory: endpoints that began with no pagination, graduated to offset pagination, and eventually had to be rebuilt around cursors because workspaces grew by orders of magnitude and offset queries became both slow and unreliable (Slack Engineering). Stripe made the same move years earlier, replacing offset parameters with starting_after and ending_before object-ID cursors (Stripe API reference).

This article walks through why that migration keeps happening, how each approach actually behaves at the database level, and how to design a cursor contract you will not regret in three years.

The three approaches, precisely defined

The terms get used loosely, so it helps to pin them down.

• Offset pagination — the client sends ?limit=50&offset=1000 (or ?page=21). The server runs ORDER BY ... LIMIT 50 OFFSET 1000.
• Keyset pagination (also called seek pagination) — the client sends the sort-key values of the last row it saw. The server runs WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50.
• Cursor pagination — an API contract, not a query strategy. The server returns an opaque token (next_cursor) that the client passes back verbatim. Behind the token is almost always keyset pagination, but it could be a snapshot ID, a search-engine scroll ID, or a composite of several per-shard positions.

The important distinction: keyset is how you query; cursor is how you expose it. Most of the design mistakes we see come from conflating the two — for example, exposing raw keyset parameters (?after_created_at=...&after_id=...) as the public contract, which permanently couples clients to your sort columns.

Why offset breaks: cost and correctness

The cost problem

OFFSET n does not skip rows for free. The database has to produce the first n rows in sorted order and throw them away. With a supporting index, that is an index scan of n + limit entries; without one, it is a sort of the whole filtered set. Either way, the work grows linearly with page depth. Page 1 is fast, page 2,000 is not, and the Citus team's survey of Postgres pagination techniques shows the same pattern across approaches (Citus Data).

This matters more for APIs than for UIs. Humans rarely click to page 2,000. Integration clients — sync jobs, ETL connectors, partners doing a full export — walk every page, every night. Total cost for a full traversal with offset pagination is O(n²) in the number of pages; with keyset it is O(n).

The correctness problem

The cost problem is visible in dashboards. The correctness problem is worse because it is silent.

Consider a list sorted by created_at DESC, 50 per page. A client fetches page 1 (rows 1–50). Before it fetches page 2, a new row is inserted at the top. Everything shifts down by one; what was row 50 is now row 51, and the client receives it again as the first item of page 2. That is a duplicate — annoying but survivable if the client dedupes.

Now reverse it: a row on page 1 is deleted between requests. Everything shifts up by one. What was row 51 becomes row 50 — which belongs to page 1, which the client already fetched. Page 2 starts at the old row 52. Row 51 is never delivered. No error, no warning. For a sync integration this is data loss (Sequin).

On a mutable dataset with continuous writes, there is no offset value that is "correct" across two requests. The offset is a position in a list that no longer exists.

How keyset pagination works

Keyset pagination replaces "skip n rows" with "start after this row." The key requirements:

1. A deterministic, total ordering. The sort key must be unique. created_at alone is not — two rows can share a timestamp, and rows sharing a boundary timestamp will be skipped or repeated. Always add a unique tiebreaker, typically the primary key: ORDER BY created_at DESC, id DESC.
2. An index that matches the ordering. A composite B-tree index on (created_at DESC, id DESC) — plus any equality filters as leading columns — lets the database seek directly to the anchor and read forward.
3. A row-value comparison. In Postgres, (created_at, id) < ($1, $2) is a row comparison that the planner can use as an index range condition. Expanding it by hand into created_at < $1 OR (created_at = $1 AND id < $2) is logically equivalent but some planners handle it less efficiently; test on your engine.

-- Supporting index: equality filter first, then sort keys
CREATE INDEX orders_tenant_created_id
  ON orders (tenant_id, created_at DESC, id DESC);

-- First page
SELECT id, created_at, total
FROM orders
WHERE tenant_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 51;            -- fetch limit + 1 to compute has_more

-- Subsequent pages
SELECT id, created_at, total
FROM orders
WHERE tenant_id = $1
  AND (created_at, id) < ($2, $3)   -- values from the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 51;

Fetching limit + 1 rows is a small trick worth adopting: if you get 51 back, return 50 and set has_more: true. This avoids a separate COUNT(*) query, which on a large filtered table can cost more than the page itself.

Why it stays correct under writes

The anchor is a value, not a position. Inserts above the anchor do not affect subsequent pages. Deletes of rows already delivered do not shift anything. A row inserted below the anchor (a backdated created_at, say) will appear in a later page, which is usually the desired behaviour. The one remaining anomaly — a row whose sort key is updated across the anchor while a client is mid-traversal — is discussed below.

Designing the cursor contract

Keyset is the engine; the cursor is the API surface. A good cursor contract has a few properties.

Make it opaque

Encode the anchor values into a token the client treats as a black box. At minimum, base64url-encode a small JSON or binary structure. Opaqueness buys you:

• Freedom to change sort keys or add a tiebreaker without a breaking change.
• Freedom to encode multiple positions — Slack's cursors, for instance, can carry position information for more than one underlying data source, which is how they paginate across sharded datasets (Slack Engineering).
• A clean place to add a version byte.

import base64, hmac, hashlib, json

SECRET = load_secret("cursor-signing-key")

def encode_cursor(last_row, sort_spec, filters_hash):
    payload = {
        "v": 1,                          # cursor format version
        "k": [last_row["created_at"].isoformat(), last_row["id"]],
        "s": sort_spec,                  # e.g. "created_at:desc"
        "f": filters_hash,               # binds the cursor to the query
    }
    raw = json.dumps(payload, separators=(",", ":")).encode()
    sig = hmac.new(SECRET, raw, hashlib.sha256).digest()[:12]
    return base64.urlsafe_b64encode(sig + raw).rstrip(b"=").decode()

def decode_cursor(token, expected_filters_hash):
    data = base64.urlsafe_b64decode(token + "=" * (-len(token) % 4))
    sig, raw = data[:12], data[12:]
    if not hmac.compare_digest(sig, hmac.new(SECRET, raw, hashlib.sha256).digest()[:12]):
        raise InvalidCursor()
    payload = json.loads(raw)
    if payload["f"] != expected_filters_hash:
        raise InvalidCursor("cursor does not match current filters")
    return payload

Sign it (or at least validate it)

Opaque is not the same as tamper-proof. A base64 JSON blob is trivially editable, and a client that edits the anchor values can probe data it should not see if your authorization relies on anything in the cursor. Keep authorization in the query (WHERE tenant_id = $caller_tenant), never in the cursor, and add an HMAC so malformed or forged cursors fail fast with a clear 400 rather than producing confusing results.

Bind it to the query

A cursor produced for ?status=open&sort=created_at is meaningless for ?status=closed&sort=total. Hash the filter and sort parameters into the cursor and reject mismatches. Otherwise clients will eventually change filters mid-traversal and get silently wrong pages.

Decide on expiry deliberately

Keyset cursors do not need server-side state, so they can live indefinitely. That is a feature for sync clients that checkpoint a cursor and resume the next day. Snapshot-style cursors (search engine scroll contexts, for example) hold server resources and must expire. Document which kind you issue. If you ever need to invalidate old cursors — say, after changing the sort key — the version byte lets you return a specific error code instructing clients to restart from the beginning.

Shape of the response

A response envelope that has aged well across many public APIs:

{
  "data": [ ... ],
  "has_more": true,
  "next_cursor": "q3Zk...",
  "prev_cursor": "a91L..."
}

Avoid returning a total_count by default. On large, filtered, multi-tenant tables an exact count is expensive and, on a mutable dataset, already stale by the time the client reads it. If product needs it, offer it as an opt-in (?include=total_count), serve an estimate, or cache it with an explicit freshness indicator.

Trade-offs at a glance

The honest cost of keyset pagination is the loss of random access and the index requirement per sort order. Both are usually acceptable for APIs, and neither is usually acceptable for a data-grid UI with a "go to page 37" box. That is why many teams run both: offset for shallow, human-driven UI pages with a hard cap on maximum offset, keyset cursors for API consumers and infinite scroll.

Failure modes and how to design against them

Non-unique sort keys

The most common keyset bug. Sorting by updated_at alone, with bulk imports that stamp thousands of rows with the same timestamp, will drop rows at every page boundary that falls inside a tie. Always append the primary key.

Sort keys that change during traversal

If you paginate by updated_at and a row is updated while a client is traversing, it jumps from "not yet seen" to "already past the anchor" — or the reverse — and is skipped or delivered twice. For sync use cases this is actually the pattern you want if you invert it: paginate by updated_at ASC, id ASC from a checkpoint, and any row modified after the checkpoint reappears later in the stream. Clients must upsert by ID. This is the "incremental sync" pattern and it is the right default for integration APIs; pair it with soft-delete tombstones so deletions propagate too.

Nullable sort columns

NULL ordering differs across databases and breaks row-value comparisons (NULL < x is unknown, not true). Either make sort columns NOT NULL, or coalesce to a sentinel in both the index expression and the query.

Filters that defeat the index

A keyset query is only fast if the planner can use the composite index as a range scan. An equality filter on a column that is not a leading index column, or an OR across columns, can push the planner back to sorting the full filtered set. Run EXPLAIN (ANALYZE, BUFFERS) on the deepest page you expect clients to reach, not the first page.

Sharded or federated data

Across shards, a single keyset anchor is not enough. Options: a globally sortable ID (ULID, Snowflake-style IDs) so one anchor works everywhere; a composite cursor carrying one anchor per shard with a merge on read; or routing a given list to a single shard by tenant. The opaque cursor is what makes any of these possible without client changes.

Clients that hammer the list endpoint for exports

Cursor pagination makes full traversals cheap per page, but a client walking millions of rows is still a heavy workload. Combine pagination with sensible rate limiting, and for genuinely large exports offer an asynchronous bulk export job that writes a file and returns a download URL, rather than forcing clients through tens of thousands of synchronous page requests.

Decision framework

Use this checklist when designing a new list endpoint:

1. Who consumes it? Integration clients, sync jobs, or infinite scroll → cursor over keyset. Admin UI with page numbers over a small, bounded set → offset with a max-offset cap is fine.
2. Is the data mutable while being read? If yes, offset is incorrect, not just slow.
3. What sort orders must you support? Each one needs a matching composite index with a unique tiebreaker. Keep the list short; do not accept arbitrary ?sort= values.
4. Is this a sync endpoint? Paginate on updated_at, id ascending, return tombstones for deletes, and document that clients must upsert.
5. What does the cursor contain? Version, anchor values, sort spec, filter hash, and an HMAC. Never authorization data.
6. Do you need counts? Default no. Opt-in, estimated, or cached if yes.
7. What is the largest traversal you expect? If it is "the whole table, nightly," offer a bulk export as well.

Teams building API development and integration layers, or SaaS products where partners sync data out every night, tend to hit the offset wall at roughly the same moment their largest customer does. Designing the cursor contract up front — even if the first implementation is simple — is far cheaper than migrating every client later.

Syslabs' engineering team designs and hardens integration APIs like these for clients whose data volumes have outgrown their first implementation.

Sources: Slack Engineering — Evolving API Pagination at Slack · Stripe API reference — Pagination · Citus Data — Five ways to paginate in Postgres · Sequin — Keyset cursors, not offsets, for Postgres pagination · Slack Developer Docs — Pagination