Offset vs. Keyset Pagination: How to Efficiently Process Millions of Database Records

Imagine that you need to read 10 million records from a database and perform some processing on them. Naturally, the first approach that comes to mind is to read the data in pages, using a specific ordering, process each page, and then move on to the next one. However, as you read more and more records, the database queries start to become slower. The time required to fetch each page gradually increases.

If you have used traditional offset-based pagination, you have probably sent a query like this to the database:

SELECT *
FROM dbo.OrderItems
ORDER BY CreatedAt DESC,
         Id DESC OFFSET 20000 ROWS FETCH NEXT 100 ROWS ONLY;

The problem with this query is that for every page, the database has to scan and skip all the previous rows before it can return the requested records.

A solution that allows us to read large amounts of data more efficiently is Keyset Pagination.

Keyset Pagination

With Keyset Pagination, instead of specifying how many rows to skip, we provide the values of the last record from the previous page.

For example:

SELECT TOP 100
       *
FROM dbo.OrderItems
WHERE CreatedAt < @LastCreatedAt
      OR
      (
          CreatedAt = @LastCreatedAt
          AND Id < @LastId
      )
ORDER BY CreatedAt DESC,
         Id DESC;

In this approach, the database can use the values of CreatedAt and Id as a cursor to find the next set of records, instead of scanning and skipping all the previous rows.

This makes Keyset Pagination much more efficient, especially when dealing with large datasets.

However, every implementation has its own trade-offs.

With a basic Keyset Pagination implementation, you cannot directly jump to an arbitrary page. For example, you cannot simply request page 51 out of 10,000.

Also, the implementation above is designed primarily for moving forward through the dataset. Going back to the previous page requires additional logic.

If you need to support navigation to the first, previous, next, and last pages, you can implement it differently. For example:

switch (direction)
{
    case KeysetPageDirection.First:
        query = query.OrderByDescending(oi => oi.CreatedAt).ThenByDescending(oi => oi.Id);
        queryingAscending = false;
        break;

    case KeysetPageDirection.Last:
        query = query.OrderBy(oi => oi.CreatedAt).ThenBy(oi => oi.Id);
        queryingAscending = true;
        break;

    case KeysetPageDirection.Previous when decodedCursor != null:
        query = query
            .Where(oi =>
                oi.CreatedAt > decodedCursor.CreatedAt ||
                (oi.CreatedAt == decodedCursor.CreatedAt && oi.Id > decodedCursor.Id))
            .OrderBy(oi => oi.CreatedAt)
            .ThenBy(oi => oi.Id);
        queryingAscending = true;
        break;

    case KeysetPageDirection.Next:
    default:
        if (decodedCursor != null)
        {
            query = query.Where(oi =>
                oi.CreatedAt < decodedCursor.CreatedAt ||
                (oi.CreatedAt == decodedCursor.CreatedAt && oi.Id < decodedCursor.Id));
        }
        query = query.OrderByDescending(oi => oi.CreatedAt).ThenByDescending(oi => oi.Id);
        queryingAscending = false;
        break;
}

What About the Frontend?

Implementing Keyset Pagination for a traditionally paginated UI introduces some additional complexity on the frontend.

Instead of simply sending a page number, the client needs to send the cursor associated with the current position.

However, a more common and cleaner approach is to let the backend generate and return a cursor to the frontend rather than exposing the actual database IDs directly.

Typically, this cursor contains the fields required to execute the next query.

In our example, these fields would be:

• Id
• CreatedAt

The cursor is usually serialized first and then encoded using Base64 before being returned to the frontend.

The frontend then sends this cursor back to the backend when requesting the next page.

This keeps the pagination state encapsulated within the cursor and prevents the frontend from having to understand how the database pagination works internally.

When Should You Use Keyset Pagination?

Keyset Pagination is not necessarily a replacement for Offset Pagination in every scenario.

If your application needs a traditional pagination experience where users can jump directly to a specific page—for example, page 51 of 10,000—Offset Pagination may be more appropriate.

However, if your use case involves reading and processing a large volume of data, where you primarily need to move sequentially through the dataset and do not need arbitrary page navigation, Keyset Pagination can be a much better choice.

For scenarios such as batch processing, data exports, background jobs, ETL pipelines, and processing millions of records, Keyset Pagination can significantly improve database performance and provide more predictable query execution times.

Powered by Froala Editor