What is the difference between cursor pagination and offset pagination?
Both styles answer "give me the next page," but they define "next" differently. Take an events table with tens of millions of rows and a steady stream of inserts, listed newest first.
Offset pagination describes a page by its position: "skip 40,000 rows, return 50." In SQL that is ORDER BY created_at DESC, id DESC LIMIT 50 OFFSET 40000. Postgres still has to produce and discard the 40,000 skipped rows. If rows are inserted or deleted between requests, every position shifts, so a client can see the same row twice or miss one.
Keyset (cursor) pagination describes a page by the last row the client saw: "return 50 rows that sort after (created_at, id) = ('2026-11-10 09:15:02', 88123)." In SQL that is WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50. With a matching index, Postgres seeks directly to that point. Concurrent writes elsewhere in the table do not move it.
Recommended default: for REST list endpoints, use keyset pagination over a unique, indexed sort key and return it to clients as an opaque cursor. Keep offset for small or bounded result sets where users need to jump to page 7. This matters most to backend and platform engineers who own feeds, sync jobs, exports and public list APIs, because clients there walk deep into the data while it keeps changing.
| Offset | Keyset (cursor) | |
|---|---|---|
| Request shape | ?page=801&per_page=50 |
?cursor=<opaque>&limit=50 |
| Query shape | LIMIT n OFFSET k |
WHERE (key) < last LIMIT n |
| Cost of deep pages | Grows with k |
Roughly constant with an index |
| Stable under writes | No | Yes, relative to the cursor |
| Jump to arbitrary page | Yes | No |
Who should use keyset pagination?
Keyset pagination is for teams whose clients read deep into large, changing tables. In practice that means:
- Backend engineers who own public or partner list APIs. External clients often iterate an entire collection, and they cannot be told to avoid page 5,000. Keyset keeps every page as cheap as the first, and it does not return duplicates or drop rows when data changes mid-iteration.
- Teams building feeds, timelines and infinite scroll. Users only move forward or back from where they are, which is exactly what a cursor supports. New items arriving at the top do not push repeated rows onto the next page.
- Platform and data engineers running sync, export and backfill jobs. These jobs walk a whole table in batches. With offset, each batch costs more than the last. With keyset, each batch costs about the same.
- Mobile and integration client developers. A cursor gives them a single value to persist and resume from after a crash or a lost connection.
It is a poor fit for admin screens over small or filtered sets, reports that need "page 3 of 112" with exact totals, and UIs that must jump to an arbitrary page number. Those cases are covered in the sections on when to avoid keyset and which approach to choose.
How does it work end to end?
A keyset-paginated request cycle has five steps:
- First request. The client calls the endpoint without a cursor, for example
GET /events?limit=50. The server runs the query with only theORDER BYandLIMIT, and no cursor predicate. - Fetch one extra row. The server asks for
LIMIT 51. If 51 rows come back, another page exists. The server drops the extra row and returns 50. - Build the cursor. The server takes the sort-key values of the last row it returned (for example
created_atand the uniqueid), packs them together with the sort and filter parameters, signs the result, and returns it asnext_cursor. - Next request. The client sends the cursor back unchanged. The server verifies it, checks that the sort and filters still match, and decodes the key values.
- Seek. The server adds
WHERE (created_at, id) < ($1, $2)to the same query. Postgres uses the composite index to descend directly to that position and reads the next 51 entries. The loop repeats until the response comes back withoutnext_cursor.
The database never counts or skips rows. Each page is an index seek plus a short range read, so page 1 and page 10,000 cost about the same. The following sections explain why OFFSET behaves differently, how the row-value comparison maps onto the index, and how to build and validate the cursor.
Why does OFFSET get slower and return wrong rows on large tables?
OFFSET does not skip rows. The executor still produces every row up to k + n, passes each through the Limit node, and throws the first k away. An index on the sort key avoids a sort, but Postgres still walks k index entries and usually fetches each heap tuple. B-tree indexes have no positional lookup, so Postgres cannot jump to "row 500,000."
Consider ORDER BY created_at DESC, id DESC LIMIT 50 with a matching composite index, run under EXPLAIN (ANALYZE, BUFFERS). These are illustrative estimates, not benchmarks:
- At
OFFSET 0, the Index Scan node reports about 50 actual rows and a few dozen shared buffer hits. - At
OFFSET 500000, the same plan shape reports about 500,050 actual rows from the Index Scan. The Limit node returns 50. Buffer counts grow by several orders of magnitude, and on a cold cache many of those reads come from disk.
The plan looks the same at both offsets. Only the row and buffer counts show the difference, so check those in EXPLAIN output rather than the plan shape.
Writes shift page boundaries
An offset is a position, not a row identity, so concurrent writes change what each position points to.
- Insert: A client reads page 1 (rows 1–50, newest first). Before it requests page 2, three new rows arrive at the top.
OFFSET 50now starts at what used to be row 48, so the client sees rows 48–50 twice. - Delete: Two rows on page 1 are deleted between requests. Every later row moves up two positions, so the old rows 51–52 now sit at positions 49–50. Page 2 starts at the old row 53, and the client never sees rows 51–52. Nothing reports an error.
-- Setup: a table with a composite index that matches the ORDER BY exactly.
CREATE TABLE events (
id bigserial PRIMARY KEY,
created_at timestamptz NOT NULL,
payload text
);
CREATE INDEX events_created_id_idx ON events (created_at DESC, id DESC);
INSERT INTO events (created_at, payload)
SELECT now() - g * interval '1 second', 'row ' || g
FROM generate_series(1, 2000000) AS g;
ANALYZE events;
-- Shallow page: the Index Scan should stop after about 50 rows.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at FROM events
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 0;
-- Deep page: same query, same plan shape. Compare the Index Scan's
-- "actual rows" and "Buffers: shared hit/read" with the shallow run.
-- The scan produces OFFSET + LIMIT rows, and Limit discards all but 50.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at FROM events
ORDER BY created_at DESC, id DESC
LIMIT 50 OFFSET 500000;
-- Boundary shift: the client fetches page 1 ...
SELECT id FROM events ORDER BY created_at DESC, id DESC LIMIT 50 OFFSET 0;
-- ... another session inserts three newer rows before page 2 is requested ...
INSERT INTO events (created_at, payload)
VALUES (now(), 'new a'), (now(), 'new b'), (now(), 'new c');
-- ... so page 2 begins with the last three ids from page 1 (duplicates).
-- If rows on page 1 were deleted instead, rows would shift up and be skipped.
SELECT id FROM events ORDER BY created_at DESC, id DESC LIMIT 50 OFFSET 50;
Runs the same ORDER BY ... LIMIT 50 query at OFFSET 0 and OFFSET 500000 under EXPLAIN (ANALYZE, BUFFERS) so you can compare row and buffer counts, then shows how an insert between page requests makes page 2 repeat rows from page 1.
How does keyset pagination work in Postgres?
Keyset pagination replaces "skip N rows" with "continue after this row." The client sends back the sort-key values of the last row it received, and the next query asks for rows strictly after that position: WHERE (created_at, id) > ($1, $2) ORDER BY created_at, id LIMIT 50. Deletes and inserts before the cursor no longer shift anything, because the query never counts rows. It only compares values.
Postgres evaluates the row-value comparison lexicographically. It compares created_at first and uses id only when the timestamps are equal. That ordering is identical to a B-tree index on (created_at, id), so the planner can treat the predicate as an index condition. It descends the tree directly to the cursor position, reads 50 entries forward, and stops. The cost depends on the page size, not on how deep into the result set the client is. The longhand form created_at > $1 OR (created_at = $1 AND id > $2) returns the same rows, but the planner is less likely to turn it into a single range start.
| Strategy | Index entries read for page 1,000 (50 rows/page) |
|---|---|
OFFSET 49950 |
[start] ███████████████…████ [skip 49,950] → read 50 |
Keyset > (t, id) |
[start] ··········· [seek to cursor] → read 50 |
The id tie-breaker is required. Suppose page 1 ends on a row at 12:00:00 and three more rows share that timestamp. With created_at > $1, those three rows are skipped. With >=, the last row of page 1 is returned again. Postgres also does not guarantee the order of tied rows between executions. Only a unique trailing key makes each cursor position point to exactly one row.
Keep the index column order and the ORDER BY direction aligned. Ascending on both columns uses a forward scan. Descending on both columns uses a backward scan of the same index.
-- Composite index whose column order matches the ORDER BY exactly.
CREATE INDEX events_created_at_id_idx ON events (created_at, id);
-- First page: no cursor yet.
SELECT id, created_at, payload
FROM events
ORDER BY created_at, id
LIMIT 50;
-- Next page: pass the (created_at, id) of the last row received.
-- Row-value comparison is lexicographic, matching the index order,
-- so the planner can start the index range scan at the cursor.
SELECT id, created_at, payload
FROM events
WHERE (created_at, id) > ('2024-03-01 12:00:00+00', 81234)
ORDER BY created_at, id
LIMIT 50;
-- Check the plan: expect an Index Cond on (created_at, id) > (...)
-- rather than a scan from the start followed by discarded rows.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, payload
FROM events
WHERE (created_at, id) > ('2024-03-01 12:00:00+00', 81234)
ORDER BY created_at, id
LIMIT 50;
-- Broken variant: no tie-breaker. Rows sharing the last timestamp
-- are skipped with >, or the boundary row repeats with >=.
-- SELECT ... WHERE created_at > '2024-03-01 12:00:00+00'
-- ORDER BY created_at LIMIT 50;
-- Newest-first: flip BOTH directions and the comparison operator;
-- the same index is then read with a backward scan.
SELECT id, created_at, payload
FROM events
WHERE (created_at, id) < ('2024-03-01 12:00:00+00', 81234)
ORDER BY created_at DESC, id DESC
LIMIT 50;
A composite (created_at, id) index paired with a row-value keyset predicate, showing the first page, the next page, an EXPLAIN check that the cursor becomes an index condition, the tie-breaker failure mode, and a descending variant that reuses the same index.
How do you handle mixed sort directions, NULLs, filters and backward paging?
Mixed sort directions
A row comparison like (created_at, id) < ($1, $2) applies one direction to every column. For created_at DESC, id ASC you have to expand it: created_at < $1 OR (created_at = $1 AND id > $2). An index on (created_at DESC, id ASC) matches the ordering, but the planner often cannot turn the OR into a single index range. It may scan from the start of the index and filter. Adding a redundant created_at <= $1 gives it a starting bound. The simpler fix is to make both directions match, because the tie-breaker's direction rarely matters to users.
NULLs
NULL compared to anything yields NULL, so rows with a null sort key fall out of every page. The fixes are:
- Declare the column
NOT NULL. - Sort on
COALESCE(col, sentinel)and back it with an expression index on the same expression. - Use the same
NULLS FIRST/NULLS LASTin both the index and the query, and handle the null group with explicitIS NULLbranches.
Filters
Put equality filters first in the index: (tenant_id, created_at, id). With tenant_id = $t fixed, the cursor predicate becomes a tight range inside that tenant's slice of the index. Filters that are not in the index work differently. Postgres walks the index and discards rows that don't match until it collects LIMIT rows. With a selective filter, one page can read most of the tenant's rows.
Backward paging
To fetch the previous page, flip the comparison (> instead of <) and the ORDER BY directions, and apply the same LIMIT. Then reverse the returned rows in the application so the client still sees them in display order.
-- Equality filter leads the index; sort columns share one direction.
-- Assumes created_at and id are NOT NULL, so row comparisons never drop rows.
CREATE INDEX events_tenant_created_id_idx
ON events (tenant_id, created_at DESC, id DESC);
-- Next page (newest first). The row comparison maps to one index range.
SELECT id, created_at, payload
FROM events
WHERE tenant_id = $1
AND (created_at, id) < ($2, $3) -- cursor = last row of current page
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- Previous page: flip the comparison and ORDER BY, keep the LIMIT.
-- The application reverses these rows before returning them.
SELECT id, created_at, payload
FROM events
WHERE tenant_id = $1
AND (created_at, id) > ($2, $3) -- cursor = first row of current page
ORDER BY created_at ASC, id ASC
LIMIT 50;
-- Mixed directions (created_at DESC, id ASC) need the expanded OR form
-- and an index built in that order: (tenant_id, created_at DESC, id ASC).
-- The redundant created_at <= $2 gives the planner a starting bound.
SELECT id, created_at, payload
FROM events
WHERE tenant_id = $1
AND created_at <= $2
AND (created_at < $2 OR (created_at = $2 AND id > $3))
ORDER BY created_at DESC, id ASC
LIMIT 50;
This example shows tenant-scoped keyset queries for the next page, the previous page with flipped ordering, and the expanded OR form with a redundant bound that mixed sort directions require.
How should you implement opaque cursors in a REST API?
Clients should treat the cursor as an opaque string, so you can change its contents without breaking anyone. Put in it everything needed to resume the query: the last row's sort-key values (including the unique tiebreaker), the sort specification, a hash of the filter parameters, an issued-at timestamp, and a version field so you can change the format later. Serialize the payload, base64url-encode it, and append an HMAC-SHA256 computed with a server-side secret. The signature stops clients from editing values to build arbitrary seek predicates, but it does not hide anything. Base64 is readable. If the key values are sensitive, such as emails or internal scores, use an authenticated cipher instead.
On each request, decode the cursor and recompute the HMAC using a constant-time comparison. Then check the embedded sort and filter hash against the current query string. A cursor issued for ?status=open&sort=-created_at and replayed with ?status=closed would seek into a different ordering and silently skip rows. Return 400 with a distinct error code for a bad signature, a parameter mismatch, an unknown version, or an expired cursor.
A typical exchange looks like this: GET /orders?limit=50&cursor=eyJ2IjoxLC... returns {"data": [...], "next_cursor": "eyJ2IjoxLC..."}, optionally with Link: <...>; rel="next". When next_cursor is absent, the client has reached the end. Don't return a total count, because COUNT(*) reintroduces the full scan you were avoiding. Clamp limit to a server-side maximum. Query LIMIT limit + 1. If the extra row comes back, another page exists. Drop that row and build the next cursor from the last row you return.
import base64, hashlib, hmac, json, time
SECRET = b"placeholder-load-from-secret-store" # never hard-code in production
VERSION, MAX_AGE_S, MAX_LIMIT = 1, 3600, 100
class CursorError(Exception): # handler maps .code to a 400 response body
def __init__(self, code): super().__init__(code); self.code = code
def _b64e(b): return base64.urlsafe_b64encode(b).rstrip(b"=").decode()
def _b64d(s): return base64.urlsafe_b64decode(s + "=" * (-len(s) % 4))
def filter_hash(params): # params: filters only, excluding cursor and limit
canon = json.dumps(params, sort_keys=True, separators=(",", ":"))
return hashlib.sha256(canon.encode()).hexdigest()[:16]
def encode_cursor(row, sort, params):
# created_at is an ISO-8601 string; id is the unique tiebreaker
body = json.dumps({"v": VERSION, "k": [row["created_at"], row["id"]], "s": sort,
"f": filter_hash(params), "iat": int(time.time())}).encode()
return _b64e(body) + "." + _b64e(hmac.new(SECRET, body, hashlib.sha256).digest())
def decode_cursor(cursor, sort, params):
try:
body_s, sig_s = cursor.split(".")
body, sig = _b64d(body_s), _b64d(sig_s)
except ValueError: # covers bad split and bad base64 (binascii.Error)
raise CursorError("cursor_malformed")
if not hmac.compare_digest(sig, hmac.new(SECRET, body, hashlib.sha256).digest()):
raise CursorError("cursor_bad_signature") # constant-time check
p = json.loads(body)
if p.get("v") != VERSION: raise CursorError("cursor_unknown_version")
if p["s"] != sort or p["f"] != filter_hash(params): raise CursorError("cursor_param_mismatch")
if time.time() - p["iat"] > MAX_AGE_S: raise CursorError("cursor_expired")
return p["k"] # values for: WHERE (created_at, id) < (%s, %s)
def build_page(rows, limit, sort, params): # rows came from LIMIT min(limit, MAX_LIMIT) + 1
limit = min(limit, MAX_LIMIT)
has_more, rows = len(rows) > limit, rows[:limit] # extra row only signals another page
resp = {"data": rows} # no total count: COUNT(*) would rescan the table
if has_more: resp["next_cursor"] = encode_cursor(rows[-1], sort, params)
return resp # absent next_cursor means the client reached the end
A Python sketch that signs a versioned cursor holding the sort keys, sort spec, filter hash and issue time with HMAC-SHA256, verifies it with a constant-time comparison, raises a distinct 400 error code for each failure case, and uses a limit+1 fetch to decide whether to return next_cursor.
When should you avoid keyset pagination, and what are the alternatives?
Keyset pagination only moves forward or backward from a known row. It cannot jump to page 40 or render "page 3 of 112". Exact totals are a separate problem. COUNT(*) on a large filtered set scans every matching row whether you page by offset or by keyset. Either return an estimate, such as the planner's row estimate or reltuples for unfiltered lists, or drop totals and expose only has_more.
Every sort order a client can pick needs its own composite index ending in a unique tiebreaker. Each index adds storage and write amplification on every insert and update, so letting clients sort by any column is rarely practical. Offer a short list of supported sorts instead.
Sort keys that change, such as updated_at or score, let rows move during iteration. A row can move behind the cursor and be skipped, or move ahead of it and appear twice. Sequence values are assigned at insert but become visible at commit, which can happen in a different order. A sync client paging by id can therefore pass a gap that a slower transaction fills later. Sync endpoints should stop short of the newest rows by a safety lag window.
No approach gives a consistent snapshot across HTTP requests unless you hold a transaction or server-side cursor open between them. Doing that pins a connection per client. It also holds back the xmin horizon, so vacuum cannot remove dead tuples anywhere in the database until the client finishes or times out.
| Approach | Random access / page N | Cost on deep pages | Consistency across pages | Main drawback |
|---|---|---|---|---|
| Offset | Yes | Grows with offset | Rows shift on writes | Slow and drifts on large tables |
| Keyset | No | Stays flat with a matching index | Stable order, no snapshot | One index per sort order |
| Server-side cursor | Sequential only | Low per fetch | Snapshot-consistent | Holds a connection open and blocks vacuum |
| Change feed / sync with lag window | No | Low | Complete if lag covers in-flight commits | Data lags by the window, adds infrastructure |
Which pagination should you choose, and what does it cost to adopt?
| Use case | Recommended approach | Why |
|---|---|---|
| Admin UI over small or filtered sets | Offset | Page numbers and totals are cheap when the set stays small |
| "Jump to page N" requirement | Offset | Keyset cannot skip ahead without walking every earlier page |
| Stable or archived data | Offset | No concurrent writes means no shifted rows |
| Feeds and infinite scroll | Keyset | Constant cost per page, and new rows don't cause duplicates |
| Sync and export endpoints | Keyset | Deep pages are the normal case, not the edge case |
| Any API that clients iterate to completion | Keyset with opaque cursors | Correctness doesn't depend on how far the client gets |
What adoption costs
Each supported sort order needs a matching composite index with a unique tiebreaker. You end up exposing fewer sorts on purpose, or paying write amplification for each extra one. A cursor is also a long-lived contract. Clients persist tokens, so you need a version field and decoders for old versions as long as those tokens can still arrive. Signed cursors bring key management with them: rotation means accepting the previous key during an overlap window. Debugging is harder too, because a support ticket now contains a base64 blob rather than page=40, so build an internal decode tool early.
Moving an existing endpoint off offset is a breaking change. Run both styles side by side, mark page/offset as deprecated in responses and documentation, and track usage so you know when it is safe to remove.
What it saves
You get fewer incidents from crawlers or exports reaching page 5,000, fewer client reports of duplicate or missing rows, and query plans that stay the same at every depth. That makes capacity planning a matter of per-page cost multiplied by request rate, without the long tail of deep-page queries.
How can keyset pagination save engineering time?
Most of the time saved comes from problems that never reach an engineer.
Fewer correctness investigations. Duplicate and missing rows under offset pagination produce no errors. They show up as client reports like "our export is missing records" or "the feed shows the same item twice," and someone has to reproduce a race between a write and a page request to explain them. With a unique tiebreaker and a cursor that records position by value, that class of bug goes away, along with the investigation time.
No re-tuning as tables grow. An offset endpoint that is fast at launch slows down as the table and client depth grow. That usually leads to repeated rounds of work: adding indexes that don't help, raising statement timeouts, caching deep pages, or eventually rewriting the endpoint under pressure. A keyset query with a matching index has the same plan and roughly the same per-page cost at any depth, so it does not need revisiting as data grows.
Batch jobs that finish predictably. Backfills, migrations and exports that walk a table with OFFSET get slower with each batch. A job that was tested on a small staging table can run far longer in production. Keyset batching keeps every batch the same cost, so a run on a sample predicts the full run.
As an illustrative estimate with assumed inputs: a backfill over an assumed 10 million rows in assumed batches of 1,000 needs 10,000 batches. With OFFSET, batch i reads about i × 1,000 index entries, so the whole job reads roughly 1,000 × (10,000 × 10,001 / 2), or about 5 × 10^10 entries. With keyset, each batch seeks and reads about 1,000 entries, so the job reads about 10^7 in total. That is arithmetic, not a measurement, and real cost also depends on heap fetches, caching and I/O, but the gap grows with the square of the table size.
-- Keyset batching for a backfill: each batch resumes after the last id seen.
-- The application loop passes the previous batch's max id as $1 (start at 0)
-- and stops when the query returns no rows.
SELECT id, payload
FROM events
WHERE id > $1
ORDER BY id
LIMIT 1000;
A single-column keyset batch over the primary key. Because id is unique, it needs no tiebreaker, and each batch is an index seek followed by a 1,000-entry range read.
Simpler capacity planning and on-call. When every page costs about the same, load is per-page cost multiplied by request rate. On-call engineers do not have to trace a CPU or I/O spike back to one client crawling deep pages, and slow-query logs stop filling with the same statement at ever larger offsets.
The adoption costs described above, including indexes per sort order, cursor versioning, key rotation and a decode tool, are mostly one-time work per endpoint. The savings recur for as long as the endpoint serves deep or concurrent traffic.
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
.envby 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.