High-Scale Database Pagination: Why OFFSET Kills Performance and How Cursor Wins
1. The Fatal Flaw of OFFSET / LIMIT
Almost every tutorial implements pagination using OFFSET and LIMIT:
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 500000;
How the database executes this:
The database engine scans 500,020 rows from disk, sorts them in memory, discards the first 500,000, and returns only the last 20.
As page numbers grow, query latency degrades from 2ms to over 5,000ms ($O(N)$ degradation)!
[ OFFSET 500,000 ] ===> (Scans & discards 500,000 rows from index) ===> 💥 Severe DB Load!
2. Keyset / Cursor-Based Pagination ($O(1)$)
Instead of skipping records, cursor pagination uses indexed comparison operators (> or <) anchored to the last seen record:
SELECT * FROM posts
WHERE created_at < '2026-08-01T10:00:00.000Z'
ORDER BY created_at DESC
LIMIT 20;
The database jumps directly to the B-Tree index location in $O(log N)$ and reads exactly 20 rows. Execution time remains identical whether viewing page 1 or page 10,000.
3. Comparison Matrix
+--------------------+-----------------------+-----------------------+
| Feature | Offset-Based | Cursor-Based (Keyset) |
+--------------------+-----------------------+-----------------------+
| Query Performance | O(N) - Degrades | O(1) - Constant |
| Jump to Page N | Yes (Page 1, 2, 100) | No (Next / Previous) |
| Data Drift / Dups | Common in active feeds| Completely eliminated |
| Implementation | Trivial | Requires cursor encode|
+--------------------+-----------------------+-----------------------+
4. Production Cursor Encoding in TypeScript / Prisma
export async function getPaginatedFeed(cursor?: string, limit = 20) {
const decodedCursor = cursor
? JSON.parse(Buffer.from(cursor, 'base64').toString('utf8'))
: null;
const items = await db.post.findMany({
take: limit + 1, // Fetch +1 to check for next page existence
where: decodedCursor
? {
createdAt: { lt: new Date(decodedCursor.createdAt) },
}
: undefined,
orderBy: { createdAt: 'desc' },
});
const hasNextPage = items.length > limit;
const pageItems = hasNextPage ? items.slice(0, limit) : items;
const nextCursor = hasNextPage
? Buffer.from(JSON.stringify({ createdAt: pageItems[pageItems.length - 1].createdAt })).toString('base64')
: null;
return { items: pageItems, nextCursor, hasNextPage };
}
5. Summary
- Use Cursor pagination for infinite scroll feeds, activity logs, and high-volume data APIs.
- Reserve Offset pagination strictly for small, static datasets (e.g., admin panels with $< 500$ rows).
