Stop Using Offset for Pagination: Switching to Cursor-Based Filtering for Massive Datasets

The Trap: Why OFFSET Destroys Database Performance

Relational database engines cannot jump directly to a specific row offset. To give you rows 500,001 to 500,020, the database engine must actually read through the first 500,000 rows from disk, sort them in memory, discard them, and then return only the 20 rows you requested.

As the offset number gets larger, the query gets slower. This is called a Linear Scan Limitation ($O(N)$ complexity).

The Solution: Cursor-Based Pagination ($O(1)$ Complexity)

Instead of telling the database how many rows to skip, you tell the database exactly where to start looking based on a unique identifier (a cursor) from the previous page's payload.

The Old Offset Way:

-- Database scans 500,000 rows just to throw them away
SELECT * FROM activity_logs ORDER BY id DESC LIMIT 20 OFFSET 500000;

The Cursor Way:

-- Database uses primary index to jump instantly to the exact row ID
SELECT * FROM activity_logs WHERE id < 145023 ORDER BY id DESC LIMIT 20;

Because your id field is indexed, the database utilizes a direct pointer lookup to locate the target row instantly, completely ignoring the preceding millions of rows. It doesn't matter if you are loading page 2 or page 50,000-the query execution time remains constant ($O(1)$).

Implementation in Application Code

When returning a collection payload to your frontend, you simply include the last item's ID as the next_cursor pointer:

{
  "data": [
    { "id": 145025, "event": "user_login" },
    { "id": 145024, "event": "file_upload" },
    { "id": 145023, "event": "user_logout" }
  ],
  "meta": {
    "next_cursor": 145023,
    "has_more": true
  }
}

The frontend reads next_cursor and appends it to the query parameters (?cursor=145023) for the next page request.

Conclusion

If you are architecting large enterprise systems, application performance comes down to minimizing disk I/O operations. Swapping out standard numerical offset pagination for deterministic, index-driven cursors is one of the easiest ways to scale your read-heavy data pipelines smoothly.