Pagination

A technique for dividing large datasets into smaller, manageable chunks called pages.

Pagination divides large datasets into smaller chunks (pages) so APIs can return manageable amounts of data. Without pagination, an endpoint returning 1 million records would crash browsers, timeout requests, and overwhelm servers. Every list endpoint needs pagination.

Why Pagination Matters

Performance: Returning 10,000 records takes seconds. Returning 50 takes milliseconds.

Memory: Large responses consume memory on server and client. Mobile devices especially struggle.

User experience: Nobody scrolls through 10,000 items. Show what's useful, load more on demand.

Database efficiency: LIMIT 50 is fast. SELECT * without limit scans entire tables.

Pagination Strategies

Strategy	Pros	Cons	Best For
Offset-based	Simple, random access	Slow on large datasets, inconsistent with changes	Small datasets, admin panels
Cursor-based	Fast, consistent	No random access	Large datasets, real-time feeds
Keyset	Very fast, scalable	Needs sortable unique key	High-performance APIs
Page number	User-friendly	Same issues as offset	UI with page numbers

Offset-Based Pagination

GET /users?offset=100&limit=20

The database skips first 100 rows, returns next 20.

How it works: SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 100

Problems:

Slow on large offsets: Offset 1,000,000 means database scans 1 million rows
Inconsistent results: If new item added while paginating, items shift

Use when:

Dataset is small (< 10,000 items)
Random page access needed
Data doesn't change frequently

Cursor-Based Pagination

GET /users?cursor=abc123&limit=20

Cursor encodes the position - usually last item's ID or timestamp.

How it works: SELECT * FROM users WHERE id > 'abc123' ORDER BY id LIMIT 20

Benefits:

Fast: Uses index, no row scanning regardless of position
Consistent: New/deleted items don't affect pagination
Scalable: Works with millions of records

Use when:

Large datasets
Infinite scroll / "Load more" UI
Real-time feeds where data changes

Response Format

Always include metadata to help clients navigate:

Essential fields:

data: Array of items
hasMore or hasNextPage: Are there more items?
cursor or nextCursor: Token for next page

Nice to have:

total: Total count (expensive for large datasets)
pageInfo: Detailed pagination info
links: URLs for next/prev/first/last

Calculating Total Count

Total count seems useful but has costs:

Performance hit: COUNT(*) on large tables is slow. A million-row table might take seconds.

Inconsistency: By the time client uses the count, it might be wrong.

Alternatives:

Return hasMore instead of exact count
Cache the count, update periodically
Return estimate: "About 1.2M results"
Only count when explicitly requested

Pagination Best Practices

Always paginate list endpoints: Even if you think the list will be small. Data grows.

Set sensible defaults: limit defaults to 20-50. Max limit of 100-200 prevents abuse.

Validate parameters: Negative offset? Limit of 10,000? Reject with 400 error.

Include pagination in response: Don't make clients track state. Response should contain everything needed for next page.

Use consistent format: All paginated endpoints should return same structure.

Common Mistakes

1. No max limit: Client requests ?limit=1000000 and crashes your server.

2. Offset on large tables: ?offset=500000 takes 10 seconds because database scans 500K rows.

3. Total count on every request: SELECT COUNT(*) runs on every page load, killing performance.

4. Pagination in POST body: Pagination params should be in query string so URLs are shareable/cacheable.

5. Changing sort order breaks cursors: If user sorts by name then by date, previous cursor is invalid. Handle this gracefully.

Choosing the Right Strategy

Use offset when:

Small dataset (< 10K items)
Need "Jump to page 50" feature
Data rarely changes

Use cursor when:

Large dataset
Infinite scroll UI
Real-time data
Mobile app (needs efficiency)

Hybrid approach: Use offset for first few pages (fast enough), switch to cursor for deep pagination.

Code Examples

Cursor-Based Pagination Implementation

// API Endpoint
app.get('/api/posts', async (req, res) => {
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const cursor = req.query.cursor;

  const where = cursor
    ? { id: { gt: cursor } }
    : {};

  const posts = await prisma.post.findMany({
    where,
    orderBy: { id: 'asc' },
    take: limit + 1, // Fetch one extra to check if more exist
  });

  const hasMore = posts.length > limit;
  const data = hasMore ? posts.slice(0, -1) : posts;
  const nextCursor = hasMore ? data[data.length - 1].id : null;

  res.json({
    data,
    pagination: {
      hasMore,
      nextCursor,
      limit,
    }
  });
});

// Client Usage
async function loadAllPosts() {
  let cursor = null;
  let allPosts = [];

  do {
    const url = cursor
      ? `/api/posts?cursor=${cursor}&limit=50`
      : '/api/posts?limit=50';

    const { data, pagination } = await fetch(url).then(r => r.json());
    allPosts.push(...data);
    cursor = pagination.nextCursor;
  } while (cursor);

  return allPosts;
}

Pagination

Why Pagination Matters

Pagination Strategies

Offset-Based Pagination

Cursor-Based Pagination

Response Format

Calculating Total Count

Pagination Best Practices

Common Mistakes

Choosing the Right Strategy

Code Examples

Related Terms

API Testing

Rate Limiting

GraphQL

Query Parameters