Cursor vs offset pagination: use cursors for large datasets

OFFSET-based pagination becomes slow on large tables and returns inconsistent results when rows are inserted or deleted between page requests.

Use cursor-based pagination for large or frequently changing datasets:

// OFFSET — simple but breaks on large offsets and concurrent writes
// SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 1000000
// This reads and discards 1 million rows before returning results

// CURSOR — efficient and consistent
app.get('/api/posts', async (req, res) => {
  const limit = parseInt(req.query.limit) || 20;
  const cursor = req.query.cursor; // base64-encoded last seen ID + timestamp

  let query, params;
  if (cursor) {
    const { id, createdAt } = JSON.parse(Buffer.from(cursor, 'base64').toString());
    query = `
      SELECT id, title, created_at
      FROM posts
      WHERE (created_at, id) < ($1, $2)
      ORDER BY created_at DESC, id DESC
      LIMIT $3
    `;
    params = [createdAt, id, limit + 1];
  } else {
    query = `SELECT id, title, created_at FROM posts ORDER BY created_at DESC, id DESC LIMIT $1`;
    params = [limit + 1];
  }

  const rows = await db.query(query, params);
  const hasMore = rows.length > limit;
  const items = hasMore ? rows.slice(0, limit) : rows;
  const nextCursor = hasMore
    ? Buffer.from(JSON.stringify({ id: items[items.length - 1].id, createdAt: items[items.length - 1].created_at })).toString('base64')
    : null;

  res.json({ items, nextCursor, hasMore });
});

Cursor pagination uses an indexed WHERE clause instead of OFFSET, so performance is constant regardless of page depth. It also handles concurrent inserts correctly.