When presenting lists of data, pagination is a crucial strategy to prevent overloading client applications or database servers by fetching an entire dataset at once. This approach significantly enhances performance and user experience, especially with large collections of items. Generally, two primary method are employed for pagination: offset-based (also known as limit/offset) and cursor-based pagination. Understanding the nuances of each is key to selecting the most appropriate solution for a given application.

Offset-Based Pagination

Offset-based pagination retrieves a specific "page" of results by skipping a certain number of initial records (the offset) and then fetching a fixed quantity of subsequent records (the limit). This method is widely supported by relational databases through constructs like LIMIT and OFFSET clauses.

SELECT * FROM products
ORDER BY product_id ASC
LIMIT 15 OFFSET 30;

This SQL query would retrieve the 15 products immediately following the first 30 ordered by their ID, effectively fetching the third "page" if each page contains 15 items.

Advantages:

• Simplicity: It is straightforward to implement and understand, making it an accessible choice for many developers.
• Direct Page Navigation: Users can easily jump to any specific page number (e.g., page 1, page 5, page 10) by calculating the corresponding offset.

Disadvantages:

• Performance Degradation: For very large datasets, skipping a substantial number of records can become inefficient. The database still needs to scan (or at least consider) the skipped records, leading to slower query times as the offset increases.
• Inconsistent Results with Mutations: If records are added or deleted while a user is navigating through pages, the offsets might shift, causing items to be duplicated across pages or entirely missed. This "phantom problem" makes it less suitable for rapidly changing data.

Cursor-Based Pagination

Cursor-based pagination (sometimes called keyset pagination) utilizes a "cursor" to mark a specific point within the dataset from which the next set of results should be retrieved. This cursor is typically an immutable, unique, and sequential identifier from the last item on the previous page, such as a timestamp or a primary key. The query then fetches records "after" or "before" this cursor.

SELECT * FROM orders
WHERE order_date < '2024-07-20T10:30:00Z'
ORDER BY order_date DESC
LIMIT 20;

This query retrieves 20 orders placed before a specific timestamp, ordered in descending chronological order. The timestamp of the 20th order returned would then serve as the cursor for the subsequent page.

Advantages:

• Improved Performance: Queries are generally more performant, especially for large datasets, as the database can directly seek to the cursor position using an index without scanning numerous skipped records.
• Resilience to Data Changes: This method is more robust against data mutations. Adding or deleting records will not cause items to be skipped or duplicated across pages, as the navigation is relative to a specific data point rather than an absolute position.
• Infinite Scrolling Friendly: It naturally supports "load more" or infinite scrolling patterns, as it inherently requests the "next N items" from the last seen item.

Disadvantages:

• Complexity: Implementation can be more intricate, requiring careful handling of cursor generation, encoding, and decoding (especially if the cursor involves multiple fields or needs to be opaque to the client).
• No Direct Page Jumps: Users cannot easily jump to an arbitrary page number (e.g., "go to page 5") because navigation is strictly sequential (next/previous).

When to Choose Which Strategy

• Offset-Based Pagination is suitable for:
  • Smaller datasets where performance isn't a critical bottleneck.
  • Administrative interfaces or dashboards where users might need to jump to specific pages quickly.
  • Data that is relatively static or changes infrequently.
  • Simpler applications or prototypes where rapid development is prioritized.
• Cursor-Based Pagination is ideal for:
  • Large, frequently updated datasets, such as social media feeds, live activity streams, or e-commerce product listings.
  • Applications requiring high performance and consistent results even with concurrent data modifications.
  • User expreiences that favor infinite scrolling or continuous "load more" functionality.
  • API designs where clients only need to navigate forward or backward from their current position.

Implementation Examples (React/TypeScript)

Offset-Based Pagination Example

This example demonstrates fetching a specific page of items from an API endpoint, using pageNumber and itemsPerPage to calculate the offset.

interface Item {
  id: string;
  name: string;
  // ... other properties
}

async function retrievePagedItems(pageNumber: number, itemsPerPage: number): Promise<Item[]> {
  const dataOffset = pageNumber * itemsPerPage;
  const response = await fetch(`/api/items?offset=${dataOffset}&limit=${itemsPerPage}`);
  if (!response.ok) {
    throw new Error('Failed to fetch items');
  }
  return await response.json();
}

// React Component Usage
import React, { useState, useEffect } from 'react';

function ItemListPage() {
  const [displayedItems, setDisplayedItems] = useState<Item[]>([]);
  const [currentPageIndex, setCurrentPageIndex] = useState(0); // 0-indexed page number
  const pageSize = 10;

  useEffect(() => {
    const loadItems = async () => {
      try {
        const fetched = await retrievePagedItems(currentPageIndex, pageSize);
        setDisplayedItems(fetched);
      } catch (error) {
        console.error("Error loading items:", error);
      }
    };
    loadItems();
  }, [currentPageIndex, pageSize]); // Re-fetch when page or page size changes

  const goToNextPage = () => setCurrentPageIndex(prev => prev + 1);
  const goToPreviousPage = () => setCurrentPageIndex(prev => Math.max(0, prev - 1));

  return (
    <div>
      <h2>Product Catalog</h2>
      <ul>
        {displayedItems.map(item => (
          <li key={item.id}>{item.name}</li>
        ))}
      </ul>
      <button onClick={goToPreviousPage} disabled={currentPageIndex === 0}>Previous</button>
      <span> Page {currentPageIndex + 1} </span>
      <button onClick={goToNextPage}>Next</button> {/* Add logic to disable if no more pages */}
    </div>
  );
}

Cursor-Based Pagination Example

This example demonstrates fetching data using a cursor, suitable for "load more" or infinite scrolling patterns.

interface DataEntry {
  id: string;
  timestamp: string; // Used as the cursor key
  content: string;
  // ... other properties
}

interface FetchResult {
  entries: DataEntry[];
  nextCursorToken: string | null;
}

async function fetchDataSegment(startCursor: string | null, segmentSize: number): Promise<FetchResult> {
  // Assuming a backend API that accepts a 'cursor' and 'count' parameter
  const cursorParam = startCursor ? `cursor=${encodeURIComponent(startCursor)}` : '';
  const response = await fetch(`/api/feed?${cursorParam}&count=${segmentSize}`);
  if (!response.ok) {
    throw new Error('Failed to fetch data segment');
  }
  const result: { data: DataEntry[], next: string | null } = await response.json();
  return { entries: result.data, nextCursorToken: result.next };
}

// React Component Usage
import React, { useState, useEffect, useCallback } from 'react';

function FeedDisplay() {
  const [feedEntries, setFeedEntries] = useState<DataEntry[]>([]);
  const [currentCursor, setCurrentCursor] = useState<string | null>(null);
  const [isLoading, setIsLoading] = useState(false);
  const [hasMoreData, setHasMoreData] = useState(true);
  const entriesPerLoad = 15;

  const loadMoreEntries = useCallback(async () => {
    if (isLoading || !hasMoreData) return;

    setIsLoading(true);
    try {
      const { entries, nextCursorToken } = await fetchDataSegment(currentCursor, entriesPerLoad);
      setFeedEntries(prevEntries => [...prevEntries, ...entries]);
      setCurrentCursor(nextCursorToken);
      if (!nextCursorToken || entries.length === 0) { // No more data if cursor is null or no entries returned
        setHasMoreData(false);
      }
    } catch (error) {
      console.error("Error fetching feed entries:", error);
      setHasMoreData(false); // Stop trying to load more on error
    } finally {
      setIsLoading(false);
    }
  }, [currentCursor, isLoading, hasMoreData, entriesPerLoad]);

  useEffect(() => {
    // Initial load
    loadMoreEntries();
  }, []); // Empty dependency array means this runs once on mount

  return (
    <div>
      <h2>Activity Feed</h2>
      <ul>
        {feedEntries.map(entry => (
          <li key={entry.id}>
            <strong>{new Date(entry.timestamp).toLocaleString()}:</strong> {entry.content}
          </li>
        ))}
      </ul>
      {hasMoreData && (
        <button onClick={loadMoreEntries} disabled={isLoading}>
          {isLoading ? 'Loading...' : 'Load More'}
        </button>
      )}
      {!hasMoreData && !isLoading && <p>No more entries to load.</p>}
    </div>
  );
}