Cursor vs Offset Pagination: Performance and Consistency

Q: Why offset pagination degrades: cost and consistency?

The core problem is that OFFSET gives the database no shortcut. With an index on the sort column, Postgres can walk the index in order, but it cannot jump to position 1,000,000. It has to produce every row before that position, pass each one up to the Limit node, and discard it. Only then does it return the 20 rows you asked for. Total work is therefore roughly O(offset + limit), and the offset term dominates. EXPLAIN (ANALYZE) shows this directly. Look at the index scan node under Limit. For OFFSET 0 LIMIT 20, its actual row count is about 20.

Q: 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.

What offset and cursor pagination actually do

Offset pagination asks the database for rows by position: sort the result, skip the first k rows, return the next n. In SQL that is LIMIT n OFFSET k, and in an API it usually surfaces as ?page=7&per_page=50 or ?offset=300&limit=50. The client can jump to any page directly, which makes it a random-access interface.

Cursor pagination, usually implemented as keyset pagination, asks for rows by value. The client sends back the sort key of the last row it saw, and the server returns the next n rows whose key comes after that value, for example WHERE (created_at, id) > ($1, $2). There is no notion of "page 7". You can only move forward (or backward) from a known point, which makes it a sequential-traversal interface.

The argument of this article is that offset is not wrong. It is a random-access tool that gets used for workloads that are sequential by nature, such as infinite-scroll feeds, incremental sync endpoints, and bulk export jobs. Those workloads walk the whole result set in order, often while it is changing, and that is where offset's position-based model breaks down.

This matters most to backend and platform engineers who own public APIs, mobile sync endpoints, webhooks backfills, and data exports. The choice is hard to change later. Once third-party clients store page numbers, build UIs around "jump to page", or write loops that increment offset, switching to opaque cursors is a breaking change to the API contract rather than an internal refactor.

Why offset pagination degrades: cost and consistency

The core problem is that OFFSET gives the database no shortcut. With an index on the sort column, Postgres can walk the index in order, but it cannot jump to position 1,000,000. It has to produce every row before that position, pass each one up to the Limit node, and discard it. Only then does it return the 20 rows you asked for. Total work is therefore roughly O(offset + limit), and the offset term dominates.

EXPLAIN (ANALYZE) shows this directly. Look at the index scan node under Limit. For OFFSET 0 LIMIT 20, its actual row count is about 20. For OFFSET 1000000 LIMIT 20, the plan shape is identical, but the same node reports about 1,000,020 actual rows. Both queries return 20 rows, but the deep one does far more work to get them.

Illustrative estimate (assumed numbers, not a benchmark). Assume 200-byte rows and 20 rows per page.

Data touched ≈ (offset + limit) × row width
Page 1: 20 × 200 B ≈ 4 KB
Page 50,001 (offset 1,000,000): 1,000,020 × 200 B ≈ 200 MB of tuples visited

Offsets also drift when the data changes between requests. Take rows A, B, C, D, E with a page size of 2:

Insert: Page 1 returns A, B. Someone then inserts A0, which sorts first. Page 2 (offset 2) now returns B, C, so B appears twice.
Delete: Page 1 returns A, B. Someone then deletes A. Page 2 (offset 2) now returns D, E, so C is never returned.

You could hold one transaction or snapshot open for the whole traversal so every page sees the same data. That pins a connection per client for as long as the traversal lasts. It also holds back the xmin horizon, which stops vacuum from removing dead tuples across the database. For public APIs, that cost is rarely worth it.

-- Reproducible setup: 2M rows of roughly 200 bytes each, plus an index on the sort key.
CREATE TABLE events (
  id         bigint PRIMARY KEY,
  created_at timestamptz NOT NULL,
  payload    text NOT NULL
);

INSERT INTO events (id, created_at, payload)
SELECT g, now() - g * interval '1 second', repeat('x', 180)
FROM generate_series(1, 2000000) AS g;

CREATE INDEX events_created_at_id_idx ON events (created_at, id);
ANALYZE events;

-- Shallow page. In the output, find the Index Scan node directly under Limit.
-- Its "actual ... rows=" should be about 20.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, payload
FROM events
ORDER BY created_at, id
LIMIT 20 OFFSET 0;

-- Deep page. The query still returns 20 rows, but the scan node under Limit
-- should report about 1,000,020 actual rows. Limit discards the first 1,000,000.
-- BUFFERS shows the extra pages touched to produce rows that are thrown away.
-- The planner may pick Seq Scan + Sort here instead of the index. In that case
-- the scan reads every row, which is worse, and the point still holds.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, payload
FROM events
ORDER BY created_at, id
LIMIT 20 OFFSET 1000000;

A reproducible Postgres setup with shallow and deep OFFSET queries under EXPLAIN (ANALYZE, BUFFERS), showing where to read the rows produced and then discarded below the Limit node.

How keyset pagination works in Postgres

Keyset pagination swaps "skip N rows" for "start after this row." The query becomes WHERE (sort_key) > last_seen ORDER BY sort_key LIMIT n. With a B-tree index on sort_key, Postgres descends the tree to last_seen and reads the next n entries. Page 5,000 costs about the same as page 1.

The sort key must be unique and totally ordered. Otherwise rows that share a value get skipped or repeated at page boundaries. Add a tiebreaker such as (created_at, id) and compare using row values: (created_at, id) > ($1, $2). Back it with a composite index on (created_at, id). Postgres treats that row comparison as a single index condition. The expanded form, created_at > $1 OR (created_at = $1 AND id > $2), has the same meaning but is easy to get wrong. The planner may also turn it into a filter or a bitmap scan instead of a clean range seek.

Run EXPLAIN on both approaches and the plans differ:

Deep OFFSET: a Limit node over an Index Scan that reads and discards every skipped row.
Keyset: the row comparison shows up under Index Cond, and the Limit stops after n rows.

DESC works the same way using <, because a B-tree can be scanned backward. Mixed directions, such as created_at DESC, id ASC, break row comparison, which assumes all columns sort the same way. You have three options: write the expanded predicate, create an index whose column directions match the sort, or negate a numeric column so the directions line up.

NULLs fall outside normal comparison. The options are:

Declare sort columns NOT NULL.
Apply the same COALESCE in both the index expression and the query.
Paginate NULLs as a separate final segment.

-- Sort key (created_at, id) is unique and totally ordered; both columns NOT NULL.
CREATE TABLE events (
  id         bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  payload    jsonb
);
CREATE INDEX events_created_id_idx ON events (created_at, id);

-- Deep OFFSET: walks and discards 100000 index entries before returning 50.
EXPLAIN SELECT * FROM events ORDER BY created_at, id LIMIT 50 OFFSET 100000;
-- Plan shape (abbreviated, costs omitted):
--   Limit
--     ->  Index Scan using events_created_id_idx on events

-- Keyset: $1, $2 are created_at and id from the last row of the previous page.
-- The row comparison becomes one Index Cond, so the scan starts at that key.
PREPARE next_page(timestamptz, bigint) AS
  SELECT * FROM events
  WHERE (created_at, id) > ($1, $2)
  ORDER BY created_at, id
  LIMIT 50;
EXPLAIN EXECUTE next_page('2026-10-01 12:00:00+00', 48213);
-- Plan shape (abbreviated, costs omitted):
--   Limit
--     ->  Index Scan using events_created_id_idx on events
--           Index Cond: (ROW(created_at, id) > ROW(...))

-- DESC on all columns: flip to < and scan the same index backward.
--   WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC

-- Mixed directions (newest first, id ascending): row comparison cannot express
-- this, so use an index with matching directions plus the expanded predicate.
CREATE INDEX events_created_desc_id_idx ON events (created_at DESC, id ASC);
SELECT * FROM events
WHERE created_at < $1 OR (created_at = $1 AND id > $2)
ORDER BY created_at DESC, id ASC
LIMIT 50;

A composite index on (created_at, id) lets the row-value keyset predicate appear as a single Index Cond, while deep OFFSET still scans and discards every skipped row; mixed sort directions need a direction-matched index and the expanded predicate.

Implementing cursors in a REST API

Treat the cursor as an opaque token, not a parameter clients construct. A practical layout is a base64-encoded payload containing a format version, the sort key values of the last row returned (for example created_at and id), the sort direction, and a hash of the active filters. The version field lets you change the encoding later without breaking clients mid-scroll. Opacity keeps clients from depending on your column names.

Base64 is not protection. Sign the payload with an HMAC, or store cursor state server-side and hand out a random key. Otherwise clients can forge positions or probe internals. On each request, recompute the filter hash and return a 400 if it differs. A cursor issued for status=open means nothing under status=closed.

Return next_cursor (and prev_cursor if you support it) in the response body, a Link header, or both. To set has_more, fetch limit + 1 rows. If the extra row exists, drop it and emit a cursor. This avoids a count query entirely.

For backward pagination, flip the comparison (< instead of >) and the ORDER BY direction, then reverse the rows in application code before responding.

Decide what happens with a stale cursor. If the anchor row was deleted, continuing from the encoded values works because the comparison doesn't need the row to exist. If the sort order or schema changed, reject the old version with a clear error code.

Finally, clamp limit to a server-side maximum so a single request can't scan an unbounded range.

import base64, binascii, hashlib, hmac, json

SECRET = b"placeholder-load-from-secret-store"  # never hardcode a real key
MAX_LIMIT, VERSION = 100, 2  # bump VERSION when sort order or schema changes

def filter_hash(filters):
    # Canonical JSON so key order does not change the hash
    return hashlib.sha256(json.dumps(filters, sort_keys=True).encode()).hexdigest()[:16]

def encode_cursor(key, direction, filters):
    payload = json.dumps({"v": VERSION, "k": key, "d": direction, "f": filter_hash(filters)}).encode()
    sig = hmac.new(SECRET, payload, hashlib.sha256).digest()  # 32 bytes, prepended
    return base64.urlsafe_b64encode(sig + payload).decode()

def decode_cursor(token, filters):
    try:
        raw = base64.urlsafe_b64decode(token.encode())
    except (binascii.Error, ValueError):
        raise ValueError("invalid_cursor")  # map every ValueError to HTTP 400
    sig, payload = raw[:32], raw[32:]
    if not hmac.compare_digest(sig, hmac.new(SECRET, payload, hashlib.sha256).digest()):
        raise ValueError("invalid_cursor")  # forged or truncated
    data = json.loads(payload)
    if data["v"] != VERSION:
        raise ValueError("cursor_expired")  # issued under an older sort/schema
    if data["f"] != filter_hash(filters):
        raise ValueError("cursor_filter_mismatch")  # e.g. issued for status=open
    return data

def fetch_page(cur, status, limit, token=None):
    limit, filters = max(1, min(limit, MAX_LIMIT)), {"status": status}  # clamp page size
    c = decode_cursor(token, filters) if token else None
    back = bool(c) and c["d"] == "prev"
    op, order = ("<", "DESC") if back else (">", "ASC")  # backward: flip both
    sql, params = "SELECT created_at, id FROM orders WHERE status = %s", [status]
    if c:  # compares values only, so a deleted anchor row is fine
        sql += f" AND (created_at, id) {op} (%s, %s)"; params += c["k"]
    cur.execute(sql + f" ORDER BY created_at {order}, id {order} LIMIT %s", params + [limit + 1])
    rows = cur.fetchall()
    has_more, rows = len(rows) > limit, rows[:limit]  # extra row means more data, no COUNT
    if back: rows.reverse()  # return in ascending order either way
    return rows, has_more

An HMAC-signed, versioned, base64 cursor that rejects forged, outdated, or filter-mismatched tokens, with a keyset query that clamps the page size, fetches limit+1 rows to set has_more, and pages backward by flipping the comparison and ORDER BY, then reversing the rows.

Total counts are a separate problem

Clients often ask for "page 3 of 412," and that number is usually what makes pagination slow. It has nothing to do with offset versus cursor.

Postgres does not store a row count for a table. Under MVCC, different transactions can see different sets of rows, so count(*) has to check visibility for every row that matches the filter. An index-only scan helps only when the visibility map is current, and even then it reads every matching index entry. The cost grows with the size of the result set no matter how you fetch pages, so the count requirement shouldn't decide your pagination strategy. Handle it as its own design choice. You have three reasonable options.

Omit it. Return next_cursor and a has_more flag. To compute has_more, fetch limit + 1 rows and check whether the extra one came back. Infinite scroll and most API consumers don't need anything else.

Cap it. Count up to a fixed ceiling, and if you hit it, show "1,000+": SELECT count(*) FROM (SELECT 1 FROM orders WHERE customer_id = $1 LIMIT 1001) AS t; The work is bounded by the cap, not by the table size.

Estimate it. For an unfiltered table, read the planner's statistics: SELECT reltuples::bigint FROM pg_class WHERE oid = 'orders'::regclass; This value is only as fresh as the last VACUUM or ANALYZE, and it can be meaningless on a table that has never been analyzed. For filtered queries, you can read the row estimate from EXPLAIN, but it can be badly off on skewed data. In both cases, label the value as approximate in the response, for example with total_estimate rather than total, so clients don't build exact logic on top of it.

-- Option 1: omit the count. Fetch limit + 1 rows (here 50 + 1).
-- If 51 rows come back, has_more = true; return only the first 50.
SELECT id, created_at, total_cents
FROM orders
WHERE customer_id = $1
  AND (created_at, id) < ($2, $3)      -- keyset cursor from the previous page
ORDER BY created_at DESC, id DESC
LIMIT 51;

-- Option 2: capped count. The work is bounded by the inner LIMIT.
-- A result of 1001 means "more than 1,000". Render it as "1,000+".
SELECT count(*) AS capped_total
FROM (
  SELECT 1
  FROM orders
  WHERE customer_id = $1
  LIMIT 1001
) AS t;

-- Option 3a: planner estimate for the whole, unfiltered table.
-- reltuples is refreshed by VACUUM/ANALYZE (including autovacuum), so it lags writes.
-- It is -1 (PG 14+) or 0 if the table has never been vacuumed or analyzed.
SELECT reltuples::bigint AS total_estimate
FROM pg_class
WHERE oid = 'orders'::regclass;

-- Option 3b: planner row estimate for a filtered query.
-- EXPLAIN without ANALYZE plans the query but does not run it, so it is cheap.
-- Read "Plan Rows" from the top plan node. It can be far off on skewed data.
EXPLAIN (FORMAT JSON)
SELECT 1 FROM orders WHERE status = 'pending';

This example shows three ways to handle totals: limit + 1 for has_more, a count capped by an inner LIMIT, and planner row estimates from pg_class.reltuples and EXPLAIN. Return the estimates as approximate values such as total_estimate.

When to use each strategy and when to avoid keyset

Offset is the right choice when the table is small or bounded and users need to jump to a specific page. An admin view with a few thousand rows and "page 12 of 40" navigation fits this. The deep-page cost never materialises, and the UI requirement is real. Keyset fits infinite scroll, sync and export jobs, and public APIs. In each of those, clients walk forward through arbitrarily deep data and you cannot control how far they go. A hybrid keeps keyset traversal but precomputes coarse jump points, such as the sort key at every 10,000th row, refreshed periodically. Users can then land near a region and page from there.

Avoid keyset when the product demands arbitrary "go to page 37". Also avoid it when users can sort by any column. Each sortable combination needs its own composite index, and every index adds write overhead and storage. When the dataset is small enough that offset never scans meaningfully, keyset only adds complexity.

Keyset guarantees stable traversal, not a snapshot. If a row's sort key changes mid-walk, the row moves relative to the cursor. It can be skipped or returned twice. For sync, sort on an immutable column, or on an updated_at or sequence column that only ever increases, so a changed row reappears ahead of the cursor instead of behind it. Note that an updated_at from a transaction that commits late can still land behind a cursor that has already passed it.

	Offset	Keyset	Hybrid
Depth cost	Grows with offset	Constant per page	Constant after jump
Consistency under writes	Skips and duplicates on insert/delete	Stable unless sort key changes	Same as keyset
Jump-to-page	Exact	Not supported	Approximate
Index requirements	Helpful for sort	Composite index matching sort + tiebreaker	Same as keyset, plus jump-point storage
Implementation complexity	Low	Moderate	High
Caching	Pages shift on writes	Cursor pages cache well	Jump points need refresh

Impact on development cost and engineering time

Offset pagination is cheaper to build. A page and limit parameter maps directly onto LIMIT/OFFSET. Responses are easy to cache by URL, and an engineer debugging a report can read a request log and know exactly which slice of data a client asked for. Nothing about the format needs to be versioned.

Keyset pagination costs more up front, and some of that cost is ongoing. Every supported sort and filter combination needs an index whose column order matches the sort key plus its tiebreaker. Adding a new sortable field therefore becomes a schema change rather than a query tweak. Cursors also need encoding, validation against tampering or stale sort keys, and a version marker so the format can change later. Opaque cursors make ad-hoc debugging slower, because you have to decode a token before you can see where a client is. Once clients store those tokens, the cursor format becomes a compatibility commitment. Moving an existing public API from page numbers to cursors breaks every client that computes page numbers or jumps to page N. Ship it as a new API version or a new parameter, and run both alongside each other through a published deprecation window.

The time keyset saves shows up later. It reduces three kinds of work:

Incidents: deep-page scans no longer surface as slow-query alerts as tables grow.
Data bugs: sync and export consumers stop reporting duplicate or missing records caused by concurrent writes.
Capacity planning: per-page cost stays roughly constant regardless of table size.

For new endpoints built for sequential traversal, that trade usually favors keyset as the default.

Alternatives to consider

Keyset is not the only way to avoid deep offsets. Each alternative below fixes a specific weakness of offset, and each adds its own cost.

Deferred join over offset

If clients need numbered pages, run the offset inside a subquery that selects only primary keys from a covering index, then join back to fetch full rows for that page. Skipped rows are read as index entries, not heap tuples, so each skipped row costs less. The cost is still linear in the offset, and concurrent writes still cause duplicates and gaps. It works as a stopgap for an existing page-number API that cannot change its contract.

Server-side database cursors

Postgres can hold an open cursor and return rows with repeated FETCH calls from a consistent snapshot. The catch is state. A cursor without WITH HOLD exists only inside its transaction. A held cursor materializes its result when the transaction commits. In both cases, spreading the cursor across HTTP requests means pinning a connection to one client, which breaks transaction-mode connection pooling and stateless load balancing. Use this for internal batch jobs, not public APIs.

Snapshot result sets

Run the query once, store the ordered IDs under an expiring token, and serve pages from that list. You get stable page numbers and a frozen view of the data. In exchange, you pay for storage, expiry logic, and results that go stale.

Change feeds and bulk export

Clients that paginate only to sync data are better served by an updated_after feed, which is effectively keyset on (updated_at, id), or by an asynchronous export job. With either, the client is no longer walking the full dataset one page at a time.

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

Cursor vs Offset Pagination: Performance and Consistency in REST APIs

Implementing cursors in a REST API

Total counts are a separate problem

When to use each strategy and when to avoid keyset

Impact on development cost and engineering time

Alternatives to consider

Deferred join over offset

Server-side database cursors

Snapshot result sets

Change feeds and bulk export

How OneEnv can help

Saves time

Saves cost

Sources

Cursor Pagination for REST APIs: Keyset vs Offset at Scale

REST API Pagination, Filtering and Sorting: One Contract

REST API Design: Resources, Status Codes and Consistency

What offset and cursor pagination actually do

Why offset pagination degrades: cost and consistency

How keyset pagination works in Postgres

Implementing cursors in a REST API

Total counts are a separate problem

When to use each strategy and when to avoid keyset

Impact on development cost and engineering time

Alternatives to consider

Deferred join over offset

Server-side database cursors

Snapshot result sets

Change feeds and bulk export

How OneEnv can help

Saves time

Saves cost

Sources

Keep reading

Cursor Pagination for REST APIs: Keyset vs Offset at Scale

REST API Pagination, Filtering and Sorting: One Contract

REST API Design: Resources, Status Codes and Consistency