Pagination is one of those things every API needs and most implementations get subtly wrong. Offset pagination ships fast and breaks at scale. Cursor pagination is robust but requires a bit more design. Keyset pagination is the fastest of all but is tightly coupled to your sort order. Knowing which to reach for — and how to implement it correctly — saves you a painful migration later.
What changed in 2026
- Cursor/keyset became the default expectation. GitHub, Stripe, and most major APIs moved to opaque cursor tokens; clients now expect this pattern.
- ORM support improved. Prisma, Drizzle, and SQLAlchemy now have first-class cursor pagination helpers that generate correct
WHERE clauses.
- Infinite scroll dominated mobile UX, making append-only cursor pagination the natural fit for most list endpoints.
- Vector search results added a new wrinkle — similarity search returns top-K results with no stable natural order, requiring special handling.
The three approaches compared
| Approach |
How it works |
Best for |
Breaks when |
| Offset |
LIMIT n OFFSET m |
Small tables, admin UIs, known total count needed |
Table > 100k rows; concurrent inserts |
| Cursor |
Encode last-seen ID in a token |
Feeds, large lists, real-time data |
Complex multi-column sorts |
| Keyset |
WHERE (created_at, id) < (?, ?) |
High-performance large datasets |
Non-indexed sort columns |
Offset pagination
Simple and familiar, but OFFSET forces the DB to read and discard m rows on every request:
-- Page 3, 20 items per page
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;
{
"data": [...],
"pagination": {
"page": 3,
"pageSize": 20,
"total": 1842
}
}
Works fine for admin tables with < 10k rows. Avoid for feeds or user-facing lists that grow.
Cursor pagination
Encode the position as an opaque token (usually base64 of the last row's ID or sort key):
// Encode cursor
function encodeCursor(id: string): string {
return Buffer.from(id).toString('base64url');
}
function decodeCursor(cursor: string): string {
return Buffer.from(cursor, 'base64url').toString();
}
// Query
async function listPosts(cursor?: string, limit = 20) {
const where = cursor
? { id: { lt: decodeCursor(cursor) } }
: {};
const posts = await db.posts.findMany({
where,
orderBy: { id: 'desc' },
take: limit + 1, // fetch one extra to know if there are more
});
const hasMore = posts.length > limit;
const items = hasMore ? posts.slice(0, limit) : posts;
const nextCursor = hasMore ? encodeCursor(items[items.length - 1].id) : null;
return { items, nextCursor };
}
Response format:
{
"data": [...],
"pagination": {
"nextCursor": "eyJpZCI6IjEyMyJ9",
"hasMore": true
}
}
Keyset pagination
The fastest option — uses an index seek, zero row discard. Requires a stable, indexed sort column:
-- Next page after (created_at='2026-05-01', id=456)
SELECT * FROM events
WHERE (created_at, id) < ('2026-05-01T12:00:00Z', 456)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Ensure a composite index on (created_at DESC, id DESC) or performance degrades.
How to pick
- Admin table with < 50k rows and users need "go to page 5"? Offset is fine; total count is cheap.
- Feed, activity stream, or any list that updates in real time? Cursor — consistent across concurrent writes.
- Very large table (millions of rows) with a clear sort column? Keyset for maximum query performance.
- GraphQL? Use Relay-spec cursor pagination (
edges, node, pageInfo.endCursor).
- Vector / similarity search? Return the raw ranked list with
limit; there is no stable cursor across re-ranking.
Common mistakes
No page size cap. Without a server-enforced maximum, a client can request limit=100000 and crash your DB. Cap at 100 or 200.
Exposing raw database IDs in cursors. Encode and opacify them. Even if the cursor is not sensitive, coupling clients to internal IDs makes future migrations painful.
Missing index on the cursor column. A cursor-based query without an index on the sort column performs full table scans.
Total count on every request. SELECT COUNT(*) on large tables is slow. Only compute totals when the UI genuinely needs them; omit from cursor-based endpoints.
Different sort orders on different pages. If sort order is not deterministic (e.g. ties in created_at not broken by ID), the cursor can skip or repeat rows.
What to skip
- Rolling your own "seek" logic without a unique tiebreaker — always include
id as a secondary sort key.
OFFSET beyond ~1000 rows in high-traffic user-facing APIs — use keyset or cursor instead.- Returning the full dataset and paginating in the application layer — this breaks at any meaningful table size.
FAQ
Can I jump to a specific page with cursor pagination?
Not directly — that is offset's strength. If you need "go to page N," use offset. If you only need "load more," use cursor.
How do I handle deleted rows with cursor pagination?
A deleted row does not affect cursor correctness because you are seeking after the cursor ID, not counting rows. This is a key advantage over offset.
What is the right response envelope for pagination?
Return data (the items) and pagination (cursor or page info) at the top level. Be consistent across all list endpoints.
Should the cursor be human-readable?
No — opaque base64 or JWT-like tokens prevent clients from assuming structure. Treat them as black boxes on both ends.
Where to go next
See How to cache API responses in 2026, How to version an API in 2026, and Pagination explained in 2026.