What is the difference between cursor pagination vs offset?

Offset pagination asks the database for a slice by position: ORDER BY created_at, id LIMIT 50 OFFSET 5000. Clients send a page number or offset, and the server converts it into a row count to skip. Keyset pagination, which APIs usually expose as cursor pagination, asks for a slice by value. It remembers the sort key of the last row the client saw and fetches rows that come after it: WHERE (created_at, id) > ($1, $2) ORDER BY created_at, id LIMIT 50. The cursor is that last key, encoded in an opaque token.

The difference matters for API pagination design because position and value behave differently as tables grow and change. To honour an offset, Postgres must produce and discard every skipped row, and positions shift whenever rows are inserted or deleted ahead of the client. A keyset query descends an index directly to the boundary value, so page 1,000 costs about the same as page 1. Because it is anchored to a value and not a position, it does not drift when rows move around it. The trade-off is that you lose arbitrary page jumps and take on cursor design work.

Dimension Offset (LIMIT/OFFSET) Keyset / cursor
Cost with depth Grows linearly; skipped rows are still read Roughly constant per page with a matching index
Stability under writes Rows skipped or duplicated as positions shift Stable; anchored to last seen key
Random access Jump to any page number Next/previous only
Total counts Natural fit, though COUNT(*) is its own cost Not implied; must be computed separately if needed
Index requirements Helps, but cannot avoid scanning skipped rows Required on the full, unique sort key
Implementation effort Low Higher: tie-breakers, cursor encoding, direction handling

Why is a large OFFSET slow in Postgres?

An index on the ORDER BY column lets Postgres read rows in sorted order, but it does not let Postgres jump to row 100,000. A B-tree has no positional lookup: its pages do not record how many entries sit beneath them, so the only way to find the 100,001st entry is to walk the first 100,000. MVCC adds more work. Each index entry may point to a row version that is invisible to your snapshot, so Postgres usually visits the heap to check visibility before it can count a row toward the offset. The Limit node pulls rows from its child, throws away the first N, and returns the next page. Page N therefore costs roughly in proportion to N, and the index only removes the sort step.

You can confirm this with EXPLAIN (ANALYZE, BUFFERS). Run the same query twice, once with OFFSET 0 and once with a deep offset. Compare these lines in the plan:

  • Limit node, actual rows: the page size in both runs. This is what the client receives.
  • Index Scan node (under the Limit), actual rows: roughly offset plus limit. This is how many rows the executor produced.
  • Buffers: shared hit=… read=…: grows with the offset. A large read count means the deep page also caused disk I/O.

Illustrative estimate, not measured: with a page size of 20, the shallow query might touch a handful of buffers. At OFFSET 100000 the scan produces about 100,020 rows and touches thousands of buffers, and the client still gets only 20 rows.

-- Assumes an index on the sort key, e.g.:
-- CREATE INDEX orders_created_at_id_idx ON orders (created_at, id);

-- Shallow page: the Index Scan should stop after about 20 rows.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, created_at
FROM orders
ORDER BY created_at, id
LIMIT 20 OFFSET 0;

-- Deep page: same query, same index, much larger offset.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, created_at
FROM orders
ORDER BY created_at, id
LIMIT 20 OFFSET 100000;

-- What to compare in the deep plan (illustrative shape, not measured output):
--
-- Limit (actual ... rows=20 loops=1)
--   Buffers: shared hit=... read=...
--   -> Index Scan using orders_created_at_id_idx on orders
--        (actual ... rows=100020 loops=1)
--        Buffers: shared hit=... read=...
--
-- Limit rows=20             -> rows the client receives
-- Index Scan rows=~100020   -> rows produced, then mostly discarded
-- Buffers (hit + read)      -> grows with OFFSET; read > 0 means disk I/O
--
-- If the plan shows a Sort or Seq Scan instead of an Index Scan, the
-- planner judged the index too costly at this offset. The deep page is
-- still slow, because every skipped row is still produced.

Running EXPLAIN (ANALYZE, BUFFERS) on a shallow and a deep OFFSET shows the Limit node returning 20 rows in both cases, while the Index Scan below it produces roughly offset plus limit rows and touches more buffers as the offset grows.

Why do offset pages skip or duplicate rows under concurrent writes?

The cause is that OFFSET counts positions, not rows. Each page request is a separate query against whatever the table looks like at that moment. Page 2 means "skip the first N rows that exist now." Nothing ties it to the rows the client saw on page 1. A delete or insert that lands before the client's position changes which row sits at position N.

Take a page size of 5, ordered by id. The client fetches page 1 (LIMIT 5 OFFSET 0) and gets ids 1–5. Before it asks for page 2, another transaction deletes id 3:

Before: [ 1 2 3 4 5 ] [ 6 7 8 9 10 ] After: [ 1 2 4 5 6 ] [ 7 8 9 10 11 ]

Every row after id 3 moves up one position, and id 6 slides into page 1. The client requests OFFSET 5 and gets ids 7–11. It never sees id 6, and no error tells it anything went wrong. Every deleted row before the client's position skips one row.

Inserts cause the opposite problem. Suppose a feed is ordered by created_at DESC and a new row arrives while the user reads page 1. The new row takes position 1 and pushes the old position-5 row onto page 2. The client then sees that row twice. In a UI that shows up as a repeated item. In a sync job it can mean double-processing.

Repeatable-read isolation doesn't help, because each page is normally its own request and its own snapshot. The defect is in how page positions are defined, not in Postgres. A position only means something relative to a fixed set of rows, and a live table is never fixed.

DELETE  (ORDER BY id, LIMIT 5)
┌──────────────────┐     ┌────────────────────┬─────────────────┐
│ page 1: OFFSET 0 │────►│ pos  1  2  3  4  5 │  6  7  8  9 10  │
└──────────────────┘     │ id   1  2  3  4  5 │  6  7  8  9 10  │
  sees 1 2 3 4 5         └────────────────────┴─────────────────┘
                                   │ other txn: DELETE id 3
                                   ▼
┌──────────────────┐     ┌────────────────────┬─────────────────┐
│ page 2: OFFSET 5 │────►│ pos  1  2  3  4  5 │  6  7  8  9 10  │
└──────────────────┘     │ id   1  2  4  5  6 │  7  8  9 10 11  │
  sees 7 8 9 10 11       └────────────────────┴─────────────────┘
                                            ▲
  id 6 shifted onto page 1, never returned ─┘

INSERT  (ORDER BY created_at DESC, LIMIT 5)
┌──────────────────┐     ┌────────────────────┬─────────────────┐
│ page 1: OFFSET 0 │────►│ row  A  B  C  D  E │  F  G  H  I  J  │
└──────────────────┘     └────────────────────┴─────────────────┘
  sees A B C D E                   │ other txn: INSERT N (newest)
                                   ▼
┌──────────────────┐     ┌────────────────────┬─────────────────┐
│ page 2: OFFSET 5 │────►│ row  N  A  B  C  D │  E  F  G  H  I  │
└──────────────────┘     └────────────────────┴─────────────────┘
  sees E F G H I  ──► E was already shown on page 1: duplicate

OFFSET counts positions in the table as it exists at query time, so a delete ahead of the client's position makes page 2 skip a row and an insert ahead of it makes page 2 repeat one.

How does keyset pagination work in Postgres?

Keyset pagination defines a position by the values of the last row the client saw. For the next page, the query keeps rows that sort after those values: WHERE (sort_col, id) > ($1, $2) ORDER BY sort_col, id LIMIT n, backed by a composite B-tree index on (sort_col, id).

Postgres treats the row-value comparison as a single condition on the index key. It can descend the tree directly to ($1, $2) and read the next n entries in order. Nothing before the cursor is read and then discarded, so page 5,000 costs roughly the same as page 2. Writing the predicate as separate AND clauses gives the planner a weaker bound and does not express the same ordering, so use the row form.

The sort key must be total. If sort_col has duplicates, ties at a page boundary are ambiguous, and rows can be skipped or repeated. Appending a unique tiebreaker such as the primary key removes that ambiguity.

NULLs need a deliberate choice. A row comparison that reaches a NULL element yields NULL rather than true or false, so those rows silently drop out. You can declare the column NOT NULL, exclude NULLs explicitly, or page through the NULL group as a separate segment that matches your NULLS FIRST/NULLS LAST ordering.

Mixed sort directions break the row comparison, because it assumes every column sorts the same way. You can expand it into OR'd predicates, such as created_at < $1 OR (created_at = $1 AND id > $2). Alternatively, keep the directions consistent and add a matching index, for example (created_at DESC, id DESC).

To page backward, flip the comparison to <, reverse every ORDER BY direction, fetch n rows, and reverse them in the application before returning them.

-- created_at is NOT NULL so row comparisons never hit a NULL element.
CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  payload    jsonb
);

-- The index matches the sort order exactly, with id as the unique tiebreaker.
CREATE INDEX events_created_id_desc ON events (created_at DESC, id DESC);

-- First page, newest first.
SELECT id, created_at FROM events
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page. $1, $2 are created_at and id of the LAST row on the current page.
-- Both columns sort DESC, so one row comparison maps to a single index seek.
SELECT id, created_at FROM events
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Previous page. $1, $2 are created_at and id of the FIRST row on the current page.
-- Flip the comparison and every ORDER BY direction, then reverse the rows in the app.
SELECT id, created_at FROM events
WHERE (created_at, id) > ($1, $2)
ORDER BY created_at ASC, id ASC
LIMIT 20;

-- Mixed directions (created_at DESC, id ASC) cannot use one row comparison.
-- Expand it into OR'd predicates instead.
SELECT id, created_at FROM events
WHERE created_at < $1
   OR (created_at = $1 AND id > $2)
ORDER BY created_at DESC, id ASC
LIMIT 20;

Keyset pagination on a table with a NOT NULL sort column and an id tiebreaker, showing a matching (created_at DESC, id DESC) index, forward and backward page queries, and the expanded OR predicate needed when the sort directions are mixed.

How should you design cursors and the pagination response contract?

Treat the cursor as an opaque token. A common format is base64-encoded JSON holding the last row's sort values (created_at, id), the sort direction, a hash of the active filters, and a version field. Clients must not parse or build cursors. That leaves you free to change the encoding later, and the version field lets you reject old formats cleanly.

Base64 is not protection. Sign the payload with an HMAC, or store cursor state server-side, so clients cannot forge arbitrary sort values. When a request's filters or sort differ from what the cursor encodes, reject it. Mixing them produces pages that look plausible but are wrong. Return one defined error, such as a 400 with a stable invalid_cursor code, for tampered, mismatched, or expired cursors. Clients then have a single case to handle: restart from the first page.

Response shape

Return items, next_cursor, has_more, and optionally prev_cursor. To set has_more, query LIMIT n+1, return n rows, and check whether the extra row came back. This avoids a second query.

Leave out exact totals by default. Postgres has no stored row count. Under MVCC, count(*) has to check visibility row by row, so it scans the whole table or index and gets slower as the table grows. You have three alternatives:

  • Drop the total.
  • Return an estimate from planner statistics, and label it as an estimate.
  • Compute the exact count only when the client explicitly asks, and rate-limit that call.

A fixed contract with limit+1 has a practical benefit. You stop tuning count queries, and you stop receiving bug reports that "page 40 says 10,000 results but shows 9,987."

import base64, hashlib, hmac, json

SECRET = b"example-cursor-signing-key-load-from-secret-store"  # placeholder, never hardcode
VERSION = 1

class InvalidCursor(Exception):
    """Map to HTTP 400 {\"error\": \"invalid_cursor\"}; client restarts from page 1."""

def params_hash(filters: dict, sort: str) -> str:
    # Bind the cursor to the exact filters and sort it was issued for.
    raw = json.dumps({"f": filters, "s": sort}, sort_keys=True).encode()
    return hashlib.sha256(raw).hexdigest()[:16]

def encode_cursor(last_row: dict, filters: dict, sort: str) -> str:
    body = {"v": VERSION, "k": [last_row["created_at"], last_row["id"]], "p": params_hash(filters, sort)}
    payload = json.dumps(body, separators=(",", ":")).encode()
    sig = hmac.new(SECRET, payload, hashlib.sha256).digest()  # 32 bytes, stops forged sort values
    return base64.urlsafe_b64encode(payload + sig).decode()

def decode_cursor(token: str, filters: dict, sort: str) -> list:
    try:
        raw = base64.urlsafe_b64decode(token.encode())
        payload, sig = raw[:-32], raw[-32:]
        expected = hmac.new(SECRET, payload, hashlib.sha256).digest()
        if not hmac.compare_digest(sig, expected):
            raise InvalidCursor("bad signature")
        data = json.loads(payload)
    except ValueError as exc:  # covers bad base64 and bad JSON
        raise InvalidCursor("malformed") from exc
    # Old format, or a cursor reused with different filters or sort: same single error.
    if data.get("v") != VERSION or data.get("p") != params_hash(filters, sort):
        raise InvalidCursor("version or params mismatch")
    return data["k"]  # [created_at, id] -> WHERE (created_at, id) < (%s, %s)

def build_page(rows: list, limit: int, filters: dict, sort: str) -> dict:
    # rows came from: ... ORDER BY created_at DESC, id DESC LIMIT limit + 1
    has_more = len(rows) > limit  # the extra row only signals that more exist; no COUNT(*)
    items = rows[:limit]
    next_cursor = encode_cursor(items[-1], filters, sort) if has_more else None
    return {"items": items, "next_cursor": next_cursor, "has_more": has_more}

A minimal Python sketch of opaque cursors signed with HMAC, bound to a version and a hash of filters and sort, rejected with a single invalid_cursor error, and paired with a LIMIT n+1 response builder that sets has_more without a count query.

How does it work end to end, from request to response?

The pieces above fit into one request cycle. Here it is for a newest-first list of events:

  1. First request, no cursor. The client sends its filters and a page size. The server clamps the page size to an allowed maximum and checks the sort against its allowlist. It then runs the first-page query with LIMIT n+1, which has no WHERE boundary.
  2. Build the response. The server returns the first n rows. If row n+1 came back, it sets has_more to true. It then encodes the last returned row's (created_at, id) into next_cursor, together with the version and the filter/sort hash, and signs the result.
  3. Next request, with cursor. The client sends next_cursor back unchanged, along with the same filters and sort. The server verifies the signature, version, and parameter hash. If any check fails, it returns invalid_cursor.
  4. Seek, don't skip. The server binds the decoded values into the row comparison, WHERE (created_at, id) < ($1, $2). Postgres descends the (created_at DESC, id DESC) index to that key and reads the next n+1 entries. No rows before the boundary are produced.
  5. Repeat until has_more is false. The client keeps no state except the latest token. The server keeps none at all.

Every page therefore costs one index seek plus n+1 entries, wherever it sits in the table. The position travels inside the cursor, so no server-side session is involved. Any application instance can serve the next page, and a client that crashes mid-traversal resumes by replaying the last cursor it stored.

What does keyset pagination cost you in development and operations?

That simplicity has a price, and most of it lands on product and on whoever maintains the query layer.

Cost Why it happens Mitigation
No random access A cursor only knows its neighbour, so "jump to page 37" has no key to seek from Replace page numbers with next/previous and filters; offer search instead of deep browsing
Index per sort Each sort order needs a matching composite index ending in a unique column, and every index adds write overhead Support a short allowlist of sorts and reject the rest
Not a snapshot Rows inserted behind the cursor are never seen. Updating a sort key can move a row across the boundary, so it repeats or disappears Page exports on immutable keys such as id or created_at
Cursor as contract Clients store cursors and replay them after deploys Version the cursor format and accept old versions for a stated window
Opaque cursors Base64 hides the values, so a bad page is hard to reproduce Log decoded cursors server-side. Validate type, version and sort field as untrusted input
Boundary bugs Ties, NULLs and mixed ASC/DESC need careful row comparisons Test page boundaries explicitly with duplicate and NULL sort values

Boundary bugs deserve the most attention. They only appear when a tie or a NULL lands exactly on a page edge, so ordinary tests and casual QA rarely hit them. Write fixtures with a page size of 2 or 3 and runs of identical sort values. Then assert that concatenating every page returns exactly the full ordered set.

Against those costs, you stop investigating why deep pages time out under load. You also stop chasing "missing row" reports that turn out to be offset drift on a busy table. Both problems recur and are hard to diagnose. Keyset's costs are mostly paid once, at design time.

How can it save engineering time?

Most of the savings come from failures that no longer happen. Engineers then spend less time on investigations, workarounds, and support threads.

  • Fewer performance incidents on deep pages. With offset, a scraper, a sync client, or a user who scrolls far enough sends a query whose cost grows with depth. Fixing that usually means reading plans, adding caps, or tuning statement timeouts. Keyset keeps per-page cost flat, so that class of incident goes away and the query does not need retuning as the table grows.
  • No drift debugging. Reports of rows that are "missing" or "shown twice" are expensive to diagnose because they depend on writes that happened between requests and can't be reproduced afterwards. Keyset removes the position shifting that causes them, which leaves a much smaller set of causes to rule out. Rows whose sort key changed mid-traversal are still a possible cause.
  • Simpler sync and export jobs. When offset pages can skip or repeat rows, consumers need compensating logic: dedupe tables, full re-reads to catch skipped rows, or reconciliation passes. With a cursor, the last token is an exact checkpoint, so a failed job resumes where it stopped and does not start over.
  • No count-query maintenance. The LIMIT n+1 contract answers "is there more?" without count(*). Nobody has to optimise or cache totals, and mismatched page counts stop generating tickets.
  • Stateless servers. The cursor carries the position, so there is no server-side pagination session to store, expire, or replicate.

The time you spend goes up front: picking sort keys, adding matching indexes, writing the cursor codec, and building boundary tests. Illustrative estimate, with assumed inputs: assume a team's offset-based list endpoints cause one deep-page timeout or drift investigation per month, and each investigation takes an engineer about a day. The keyset migration plus its boundary tests is assumed to take one to two weeks. Under those assumptions, the up-front work is recovered within the first year, before counting simpler consumers. Substitute your own incident rate and effort figures; the comparison only holds if those recurring problems actually occur on your tables.

Who should use it?

Keyset pagination fits teams whose list endpoints face growing tables, deep traversal, or concurrent writes:

  • Backend and API engineers who own list endpoints over tables that grow without bound, such as event logs, audit trails, order history, messages, and notifications.
  • Teams building sync, export, backfill, or webhook-replay jobs, and data pipeline owners whose consumers walk an entire table and need exact, resumable checkpoints.
  • Frontend and mobile teams with infinite scroll or "load more" feeds, where users only move forward and new rows arrive while they read.
  • Owners of public or partner APIs. You don't control how deep clients go or how often they poll, so a predictable per-page cost protects the database from client behaviour you can't change.

It is a poor fit when users need page numbers and arbitrary jumps, when result sets are naturally small or bounded, or when the data is static. Offset is simpler in those cases. The next section maps these situations to a recommendation.

When should you use offset, keyset, or a hybrid?

The choice depends on two questions. Do clients need to jump to an arbitrary page? How deep will they go on a table that changes while they read?

Scenario Recommendation Why
Admin table or report with page numbers, a few thousand rows Offset Users need "go to page 40" and a total count. At this size the skipped rows cost little, and occasional drift is tolerable.
Bounded result set, such as one customer's invoices Offset Depth is naturally capped, so the large-offset penalty never shows up.
Activity feed or infinite scroll Keyset Clients only ever ask for "next". Each page costs about the same however far the user scrolls, and concurrent inserts don't shift rows between pages.
Sync, webhook backfill, or API export endpoint Keyset Clients walk the whole table and resume after failures. A cursor makes resumption exact, and the last cursor doubles as a checkpoint.
Search UI with page numbers on a large, busy table Hybrid Allow offset for the first N pages, where people actually click. Beyond that, require a cursor or a narrower filter. This bounds the worst-case scan without removing page numbers.
Small, static lookup list Offset, or no paging Cursor encoding and tie-breaker columns add complexity and buy nothing here.

Avoid keyset when random access is a hard product requirement. Faking it means walking cursors forward, which is slower than the offset it replaces.

Neither keyset nor offset gives a point-in-time view across pages. Rows updated mid-traversal can still appear with newer values. If an export must be consistent with itself, read it inside a single REPEATABLE READ transaction, or materialize the result to a table or file first and page over that.

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 vs offset, 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.