How Cursor Pagination Really Works Under the Hood

Cursor pagination is widely used in modern APIs and web backends, yet many developers only understand its surface level behavior. Underneath, cursor based paging relies on specific ordering guarantees, cursor encoding, stable indexes, and efficient query strategies. This article explains the underlying mechanics in detail so you can design and reason about cursor pagination confidently.

Why Cursor Pagination Exists

Offset pagination (LIMIT and OFFSET) seems intuitive, but it becomes inefficient as data grows. Large offsets force your database to scan and discard increasing amounts of rows, which results in slower queries. Offset pagination also suffers from race conditions. If data is inserted or removed during browsing, the same item can appear on multiple pages or be skipped entirely.

Cursor pagination avoids these pitfalls by using stable points in the dataset to mark a user’s position rather than using a numeric offset. This approach avoids costly scans and provides more consistent results when the dataset changes.

Core Principles of Cursor Pagination

1. A Cursor Is a Pointer to a Position in a Sorted Dataset

A cursor is not a page number. It represents the sort key of the last item in the previous page. When the client requests the next page, it provides the cursor, and the server queries items strictly greater or less than that cursor depending on direction.

This implies one critical design requirement

• **You must have a deterministic, stable sort order.***

Most implementations use an indexed column such as:

• Created timestamp
• Updated timestamp
• Auto increment ID
• UUID with embedded time
• Composite key

Without a stable ordering, cursor pagination breaks because the server cannot reliably determine what comes before or after the cursor.

How Cursors Are Generated

A cursor is usually not the raw ID or timestamp by itself. Instead, the value is encoded to hide internal structure and prevent tampering.

Common encoding patterns:

• Base64 encoding of the sort key
• JSON object of multiple fields encoded as Base64
• Signed tokens to prevent modification
• Encrypted values for strong protection

Example If the last item in a page has an ID of 150, the server may generate a cursor like

• Y3Vyc29yOjE1MA==* Client sends this cursor back, and the server decodes it internally to retrieve the actual value.

How the Database Query Works

Cursor pagination relies on comparison rather than skipping.

Basic structure:

SELECT *
FROM items
WHERE id > :cursor_value
ORDER BY id ASC
LIMIT :page_size + 1;

Key details:

1. The query uses a comparison operator

Using > or < allows the database to perform an indexed range scan. Range scans scale efficiently even with large tables.

2. One extra item is fetched

Fetching page_size + 1 items helps determine if the next page exists. If you requested 20 items, the database fetches 21.

• If 21 results appear, a next cursor can be generated.
• If not, it is the final page.

3. Indexing is mandatory

Without an index on the sort key, cursor pagination loses its efficiency advantage.

Handling Backward Pagination

Backward pagination requires special care. If you support both forward and backward navigation, you must:

1. Reverse the ordering before querying
2. Apply comparison in the opposite direction
3. Re reverse the results so they return in user facing order

Example for fetching previous page:

SELECT *
FROM items
WHERE id < :cursor
ORDER BY id DESC
LIMIT page_size + 1;

Then reverse the list before returning it to the client.

Backward pagination often requires including both a start cursor and an end cursor so the client can navigate in both directions cleanly.

Dealing with Changing Data

Data changes can introduce anomalies. Cursor pagination reduces these issues, but certain scenarios require planning.

Insertions

If new records are inserted, they appear only on future pages and do not disturb previously viewed pages.

Deletions

If an item in a previous page is deleted, the cursor still works because it refers to a position relative to the dataset, not an offset.

Updates

Updating the sort key can cause jumps or disappearance of items. To avoid this, always sort by a field that does not change. If you need multi field sorting, include a tiebreaker such as a unique ID that remains stable.

Composite Cursors

Sometimes a single ordering field is not enough. You may need a deterministic multi column ordering.

Example composite key:

• created_at
• id as a tiebreaker

Your cursor must then include both values.

Cursor data might look like:

{
  "created_at": "2025 11 01T10:03:22Z",
  "id": 19422
}

This pair uniquely identifies the item’s position even if multiple items share the same timestamp.

Designing Cursor APIs

1. Return explicit fields

A good response includes:

• items
• next_cursor
• prev_cursor
• has_next
• has_prev

2. Make cursor values opaque

Clients should never depend on the internal format.

3. Keep payloads small

Cursor pagination encourages real time data browsing. Small responses help maintain responsiveness.

Common Mistakes

• Using a non indexed sort key, this destroys performance.
• Changing the sort field during data updates, breaks cursor consistency.
• Using offset pagination for the first page and cursor pagination afterwards, this introduces subtle inconsistencies.
• Allowing clients to manipulate raw cursor values, You must encode and validate cursors.

When Not to Use Cursor Pagination

Cursor pagination is powerful but not universal. Avoid it in cases like:

• Arbitrary sorting selected by the user
• Complex filter combinations without stable indexes
• Reporting or exporting tasks that depend on static snapshots

In these cases, offset pagination or full dataset streaming may be acceptable.

Summary

Cursor pagination works by:

• Sorting data with a stable, indexed key
• Using a cursor that encodes the position of the last item
• Performing efficient range queries
• Avoiding unstable offset based skipping
• Providing consistent navigation even as data changes

Understanding the internal mechanics gives you the confidence to implement robust cursor based pagination and avoid pitfalls that cause inconsistent or inefficient behaviour.

I help businesses design reliable software systems and build AI powered automated solutions that streamline operations and reduce manual work. My focus is on creating scalable, efficient architectures tailored to each client’s needs. If you’re looking for a partner who can guide your technical vision from idea to execution, you can reach me on LinkedIn.