API Pagination Patterns: Cursor vs Offset vs Keyset Guide

Pagination is fundamental to every API that returns lists. Choosing the wrong strategy causes performance cliffs, duplicate results, and poor developer experience. This guide covers the three main patterns with real code and trade-off analysis.

Offset Pagination

The classic approach — simple to build and understand but degrades on large datasets because the database must scan all skipped rows:

// GET /posts?page=3&limit=20
// Response includes metadata for UI controls

app.get('/posts', async (req, res) => {
  const page = Math.max(1, parseInt(req.query.page) || 1);
  const limit = Math.min(100, parseInt(req.query.limit) || 20);
  const offset = (page - 1) * limit;

  const [items, total] = await Promise.all([
    db.query('SELECT * FROM posts ORDER BY created_at DESC LIMIT $1 OFFSET $2', [limit, offset]),
    db.query('SELECT COUNT(*) FROM posts'),
  ]);

  const totalPages = Math.ceil(total.rows[0].count / limit);

  res.json({
    data: items.rows,
    meta: {
      page,
      limit,
      total: parseInt(total.rows[0].count),
      totalPages,
      hasNext: page < totalPages,
      hasPrev: page > 1,
    },
    links: {
      first: `/posts?page=1&limit=${limit}`,
      prev: page > 1 ? `/posts?page=${page - 1}&limit=${limit}` : null,
      next: page < totalPages ? `/posts?page=${page + 1}&limit=${limit}` : null,
      last: `/posts?page=${totalPages}&limit=${limit}`,
    },
  });
});

Cursor-Based Pagination (Opaque Cursor)

Cursor pagination encodes position as an opaque string. The server decodes it; clients treat it as a black box. No missing or duplicate items when data changes between pages:

// GET /posts?after=eyJpZCI6NDJ9&limit=20
// Cursor = base64(JSON.stringify({ id: 42 }))

function encodeCursor(id: number): string {
  return Buffer.from(JSON.stringify({ id })).toString('base64url');
}

function decodeCursor(cursor: string): { id: number } {
  return JSON.parse(Buffer.from(cursor, 'base64url').toString());
}

app.get('/posts', async (req, res) => {
  const limit = Math.min(100, parseInt(req.query.limit) || 20);
  const after = req.query.after as string | undefined;

  let query: string;
  let params: any[];

  if (after) {
    const { id } = decodeCursor(after);
    query = 'SELECT * FROM posts WHERE id < $1 ORDER BY id DESC LIMIT $2';
    params = [id, limit + 1];  // fetch one extra to determine hasNextPage
  } else {
    query = 'SELECT * FROM posts ORDER BY id DESC LIMIT $1';
    params = [limit + 1];
  }

  const rows = await db.query(query, params);
  const hasNextPage = rows.length > limit;
  const items = hasNextPage ? rows.slice(0, limit) : rows;

  res.json({
    data: items,
    pageInfo: {
      hasNextPage,
      endCursor: items.length ? encodeCursor(items[items.length - 1].id) : null,
    },
  });
});

Keyset Pagination (Seek Method)

Keyset pagination uses compound WHERE clauses matching the ORDER BY columns. It hits the index directly — O(1) for any page depth:

-- Keyset query — no OFFSET, uses index on (created_at, id)
-- First page
SELECT * FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page — cursor encodes (created_at, id) of last item
SELECT * FROM posts
WHERE (created_at, id) < ('2026-01-10T12:00:00Z', 99)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Ensure the index exists for O(log n) performance
CREATE INDEX idx_posts_keyset ON posts (created_at DESC, id DESC);

-- Node.js implementation
const cursor = { createdAt: lastItem.created_at, id: lastItem.id };
const encoded = Buffer.from(JSON.stringify(cursor)).toString('base64url');

// Next request: GET /posts?cursor=eyJjcmVhdGVkQXQ...
// Server decodes and builds the WHERE clause above

Offset vs Cursor vs Keyset Comparison