Pagination is one of those problems that looks solved until the table has a million rows and the OFFSET 990000 LIMIT 10 query starts taking two seconds. Every API that returns lists needs a pagination strategy, and the right choice depends on query patterns, product requirements, and expected data volume. Here is how each strategy actually works in 2026.
What changed in 2026
- Cursor-based pagination became the de-facto standard for public APIs. GitHub, Stripe, and Notion all use opaque page tokens instead of raw offsets.
- Postgres
keyset + pgvector patterns merged — pagination over embedding search results now requires keyset semantics because cosine-distance offsets are meaningless.
- Edge databases (Turso, Neon's branching, PlanetScale) pushed developers toward stable cursors to avoid inconsistent reads across distributed replicas.
- OpenAPI 3.1 standardized
Link headers and next_cursor fields, making cursor pagination easier to document and consume.
The three strategies
Offset pagination
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;
The database scans and discards the first 40 rows every time. Simple, but:
- O(n) scan cost —
OFFSET 100000 reads 100 020 rows.
- Drift — a new row inserted before page 2 is fetched pushes an item to the next page, causing duplicates or skipped rows.
- Fine for small tables and admin UIs; bad for user-facing feeds.
Cursor pagination
GET /posts?after=eyJpZCI6NDJ9&limit=20
The server encodes the last-seen record into an opaque token (base64 JSON or ULID), then decodes it on the next request:
SELECT id, title, created_at
FROM posts
WHERE id < 42 -- decoded from cursor
ORDER BY id DESC
LIMIT 20;
No scan cost. Stable under concurrent inserts. Downside: no random access — you cannot jump to page 7 directly.
Keyset pagination
A generalization of cursor pagination that supports multi-column sort orders:
-- Page after (created_at = '2026-05-20', id = 99)
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2026-05-20', 99)
ORDER BY created_at DESC, id DESC
LIMIT 20;
With a composite index on (created_at, id), Postgres uses an index seek — constant cost regardless of offset depth. This is the right pattern for any high-volume, real-time feed.
Comparison table
| Strategy |
Seek cost |
Stable under insert |
Random access |
Implementation complexity |
| Offset |
O(offset) |
No |
Yes |
Low |
| Cursor (ID-based) |
O(1) |
Yes |
No |
Medium |
| Keyset (multi-column) |
O(1) |
Yes |
No |
Medium-High |
| Search-after (Elasticsearch) |
O(1) |
Yes |
No |
Medium |
How to pick
- Admin list UI with small table (<50k rows) and numbered pages? Offset is fine. Ship fast.
- Public REST API returning recent items? Cursor pagination with an opaque token. Encode
{id, created_at}, not just the ID, so you can add sort fields later without breaking existing cursors.
- High-throughput feed or infinite scroll on a large table? Keyset. Add the composite index before deploying.
- Search results (Elasticsearch / Typesense)? Use
search_after — it is keyset semantics for inverted indexes.
- Need total page count? Only offset gives it cheaply. Cache the count or drop the requirement.
Encoding cursors safely
import base64, json
def encode_cursor(id: int, created_at: str) -> str:
payload = json.dumps({"id": id, "ts": created_at})
return base64.urlsafe_b64encode(payload.encode()).decode()
def decode_cursor(token: str) -> dict:
payload = base64.urlsafe_b64decode(token.encode())
return json.loads(payload)
Treat the cursor as opaque at the API surface — clients must not construct or parse them. This lets you change encoding without a versioning break.
Common mistakes
Sorting by non-unique column alone. ORDER BY created_at with ties breaks keyset pagination. Always add a unique tiebreaker (id) to the sort and the cursor.
Exposing internal IDs in cursors. Base64 is not encryption. If row IDs are sensitive, use ULIDs or HMACs.
Counting every page. SELECT COUNT(*) on large tables is expensive. Return has_more: true/false instead of a total unless required.
Mixing ASC and DESC in keyset. The row-value comparison (col1, col2) < (v1, v2) only works when all columns share the same sort direction. For mixed directions, build explicit WHERE clauses.
No index on the cursor columns. A cursor query without an index on (created_at, id) is worse than offset — you lose the simplicity with none of the speed.
What to skip
- Offset on tables projected to grow past 100k rows — the rewrite will come; plan for cursors from day one.
- Sending raw database IDs as cursors when row-level privacy matters.
- Total count on every page for public consumer APIs — clients rarely use it and the query cost is high.
FAQ
Can I support both offset and cursor at the same endpoint?
Technically yes, but it creates confusing semantics. Better to pick one and document it. If you need numbered pages for a specific UI and a feed elsewhere, use separate endpoints.
How do I implement prev/next with keyset?
For prev, reverse the sort direction in the query, then reverse the returned rows in code. Encode a before token alongside the after token.
What is a good cursor TTL?
Most APIs make cursors valid for 15–60 minutes. After that, re-fetch from the start. Stripe uses starting_after objects that never expire because they reference a stable record ID.
Does GraphQL have a standard pagination spec?
Yes — the Relay Cursor Connections spec defines edges, node, cursor, pageInfo.hasNextPage, and pageInfo.endCursor. It is cursor-based by design.
Where to go next