Pagination Patterns: Efficiently Loading Large Datasets

Pagination prevents loading entire datasets at once. This guide covers pagination strategies with their tradeoffs.

Offset Pagination#

Simple but has performance issues at scale:

// API endpoint
app.get('/api/posts', async (req, res) => {
  const page = parseInt(req.query.page as string) || 1;
  const limit = parseInt(req.query.limit as string) || 20;
  const offset = (page - 1) * limit;

  const [posts, total] = await Promise.all([
    db.posts.findMany({
      skip: offset,
      take: limit,
      orderBy: { createdAt: 'desc' },
    }),
    db.posts.count(),
  ]);

  res.json({
    data: posts,
    pagination: {
      page,
      limit,
      total,
      totalPages: Math.ceil(total / limit),
      hasNext: page * limit < total,
      hasPrev: page > 1,
    },
  });
});

-- SQL query
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40;

-- Problem: Database still scans offset rows
-- OFFSET 10000 means scanning 10000 rows to skip them

When to Use#

Small datasets (< 10,000 rows)
Need to jump to specific pages
Simple UI requirements

Cursor-Based Pagination#

Better performance for large datasets:

interface CursorPaginationParams {
  cursor?: string;
  limit?: number;
  direction?: 'forward' | 'backward';
}

app.get('/api/posts', async (req, res) => {
  const { cursor, limit = 20 } = req.query;

  const posts = await db.posts.findMany({
    take: limit + 1, // Fetch one extra to check if more exist
    cursor: cursor ? { id: cursor } : undefined,
    skip: cursor ? 1 : 0, // Skip the cursor itself
    orderBy: { createdAt: 'desc' },
  });

  const hasMore = posts.length > limit;
  const data = hasMore ? posts.slice(0, -1) : posts;

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

Encoding Cursors#

// Encode cursor to hide implementation details
function encodeCursor(data: object): string {
  return Buffer.from(JSON.stringify(data)).toString('base64url');
}

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

// Usage
const cursor = encodeCursor({
  id: post.id,
  createdAt: post.createdAt.toISOString(),
});

// Decode on next request
const { id, createdAt } = decodeCursor(req.query.cursor);

Bidirectional Cursor Pagination#

app.get('/api/posts', async (req, res) => {
  const { cursor, direction = 'forward', limit = 20 } = req.query;

  let posts;
  const cursorData = cursor ? decodeCursor(cursor) : null;

  if (direction === 'forward') {
    posts = await db.posts.findMany({
      where: cursorData ? {
        createdAt: { lt: new Date(cursorData.createdAt) },
      } : undefined,
      take: limit + 1,
      orderBy: { createdAt: 'desc' },
    });
  } else {
    posts = await db.posts.findMany({
      where: cursorData ? {
        createdAt: { gt: new Date(cursorData.createdAt) },
      } : undefined,
      take: limit + 1,
      orderBy: { createdAt: 'asc' },
    });
    posts.reverse();
  }

  const hasMore = posts.length > limit;
  const data = hasMore ? posts.slice(0, -1) : posts;

  res.json({
    data,
    pagination: {
      nextCursor: hasMore ? encodeCursor({
        id: data[data.length - 1].id,
        createdAt: data[data.length - 1].createdAt,
      }) : null,
      prevCursor: data.length > 0 ? encodeCursor({
        id: data[0].id,
        createdAt: data[0].createdAt,
      }) : null,
    },
  });
});

Keyset Pagination#

Most efficient for sorted data:

// Using multiple columns for stable ordering
app.get('/api/posts', async (req, res) => {
  const { lastCreatedAt, lastId, limit = 20 } = req.query;

  const whereClause = lastCreatedAt && lastId
    ? {
        OR: [
          { createdAt: { lt: new Date(lastCreatedAt) } },
          {
            createdAt: new Date(lastCreatedAt),
            id: { lt: lastId },
          },
        ],
      }
    : undefined;

  const posts = await db.posts.findMany({
    where: whereClause,
    take: limit + 1,
    orderBy: [
      { createdAt: 'desc' },
      { id: 'desc' },
    ],
  });

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

  res.json({
    data,
    pagination: {
      hasMore,
      nextParams: hasMore ? {
        lastCreatedAt: lastPost.createdAt.toISOString(),
        lastId: lastPost.id,
      } : null,
    },
  });
});

-- Efficient SQL with index on (created_at DESC, id DESC)
SELECT * FROM posts
WHERE (created_at, id) < ('2024-01-15 10:30:00', 'abc123')
ORDER BY created_at DESC, id DESC
LIMIT 20;

GraphQL Pagination (Relay Style)#

type Query {
  posts(first: Int, after: String, last: Int, before: String): PostConnection!
}

type PostConnection {
  edges: [PostEdge!]!
  pageInfo: PageInfo!
  totalCount: Int
}

type PostEdge {
  cursor: String!
  node: Post!
}

type PageInfo {
  hasNextPage: Boolean!
  hasPreviousPage: Boolean!
  startCursor: String
  endCursor: String
}

// Resolver implementation
const postsResolver = async (_, args) => {
  const { first, after, last, before } = args;
  const limit = first || last || 20;

  const posts = await fetchPosts({ after, before, limit: limit + 1 });

  const hasMore = posts.length > limit;
  const nodes = hasMore ? posts.slice(0, -1) : posts;

  return {
    edges: nodes.map(post => ({
      cursor: encodeCursor({ id: post.id }),
      node: post,
    })),
    pageInfo: {
      hasNextPage: first ? hasMore : false,
      hasPreviousPage: last ? hasMore : false,
      startCursor: nodes[0] ? encodeCursor({ id: nodes[0].id }) : null,
      endCursor: nodes.length ? encodeCursor({ id: nodes[nodes.length - 1].id }) : null,
    },
  };
};

Frontend Implementation#

React with Infinite Scroll#

function PostList() {
  const [posts, setPosts] = useState<Post[]>([]);
  const [cursor, setCursor] = useState<string | null>(null);
  const [hasMore, setHasMore] = useState(true);
  const [loading, setLoading] = useState(false);

  const loadMore = async () => {
    if (loading || !hasMore) return;

    setLoading(true);
    const params = new URLSearchParams({ limit: '20' });
    if (cursor) params.set('cursor', cursor);

    const response = await fetch(`/api/posts?${params}`);
    const { data, pagination } = await response.json();

    setPosts(prev => [...prev, ...data]);
    setCursor(pagination.nextCursor);
    setHasMore(pagination.hasMore);
    setLoading(false);
  };

  // Intersection Observer for infinite scroll
  const observerRef = useRef<IntersectionObserver>();
  const lastPostRef = useCallback((node: HTMLElement | null) => {
    if (loading) return;
    if (observerRef.current) observerRef.current.disconnect();

    observerRef.current = new IntersectionObserver(entries => {
      if (entries[0].isIntersecting && hasMore) {
        loadMore();
      }
    });

    if (node) observerRef.current.observe(node);
  }, [loading, hasMore]);

  return (
    <div>
      {posts.map((post, index) => (
        <PostCard
          key={post.id}
          post={post}
          ref={index === posts.length - 1 ? lastPostRef : null}
        />
      ))}
      {loading && <Spinner />}
    </div>
  );
}

Comparison#

Method	Performance	Jump to Page	Consistency
Offset	Poor at scale	Yes	Inconsistent
Cursor	Good	No	Consistent
Keyset	Excellent	No	Consistent

Use offset pagination for simple cases with small datasets. Switch to cursor or keyset pagination for large datasets or real-time data. Cursor pagination provides the best balance of performance and usability for most applications.