The Staff Engineer Pagination Interview: Cursors, Consistency, and Large Feeds

Elasticsearch stops ordinary from/size pagination beyond 10,000 hits by default; DynamoDB caps an individual Query page at 1 MB. Those are documented boundaries, not hypothetical scaling concerns. A pagination interview asks you to explain what happens when the next page crosses one. [1] [2]

The useful exercise is broader than choosing between offsets and cursors. You need to define the ordering, show the access path, trace concurrent changes, and describe recovery when a continuation expires. This guide develops those decisions through an activity feed you can explain at a whiteboard, then extends the design to search and distributed storage.

1. Start With the Traversal Contract

Before drawing a database, ask what the reader is trying to do. A person scrolling recent activity, an administrator jumping to a numbered page, and a worker exporting every matching record have different requirements. Treat them as separate use cases even if the first implementation shares a table.

For an activity feed, propose newest-first order, sequential navigation, and an explicit refresh action for new entries. For a small administrative table, numbered pages may be worth a bounded offset. For an export, ask whether the output must represent one consistent database state, and whether deleted or modified records must retain their original values.

Then establish the constraints: tenant scope, filters, maximum page size, expected traversal depth, and whether sort fields can change. State the product promise in ordinary language: “Continuing moves toward older activity; refreshing starts a new traversal.” That sentence gives you a standard against which to evaluate later decisions.

Expose pagination from the first version of a collection API. Google's AIP-158 explains that adding it later can break behavior: a client expecting the entire collection starts receiving only its first page. This is a compatibility decision, even when adding the response fields appears syntactically harmless. [3]

2. Explain What OFFSET Actually Costs

Offset pagination has an appealing interface: request page number and page size, calculate the starting position, return that slice. Its database work deserves a separate explanation. PostgreSQL documents that skipped rows still have to be computed inside the server; a large OFFSET can therefore be inefficient. [4]

Consider an illustrative OFFSET 100000 LIMIT 50 request with enough matching rows. It asks the query to pass 100,000 results before returning 50: 100,050 results at that boundary, or 2,001 times the returned count. That arithmetic describes traversal work, not measured latency or a guarantee about physical reads. Indexes, filtering, caching, and the chosen plan still matter.

MongoDB makes a similar distinction: its skip() documentation says that increasing offsets slow the operation and recommends indexed range queries for growing traversals. The underlying design question survives a change of database. Where is the next boundary, and can the storage engine seek to it? [5]

Positions move when the collection changes

Trace a toy list ordered A, B, C, D, E, F. Page one returns A, B, C. Insert X at the front; skipping three now returns C, D, E, repeating C. In a separate scenario, delete A after page one; skipping three returns E, F, missing D. These are consequences of the example's changing positions.

Keep offsets where their benefits fit the requirement. Bound the depth and acknowledge what concurrent changes mean. An interview answer becomes more convincing when the choice has a stated operating range.

3. Build a Total, Stable Order

A timestamp alone is an incomplete pagination key when multiple events can share it. Use a unique tie-breaker: ORDER BY created_at DESC, id DESC. PostgreSQL specifically requires a unique ordering for predictable limited subsets. Both fields must travel in the continuation boundary; remembering only the timestamp can discard remaining events tied at that time. [4]

Separate uniqueness from chronological meaning. A unique ID breaks ties; it does not necessarily describe commit order. MongoDB warns that ObjectIds are not necessarily monotonic, including because of their one-second time resolution and differing client clocks. Choose the primary sort field to match the product's actual meaning of “newest.” [5]

For the example, make created_at and id non-null and immutable. PostgreSQL compares row constructors from left to right for inequality operators; a decisive null comparison can produce an unknown result. Nullable sort fields need an explicit normalization and matching index strategy, rather than an accidental omission from subsequent pages. [6]

If the interviewer changes the feed to “most popular,” pause. A mutable score can move an already-read entry below the boundary or an unread entry above it. The ordering and consistency contract must be reconsidered together.

4. Work Through a Tenant Activity Feed

Assume a SaaS application stores tenant activity in PostgreSQL. Each request returns up to 50 entries, newest first. Users navigate sequentially, and current authorization determines visibility. These are design assumptions for the exercise, not production measurements.

Align the query and index

CREATE INDEX events_tenant_page_idx
  ON events (tenant_id, created_at DESC, id DESC);

-- Subsequent page; parameters come from validated request state.
SELECT id, created_at, event_type
FROM events
WHERE tenant_id = $1
  AND (created_at, id) < ($2::timestamptz, $3::bigint)
ORDER BY created_at DESC, id DESC
LIMIT 51;

The tenant equality comes first in the index, followed by the ordering fields. PostgreSQL's multicolumn-index documentation describes how leading equalities and the next inequality constrain a B-tree scan. The tuple predicate expresses “strictly older than the last returned key” under this descending order. [7]

For the first page, omit the tuple boundary. Retrieve at most 51 visible, matching rows, return the first 50, and use the extra row to determine whether another page existed for that query. Encode the key of the fiftieth returned row. Using the lookahead row as the cursor would skip it on the next request.

Check current tenant access before either query. If individual events have additional visibility rules, incorporate those predicates before the lookahead limit. Filtering forbidden entries only after selecting 51 rows changes the meaning of both page fullness and the continuation indicator.

A matching B-tree can supply sorted output without a separate sort, and PostgreSQL documents the benefit of combining such an index with LIMIT. That supports the proposed access path; it does not prove every filter combination will use it. [8]

Trace a boundary with equal timestamps

Shrink the page to three entries for the whiteboard. Events 903, 902, 901 share time 10:00; event 900 has time 09:59. Page one ends at (10:00, 901). The strict tuple comparison admits event 900 and any smaller IDs still tied at 10:00. A new event at 10:01 does not shift that boundary.

Deleting event 901 also need not break continuation: the token stores its ordering values, so the next query does not need to find the original row. This is a useful reason to store a complete boundary rather than requiring a lookup of the last object.

For a previous-page request, seek greater than the first returned key, retrieve the nearest entries in ascending order, and reverse them before returning the usual descending presentation. Relay's connection specification requires forward and backward pagination to preserve the same edge ordering. Direction changes the retrieval operation; the response order remains consistent. [9]

5. Separate Cursors From Snapshots

A boundary specifies where the next query resumes. It does not, by itself, preserve the original collection. PostgreSQL's default Read Committed isolation gives each statement a snapshot at statement start; successive statements can see different committed data. Separate HTTP page requests therefore do not inherit one shared snapshot just because they reuse a cursor. [10]

For our live feed, accept that later pages reflect current visibility and deletions. Immutable ordering keys avoid movement caused by edits to those keys, but a newly committed event with an older timestamp can still enter the remaining range. An item inserted above a boundary already passed will not be discovered by continuing downward.

An optional first-page upper fence can exclude entries whose ordering keys exceed the initial maximum. Describe its limit carefully: backdated entries or transactions committing later with smaller keys can still pass the fence. A timestamp or maximum allocated ID is not automatically a committed-data watermark. The exercise must establish those semantics before promising a frozen membership.

Give exports an explicit snapshot mechanism

For a bounded database export, a single PostgreSQL Repeatable Read transaction provides a stable view across its queries. Avoid holding that transaction open while a human scrolls for an unbounded period: old row versions cannot be reclaimed while they remain visible to a transaction. Snapshot lifetime is an operational cost. [10] [11]

Another proposed design is a background job that materializes the export's required data under a defined snapshot, then serves its stored output. Materializing IDs alone preserves a membership list, but later hydration can still change values or encounter deletions. Match the saved representation to the promised result.

Kubernetes illustrates a real API with an explicit stronger contract: chunked list requests retain the collection's resourceVersion and provide a consistent snapshot. Its continuation can expire, producing 410 Gone. That guarantee belongs to Kubernetes's documented mechanism; arbitrary cursor APIs do not inherit it. [12]

6. Design the Cursor as a Public Contract

Propose a token containing an internal format version, the last ordering tuple, direction, normalized-filter fingerprint, tenant scope, and expiry. If the traversal uses a snapshot, include its reference too. Explain which fields are implementation choices and which request properties must remain compatible on continuation.

Keep the token opaque to clients. AIP-158 requires opaque, URL-safe page tokens and explicitly warns that base64-wrapping a transparent value is insufficient. It also distinguishes page-size changes, which its contract permits, from changes to the other request arguments. Validate a reused cursor against the query it represents. [3]

Encoding and integrity are different concerns. RFC 4648 states that base encodings provide no computational confidentiality. HMAC supplies keyed message authentication, as defined in RFC 2104; it can help detect token tampering but does not conceal the payload. Use a vetted token format and cryptographic library, or store continuation state behind a random opaque handle when that better fits the contract. [13] [14]

Reauthorize every request. AIP-158 says page tokens must not grant access to underlying resources. Binding a token to a tenant prevents accidental cross-query reuse; it does not replace current tenant membership and object-visibility checks. Decide how revoked access affects the next page. [3]

Return a recognizable invalid-or-expired-continuation error and a clear restart path. Silently interpreting a bad token as page one can create duplicate processing while making the client believe traversal succeeded.

7. Respect Each Provider's Continuation Rules

Do not teach one universal termination rule. Slack says a response may contain fewer results than requested while more remain; clients should inspect response_metadata.next_cursor. Google's pagination guidance likewise permits short or empty pages before the end, with continuation signaled through the token. [15] [3]

DynamoDB makes this distinction concrete. Pass the returned LastEvaluatedKey as the next request's ExclusiveStartKey. A nonempty key means evaluation stopped at a boundary, not that another matching item definitely exists; continue until the key is absent or empty. [2]

Its Query API applies filters after reading up to the item limit or 1 MB cap. Every evaluated item can be filtered out, yielding an empty result with continuation. If your API promises a full page of visible items, it may need several bounded backend requests to fill one response. [16]

Storage consistency is a separate axis. DynamoDB supports strongly consistent reads on tables and local secondary indexes; global secondary indexes support eventual reads. Neither the continuation key nor “strong read” wording establishes a snapshot spanning separate page requests. [17]

Stripe's list APIs accept object-ID cursors through mutually exclusive starting_after and ending_before parameters, with reverse chronological ordering. GitHub GraphQL instead exposes connection cursors and page information, requiring first or last between 1 and 100. Adapt the client to the provider's contract. [18] [19]

8. Handle Search and Distributed Feeds

Elasticsearch documents why deep offsets are expensive: each shard loads the requested hits and preceding hits into memory. Its recommended continuation uses the last hit's sort values in search_after; a point in time preserves the index view across requests. Keep query and sort unchanged, retain the tie-breaker values, and use the latest returned PIT ID. [1]

Elastic also documents the cost of keeping PITs open: retained segments require disk space and file handles, and updated or deleted documents can require additional heap. Set retention around continuation needs, cap concurrent contexts, and close completed traversals. A snapshot feature needs a resource budget and an expiry policy. [20]

For a proposed feed merged from several partitions, define a common total order before discussing the merge algorithm. Fetch candidates from each partition and select the next global page. The cursor must preserve enough progress to continue that merge; a single timestamp is insufficient when ties or partition-local positions remain unresolved.

Be careful with prefetching. A partition may fetch entries that the global page does not emit. Advancing its saved cursor past those unused entries loses them. Either retain the buffered candidates, persist progress only through emitted entries, or use an equivalent boundary scheme that can reconstruct the remainder.

If one partition is unavailable, choose explicitly between failing the page and returning a marked partial result with recoverable state. Do not claim complete ordering while silently moving beyond unknown candidates. This is a design tradeoff to discuss, rather than a guarantee supplied by the word “cursor.”

9. Budget the Whole Page Request

A row limit does not bound every kind of work. Measure rows examined, backend calls, serialized bytes, hydration queries, and tail latency alongside returned items. Set a response-byte ceiling and a backend-work ceiling; a filter that accepts very few candidates can otherwise make filling a modest page expensive.

Nested GraphQL connections demonstrate amplification. An illustrative request for 100 parent nodes with 100 children each can request 10,100 nodes before additional nesting. GitHub documents a separate limit of 500,000 requested nodes per call. The page-size limit on one connection is only one part of the request budget. [21]

Also question the requirement for an exact total. PostgreSQL documents that a whole-table count(*) requires work proportional to table size, scanning the table or an index containing all rows. For the feed, propose a continuation indicator without an exact count; provide counts separately when the product needs them. [22]

Validate the access path using EXPLAIN (ANALYZE, BUFFERS) on representative data. PostgreSQL's documentation distinguishes estimated plans from execution measurements. Compare initial and deep pages, selective and broad filters, and warm and cold conditions; examine scans and buffer activity instead of promising constant latency from SQL syntax alone. [23]

10. Rehearse the Staff Engineer Explanation

Practice one coherent walkthrough: define sequential browsing, choose immutable compound ordering, align the index, fetch one extra authorized row, and encode the last returned boundary. Then explain the live-data contract and give exports a separate snapshot mechanism. Each decision should answer a requirement already established.

Use concrete changes to test your reasoning. Insert an entry ahead of the cursor. Delete its boundary row. Add an event with the same timestamp. Change a filter, revoke access, expire a snapshot, and replay a request after a network timeout. For each case, say what the client sees and where it resumes.

Distinguish repeatable retrieval from exactly-once processing. A retry can deliver a page again, and a consumer can fail after writing some output. Propose durable checkpoints and idempotent handling when processing matters; pagination alone cannot settle the consumer's transaction boundary.

The staff-level explanation makes the promised behavior defensible from API to storage to failure recovery. Finish by naming the invariant your design preserves and the condition under which that promise expires. Then let the interviewer change one assumption and show how the design changes with it.