High-Scale Database Pagination: Why OFFSET Kills Performance and How Cursor Wins

SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 500000;

[ OFFSET 500,000 ] ===> (Scans & discards 500,000 rows from index) ===> 💥 Severe DB Load!

SELECT * FROM posts
WHERE created_at < '2026-08-01T10:00:00.000Z'
ORDER BY created_at DESC
LIMIT 20;

+--------------------+-----------------------+-----------------------+
| 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|
+--------------------+-----------------------+-----------------------+

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 };
}