Pagination — offset/limit vs cursor-based vs keyset pagination
Intermediate5 min read·eng-13-016
interviewsqlperformance
Concept
Pagination — the mechanism for returning a large dataset in discrete pages rather than all at once. Critical for performance: loading 1 million records into memory would crash your server and client.
Three main strategies:
Offset/Limit (page-based):
GET /users?page=3&per_page=20orGET /users?offset=40&limit=20- SQL:
SELECT * FROM users LIMIT 20 OFFSET 40 - Pros: Jump to any page. Simple to implement. Users understand "page 3 of 10."
- Cons: Slow for large offsets (DB scans and discards all rows before the offset). Inconsistent if data changes mid-pagination (new rows shift pages).
COUNT(*)for total pages is expensive.
Cursor-based (keyset pagination):
GET /users?cursor=eyJpZCI6NDB9(cursor = encoded last item's key)- SQL:
SELECT * FROM users WHERE id > 40 ORDER BY id LIMIT 20 - Pros: Consistent (inserts/deletes don't affect cursor position). O(log n) — fast even with millions of rows. Perfect for infinite scroll.
- Cons: Cannot jump to arbitrary page. Cannot show total count. Cursor must match the sort order (if you sort by
name, cursor must encodename+id).
Keyset pagination (explicit): Same idea as cursor-based but using explicit column values: GET /users?after_id=40. Less opaque than a base64 cursor.
When to use which:
- Offset: Admin UIs, search results where users need page numbers and total count.
- Cursor: Social feeds, activity logs, any large or real-time dataset. Mobile infinite scroll.
Response structure: Include data (the items), pagination (total, per_page, current_page, links) or next_cursor.
Code Example
php
<?php
// OFFSET PAGINATION — Laravel's paginate()
Route::get('/users', function (Request $request) {
$perPage = min($request->integer('per_page', 15), 100); // cap at 100
$users = User::orderBy('id')->paginate($perPage);
return response()->json([
'data' => $users->items(),
'pagination' => [
'total' => $users->total(), // total record count (expensive COUNT!)
'per_page' => $users->perPage(),
'current_page' => $users->currentPage(),
'last_page' => $users->lastPage(),
'from' => $users->firstItem(),
'to' => $users->lastItem(),
'next_page_url' => $users->nextPageUrl(),
'prev_page_url' => $users->previousPageUrl(),
],
]);
// SQL: SELECT * FROM users ORDER BY id LIMIT 15 OFFSET 0
// SQL: SELECT COUNT(*) FROM users (for total)
});
// CURSOR PAGINATION — Laravel's cursorPaginate() (fast for large datasets)
Route::get('/feed', function (Request $request) {
$posts = Post::orderBy('created_at', 'desc')
->orderBy('id', 'desc') // secondary sort for tie-breaking
->cursorPaginate(20, ['*'], 'cursor', $request->cursor);
return response()->json([
'data' => $posts->items(),
'next_cursor' => $posts->nextCursor()?->encode(),
'prev_cursor' => $posts->previousCursor()?->encode(),
'has_more' => $posts->hasMorePages(),
]);
// SQL: SELECT * FROM posts WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC LIMIT 21
// No COUNT — fast even with 10M rows!
});
// MANUAL KEYSET PAGINATION — when you need control
Route::get('/users', function (Request $request) {
$afterId = $request->integer('after_id', 0);
$limit = 20;
$users = User::where('id', '>', $afterId)
->orderBy('id')
->limit($limit + 1) // fetch one extra to know if there's a next page
->get();
$hasMore = $users->count() > $limit;
if ($hasMore) $users->pop(); // remove the extra item
return response()->json([
'data' => $users,
'has_more' => $hasMore,
'next_after_id' => $hasMore ? $users->last()->id : null,
// Usage: GET /users?after_id=20 → GET /users?after_id=40 → ...
]);
});
// SIMPLE PAGINATION — no total count (cheaper)
Route::get('/articles', function () {
return ArticleResource::collection(
Article::orderBy('id')->simplePaginate(10)
// No COUNT(*) — just checks if there's a next page
// Returns: data, current_page, from, to, per_page, next_page_url, prev_page_url
);
});