Why does OFFSET pagination get slower on deep pages?
Offset pagination translates a page number into LIMIT n OFFSET k. Page 1 is LIMIT 20 OFFSET 0, and page 5,000 is LIMIT 20 OFFSET 99980. The SQL looks equally cheap for both, but it isn't. Postgres has no way to jump directly to the 99,981st row of an ordered result. It has to generate rows in sort order, count them, and throw away the first k before returning anything. An index on the sort column removes the sort step, but the executor still walks every skipped index entry and usually fetches the matching heap tuples. Cost grows linearly with depth.
Run EXPLAIN (ANALYZE, BUFFERS) on a shallow page and on a deep page of the same query, and compare the nodes:
- Limit node: reports
actual rows=20in both plans. - Index Scan beneath it: reports roughly 20 rows on page 1 and roughly 100,000 on the deep page. That gap is the work you pay for and discard.
- Buffers: shared hits and reads rise with the offset.
- Execution Time: rises with them, so a page that responds quickly near the start of the table becomes slow near the end.
There is also a correctness problem. An offset counts positions, not rows. If a row is inserted ahead of the client's position between two requests, every row after it shifts down by one, and the client sees one row twice. If a row is deleted, the rows shift up, and the client skips one row without any indication. On tables with steady writes, a client that walks every page of the result will regularly hit these duplicates and gaps.
-- Example table: orders sorted by created_at, with id as a tiebreaker.
-- The composite index lets Postgres read rows in sort order without a Sort node.
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT now(),
total numeric(10,2) NOT NULL
);
CREATE INDEX orders_created_at_id_idx ON orders (created_at, id);
-- Load enough rows that deep pages exist.
INSERT INTO orders (created_at, total)
SELECT now() - (g * interval '1 second'), (random() * 500)::numeric(10,2)
FROM generate_series(1, 200000) AS g;
ANALYZE orders;
-- Shallow page (page 1).
-- Check: Limit shows actual rows=20, and the Index Scan below it also
-- shows about 20 rows. Buffers are low.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total
FROM orders
ORDER BY created_at, id
LIMIT 20 OFFSET 0;
-- Deep page (page 5,000).
-- Check: Limit still shows actual rows=20, but the Index Scan shows about
-- 100,000 rows (offset + limit). Every skipped row is read and discarded.
-- Shared hit/read counts and Execution Time grow with the offset.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total
FROM orders
ORDER BY created_at, id
LIMIT 20 OFFSET 99980;
Running EXPLAIN (ANALYZE, BUFFERS) on the same indexed query at OFFSET 0 and OFFSET 99980 shows the Limit node returning 20 rows in both cases, while the Index Scan beneath it produces roughly offset + limit rows.
How does cursor-based pagination work, end to end?
Cursor-based pagination replaces "skip k rows" with "continue after this row". The request cycle has four steps:
- First request. The client asks for a page with no cursor. The server runs the ordered query with
LIMIT n + 1and no seek predicate. - Response. The server returns up to
nrows. It also returns an opaquenext_cursorthat encodes the sort key of the last row returned, such as(created_at, id). If the extra probe row was not present,next_cursoris null. - Next request. The client sends the cursor back unchanged. The server validates and decodes it, then runs the same query with a seek predicate such as
WHERE (created_at, id) < ($1, $2). - Seek. Postgres descends the matching B-tree index directly to that key and reads the next
n + 1entries. The cycle repeats until the server returns a null cursor.
Each page costs about the same amount of work no matter how deep the client is, because the database never reads and discards earlier rows. The sections below cover each part: the keyset query and its index, the cursor token, navigation in both directions, sorts and filters, and the consistency guarantees.
How does keyset pagination work in Postgres?
Keyset pagination, also called the seek method, avoids that drift. Instead of counting past rows, the client sends back the sort key of the last row it saw, and the next query starts strictly after it: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50.
The row-value comparison is lexicographic. It compares created_at first and uses id only when the timestamps are equal. Postgres treats this as a single index condition. With CREATE INDEX events_created_at_id_idx ON events (created_at DESC, id DESC), the planner descends the B-tree once to the seek point and reads forward until it has 50 rows. The cost depends on page size, not page depth. In EXPLAIN, look for an Index Scan using events_created_at_id_idx node with an Index Cond on ROW(created_at, id) < ROW(...) under a Limit, and no Sort node. The equivalent hand-written OR form is logically identical, but the planner may not turn it into a clean range scan.
The sort key must be total and stable. created_at alone has ties, and rows that share the boundary timestamp get skipped or repeated. Appending the primary key as a tiebreaker makes every position unique. The key's values also must not change while a client is paging.
The index columns must follow the ORDER BY columns in the same order. Their directions must match too, or be exactly reversed, which Postgres reads as a backward scan. A mixed sort such as created_at DESC, id ASC needs an index declared that way. In that case, a plain row comparison no longer describes the seek.
Filters add one more caveat. With WHERE tenant_id = $3, the scan still starts at the seek point but may pass over many non-matching entries before it fills the page. Check this with EXPLAIN (ANALYZE, BUFFERS) and put the filter column first, as in (tenant_id, created_at DESC, id DESC).
-- One composite index whose column order and direction match the ORDER BY.
CREATE INDEX events_created_at_id_idx
ON events (created_at DESC, id DESC);
-- First page: no seek predicate yet.
SELECT id, created_at, payload
FROM events
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- Next page: the client sends back (created_at, id) of the last row it saw.
-- The row-value comparison is lexicographic, so id only breaks timestamp ties.
EXPLAIN (COSTS OFF)
SELECT id, created_at, payload
FROM events
WHERE (created_at, id) < ('2024-05-01 12:00:00+00', 98765)
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- Plan shape to look for (illustrative): one range scan, no Sort node.
-- Limit
-- -> Index Scan using events_created_at_id_idx on events
-- Index Cond: (ROW(created_at, id) < ROW('2024-05-01 12:00:00+00'::timestamp with time zone, 98765))
-- With a tenant filter, lead the index with the equality column so the
-- seek lands inside that tenant's entries instead of skipping other tenants.
CREATE INDEX events_tenant_created_at_id_idx
ON events (tenant_id, created_at DESC, id DESC);
-- Verify with real execution: check rows removed by filter and buffers read.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, payload
FROM events
WHERE tenant_id = 42
AND (created_at, id) < ('2024-05-01 12:00:00+00', 98765)
ORDER BY created_at DESC, id DESC
LIMIT 50;
Index DDL, a keyset query with the expected plan shape (Limit over an Index Scan with a ROW comparison in Index Cond and no Sort), and a tenant-leading index checked with EXPLAIN (ANALYZE, BUFFERS).
What is the difference between a cursor and keyset pagination?
Keyset is the query technique: a WHERE (created_at, id) < ($1, $2) predicate that seeks into an index. A cursor is the API contract that carries those values between requests. Clients see only an opaque string. The server decodes it into key values and runs the keyset query. It then encodes the last row of the page into a new token.
What goes into the token
A useful token holds four things:
- A format version, so the server knows how to parse it.
- The last-seen key values, including the unique tiebreaker.
- The sort spec, meaning columns and directions.
- A fingerprint of the active filters, such as a hash of the normalized filter parameters.
The fingerprint lets the server reject a cursor that is replayed against a different query. Without that check, the server silently returns the wrong rows.
Sign the token with an HMAC, or store the state server-side and hand out only an ID. Base64 alone is encoding, not protection.
Why clients must not build cursors
If clients assemble cursors themselves, your sort keys, column types and tiebreaker become public API. You can no longer add an index column or change timestamp precision without breaking someone. Opacity is what keeps those details private.
The cost
You can't read a token in a bug report, so you need an internal decode tool or logging of the decoded values. The token is also a compatibility surface. Clients may hold tokens across a deploy, so either accept old versions for a defined window or document an expiry and return a clear error when a token is stale.
import base64, hashlib, hmac, json
SECRET = b"placeholder-load-from-secret-store" # never hardcode in production
VERSION = 1
SORT = [["created_at", "desc"], ["id", "desc"]] # id is the unique tiebreaker
def fingerprint(filters: dict) -> str:
# Normalize so equivalent filter sets hash identically
canon = json.dumps(filters, sort_keys=True, separators=(",", ":"))
return hashlib.sha256(canon.encode()).hexdigest()[:16]
def encode_cursor(last_row: dict, filters: dict) -> str:
# created_at is an ISO-8601 string here so precision survives the round trip
payload = {"v": VERSION, "k": [last_row["created_at"], last_row["id"]],
"s": SORT, "f": fingerprint(filters)}
body = json.dumps(payload, separators=(",", ":")).encode()
sig = hmac.new(SECRET, body, hashlib.sha256).digest() # 32 bytes
return base64.urlsafe_b64encode(body + sig).decode().rstrip("=")
def decode_cursor(token: str, filters: dict) -> list:
# Any ValueError below should become a clear 400, not a silent first page
raw = base64.urlsafe_b64decode(token + "=" * (-len(token) % 4))
body, sig = raw[:-32], raw[-32:]
expected = hmac.new(SECRET, body, hashlib.sha256).digest()
if not hmac.compare_digest(sig, expected):
raise ValueError("cursor signature invalid")
p = json.loads(body)
if p["v"] != VERSION:
raise ValueError("cursor version unsupported or expired")
if p["s"] != SORT or p["f"] != fingerprint(filters):
raise ValueError("cursor does not belong to this query")
return p["k"] # [created_at, id] for the keyset predicate
PAGE_SQL = """
SELECT id, created_at, title FROM posts
WHERE status = %(status)s AND (created_at, id) < (%(ts)s, %(id)s)
ORDER BY created_at DESC, id DESC
LIMIT %(limit)s
"""
A versioned, HMAC-signed cursor that carries the last-seen keys, sort spec and filter fingerprint, is validated on decode, and feeds a keyset query whose last row is encoded into the next token.
How do you implement next, previous, and has_next with keyset cursors?
Moving forward is the easy direction: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC. To go backward from the first row of the current page, flip both the comparison and the sort, giving > ($1, $2) ORDER BY created_at ASC, id ASC. The index serves this scan in reverse, so it costs the same as the forward query. The rows come back in the opposite order, so reverse them in application code before encoding cursors or returning the page. A common bug is to skip that step and emit a "prev" page that reads upside down.
To get has_next without counting, request limit + 1 rows. If the extra row comes back, there is another page. Drop that row and build the next cursor from the last row you keep. On backward requests the same check tells you whether a previous page exists. A practical response shape is data, next_cursor, and prev_cursor. Set a cursor to null when there is nothing in that direction, so clients never have to infer it.
Total counts are a separate problem, and no pagination method solves it. Postgres uses MVCC, so COUNT(*) has to check row visibility for your snapshot. On a large table that means reading every qualifying row or index entry. The cheap alternatives are planner estimates from reltuples or EXPLAIN. Those come from statistics that can lag far behind reality after bulk loads, deletes, or selective filters. Decide explicitly whether the API promises an exact count, an estimate labeled as one, or no count at all, and document that choice.
import base64, json
PAGE_SIZE = 20
COLS = "SELECT id, created_at, title FROM posts"
FIRST = COLS + " ORDER BY created_at DESC, id DESC LIMIT %s"
FORWARD = COLS + """ WHERE (created_at, id) < (%s::timestamptz, %s)
ORDER BY created_at DESC, id DESC LIMIT %s"""
BACKWARD = COLS + """ WHERE (created_at, id) > (%s::timestamptz, %s)
ORDER BY created_at ASC, id ASC LIMIT %s"""
def encode(row): # opaque cursor built from the sort key (created_at, id)
return base64.urlsafe_b64encode(json.dumps([row[1].isoformat(), row[0]]).encode()).decode()
def decode(cursor):
return json.loads(base64.urlsafe_b64decode(cursor))
def fetch_page(conn, after=None, before=None):
with conn.cursor() as cur:
if before:
cur.execute(BACKWARD, (*decode(before), PAGE_SIZE + 1))
elif after:
cur.execute(FORWARD, (*decode(after), PAGE_SIZE + 1))
else:
cur.execute(FIRST, (PAGE_SIZE + 1,))
rows = cur.fetchall()
has_more = len(rows) > PAGE_SIZE # the extra row proves another page exists
rows = rows[:PAGE_SIZE] # never return the probe row
if before:
rows.reverse() # backward rows arrive ascending; restore display order
has_prev, has_next = has_more, True # we came from a later page
else:
has_next, has_prev = has_more, after is not None
return {
"data": rows,
# null means nothing in that direction; clients never infer it
"next_cursor": encode(rows[-1]) if rows and has_next else None,
"prev_cursor": encode(rows[0]) if rows and has_prev else None,
}
A Python DB-API handler that serves forward and backward keyset pages, fetches limit + 1 rows to detect further pages without COUNT(), reverses backward results before building cursors, and returns data with nullable next/prev cursors.*
How do you support user-selectable sorts and filters with keyset?
Every sort you expose needs its own composite index that matches the sort exactly, with the tiebreaker column last: (created_at, id), (price, id), (name, id). Without a matching index, the keyset predicate turns back into a scan-and-sort, and deep pages get slow again. Each index also adds work to every insert and to every update that touches an indexed column. A table with six sort options carries six extra indexes on the write path. Offer a short, fixed list of sorts and reject anything else instead of accepting arbitrary column names.
A cursor is only valid for the sort and filters that produced it. Store a fingerprint of the sort key, direction, and normalized filter parameters in the token. On each request, recompute the fingerprint and return a clear error if it doesn't match. If you skip this check, a client that changes the filter while reusing an old cursor gets a silently wrong page.
Nullable sort columns need an explicit NULLS FIRST or NULLS LAST, and the index has to declare the same ordering. A row comparison such as (price, id) > ($1, $2) evaluates to NULL whenever a NULL is involved, so those rows drop out of the results. Write the predicate in branches instead. With NULLS LAST, a non-NULL cursor value matches rows with a greater value, rows with an equal value and a higher id, or rows where the column is NULL. A NULL cursor value matches only rows where the column is NULL and the id is greater.
Sorting by mutable columns such as updated_at or a score has a known flaw. If a row's value changes between fetches, the row moves across the cursor boundary. It can then be skipped entirely or appear on two pages. Only an immutable sort key prevents this.
What consistency guarantees can a paginated API honestly document?
Each page request is a separate query, and each query sees the database as it is at that moment. No stateless pagination scheme gives a snapshot. What differs is how each scheme fails when rows change between requests.
With offset, the position is a row count, so any insert or delete ahead of the reader shifts every later row. Insert one row on page 1 while a client reads page 2, and the last row of page 2 slides onto page 3, where the client sees it again. A delete does the opposite and a row is silently skipped. Keyset anchors to a value instead of a count. Earlier deletes and inserts don't move the boundary. New rows whose key sorts past the cursor will appear, and rows that sort before it won't.
| Change during traversal | Offset | Keyset |
|---|---|---|
| Insert before reader's position | Duplicate at page boundary | Not seen |
| Insert after reader's position | Seen | Seen |
| Delete before reader's position | Row skipped | No effect |
| Sort key updated | Duplicate or skip | Duplicate or skip |
For keyset over an immutable, unique sort key, you can honestly document this: "Each row appears at most once per traversal. Rows created, modified, or deleted during traversal may or may not appear. Results are not a point-in-time snapshot."
A server-side database cursor, declared inside a transaction, is the only snapshot-like option. The costs are significant. It pins a connection and keeps a transaction open across client requests, which holds back vacuum cleanup of dead rows. It also ties the client to one backend.
Who should use keyset pagination?
Keyset pagination behind opaque cursors is a good fit for these teams and workloads:
- Teams whose tables grow without bound. Event logs, orders, messages, audit trails and activity feeds keep getting deeper, so offset cost keeps rising with them.
- Owners of public or partner APIs. External clients page further than you expect. They often walk entire collections, and you can't easily change their behavior once the contract ships.
- Engineers building sync, export, and ETL jobs. A job that reads every row of a table is the worst case for offset: it hits every deep page, and concurrent writes cause duplicates and gaps in the output.
- Mobile and web feeds with infinite scroll or "load more". These interfaces only ever need "next" and sometimes "previous", which keyset handles directly.
- Services on tables with heavy concurrent writes. Keyset gives each row at most one appearance per traversal over an immutable key, which offset cannot promise.
Keyset is a poor fit for small or bounded tables, for screens where users must jump to an arbitrary page number, and for interfaces that let users sort by any column. Every sort needs its own index, so unrestricted sorting is not practical.
When should you use offset, keyset cursors, or database cursors?
Choose based on three factors: how large the table can grow, how deep clients actually page, and whether a person or a program is reading the results.
| Approach | Use it for | Avoid it when |
|---|---|---|
| Offset | Small or bounded admin tables, capped page depth, jump-to-page UIs | The table grows without bound, clients page deeply, or rows shift between requests |
| Keyset behind opaque cursors | Public APIs, feeds, sync and export jobs on large or frequently changing tables | Users need to land on an arbitrary page number |
Server-side DECLARE CURSOR |
Single-session batch processing, such as a migration or a nightly report | Requests arrive over stateless HTTP or go through a connection pooler |
A "go to page 47" requirement does not force offset everywhere. You have three options:
- Keep offset on that screen and cap the depth. A low cap bounds the cost of skipped rows.
- Precompute bookmarks. Store the sort key at every Nth row so a page number maps to a keyset seek.
- Change the UX. Replace page numbers with search, filters or a date jump. Users rarely want page 47 itself. They want something that happens to be on it.
Offset is the cheapest to build. Keyset costs more up front: you need a tiebreaker column, cursor encoding and validation, and an index for each supported sort. That cost is paid once, and the endpoint's latency stays flat as the table grows.
Moving an existing offset API to cursors is a breaking contract change. Clients that compute page=N or display total pages will break. Ship cursors in a new API version and run both versions in parallel. Publish a deprecation window before you remove offset parameters.
How can keyset pagination save engineering time?
Most of the time savings come from problems you no longer have to investigate or work around.
- Fewer depth-dependent performance incidents. Offset endpoints often look fine in testing and degrade only once the table is large and someone pages deeply. Engineers then spend time reproducing the slow case, reading plans, and adding depth caps or caching. A keyset query does a bounded amount of work per page, so that whole class of investigation goes away.
- Fewer data-correctness bug reports. Duplicates and missing rows in exports and feeds are hard to reproduce because they depend on writes that happened between two requests. Keyset over an immutable, unique key removes the cause instead of leaving a race to debug.
- Resumable batch jobs. A sync or export job can persist its last cursor and restart from there after a failure or deploy. It doesn't have to start over, and it doesn't have to trust that an offset still points to the same row.
- Internal changes without client coordination. Because the cursor is opaque and versioned, you can change sort columns, timestamp precision or token format by bumping the version. You don't need every client to change how it builds requests.
- One reusable implementation. Cursor encoding and validation, the
limit + 1probe, and the forward/backward query pair are the same for every endpoint. Write them once as a shared helper, and each new paginated endpoint mostly comes down to choosing a sort key and adding its index.
To see the scale of the work avoided, consider an export job that walks a whole table. This is an illustrative estimate, not a measurement. Assume 200,000 rows, a page size of 20, and an index that serves the sort. That is 10,000 pages. With offset, page p reads about 20 × p index entries, so the full traversal reads 20 × (1 + 2 + … + 10,000), or about 1,000,100,000 entries. With keyset and a limit + 1 probe, each page reads about 21 entries, about 210,000 in total. Real costs also depend on caching, heap fetches and filters, so check your own workload with EXPLAIN (ANALYZE, BUFFERS). The shape still holds: offset traversal cost grows with the square of the page count, while keyset grows linearly. Deep exports therefore stop being a recurring performance problem.
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
.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.