Cursor Pagination for REST APIs: Keyset vs Offset at Scale

What is cursor pagination in a REST API, and why does offset pagination slow down?

Offset pagination maps a page number to LIMIT n OFFSET k: page 3 at 50 rows per page becomes "skip 100 rows, return the next 50." Cursor pagination, in its keyset form, replaces the skip count with a position. The client sends back the sort key of the last row it saw, and the server asks for rows that sort after that value: "return 50 rows where id is greater than 8812, ordered by id."

Offset gets slower as pages get deeper because the database cannot jump to row k. An index on the ORDER BY column gives it rows in the right order, but it still has to walk past every skipped entry, and usually fetch each row, before it can discard them. The work grows with page depth, not with page size.

Illustrative estimate (not a measurement): at 50 rows per page, a request for page 20,000 makes the database read and throw away about 1,000,000 rows to return 50. Page 2 returns the same 50 rows for a tiny fraction of that work. A keyset query for either page costs about the same, because it seeks directly to the boundary value in the index.

Offset also has a correctness problem that has nothing to do with speed. Positions are recomputed on every request. If a row is inserted before the current page between two calls, the next page repeats an item the client already received. If a row is deleted, the next page skips one. A keyset cursor anchors to a value instead of a position, so rows shifting earlier in the result set do not change where the next page begins.

Copy-- Example table: items(id BIGINT PRIMARY KEY, title TEXT, ...)
-- Assume 50 rows per page.

-- Offset pagination: page 3 = skip 100 rows, return the next 50.
SELECT id, title
FROM items
ORDER BY id
LIMIT 50 OFFSET 100;

-- Deep offset page: page 20,000 = skip 999,950 rows.
-- The primary key index supplies rows in id order, but the database
-- still walks past every skipped entry before returning 50 rows.
SELECT id, title
FROM items
ORDER BY id
LIMIT 50 OFFSET 999950;

-- Keyset (cursor) pagination: first page has no cursor.
SELECT id, title
FROM items
ORDER BY id
LIMIT 50;

-- Next page: the client sends back the last id it received (here 8812).
-- The index seeks straight to id > 8812, so the cost does not grow
-- with page depth, and inserts or deletes before 8812 do not shift
-- where this page starts.
SELECT id, title
FROM items
WHERE id > 8812
ORDER BY id
LIMIT 50;

The same table paged with LIMIT/OFFSET, where the database must skip past every earlier row, and with a keyset cursor that seeks directly to the last id the client saw.

How does keyset pagination work?

the next page starts. The cursor carries the sort values of the last row the client saw, and the query asks for rows strictly after them:

WHERE (created_at, id) > (:last_created_at, :last_id) ORDER BY created_at, id LIMIT :n

With OFFSET, the database must walk past every skipped row. With the predicate, a B-tree index on (created_at, id) seeks directly to the last key and reads n entries. The cost depends on n, not on page depth.

CopyOFFSET 100000 LIMIT 20          Keyset predicate, LIMIT 20
[r1][r2]...[r100000][r..20]     index ──seek──► (last_ts, last_id)
 └─ read + discard 100000 ─┘                    └─ read 20 ─┘

The sort key must be unique. Suppose 50 rows share one created_at and a page boundary lands among them. A predicate on created_at alone either repeats those rows or skips them. Adding the primary key as the final column gives every row a single, total position.

The index must match the ORDER BY. The column order and direction need to line up with the sort, or the planner falls back to sorting the filtered rows. ORDER BY created_at, id needs (created_at, id), not (id, created_at).

Use the expanded OR form when tuples do not fit. Row-value comparison assumes every column sorts in the same direction. For created_at DESC, id ASC, write the comparison out: created_at < :ts OR (created_at = :ts AND id > :id). Some databases also do not use the index efficiently for tuple comparison, so check the plan.

Nullable sort columns cause silent bugs. NULL > x evaluates to unknown, so rows with NULLs drop out of the result without any error. Make the column NOT NULL, or handle the NULL rows with an explicit predicate.

Copy-- Schema: created_at is NOT NULL so the predicate never compares against NULL.
CREATE TABLE orders (
  id         bigint      PRIMARY KEY,
  created_at timestamptz NOT NULL,
  total      numeric(12,2) NOT NULL
);

-- Index column order and direction match the ORDER BY below.
CREATE INDEX orders_created_id_asc ON orders (created_at, id);

-- First page: no cursor yet.
SELECT id, created_at, total
FROM orders
ORDER BY created_at, id
LIMIT 20;

-- Next page: seek past the last row seen (id is the unique tiebreaker).
SELECT id, created_at, total
FROM orders
WHERE (created_at, id) > (:last_created_at, :last_id)
ORDER BY created_at, id
LIMIT 20;

-- Mixed directions (newest first, ties by ascending id): tuples no longer fit.
CREATE INDEX orders_created_desc_id_asc ON orders (created_at DESC, id ASC);

SELECT id, created_at, total
FROM orders
WHERE created_at <= :last_created_at          -- redundant bound gives the planner a range to seek
  AND (created_at < :last_created_at
       OR (created_at = :last_created_at AND id > :last_id))
ORDER BY created_at DESC, id ASC
LIMIT 20;

-- Confirm the plan uses an index scan with no separate Sort node.
EXPLAIN SELECT id FROM orders
WHERE (created_at, id) > ('2025-06-01 12:00:00+00', 48213)
ORDER BY created_at, id LIMIT 20;

PostgreSQL keyset pagination showing the row-value predicate for a uniform ascending sort, the expanded OR form with a matching mixed-direction index, and an EXPLAIN check that the index is used for the seek.

How do you implement opaque cursors in a REST API?

Build the cursor from the last row returned: its sort values, including the unique tiebreaker such as id, plus the sort key, the active filters, and a format version. Serialize that to JSON and base64url-encode it. Document the token as opaque. Clients pass it back unchanged and never parse it, which leaves you free to change the format later. The version field lets you decode old tokens during a migration or reject them cleanly.

Base64 encodes the values but does not hide them. Anyone can decode the token and read or edit the timestamp and ID inside. If tampering matters, for example when filters enforce tenant scoping, add an HMAC over the payload with a server-side key and reject any token that fails verification. On every request, compare the sort and filter values embedded in the cursor with the current query parameters. Return a 400 if they differ, because a cursor from one ordering applied to another silently skips or repeats rows.

Response contract:

• next_cursor, and optionally prev_cursor, is null or absent when there are no more pages.
• limit defaults to a sensible value and is capped by the server, so a client cannot request a million rows at once.
• No total count by default. COUNT(*) scans the same rows you are trying to avoid. Offer a planner estimate or a separate opt-in count endpoint instead.

For backward pagination, flip the comparison (< instead of >) and the ORDER BY direction, fetch the rows, and then reverse them in application code so the page comes back in display order.

Expose a fixed set of sorts. Back each one with a matching composite index and give it its own cursor format.

Copyimport base64, hashlib, hmac, json

CURSOR_VERSION = 1
SECRET_KEY = b"example-cursor-key-load-from-config"  # placeholder, never hardcode

class InvalidCursor(ValueError):
    """Raised for any bad cursor; the API layer maps it to HTTP 400."""

def _b64(data: bytes) -> str:
    return base64.urlsafe_b64encode(data).rstrip(b"=").decode()

def _unb64(s: str) -> bytes:
    return base64.urlsafe_b64decode(s + "=" * (-len(s) % 4))

def encode_cursor(last_row: dict, sort: str, filters: dict) -> str:
    # Last row's sort values incl. the unique tiebreaker, plus the query context.
    # created_at is assumed to already be an ISO-8601 string.
    payload = {"v": CURSOR_VERSION, "sort": sort, "filters": filters,
               "after": [last_row["created_at"], last_row["id"]]}
    body = json.dumps(payload, sort_keys=True, separators=(",", ":")).encode()
    # Base64 only encodes; the HMAC is what stops clients editing the values.
    sig = hmac.new(SECRET_KEY, body, hashlib.sha256).digest()
    return _b64(body) + "." + _b64(sig)

def decode_cursor(token: str, sort: str, filters: dict) -> list:
    try:
        body_b64, sig_b64 = token.split(".")
        body, sig = _unb64(body_b64), _unb64(sig_b64)
        payload = json.loads(body)
    except ValueError:  # bad split, bad base64, or bad JSON
        raise InvalidCursor("malformed cursor")
    expected = hmac.new(SECRET_KEY, body, hashlib.sha256).digest()
    if not hmac.compare_digest(sig, expected):
        raise InvalidCursor("cursor signature mismatch")
    if payload.get("v") != CURSOR_VERSION:
        raise InvalidCursor("unsupported cursor version")
    # A cursor from a different ordering or filter set would skip or repeat rows.
    if payload.get("sort") != sort or payload.get("filters") != filters:
        raise InvalidCursor("cursor does not match current sort/filters")
    return payload["after"]  # e.g. bind into: WHERE (created_at, id) > (%s, %s)

A Python sketch that builds a versioned, HMAC-signed base64url cursor from the last row's sort values and query context, then rejects tampered, outdated or mismatched cursors with an error the API maps to HTTP 400.

How can you verify keyset pagination is faster on your own data?

Don't take anyone's word for the speedup, including this article's. Run both queries against a production-sized copy of your data and read the plans. In PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) executes the query and reports actual row counts, timing, and pages touched per plan node. Other databases have comparable tooling with different output.

Pick a deep page, such as offset 500,000 with a page size of 50. Then capture the sort-key values of the last row before that page so the keyset query returns exactly the same rows. Compare the two plans this way:

• Offset plan: Look at the scan node beneath the Limit. Its actual row count should be roughly offset plus page size. The database read all of those rows and threw away everything except the last 50. The buffer counts grow with depth, and that is the real cost.
• Keyset plan: You want an index scan whose index condition contains your cursor comparison. Its actual row count should be close to the page size, no matter how deep the page is.

Repeat the test at several depths. Offset cost should rise roughly in step with depth, while keyset cost should stay flat. Run each query a few times so you can tell cold-cache results from warm ones.

This check also tests your index. If the keyset plan shows a sequential scan followed by a sort, or an index scan with a separate filter step, the index is missing or doesn't match the query. Common causes are a missing tiebreaker column, columns in the wrong order, or a sort direction that differs from the query. You want to find that here, not under production load.

Copy-- Run in psql against a production-sized copy. Assumes a table
-- orders(id bigint, created_at timestamptz, ...) and the index the
-- keyset query depends on: (created_at DESC, id DESC).
CREATE INDEX IF NOT EXISTS orders_created_at_id_idx
    ON orders (created_at DESC, id DESC);

-- 1. Capture the sort key of the last row before the target page
--    (offset 500,000, page size 50) so both queries return the same rows.
--    \gset stores the columns as psql variables.
SELECT created_at AS last_created_at, id AS last_id
FROM orders
ORDER BY created_at DESC, id DESC
OFFSET 499999 LIMIT 1 \gset

-- 2. Offset plan: expect the scan under Limit to report ~500,050 actual
--    rows, nearly all discarded, with buffer counts that grow with depth.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
ORDER BY created_at DESC, id DESC
OFFSET 500000 LIMIT 50;

-- 3. Keyset plan: expect an Index Scan on orders_created_at_id_idx whose
--    Index Cond contains the row comparison, with ~50 actual rows.
--    A Seq Scan + Sort, or a separate Filter line, means the index is
--    missing or does not match the ORDER BY.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE (created_at, id) < (:'last_created_at', :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Repeat steps 1-3 at other depths (e.g. 5,000 and 50,000) and run each
-- query several times to separate cold-cache from warm-cache results.

A psql script that captures the cursor for a deep page and runs EXPLAIN (ANALYZE, BUFFERS) on the equivalent OFFSET and keyset queries so their row counts, buffers, and index usage can be compared directly.

When should you avoid cursor pagination and keep offset?

Keyset pagination is not a universal upgrade. Offset remains the better fit in three situations:

• The product needs random access. Keyset only moves forward or backward from a known row. It cannot jump to page 40 or report "page 3 of 112".
• The data is bounded. If a table holds a few thousand rows, or a filter always returns a small set, the database skips those rows cheaply.
• Users stay shallow. If analytics show almost nobody goes past page five, the deep-offset cost rarely occurs.

Keyset also has costs. Each sort order needs its own composite index that ends with a tiebreaker. Every index adds write amplification and storage. An API that lets clients sort by a dozen fields in either direction would need many indexes, which is rarely practical.

Opaque cursors are harder to work with than page numbers:

• Debugging and sharing. You can't read a cursor in a log line, guess one by hand, or paste a readable "page 7" link to a colleague.
• Caching. CDN or application caches keyed on page=N stop working directly.
• Compatibility. Clients store cursors, so the encoded format becomes a contract. Changing it requires a version marker and a period where old cursors are still accepted.

A cursor marks a position, not a snapshot. Rows inserted behind the cursor won't appear in later pages. A row whose sort key changes during traversal can move ahead of the cursor and appear twice, or move behind it and not appear at all. If clients need a consistent export, use a snapshot or an export job instead of pagination.

What are the alternatives to keyset cursors, and how do they compare?

Four approaches cover most real APIs. They differ mainly in where they pay their costs.

The hybrid suits UIs that show page numbers for the first few pages and an infinite scroll after that. The cap stops a crawler from requesting page 50,000. Snapshots fit compliance exports and reconciliation jobs where a stable view matters more than freshness. Pair them with an expiry, because holding a long-lived transaction or result set ties up server resources.

None of these approaches makes exact totals cheap. A count over a filtered large table still has to visit every matching row or index entry, whatever the pagination style. You can return an estimate from planner statistics, cap the count ("10,000+"), cache it with a staleness window, or drop it and return only a has_more flag.

How much does migrating to cursor pagination cost, and who should do it?

The teams that gain most share one trait: their clients read deep into collections. That includes infinite-scroll feeds, sync and export endpoints, integrations that walk every record nightly, and any table that grows or changes fast enough for offset pages to drift between requests. If your clients rarely go past page three of a small, stable table, the migration mostly adds complexity.

A migration path that doesn't break clients

1. Add cursor parameters alongside page/offset. Return a next_cursor on every response, including offset-based ones, so clients can switch one call at a time without a new API version.
2. Cap the maximum offset. Reject or clamp requests past a documented depth, and return an error that tells the client to use the cursor. This removes the expensive deep-page queries while shallow, human-driven pagination keeps working.
3. Document cursor lifetime and validity. State whether cursors expire, whether they survive sort or filter changes (they shouldn't), and which error a stale or tampered cursor returns. Clients build retry logic around this contract, so changing it later is a breaking change.

Weighing the cost

The upfront work is concrete: composite indexes that match each sort order, cursor encoding and signing, client SDK updates, and a deprecation window for deep offsets. Most of it is a one-time cost per endpoint.

The payoff grows with the table. Query cost per page stays flat instead of rising with depth, so you get fewer incidents from slow deep-page scans. Duplicate and missing-item bug reports stop, because the fix is structural rather than a series of patches. If your largest tables are still growing, the cost is easier to justify the earlier you pay it.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to cursor pagination rest api, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.