Cursor vs Offset Pagination for Large Datasets

Pagination is a critical component of web development, determining how applications fetch and present data to users. While offset-based pagination is easy to implement, it creates significant performance bottlenecks and data inconsistencies when querying large datasets. Cursor-based pagination overcomes these limitations by utilizing indexed pointers to fetch records, resulting in constant-time query execution, reduced database load, and stable results that prevent duplicated or skipped records during live data mutations.

Understanding Offset-Based Pagination and Its Flaws

Offset-based pagination relies on an offset value and a limit (e.g., LIMIT 20 OFFSET 100000) to paginate through data. To return the requested page, the database engine must read through all the skipped records from disk or memory before discarding them and returning the desired subset.

As the offset grows, this mechanism scales with \(O(N)\) time complexity:

  • Resource Exhaustion: Requesting page 5,000 with a page size of 20 requires the database to scan, sort, and discard 100,000 records just to return 20. This consumes high CPU and I/O capacity.
  • Data Drift (Missed or Duplicated Records): If an item is added or deleted while a user navigates between pages, the window shifts. A newly inserted record at the top of the dataset pushes all subsequent items down by one, causing the user to see a duplicate record on the next page. Conversely, a deleted record causes an item to be skipped entirely.

How Cursor-Based Pagination Works

Cursor-based pagination (often called keyset pagination) replaces the arbitrary page number with a reference point: the cursor. The cursor represents the unique identifier or timestamp of the last item retrieved in the previous set.

Instead of counting and discarding rows, the database uses a standard WHERE clause combined with an index:

SELECT id, title, created_at 
FROM articles 
WHERE id > 100042 
ORDER BY id ASC 
LIMIT 20;

In this model, the client provides the last seen cursor (100042) in the subsequent API request. The database uses a B-Tree index to seek directly to that exact record and read the next 20 entries.

Why Cursor-Based Pagination Outperforms Offset on Large Datasets

  1. Constant-Time Execution (\(O(1)\) Complexity): Because the query relies on a direct index seek rather than sequential scanning, the execution time for page 1 is identical to the execution time for page 10,000. The database never scans rows it does not intend to return.
  2. Elimination of Page Drift: Since the query targets an absolute position relative to a specific record rather than a relative offset from the start of the table, real-time inserts and deletes do not disrupt pagination. New records added above or below the cursor do not shift the current result set.
  3. Reduced Memory and Disk I/O: Bypassing the need to sort and discard massive row sets significantly reduces buffer pool usage and temporary disk writes on database servers, allowing the system to handle higher concurrent traffic.

Implementation Considerations

While cursor-based pagination is superior for performance and infinite scrolling interfaces, it does not support jumping directly to arbitrary page numbers (e.g., navigating directly to "Page 15"). It also requires the underlying cursor column (or composite columns) to be indexed, unique, and sequentially ordered. For high-volume applications and infinite feeds, these trade-offs are standard, making cursor-based pagination the modern standard for scalable data retrieval.