Cursor-Based Pagination

Cursor-based pagination uses a cursor representing a position in an ordered result set to fetch adjacent pages. It generally provides efficient and stable traversal when records are inserted or removed while a client is paging.

A cursor identifies where the next page should begin rather than describing an absolute page number. An API might return items plus a `nextCursor`, which the client sends back in the next request.

A common implementation uses keyset pagination: sort by a unique, indexed ordering such as `(created_at, id)`, then encode the last returned values into the cursor. The next query uses a condition such as `(created_at, id) < (:createdAt, :id)` for descending order. The unique tie-breaker prevents ambiguous boundaries when multiple rows share the same timestamp.

Cursors are often opaque to clients. The server can encode sort keys, filters, version information, or other state, and may sign or otherwise protect the value against tampering. Even when the cursor is technically decodable, treating it as an API implementation detail makes the contract easier to evolve.

Compared with offset pagination, cursor-based pagination avoids increasingly expensive `OFFSET` scans in many database workloads and does not require the server to maintain a stable numbered page. Its behavior depends on the ordering and consistency model: concurrent changes can still cause records to appear or disappear across requests unless stronger snapshot semantics are provided.

Key points

• Use a deterministic ordering with a unique tie-breaker such as `(created_at, id)`.
• Return a cursor from one response and use it to request the next page.
• Prefer indexed keyset predicates over large `OFFSET` values for scalable traversal.
• Treat cursors as opaque API tokens rather than client-managed database keys.
• Cursor pagination improves traversal stability but does not automatically provide snapshot consistency.

Interview questions

Why would you choose cursor-based pagination over offset pagination?

Cursor pagination can avoid scanning or skipping a large number of preceding rows when the database can use an index for the ordering predicate. It also behaves more predictably when records are inserted or deleted while a client is traversing results. The trade-off is that arbitrary page jumps, such as going directly to page 50, are less natural. Correct cursor design also requires a deterministic ordering.

How would you implement cursor pagination in a SQL query?

First, choose a deterministic ordering, for example `created_at DESC, id DESC`. The cursor contains the last row's `created_at` and `id`. The next query uses a lexicographic boundary such as `(created_at, id) < (:createdAt, :id)` for descending order and applies `LIMIT` for the requested page size. The corresponding columns should be covered by an appropriate index.

What should an API return for cursor pagination?

A typical response contains the current items and pagination metadata such as a `nextCursor`. The client sends that cursor in the next request to continue traversal. The API can also provide whether another page exists, although that can sometimes require extra work. Cursor values should normally be treated as opaque by clients.

Why is a unique tie-breaker important in cursor pagination?

Suppose records are sorted only by `created_at` and several rows have the same timestamp. A cursor containing only that timestamp cannot unambiguously identify which row was the boundary. Depending on the predicate, some rows can be skipped or repeated. Adding a unique field such as `id` creates a deterministic total ordering.

How would you make a cursor safe and evolvable in a public API?

I would expose an opaque token rather than require clients to understand database columns. The token can encode the required position and relevant query state, with integrity protection such as a signature when tampering would be problematic. I would define how cursors interact with filters and sort options so a cursor cannot silently be reused with incompatible parameters. I would also consider expiration, versioning, and backward compatibility if cursors are expected to live for a meaningful period.