What Are the Main API Pagination Strategies?
Compare offset-based and cursor-based API pagination, their performance tradeoffs, and how to implement both correctly.
Expected Interview Answer
The two dominant API pagination strategies are offset-based pagination, which uses a page number or row offset plus a limit, and cursor-based pagination, which uses an opaque pointer to the last seen record — offset is simpler but degrades on large or changing datasets, while cursor pagination stays fast and stable at scale.
Offset pagination works with query parameters like page=3&limit=20 or offset=40&limit=20, which the database translates into an OFFSET/LIMIT clause; it is easy to implement and lets clients jump to any page directly, but the database still has to scan and discard all skipped rows, making deep pages slower as offset grows, and rows inserted or deleted between requests can shift data so a user sees duplicates or misses items. Cursor pagination instead returns an opaque token, often an encoded last-seen id or timestamp, and the next request asks for records “after this cursor,” which the database resolves with an indexed WHERE clause instead of a full scan, staying fast regardless of depth and remaining stable even as new rows are inserted. The tradeoff is that cursor pagination cannot jump to an arbitrary page number and requires a stable sort key, usually a unique, monotonically ordered column like id or created_at plus id as a tiebreaker. Keyset pagination is essentially the same idea as cursor pagination expressed directly as a WHERE clause on the sort columns, and many production APIs (GitHub, Stripe) use cursor-based designs precisely because they serve large, frequently changing collections.
- Cursor pagination avoids the performance cliff of scanning and discarding rows at deep offsets
- Cursor pagination stays stable under concurrent inserts/deletes, avoiding skipped or duplicated items
- Offset pagination supports direct “jump to page N” navigation that cursors cannot
- Both can be combined with indexed sort keys to keep queries efficient at any strategy
AI Mentor Explanation
Offset pagination is like telling an usher “count 40 seats in from the aisle and seat me from there” — for a small stand that is instant, but for a packed stadium the usher has to physically walk past and count every seat first. Cursor pagination is like handing the usher a ticket stub that says “seat me right after seat H12” — they walk straight to that row using the seat map\u2019s index, no counting required. If spectators leave or new ones squeeze in between requests, the counted approach can seat someone twice or skip a row, while the stub-based approach stays accurate because it anchors to a specific known seat.
Step-by-Step Explanation
Step 1
Pick a stable, indexed sort key
Choose a monotonically ordered column (e.g. created_at + id) that both strategies can rely on.
Step 2
Decide if random page-jumping is required
If users need “go to page 7,” offset pagination is simpler; if not, prefer cursors.
Step 3
Encode the cursor opaquely
For cursor pagination, base64-encode the last-seen sort key values so clients treat it as an opaque token.
Step 4
Query with WHERE, not OFFSET, for cursors
Resolve “records after this cursor” with an indexed WHERE clause instead of skipping rows, keeping query cost constant with depth.
What Interviewer Expects
- Clear explanation of offset vs cursor mechanics, not just the names
- Understanding of the deep-page performance cliff with OFFSET/LIMIT
- Awareness of data consistency issues (skipped/duplicated rows) under concurrent writes
- Knowledge of when offset is still an acceptable, simpler choice
Common Mistakes
- Claiming cursor pagination is always strictly better without noting it loses random page access
- Using an unindexed or non-unique column as the cursor/sort key, breaking stability
- Not mentioning the OFFSET/LIMIT performance degradation as the offset grows
- Confusing cursor pagination with simply passing a page number as an opaque string
Best Answer (HR Friendly)
“Offset pagination is the simple approach where you ask for a page number and a page size, like page 5 with 20 results per page, but it gets slower on large datasets and can show duplicates or skips if data changes while browsing. Cursor pagination instead asks for results after a specific marker, like the last item you saw, which stays fast and consistent no matter how deep you page, though you give up the ability to jump straight to an arbitrary page.”
Code Example
// Offset pagination: simple, but slow at depth and unstable under writes
app.get('/products', async (req, res) => {
const page = Number(req.query.page) || 1
const limit = 20
const offset = (page - 1) * limit
const rows = await db.query(
'SELECT * FROM products ORDER BY id LIMIT $1 OFFSET $2',
[limit, offset]
)
res.json({ page, results: rows })
})
// Cursor pagination: constant-time via an indexed WHERE clause
app.get('/products/cursor', async (req, res) => {
const limit = 20
const cursorId = req.query.cursor ? Number(req.query.cursor) : 0
const rows = await db.query(
'SELECT * FROM products WHERE id > $1 ORDER BY id LIMIT $2',
[cursorId, limit]
)
const nextCursor = rows.length ? rows[rows.length - 1].id : null
res.json({ results: rows, nextCursor })
})Follow-up Questions
- Why does OFFSET/LIMIT get slower as the offset value grows?
- How would you make a cursor resilient to ties in the sort column?
- How does keyset pagination relate to cursor-based pagination?
- How would you paginate a feed that needs to support both “newest first” and jumping to a specific date?
MCQ Practice
1. What is the main performance downside of offset-based pagination on large tables?
OFFSET forces the database to walk through and discard every skipped row before reaching the requested page.
2. What problem can offset pagination cause when rows are inserted or deleted between page requests?
Because offset is a positional count, concurrent writes shift which rows fall at a given offset, causing skips or duplicates.
3. What does a cursor typically encode in cursor-based pagination?
The cursor anchors to a specific record\u2019s sort key so the next query can use an indexed range condition instead of counting rows.
Flash Cards
How does offset pagination find a page? — It counts and discards all rows before the requested offset via OFFSET/LIMIT.
How does cursor pagination find the next page? — It uses an indexed WHERE clause anchored to the last-seen record\u2019s sort key.
Main downside of cursor pagination? — It cannot jump directly to an arbitrary page number.
Main downside of offset pagination at scale? — Deep pages are slow, and results can skip/duplicate under concurrent writes.