Stop Offset Pagination: Switch to Cursor Pagination

When building REST or GraphQL APIs for paginated lists, almost every developer starts with offset-based pagination. It seems intuitive and easy to implement using SQL keywords like LIMIT and OFFSET. However, as your database grows to hundreds of thousands or millions of records, offset-based pagination becomes a major architectural bottleneck, causing severe query latency and subtle data consistency bugs.

In this article, we will examine why offset pagination fails at scale and how switching to cursor-based pagination can deliver constant-time O(1) query performance for your applications.

The Hidden Cost of OFFSET

To understand why offset pagination degrades performance, consider how a database engine executes a query with a high offset value:

SELECT id, title, created_at
FROM articles
ORDER BY id DESC
LIMIT 20 OFFSET 500000;

You might expect the database to immediately jump to row number 500,001 using an index scan. In reality, standard relational databases like PostgreSQL or MySQL cannot instantly navigate to an arbitrary offset without counting.

The database engine must read 500,020 rows sequentially from the index or table, discard the first 500,000 rows, and return only the remaining 20 rows. As the page number increases, the work performed by the database grows linearly. A query fetching page 1 might take 2 milliseconds, while fetching page 25,000 can easily take over 1,500 milliseconds of heavy CPU and I/O time.

The "Data Drift" Problem

Beyond query latency, offset pagination suffers from severe data consistency issues whenever items are created or deleted while a user is navigating pages.

Imagine a user browsing a news feed:

1. The user fetches Page 1 (LIMIT 10 OFFSET 0) and receives items 1 through 10.
2. While the user reads, 3 new articles are published at the top of the feed.
3. The user clicks "Page 2" (LIMIT 10 OFFSET 10).

Because 3 new rows were prepended, the previous items 8, 9, and 10 have now shifted down into positions 11, 12, and 13. As a result, Page 2 returns items 8, 9, and 10 again. The user experiences duplicate content.

Conversely, if items are deleted, the dataset shrinks, causing the user to accidentally skip items altogether.

How Cursor-Based Pagination Works

Cursor-based pagination (also known as keyset pagination) eliminates both performance degradation and data drift by replacing page numbers with a pointer to a specific record.

Instead of asking the database to "skip 500,000 rows", cursor pagination asks the database to "fetch 20 rows created before record X". Because the cursor refers to an indexed column, the database uses a fast B-tree index lookup to locate the starting point in log time.

Here is how the query changes:

-- Fast O(1) query using a cursor
SELECT id, title, created_at
FROM articles
WHERE id < 499980 -- Cursor value from the last item of the previous page
ORDER BY id DESC
LIMIT 20;

Because id is indexed, the database jumps directly to id < 499980 and reads exactly 20 rows. The execution time remains virtually identical whether you are on the first page or the one-millionth page.

Designing a Clean API Response

In practice, you should encode cursor values into an opaque base64 string rather than exposing raw database IDs directly to client applications. This protects your internal schema and gives you flexibility to change cursor implementations later.

Here is a standard REST response format for cursor pagination:

{
  "data": [
    {
      "id": 499979,
      "title": "Understanding Database Indexing",
      "created_at": "2026-08-20T10:00:00Z"
    }
  ],
  "pagination": {
    "next_cursor": "ZXlKaWFXUWlPakE1T1RrM09Tdz0=",
    "has_more": true
  }
}

When requesting the next page, the client simply sends ?cursor=ZXlKaWFXUWlPakE1T1RrM09Tdz0= back to the server.

When Should You Still Use Offset Pagination?

Cursor pagination is not a universal magic bullet. It comes with trade-offs:

• No arbitrary page jumping: Users cannot jump directly to "Page 42" because page 42 requires knowing the cursor of page 41.
• Strict ordering required: Queries must sort by unique, deterministic columns (e.g., primary key ID or created_at paired with id).

If your product requires explicit numeric pagination controls with direct page jumps, offset pagination remains necessary. However, for mobile feeds, social media timelines, infinite scroll interfaces, and high-volume background API integrations, cursor-based pagination is unequivocally the superior choice.

Conclusion

Continuing to use offset pagination for large datasets will inevitably slow down your database and confuse users with shifting data. By implementing cursor-based pagination, you guarantee predictable O(1) response times and deliver a smooth, reliable experience for your users regardless of database scale.