Pagination Done Right: Cursor vs Offset and Why It Matters at Scale

Your API uses OFFSET for pagination. At page 10000, queries take 30 seconds. Here is why, and the fix.

The Problem with OFFSET

-- Page 1: fast (skip 0, read 20)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 0;

-- Page 500: slow (database reads and discards 10000 rows)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

OFFSET scans and discards rows. The deeper you paginate, the slower it gets.

Cursor-Based Pagination

app.get("/api/posts", async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const cursor = req.query.cursor; // Last item ID from previous page

  let query = db("posts").orderBy("created_at", "desc").limit(limit + 1);

  if (cursor) {
    const cursorPost = await db("posts").where({ id: cursor }).first();
    query = query.where("created_at", "<", cursorPost.created_at);
  }

  const posts = await query;
  const hasMore = posts.length > limit;
  if (hasMore) posts.pop();

  res.json({
    data: posts,
    pagination: {
      hasMore,
      nextCursor: hasMore ? posts[posts.length - 1].id : null,
    },
  });
});

When to Use Which

• Cursor: Infinite scroll, real-time feeds, large datasets. O(1) regardless of page depth.
• Offset: Admin dashboards, small datasets, when users need to jump to specific pages.
• Keyset: Similar to cursor but uses composite keys for complex sorting.

---

Part of my Production Backend Patterns series. Follow for more practical backend engineering.