Offset vs Cursor Pagination: The Mistake Most Backend Engineers Make

Pagination is the process of dividing a large dataset into smaller, more manageable chunks. In the context of an API, returning all records at once can lead to excessive memory usage, slower database performance, increased response times, and a poor user experience. To address these issues, two common pagination strategies are employed: offset pagination and cursor pagination.

Offset pagination is the more traditional approach. When a request is made, the database queries skip a specified number of rows and returns the next set of records. For instance, a request like GET /transactions?page=3&limit=10 would result in the database executing a query such as SELECT * FROM transactions ORDER BY created_at DESC LIMIT 10 OFFSET 20.

While offset pagination is easy to implement and allows users to jump directly to any page, it becomes increasingly inefficient as the dataset grows. Large offsets mean the database has to scan and skip through countless rows, resulting in slower queries. Additionally, new records being inserted while users are browsing can lead to duplicate or missing records in subsequent pages.

Cursor pagination, on the other hand, offers a more efficient solution. Instead of specifying the number of rows to skip, it continues from the last record previously received. A request using cursor pagination might look like GET /transactions?limit=10, returning a response containing the data along with a 'next_cursor' value. The client then makes a subsequent request using this cursor value, such as GET /transactions?cursor=2026-07-31T16:06:30.

The database query is then executed using the cursor value as an index, like SELECT * FROM transactions WHERE created_at <= 2026-07-31T16:06:30 ORDER BY created_at DESC LIMIT 10. This method allows the database to jump directly to the next location, significantly speeding up the process. It also avoids the issue of duplicate or missing records caused by new inserts during browsing sessions.

To account for whether there are more records to load, production systems often fetch one extra record when using cursor pagination. If eleven records are returned, the first ten are displayed, and the tenth record is used as the cursor. If only ten records are returned, it indicates that there are no more records to load.

In summary, while offset pagination is simple and user-friendly, cursor pagination outperforms it in terms of speed and consistency, especially for large datasets. By always continuing from the last item returned, cursor pagination ensures that new records do not disrupt the user experience.