Cursor-based Pagination: High-Performance API Design

Previously in this course, we covered Offset and Limit Pagination: Implementing Scalable REST API Requests. While that approach is excellent for small-to-medium datasets, it fails as your database grows. This lesson introduces cursor-based pagination, a strategy that maintains high performance regardless of how deep a user navigates into your data.

Why Offset Pagination Fails at Scale

In offset-based pagination, the server executes queries like SELECT * FROM tasks LIMIT 10 OFFSET 10000. To satisfy this request, the database must scan through the first 10,000 rows just to discard them and return the next 10.

As the OFFSET increases, the database latency grows linearly. Furthermore, offset pagination is prone to "data skipping" or "duplicate items" if records are added or deleted while the user is navigating pages. Cursor-based pagination—often called keyset pagination—solves this by using a pointer (the "cursor") to the last seen item, allowing the database to jump directly to the next set of records.

Cursor-based Pagination from First Principles

Instead of asking for "page 5," the client asks for "10 items after this specific task." The "cursor" is typically an encoded string (often Base64) containing the unique identifier and the sort value of the last item in the previous set.

Worked Example: Implementing a Cursor

To implement this in our Task Manager API, we need to sort by a stable field, such as created_at (and use the id as a tie-breaker for identical timestamps).

When a client requests the first page, we return the tasks and a cursor to the last item.

Request: GET /v1/tasks?limit=5

Response:

JSON
{
  "data": [...],
  "meta": {
    "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyMy0xMC0wMSAxMDowMDowMCIsImlkIjoiNTUifQ=="
  }
}

The next_cursor is a Base64 encoding of {"created_at": "2023-10-01 10:00:00", "id": "55"}. When the client makes the next request, they send this cursor back:

Request: GET /v1/tasks?limit=5&cursor=eyJjcmVhdGVkX2F0IjoiMjAyMy0xMC0wMSAxMDowMDowMCIsImlkIjoiNTUifQ==

Server logic: The server decodes the cursor and executes:

SQL
SELECT * FROM tasks 
WHERE (created_at, id) < ('2023-10-01 10:00:00', '55') 
ORDER BY created_at DESC, id DESC 
LIMIT 5;

Because the database uses an index on (created_at, id), it performs a direct lookup rather than a scan.

Hands-on Exercise

Refactor your existing task retrieval logic.

1. Create a helper function that accepts a cursor string, decodes it, and returns the filter criteria.
2. Update your GET /v1/tasks endpoint to check for a cursor query parameter.
3. If the parameter exists, inject the WHERE clause into your database query; otherwise, default to standard sorting.
4. Ensure your response includes the next_cursor metadata so clients can continue fetching.

Common Pitfalls

• Non-Unique Sorting: If you sort only by a non-unique field (like status), your pagination will break when multiple items share the same value. Always include a unique identifier (like id) as a secondary sort key.
• Exposing Raw IDs: Never pass raw database primary keys in the cursor. Always Base64 encode the cursor object. This allows you to change the structure of your cursor later without breaking client integrations.
• The "Random Access" Expectation: Cursor-based pagination prevents jumping to arbitrary pages (e.g., "Page 50"). Inform your frontend team that this API only supports "Next" and "Previous" navigation.

FAQ

Can I use cursor-based pagination for search results? Yes, but you must ensure the search results are ordered deterministically. If the order changes between requests, your cursors will become invalid.

Should I ever use Offset pagination? Yes, if the dataset is small (e.g., a list of countries) or if your UI requires random page access (e.g., a pagination bar at the bottom of a table).

Is Base64 encryption? No, it is encoding. Anyone can decode your cursor. Do not store sensitive information like PII (Personally Identifiable Information) in your cursor object.

Recap

Cursor-based pagination provides the high-performance API design needed for large datasets. By using stable pointers instead of offsets, you avoid database scans and ensure consistency for your users. As discussed in our previous pagination guide, choosing the right strategy is vital for long-term API health.

Up next: We will begin our journey into documentation by exploring the OpenAPI Specification and how it standardizes your API contract.