Request Handling
Pagination — Offset vs Cursor-based
Pagination is the technique of breaking large datasets into smaller, manageable chunks to optimize network performance and resource usage. It is critical for maintaining API responsiveness and preventing server overload when dealing with potentially millions of records. Developers must choose between offset and cursor-based strategies depending on the stability of the dataset and the specific performance requirements of the application.
The Challenge of Large Datasets
When a client requests a resource that contains thousands of items, transferring the entire collection in one HTTP response is inefficient and dangerous. It leads to high latency, increased memory consumption on both the client and server, and potential connection timeouts. To solve this, APIs implement pagination, which allows clients to fetch data in slices. The primary design challenge is creating a mechanism that is both intuitive for the consumer and performant for the underlying database. By limiting the number of items returned per request, we ensure that the system remains predictable regardless of how large the total collection grows. Efficient pagination design acknowledges that database scans are expensive operations, and it seeks to minimize the amount of data processed to satisfy any individual API request while allowing for sequential navigation through the entire dataset.
# Example of a standard paginated response structure
{
"data": [{"id": 1, "name": "Resource A"}, {"id": 2, "name": "Resource B"}],
"meta": {
"limit": 2,
"total_count": 150
}
}Understanding Offset-Based Pagination
Offset-based pagination is the most common approach, relying on two parameters: 'limit' and 'offset'. The 'limit' defines the number of records to return, while 'offset' indicates how many records to skip from the beginning of the result set. This model maps directly to SQL syntax, where a query might execute 'SELECT * FROM items LIMIT 10 OFFSET 20'. This approach is highly intuitive because it allows clients to jump to any arbitrary page using a page number. However, it suffers from a significant performance drawback: as the offset increases, the database must scan and discard all preceding records before retrieving the requested chunk. Consequently, offset-based pagination becomes progressively slower as the client navigates deeper into the collection, making it unsuitable for very large datasets or high-frequency access patterns.
# SQL simulation for offset-based pagination
# Querying page 3 with limit 10
# The DB must scan first 20 rows before grabbing the next 10
SELECT * FROM products ORDER BY id ASC LIMIT 10 OFFSET 20;The Cursor-Based Alternative
Cursor-based pagination sidesteps the performance issues of offsets by using a pointer to a specific record in the dataset. Instead of an integer offset, the client sends a 'cursor' string representing the location of the last item received in the previous request. When the server receives this cursor, it performs a search filter to return records strictly 'after' that identifier. This is an O(1) operation if the identifier is indexed, as the database can jump directly to the point where the next set of data begins. This mechanism is inherently stable: even if new records are inserted or existing ones are deleted, the cursor reliably points to the next logical entry in the set, preventing the 'skipped items' or 'duplicate items' problems that frequently plague offset-based systems under concurrent modification.
# Fetching records after a known cursor (e.g., base64 encoded ID)
# This is significantly faster than OFFSET for deep pagination
SELECT * FROM products WHERE id > 'encoded_cursor_value' ORDER BY id ASC LIMIT 10;Handling Data Inconsistency
One of the most dangerous edge cases in API design occurs when the underlying data changes while a user is paginating. In offset-based systems, if an item is inserted at the beginning of the list, an item that was on page one might shift to page two, causing the user to see the same item twice when moving between pages. Cursor-based pagination provides an immutable view of the data. Because the pagination logic is anchored to a specific reference point—the cursor—the relative ordering remains consistent regardless of upstream modifications. This is crucial for applications like real-time feeds, chat histories, or log viewers where temporal consistency is a requirement. By utilizing unique, ordered identifiers as cursors, the API guarantees a seamless experience for the end-user while maintaining strict performance standards across the entire collection.
# Example of an opaque cursor response
{
"data": [...],
"next_cursor": "aWQ6MTAwMDU=" # Base64 encoded reference to last ID
}Choosing the Right Strategy
Deciding between offset and cursor pagination requires evaluating the specific requirements of your client. Use offset-based pagination when the dataset is small, or when random-access navigation (e.g., jumping to 'Page 50') is a functional requirement for the user interface. Conversely, adopt cursor-based pagination for large-scale datasets, infinite scrolls, or high-performance feeds where the user only moves forward sequentially. While cursor-based designs are objectively more efficient and robust, they add complexity, as the client cannot easily jump to an arbitrary page without iterating through all cursors. A well-designed API should favor cursors for data integrity but may provide both if the use case necessitates jumping through pages. Always ensure your sorting criteria are deterministic; otherwise, both strategies will produce unpredictable results during concurrent modifications of the data set.
# Decision logic pseudocode
if use_case == "random_access":
# Use Offset for UI jump capabilities
fetch_with_limit_offset(limit, page * limit)
elif use_case == "infinite_scroll":
# Use Cursor for performance and consistency
fetch_after_cursor(cursor_id, limit)Key points
- Offset pagination uses 'limit' and 'offset' to slice data based on integer indices.
- Cursor pagination uses an opaque pointer to identify the next set of data efficiently.
- Offset-based systems suffer from performance degradation as the offset value grows.
- Cursor-based systems perform at O(1) speed when the cursor column is indexed.
- Offset pagination is prone to duplicates or skipped records if the dataset changes frequently.
- Cursors provide temporal consistency by anchoring pagination to a specific record.
- Random-access navigation is only natively supported by offset-based approaches.
- Deterministic sorting is a mandatory requirement for stable pagination in both models.
Common mistakes
- Mistake: Using offset-based pagination for real-time feeds. Why it's wrong: As data is inserted or deleted, items shift, causing users to see duplicate entries or miss items when moving to the next page. Fix: Use cursor-based pagination with a unique, ordered identifier.
- Mistake: Exposing internal database row IDs as cursors. Why it's wrong: It leaks implementation details and allows clients to guess identifiers, which is a security risk. Fix: Use opaque, base64-encoded strings or UUIDs as cursors.
- Mistake: Implementing 'page number' pagination for large datasets. Why it's wrong: Queries like 'OFFSET 1000000 LIMIT 10' force the database to scan and discard millions of rows, leading to severe performance degradation. Fix: Use keyset (cursor) pagination to seek directly to the data.
- Mistake: Including total record counts in cursor-based responses. Why it's wrong: Calculating an accurate count requires a full table scan, defeating the performance benefits of cursors. Fix: Provide 'has_next_page' or 'next_cursor' metadata instead of total counts.
- Mistake: Allowing arbitrarily large 'limit' parameters. Why it's wrong: A client could request a massive batch size that consumes too much memory or bandwidth, crashing the server. Fix: Enforce a server-side maximum limit and default value for the page size.
Interview questions
What is the basic purpose of pagination in a REST API, and why is it considered a best practice?
The primary purpose of pagination is to break down large datasets into smaller, manageable chunks, which we call pages. It is a best practice because sending a massive response containing thousands of records can lead to high latency, increased memory consumption on the server, and potential timeouts for the client. By limiting the number of items per response, we ensure consistent performance and keep the payload size predictable for developers consuming the API.
How does offset-based pagination work, and what are its key implementation steps?
Offset-based pagination is implemented using two primary query parameters: 'limit' and 'offset'. The 'limit' parameter defines the maximum number of records to return, while the 'offset' defines how many items to skip from the beginning of the dataset. For instance, to get the second page with a limit of 10, the client requests 'offset=10'. The server then executes a database query using 'LIMIT 10 OFFSET 10'. It is very straightforward to implement, but it relies on the absolute position of records, which can be problematic if the underlying data changes frequently.
What is cursor-based pagination, and how does it differ from offset-based pagination in terms of functionality?
Cursor-based pagination replaces the 'offset' parameter with a 'cursor' or 'pagination token'. Instead of saying 'skip 20 items', the client provides a unique pointer—usually an encoded string representing the ID or timestamp of the last item seen in the previous page. The server queries the database for records where the ID is greater than the cursor value. This approach is superior for real-time data because it is not affected by records being inserted or deleted before the current page, ensuring that the client never sees duplicate items or skips records during a continuous fetch.
Could you compare offset-based and cursor-based pagination regarding performance and user experience?
When comparing the two, offset-based pagination performs poorly on large datasets because the database must scan and discard all skipped rows before reaching the desired page, which is an O(N) operation. Cursor-based pagination is much faster because it uses a direct index lookup for the cursor, resulting in O(log N) or O(1) performance regardless of how deep the user is in the data. While offset allows for random page navigation—like jumping to page 50—cursor-based pagination is strictly sequential, which forces a 'load more' user interface pattern but provides a much more stable and efficient experience for the end user.
Why does offset-based pagination often lead to data inconsistency issues in dynamic environments?
Data inconsistency occurs in offset-based pagination because the absolute position of records is unstable. If a user is on page 1 and a new record is inserted at the top of the dataset before they request page 2, all subsequent records shift down by one position. Consequently, the last record from page 1 will reappear as the first record on page 2. This duplicate data delivery is confusing for users and can break business logic, whereas cursor-based pagination remains pinned to specific record identifiers, ensuring that the sequence remains logically consistent regardless of concurrent insertions or deletions.
In a high-scale system, how would you design the response structure for a cursor-based pagination API?
For a robust cursor-based design, the response should include the data array and a 'meta' or 'links' object to assist the client. The 'links' object should provide a 'next' URL that contains the encoded cursor for the subsequent page. For example, if the data is ordered by creation date, the 'next' URL would look like '/api/v1/resources?limit=25&cursor=eyJpZCI6IDEwMn0='. Including a 'has_more' boolean flag in the response is also highly recommended so the frontend knows when to hide the 'load more' button. This opaque token approach allows the server to change the underlying sorting logic or database schema without breaking the client-side implementation, as the client only cares about the token provided by the server.
Check yourself
1. When a client asks for the 'next page' in a system using offset pagination, why might they receive duplicate data?
- A.The server failed to increment the offset value.
- B.New items were inserted into the database before the next request, shifting previous records to earlier offsets.
- C.The cursor identifier was not properly encoded.
- D.The database cache failed to invalidate the previous result set.
Show answer
B. New items were inserted into the database before the next request, shifting previous records to earlier offsets.
Option 2 is correct because offset pagination relies on row position; insertions shift subsequent records. Options 1 and 4 describe implementation bugs, and Option 3 applies to cursor-based pagination, not offset.
2. Which of the following scenarios makes cursor-based pagination strictly superior to offset-based pagination?
- A.The API requires the user to jump directly to page 500.
- B.The dataset is extremely small and static.
- C.The dataset is large, constantly changing, and requires high performance.
- D.The API response must include the total number of pages available.
Show answer
C. The dataset is large, constantly changing, and requires high performance.
Option 3 is correct because cursors avoid expensive scans on large, changing datasets. Option 1 is actually a weakness of cursors, and Option 4 is an offset feature; Option 2 doesn't justify cursors.
3. What is the primary benefit of using an opaque cursor string in a REST API?
- A.It prevents clients from calculating the distance between two pages.
- B.It hides the underlying database indexing strategy from the client.
- C.It reduces the size of the HTTP response header.
- D.It forces the client to perform server-side calculations.
Show answer
B. It hides the underlying database indexing strategy from the client.
Option 1 is incorrect as users can't calculate distance regardless. Option 2 is correct as it encapsulates the implementation. Option 3 is false, and Option 4 is wrong because clients don't calculate cursors.
4. How should a well-designed API handle an invalid or expired cursor provided by a client?
- A.Return a 400 Bad Request error.
- B.Automatically redirect the client to the first page.
- C.Return a 404 Not Found error.
- D.Return the first page of results by default.
Show answer
A. Return a 400 Bad Request error.
Option 0 is correct because an invalid cursor is a client-side error. Option 1 and 3 are bad API practice as they hide client mistakes. Option 2 is incorrect as the cursor represents a specific point in a stream that no longer exists.
5. Why is 'LIMIT' combined with 'OFFSET' generally avoided in high-scale REST APIs?
- A.It is restricted by most modern database engines.
- B.The database must scan and discard all rows preceding the offset, which is inefficient.
- C.It prevents the use of database indices for sorting.
- D.It restricts the API to only allowing sorted data in ascending order.
Show answer
B. The database must scan and discard all rows preceding the offset, which is inefficient.
Option 1 is false. Option 2 is the correct reason (performance overhead). Option 3 is incorrect as sorting can still use indices. Option 4 is wrong because limit/offset works with any sort order.