Offset vs Cursor vs Keyset Pagination for Backend Interviews

An activity feed with 100 rows is easy to return. At page 5,000, OFFSET 100000 can make the database discard a huge prefix, while new rows arriving between requests make users see duplicates or miss records. Pagination is an API contract and a database access pattern, not merely a limit parameter.

This guide compares offset, cursor, and keyset pagination for backend interviews. It follows idempotency in REST APIs in the Backend Developer Interview Guide.

Table of Contents

The Three Pagination Models

Offset asks the database to skip a count of rows. Keyset asks for rows after a known ordered value. Cursor pagination is the API contract that returns an opaque continuation token; in most production designs, that token encodes the keyset position and sort direction. The distinction matters because an API can hide its physical sort columns while still using an efficient index seek underneath.

All three need a deterministic order. ORDER BY created_at DESC alone is unsafe when several rows share the same timestamp. Add a unique tie-breaker such as id, then use that exact composite order in the query, cursor, and index.

Offset Pagination

Offset is the familiar API shape:

GET /v1/orders?limit=20&offset=40

SELECT id, created_at, total_cents
FROM orders
WHERE account_id = :account_id
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;

It is simple, supports “go to page 37,” and works well for small, mostly static admin tables. Its cost grows with depth because the database must scan or discard earlier rows before returning the next page. On a changing feed, an insert at the front shifts later offsets: a user can see a row twice or skip it entirely. COUNT(*) is also not free on a very large filtered result set.

Keyset Pagination

Keyset, also called seek pagination, starts after the final row the client already received. For a descending feed whose final row is (created_at, id) = ('2026-08-30T12:00:00Z', 900), the next query is:

SELECT id, created_at, total_cents
FROM orders
WHERE account_id = :account_id
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

With an index such as (account_id, created_at DESC, id DESC), the database can seek directly to the boundary instead of walking from row zero. It keeps next-page work roughly tied to page size and is far more stable under inserts. It does not provide true random access: reaching page 500 still means following prior boundaries, and arbitrary sorts need matching indexes.

Cursor Pagination

A cursor is an opaque, versioned token returned by the API instead of exposing created_at and id directly. For example:

{
  "data": [{ "id": "ord_900", "createdAt": "2026-08-30T12:00:00Z" }],
  "page": {
    "nextCursor": "eyJ2IjoxLCJhZnRlciI6WyIyMDI2LTA4LTMwVDEyOjAwOjAwWiIsOTAwXX0",
    "hasMore": true
  }
}

The token can encode the keyset boundary, filter version, direction, and an expiry. Sign or encrypt it if it contains data the client must not alter or see; Base64 alone is only encoding. The server still validates every request against the caller’s tenant and permitted filters. Opaque cursors let you change internal columns later, but they add token lifecycle, debugging, and backwards-compatibility work.

Request Flow and Stable Ordering

flowchart TD
    A[Client requests first page] --> B[Validate filters and limit]
    B --> C[Apply deterministic sort: created_at plus id]
    C --> D{Cursor supplied?}
    D -->|No| E[Read first limit rows]
    D -->|Yes| F[Decode and validate cursor]
    F --> G[Keyset seek after boundary]
    E --> H[Return rows plus next cursor]
    G --> H
    H --> I{More rows?}
    I -->|Yes| J[Client sends next cursor]
    J --> B
    I -->|No| K[Finish]

    classDef flow fill:#e8f0fe,stroke:#1a73e8,stroke-width:2px,color:#000000;
    class A,B,C,D,E,F,G,H,I,J,K flow;

Do not let clients change filters or sort midway through a cursor sequence unless the cursor binds those choices and the endpoint explicitly supports it. Otherwise the boundary refers to one result set while the next query reads another. For a snapshot-like export, record a high-water mark or use a database snapshot, accepting the extra storage or transaction cost needed for a stable view.

How to Choose

Choose offset for a small internal table where page numbers and random access are more valuable than deep-page efficiency. Choose cursor pagination backed by keyset queries for feeds, inboxes, timelines, and public APIs where users continuously request “next” and writes occur frequently. Expose a clear limit maximum in either case; unbounded pages become accidental denial-of-service endpoints and should be protected with rate limiting.

The database index is part of the answer. A keyset predicate that sorts on unindexed fields simply moves the bottleneck. Review database indexes before designing the query, and use the Backend category for the broader path.

Interview Questions

1. Why does offset pagination become slow on deep pages?

The database must locate and discard the offset rows before it can return the requested page. Even when an index helps with ordering, a large offset means work proportional to the skipped prefix rather than the page size. Keyset pagination instead seeks from a known boundary, which makes sequential next-page reads much more predictable. I would still choose offset for a small admin view if users genuinely need page numbers.

2. Why must a keyset sort include a unique tie-breaker?

Timestamps and scores are often shared by several records. If the cursor only stores created_at, a next-page predicate can skip or repeat all rows with the same timestamp at the boundary. Adding a unique id creates a total order and makes the cursor unambiguous. The composite index must use that same order to preserve performance.

3. Is a Base64 cursor secure?

No. Base64 is reversible encoding, so a client can inspect and potentially alter the values. Sign the token to detect changes, or encrypt it when its contents should remain private, then validate its tenant, filters, version, and expiry server-side. Authorization never comes from trusting the cursor itself.

4. Can cursor pagination support previous pages?

Yes, but the API must include a previous boundary and invert the sort predicate to fetch rows before the first item of the current page. Fetch in reverse order, then reverse the returned rows so the client sees the normal ordering. This is more complex than next-only infinite scroll, so I would add it only when the product needs it. Page-number jumps remain a poor fit for keyset traversal.

5. How would you paginate an export consistently while writes continue?

I would define what consistency the export needs. For a strict snapshot, capture a high-water mark or use a database snapshot and bind it into the cursor, so new rows do not shift the result set. For a best-effort feed, deterministic keyset ordering is usually enough and accepts that updates can move items. The choice trades storage and longer-lived database state for a stronger user-visible guarantee.

Conclusion

1. Offset is simple and supports random page access, but deep pages and changing data expose its limits.
2. Keyset pagination seeks from a composite boundary and needs a matching index.
3. Cursor pagination hides that boundary behind a versioned API token.
4. Stable ordering requires a unique tie-breaker, not just a timestamp or score.
5. Choose the contract from user navigation and consistency needs, not from convenience alone.

The next topic in this series covers webhooks - how systems deliver events safely when the receiver may retry or be unavailable. Revisit idempotency for the duplicate-delivery protection webhooks also need.

References

1. Pagination - REST API Guidelines https://opensource.zalando.com/restful-api-guidelines/#pagination
2. LIMIT and OFFSET - PostgreSQL Documentation https://www.postgresql.org/docs/current/queries-limit.html

YouTube Videos

1. “What Are Offset, Cursor, And Keyset API Pagination?” https://www.youtube.com/watch?v=mzQsFdzgon4