Cursor vs Offset Pagination Performance in PostgreSQL APIs

How OFFSET and keyset pagination actually execute

With offset pagination, the client asks for page N and the server issues ORDER BY created_at, id LIMIT 20 OFFSET 10000. With keyset (cursor) pagination, the client sends back the sort key of the last row it saw, and the server issues WHERE (created_at, id) > ($1, $2) ORDER BY created_at, id LIMIT 20.

OFFSET: fetch, then discard

PostgreSQL has no way to jump to the 10,001st row of an ordered result. The Limit node pulls rows from its child, which is either an Index Scan on (created_at, id) or a Sort if no such index exists. It throws away the first 10,000 rows and returns the next 20. In EXPLAIN ANALYZE, the child node reports roughly 10,020 actual rows while the Limit node reports 20. Each skipped row still costs index traversal, heap access, and visibility checks, so the work grows with OFFSET + LIMIT.

[root] → leaf: r1 r2 r3 … r10000 | r10001 … r10020 ^ read and discarded ^ ^ returned ^

Keyset: start where you left off

The row-value comparison (created_at, id) > ($1, $2) matches the column order of a composite B-tree index, so the planner uses it as an Index Cond. The scan descends the tree directly to the cursor position and reads forward 20 entries. Page depth stops mattering because the cost is a logarithmic descent plus LIMIT.

[root] → descend to (t, id) → r10001 … r10020 → stop

This only works if the index columns and their sort directions match the ORDER BY clause.

Copy-- Composite index whose column order and direction match the ORDER BY below.
CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL,
  payload    text
);
CREATE INDEX events_created_at_id_idx ON events (created_at, id);

-- Offset pagination: page 501 at 20 rows per page.
-- Compare actual rows on the Index Scan node (about OFFSET + LIMIT)
-- with actual rows on the Limit node (LIMIT). The difference is the
-- rows the executor read and then discarded.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, payload
FROM events
ORDER BY created_at, id
LIMIT 20 OFFSET 10000;

-- Keyset pagination: the cursor holds (created_at, id) from the last
-- row of the previous page. These are placeholder values.
-- Look for the row comparison listed as an Index Cond. The scan
-- descends to the cursor position and reads about LIMIT rows,
-- no matter how deep the page is.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, payload
FROM events
WHERE (created_at, id) > ('2024-03-01 12:00:00+00', 10000)
ORDER BY created_at, id
LIMIT 20;

Run EXPLAIN ANALYZE on the same table with each method: the OFFSET plan's index scan reads OFFSET + LIMIT rows and the Limit node discards all but 20, while the keyset plan uses the row-value comparison as an Index Cond and reads only the page it returns.

Why deep pages hurt under load

Before the server discards any OFFSET rows, it still has to produce them. For each skipped row it walks an index entry, usually fetches the heap tuple to check visibility, and then throws the result away. A deep page therefore pulls far more index and heap pages through shared buffers than it returns. That traffic can evict pages that other queries need. The query also runs longer, so its snapshot stays open longer, and a long-lived snapshot holds back the cleanup horizon that vacuum depends on.

A single deep request is tolerable. The damage comes from clients that walk every page: crawlers following next links, scrapers, and "export all" jobs. Each successive request costs more than the one before it, so a full walk costs roughly quadratic work in the number of pages.

Illustrative estimate. Assumptions: 20 rows per page, an index matching the sort order, and about 2 µs to visit each row (index entry plus heap visibility check). Fixed planning and network overhead is ignored.

The full-walk OFFSET figure is the sum of 20·k for k = 1 to 10,000. One bot paging to the end can consume half an hour of backend time. Several bots doing it in parallel become sustained load, which is the main reason teams move to keyset pagination.

Implementing keyset pagination in PostgreSQL

The core query is WHERE (created_at, id) > ($1, $2) ORDER BY created_at, id LIMIT n, backed by a B-tree index on (created_at, id). PostgreSQL treats the row comparison as a single index condition. It descends the tree to the cursor position, reads n entries in order, and stops. The logically equivalent created_at > $1 OR (created_at = $1 AND id > $2) is harder for the planner to turn into one contiguous range scan. It may apply part of the predicate as a filter and read rows it then discards.

id is there as a tiebreaker. If many rows share a created_at value, ordering by timestamp alone is nondeterministic, and rows can be skipped or repeated at page boundaries. Appending the primary key makes every position unique. The index columns must match the ORDER BY in order and direction, or the planner adds a Sort node.

For filtered lists, put equality columns first: (tenant_id, created_at, id) with WHERE tenant_id = $1 AND (created_at, id) > ($2, $3). Run EXPLAIN and confirm a Limit over an Index Scan (or Index Only Scan) whose Index Cond includes the row comparison, with no Sort node.

Row comparisons are lexicographic in one direction, so ORDER BY a DESC, b ASC cannot use them. Write the expanded predicate by hand and create a matching index, (a DESC, b ASC).

NULL in a row comparison yields NULL, so those rows silently drop out. Declare NULLS FIRST/LAST consistently and handle NULL explicitly, or sort on a NOT NULL column.

Copy-- Assumes created_at and total are NOT NULL. A NULL in a row comparison drops the row.
-- Equality column first, then the sort key, with the primary key as tiebreaker.
CREATE INDEX orders_tenant_created_id_idx
    ON orders (tenant_id, created_at, id);

-- Next page. The cursor is (created_at, id) of the last row on the previous page.
EXPLAIN
SELECT id, created_at, total
FROM orders
WHERE tenant_id = 42
  AND (created_at, id) > ('2026-10-01 12:00:00+00', 98765)
ORDER BY created_at, id
LIMIT 50;
-- Expect: Limit -> Index Scan using orders_tenant_created_id_idx
--   Index Cond includes tenant_id = 42 AND ROW(created_at, id) > ROW(...)
--   with no Sort node in the plan.

-- Mixed directions. The index matches ORDER BY total DESC, id ASC column by column.
CREATE INDEX orders_tenant_total_desc_id_idx
    ON orders (tenant_id, total DESC, id ASC);

-- A row comparison cannot express DESC on one column and ASC on another,
-- so expand the predicate by hand. The cursor is (total, id) of the last row seen.
SELECT id, created_at, total
FROM orders
WHERE tenant_id = 42
  AND (total < 199.00 OR (total = 199.00 AND id > 98765))
ORDER BY total DESC, id ASC
LIMIT 50;

Keyset pagination for a tenant-filtered list: a composite index led by the equality column, a row-comparison cursor checked with EXPLAIN, and a hand-expanded predicate with a per-column-direction index for ORDER BY total DESC, id ASC.

Designing the API cursor and handling counts

The cursor carries the last row's sort-key values, and clients should treat it as opaque. A common encoding is base64url-encoded JSON holding a version number, the key values (for example created_at and id), and a hash of the sort and filter parameters. On each request, recompute that hash from the incoming query and reject a mismatch. Without that check, a cursor issued for status=open sorted ascending can be replayed against a different query and silently skip or repeat rows.

Base64 is an encoding, not protection. Treat the decoded cursor as untrusted input. Require every field, parse timestamps and integers strictly, and pass values only as bind parameters. If forged positions matter, for example to probe rows a user cannot see, add an HMAC over the payload and verify it before parsing anything else.

The version field lets you change sort keys later. When an outdated version arrives, return a clear 400 that tells the client to restart from the first page instead of misreading the payload.

For backward pagination, flip the comparison (> becomes <) and every ORDER BY direction, fetch the rows, and reverse them in application code. Three bugs are common here: forgetting to reverse the rows, building the next cursor from the wrong boundary row, and applying the n+1 probe to the wrong end.

Avoid COUNT(*) on large filtered sets, because it visits every matching row. Instead, fetch LIMIT n+1 and set has_next_page when the extra row appears. If a total is required, use the planner's row estimate from EXPLAIN, or count over a subquery with LIMIT 1001 and display "1,000+".

Copyimport base64, hashlib, hmac, json
from datetime import datetime

SECRET = b"replace-with-key-from-secret-store"  # placeholder; load from a secret store
CURSOR_VERSION = 2  # bump whenever the sort keys change

def query_hash(filters: dict, sort: str) -> str:
    # Canonical JSON, so the same query always produces the same hash
    canon = json.dumps({"f": filters, "s": sort}, sort_keys=True, separators=(",", ":"))
    return hashlib.sha256(canon.encode()).hexdigest()[:16]

def encode_cursor(row: dict, filters: dict, sort: str) -> str:
    payload = json.dumps({"v": CURSOR_VERSION, "q": query_hash(filters, sort),
                          "created_at": row["created_at"].isoformat(), "id": row["id"]},
                         separators=(",", ":")).encode()
    sig = hmac.new(SECRET, payload, hashlib.sha256).digest()  # 32 bytes
    return base64.urlsafe_b64encode(sig + payload).decode().rstrip("=")

class InvalidCursor(ValueError):
    """Map to HTTP 400 and tell the client to restart from the first page."""

def decode_cursor(token: str, filters: dict, sort: str) -> tuple[datetime, int]:
    try:
        raw = base64.urlsafe_b64decode(token + "=" * (-len(token) % 4))
    except ValueError:
        raise InvalidCursor("malformed cursor")
    sig, payload = raw[:32], raw[32:]
    # Verify the signature before trusting or parsing anything in the payload
    expected = hmac.new(SECRET, payload, hashlib.sha256).digest()
    if not hmac.compare_digest(sig, expected):
        raise InvalidCursor("bad signature")
    data = json.loads(payload)
    if data.get("v") != CURSOR_VERSION:
        raise InvalidCursor("cursor version expired; restart from first page")
    if data.get("q") != query_hash(filters, sort):
        raise InvalidCursor("cursor was issued for a different query")
    # Strict typing: these values are passed only as bind parameters
    if type(data.get("id")) is not int or not isinstance(data.get("created_at"), str):
        raise InvalidCursor("bad key types")
    return datetime.fromisoformat(data["created_at"]), data["id"]

Encoding and decoding an opaque base64url cursor with an HMAC signature, a version number and a hash of the filter and sort parameters, so tampered, outdated or replayed cursors are rejected before any value reaches the query.

Consistency under concurrent writes

OFFSET pagination defines a page by position, so any write that lands before the current position moves the window. Suppose a client has read rows 1–20 of a feed sorted newest first. If five rows are then inserted at the head, OFFSET 20 now starts at what used to be row 16, and the client sees those five rows again. Deletes do the opposite. Remove three rows from the first page and the next request skips three rows the client never saw. Under steady write traffic this happens on almost every page, and nothing in the response tells the client it happened.

Keyset pagination defines a page by value: "rows where (created_at, id) is less than the last pair I saw." Inserts and deletes elsewhere in the table do not change which rows satisfy that predicate. For append-mostly data this gives stable, duplicate-free iteration.

Keyset is not a snapshot, though. Two anomalies remain:

• Rows inserted behind the cursor are never seen. In a newest-first scan, a new row sorts ahead of the cursor, so the current iteration never reaches it.
• Sort-key updates move rows. If the sort key is updated_at, editing a row can move it past the cursor, so the client misses it. It can also move the row ahead of the cursor, so the client sees it twice.

True point-in-time iteration requires every page to read from a single snapshot. That means holding a long-lived REPEATABLE READ transaction open, or exporting a snapshot and importing it into each page's transaction. Either approach has costs. While the snapshot is held, vacuum cannot remove dead tuples newer than it, so table bloat grows across the whole database. The approach also ties a stateless API to server-side session state.

When to use each approach, and when to avoid keyset

The choice depends on the result set's size and what users need to do with it.

Avoid keyset when the product needs numbered pages or a "last page" jump. A cursor only knows its current position, so it cannot compute page 40.

Keyset also fits poorly with sorting by arbitrary columns. Each sort order needs its own composite index. Every insert and update then writes to all of those indexes, which adds write amplification and storage cost.

Keyset only performs well when the predicate can use the index. Several patterns break that:

• Mixed sort directions. These rule out a simple row comparison unless the index matches those directions.
• Functions on the sort column. These require a matching expression index.
• Expanded OR predicates. The planner may turn these into a full scan.
• Highly selective filters. An index walk in sort order may read many rows to find a handful of matches.

If keyset doesn't fit, there are three alternatives:

• Precompute page boundaries periodically and store the boundary keys.
• Cap the offset and require tighter filters beyond the cap.
• Change the UX so search replaces deep browsing.

Impact on development cost and engineering time

The costs of keyset pagination show up early in the work, while the savings come later as the data grows.

Costs:

• Client migration. Switching an existing endpoint from ?page=N to cursors is a breaking change. Every consumer has to change how it loops, and you have to support both contracts during a deprecation window.
• Debuggability and caching. An opaque cursor cannot be read in a log line or a support ticket the way ?page=3 can. CDN and client caches also get fewer shared keys, because two users rarely request the same cursor.
• Index cost. Each sort order you support needs its own composite index. That adds write overhead, storage, and another index to watch for bloat.

Savings:

• Fewer incidents caused by a deep-page query saturating the database.
• Less effort spent building rate limits to stop scrapers that walk to page 50,000.
• Sync and export jobs whose runtime grows linearly with the table instead of quadratically.

Illustrative estimate (assumed numbers, not a benchmark): A full export of N rows with page size P reads about N²/(2P) rows using OFFSET, but about N rows using keyset. With an assumed N = 10,000,000 and P = 1,000, that is roughly 5×10¹⁰ rows versus 10⁷, about 5,000 times more work for OFFSET.

A staged rollout keeps the cost manageable:

1. Cap OFFSET on existing endpoints. This is non-breaking for nearly all real users.
2. Use keyset for new endpoints and for internal export jobs.
3. Keep OFFSET wherever the product genuinely needs jump-to-page navigation.

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 vs offset pagination performance, 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.
• Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

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.
• Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
• 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.