API Pagination: Offset, Cursor, and Keyset

Key Insights

Offset pagination is simple but suffers from performance degradation on large datasets and inconsistent results when data changes during pagination
Cursor-based pagination provides stable results for real-time feeds but prevents users from jumping to arbitrary pages
Keyset pagination offers the best performance by leveraging database indexes, but requires careful implementation with unique, sequential keys

Why Pagination Matters

When your API returns thousands or millions of records, pagination isn’t optional—it’s essential. Without it, you’ll overwhelm clients with massive payloads, crush database performance, and create terrible user experiences.

Consider this naive approach:

app.get('/api/posts', async (req, res) => {
  const posts = await db.query('SELECT * FROM posts ORDER BY created_at DESC');
  res.json({ posts }); // Returns all 50,000 posts
});

This endpoint will transfer megabytes of data, lock up your database with a massive result set, and likely timeout before completing. Pagination solves these problems by breaking results into manageable chunks.

The three main pagination strategies each make different trade-offs:

Method	Performance	Consistency	Random Access	Complexity
Offset	Poor (large offsets)	Unstable	Yes	Low
Cursor	Good	Stable	No	Medium
Keyset	Excellent	Stable	Limited	High

Offset-Based Pagination

Offset pagination uses limit and offset parameters to skip a certain number of records and return the next batch. It’s the most intuitive approach—think “page 5 of results.”

app.get('/api/posts', async (req, res) => {
  const limit = parseInt(req.query.limit) || 20;
  const offset = parseInt(req.query.offset) || 0;
  
  const posts = await db.query(
    'SELECT * FROM posts ORDER BY created_at DESC LIMIT $1 OFFSET $2',
    [limit, offset]
  );
  
  const total = await db.query('SELECT COUNT(*) FROM posts');
  
  res.json({
    posts,
    pagination: {
      limit,
      offset,
      total: total.rows[0].count
    }
  });
});

Client-side usage is straightforward:

async function fetchPosts(page = 1, limit = 20) {
  const offset = (page - 1) * limit;
  const response = await fetch(`/api/posts?limit=${limit}&offset=${offset}`);
  return response.json();
}

// Get page 3
const data = await fetchPosts(3, 20);

The corresponding SQL query looks clean, but there’s a hidden problem:

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

When offset is large, the database must scan and discard 10,000 rows before returning your 20 results. This gets exponentially slower as offset increases.

Even worse, offset pagination produces inconsistent results when data changes. If someone inserts a new post while you’re on page 2, you might see duplicate records or miss records entirely when navigating to page 3.

Use offset pagination when:

You need page numbers for admin panels or search results
Your dataset is small (< 10,000 records)
Random page access is required
Data doesn’t change frequently

Cursor-Based Pagination

Cursor pagination uses an opaque token that encodes the position in the result set. Instead of “give me page 5,” you say “give me the next 20 items after this cursor.”

app.get('/api/posts', async (req, res) => {
  const limit = parseInt(req.query.limit) || 20;
  const cursor = req.query.cursor;
  
  let query = { deleted: false };
  
  if (cursor) {
    // Decode cursor to get the last seen ID
    const decodedCursor = JSON.parse(
      Buffer.from(cursor, 'base64').toString('utf-8')
    );
    query._id = { $lt: decodedCursor.id };
  }
  
  const posts = await Post.find(query)
    .sort({ _id: -1 })
    .limit(limit + 1); // Fetch one extra to determine if there's a next page
  
  const hasNext = posts.length > limit;
  const results = hasNext ? posts.slice(0, -1) : posts;
  
  const nextCursor = hasNext
    ? Buffer.from(JSON.stringify({ id: results[results.length - 1]._id }))
        .toString('base64')
    : null;
  
  res.json({
    posts: results,
    pagination: {
      nextCursor,
      hasNext
    }
  });
});

The cursor is just a base64-encoded JSON object containing the position marker. This keeps the implementation details hidden from clients.

Client-side implementation works well with infinite scroll:

function PostFeed() {
  const [posts, setPosts] = useState([]);
  const [cursor, setCursor] = useState(null);
  const [hasMore, setHasMore] = useState(true);

  const loadMore = async () => {
    const url = cursor 
      ? `/api/posts?cursor=${cursor}&limit=20`
      : '/api/posts?limit=20';
    
    const response = await fetch(url);
    const data = await response.json();
    
    setPosts(prev => [...prev, ...data.posts]);
    setCursor(data.pagination.nextCursor);
    setHasMore(data.pagination.hasNext);
  };

  return (
    <InfiniteScroll loadMore={loadMore} hasMore={hasMore}>
      {posts.map(post => <PostCard key={post.id} post={post} />)}
    </InfiniteScroll>
  );
}

Cursor pagination solves the consistency problem because the cursor represents an absolute position. Even if new items are added, you’ll continue from where you left off without duplicates or gaps.

Use cursor pagination when:

Building social feeds or activity streams
Data changes frequently
You need stable, consistent results
Infinite scroll is your UI pattern

Keyset Pagination

Keyset pagination (also called “seek method”) uses the values of indexed columns as continuation markers. It’s the most performant approach because it leverages database indexes efficiently.

app.get('/api/posts', async (req, res) => {
  const limit = parseInt(req.query.limit) || 20;
  const lastId = req.query.lastId;
  const lastCreatedAt = req.query.lastCreatedAt;
  
  let query = 'SELECT * FROM posts WHERE deleted = false';
  const params = [limit];
  
  if (lastId && lastCreatedAt) {
    // Continue from the last seen record
    query += ` AND (created_at, id) < ($2, $3)`;
    params.push(lastCreatedAt, lastId);
  }
  
  query += ' ORDER BY created_at DESC, id DESC LIMIT $1';
  
  const result = await db.query(query, params);
  const posts = result.rows;
  
  const nextKeys = posts.length === limit ? {
    lastId: posts[posts.length - 1].id,
    lastCreatedAt: posts[posts.length - 1].created_at
  } : null;
  
  res.json({
    posts,
    pagination: {
      nextKeys,
      hasNext: posts.length === limit
    }
  });
});

The SQL query uses a WHERE clause on indexed columns, which is dramatically faster than OFFSET:

-- Keyset: Uses index scan
SELECT * FROM posts 
WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC 
LIMIT 20;

-- vs. Offset: Scans and discards rows
SELECT * FROM posts 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

The execution plan difference is stark. Keyset pagination performs an index scan and immediately finds the starting point. Offset pagination must scan through all skipped rows.

For this to work efficiently, you need a composite index:

CREATE INDEX idx_posts_pagination ON posts (created_at DESC, id DESC);

Use keyset pagination when:

Performance is critical
You have large datasets (millions of records)
You can sort by indexed, unique columns
Sequential navigation is acceptable

Choosing the Right Approach

Select your pagination strategy based on your specific requirements:

const paginationConfig = {
  adminPanel: {
    method: 'offset',
    reason: 'Users need page numbers and random access',
    defaultLimit: 50,
    maxLimit: 100
  },
  
  socialFeed: {
    method: 'cursor',
    reason: 'Real-time data with infinite scroll',
    defaultLimit: 20,
    maxLimit: 100
  },
  
  analyticsExport: {
    method: 'keyset',
    reason: 'Large dataset, sequential processing',
    defaultLimit: 1000,
    maxLimit: 5000
  }
};

You can also use hybrid approaches. For example, combine offset for the first few pages (where performance is acceptable) with cursor or keyset for deeper pagination.

Implementation Best Practices

Here’s a complete pagination middleware that supports all three methods:

class PaginationService {
  constructor(config = {}) {
    this.defaultLimit = config.defaultLimit || 20;
    this.maxLimit = config.maxLimit || 100;
  }

  validateParams(limit, offset, cursor) {
    const validatedLimit = Math.min(
      parseInt(limit) || this.defaultLimit,
      this.maxLimit
    );
    
    if (validatedLimit < 1) {
      throw new Error('Limit must be positive');
    }
    
    const validatedOffset = Math.max(parseInt(offset) || 0, 0);
    
    return { limit: validatedLimit, offset: validatedOffset, cursor };
  }

  buildResponse(items, pagination, total = null) {
    const response = { items, pagination };
    
    if (total !== null) {
      response.pagination.total = total;
      response.pagination.totalPages = Math.ceil(total / pagination.limit);
    }
    
    return response;
  }

  async offsetPaginate(query, params) {
    const { limit, offset } = this.validateParams(
      params.limit,
      params.offset
    );
    
    const [items, countResult] = await Promise.all([
      query.limit(limit).offset(offset),
      query.clone().count()
    ]);
    
    return this.buildResponse(items, {
      limit,
      offset,
      hasNext: offset + limit < countResult,
      hasPrevious: offset > 0
    }, countResult);
  }

  async cursorPaginate(query, params, cursorField = '_id') {
    const { limit, cursor } = this.validateParams(params.limit, 0, params.cursor);
    
    if (cursor) {
      const decoded = this.decodeCursor(cursor);
      query = query.where(cursorField).lt(decoded[cursorField]);
    }
    
    const items = await query.limit(limit + 1);
    const hasNext = items.length > limit;
    const results = hasNext ? items.slice(0, -1) : items;
    
    return this.buildResponse(results, {
      limit,
      cursor: hasNext ? this.encodeCursor(results[results.length - 1], cursorField) : null,
      hasNext
    });
  }

  encodeCursor(item, field) {
    return Buffer.from(JSON.stringify({ [field]: item[field] }))
      .toString('base64');
  }

  decodeCursor(cursor) {
    return JSON.parse(Buffer.from(cursor, 'base64').toString('utf-8'));
  }
}

module.exports = PaginationService;

Always include metadata in your responses so clients know how to navigate:

{
  "items": [...],
  "pagination": {
    "limit": 20,
    "hasNext": true,
    "hasPrevious": true,
    "total": 5420,
    "nextCursor": "eyJpZCI6MTIzNDV9"
  }
}

Choose pagination strategies deliberately based on your data characteristics and user needs. Offset works for small datasets with random access requirements. Cursor excels with real-time feeds. Keyset delivers the best performance for large-scale sequential access. Understanding these trade-offs will help you build APIs that scale efficiently and provide excellent user experiences.