Cursor Pagination and Choosing an API Protocol

01 The idea

The problem this lesson solves

A user reads the first page of links and then presses Next. In the meantime, another link is created. If page two means “skip the first two rows of whatever exists now,” the user may see a duplicate. Pagination is a consistency contract over a changing order. After solving that problem, we will examine REST-style HTTP, gRPC and GraphQL as choices about client interaction rather than competing labels for the same behavior.

Use the requirements and ownership vocabulary from module 1. This module follows one request across the API boundary.

02 Worked example

Insert a row between two page requests

03 The method

Understand the mechanism

Establish an order before dividing it into pages

A list has no dependable page boundaries without a deterministic order. “Newest first” needs a timestamp and a tie-breaker because several rows can share one timestamp. ORDER BY created_at DESC, id DESC gives each distinct id a position when the pair is unique.

Choose the order to match the access pattern. An owner listing also filters by the authenticated owner. If the query orders by a mutable popularity score, a row can move between pages as its score changes. A cursor does not prevent that movement by itself.

Page size is part of resource control. Accepting LIMIT=1000000 can turn a small listing endpoint into an expensive export. Apply a server-side maximum, return a continuation mechanism and define whether an empty page means the traversal is finished.

The response should identify how to continue without requiring the caller to reverse-engineer database keys. An opaque cursor can encode the ordering tuple and relevant query context. Opaque does not mean authorized or tamper-proof; validate it and reapply permissions on every request.

See exactly why an offset shifts

Start with descending ids 9,8,7,6 and page size two. Page one returns 9 and 8. Before page two, id 10 is inserted at the front, so the current order is 10,9,8,7,6. OFFSET 2 skips 10 and 9, then returns 8 and 7. The client sees 8 twice.

Deletion can cause the opposite problem. Start again with page one 9,8, then delete 9. The list becomes 8,7,6. OFFSET 2 now skips 8 and 7 and returns 6, so the caller never sees 7. The offset describes a row count in the current result, not a stable position in the earlier result.

Large offsets can also require scanning or discarding many qualifying rows before returning a small page. The exact cost depends on the execution plan, but asking for page 100,000 does not make the skipped work free.

Offset pagination remains useful for small or stable datasets and interfaces that genuinely need numbered pages. Its simplicity can be appropriate. The important point is to state the changing-data behavior instead of promising a stable traversal that the method does not provide.

Continue from a key instead of a row count

In the id-only example, page one ends at 8. The next query asks for ids less than 8 in descending order and returns 7,6 even after 10 arrives. The continuation is tied to an observed boundary rather than a number of rows to skip.

For the practical timestamp-plus-id order, store the last returned pair in the cursor. The next query uses (created_at,id) < (last_time,last_id) with the same descending ordering. If several rows have the same timestamp, id determines which of them comes next. Using timestamp alone can omit rows tied at the boundary.

An index beginning with owner_id and followed by the order columns can support the filtered continuation. The database lesson will examine how to verify that with a plan. The API still needs to bind parameters, bound page size and prevent a cursor from bypassing the owner filter.

A cursor must also belong to the query shape. If page one filtered by status=active and page two silently changes to all statuses, the boundary may no longer describe the intended traversal. Encode or otherwise bind relevant filters and ordering choices to the continuation contract.

Know what cursor pagination does not guarantee

A cursor handles leading inserts better for the demonstrated order. It does not automatically freeze the dataset. A row can be deleted before the client reaches it. A mutable ordering field can move a row across the boundary. Authorization can change between pages.

If the product requires a consistent export, consider a database snapshot, an immutable export job or a recorded upper-bound/version contract suited to the data. Holding a database transaction open while a person slowly clicks through pages is usually a poor way to achieve this: it retains resources and can interfere with maintenance.

Decide how long cursors remain valid and what happens when the underlying query version changes. Return a useful error for an expired or malformed cursor instead of an unexplained empty result. Do not place secrets in a cursor merely because it is base64-encoded.

The right question is “what must the user observe while data changes?” A social feed, an administrative listing and a financial export may legitimately choose different answers even when all three display pages.

Choose REST-style HTTP or gRPC from the callers

REST-style HTTP resource APIs often fit browser and public integrations because callers can inspect familiar methods, status codes and representations. Their quality still depends on explicit schemas, authorization, compatibility and error semantics. Returning JSON does not by itself make an interface well designed.

gRPC uses service definitions and generated client/server code, commonly with Protocol Buffers. Typed messages, streaming and defined service methods can be useful for internal communication or clients built around that tooling. It also introduces schema-evolution rules, operational tooling and client-environment considerations.

Neither choice eliminates deadlines or duplicate effects. A typed RPC called CreateLink can still time out after committing. A streaming connection can still break and require a resumption contract. A public HTTP API can still use strong schema validation and generated clients.

Choose based on client support, interaction patterns, deployment boundaries and team operations. For this course’s public short-link management API, ordinary HTTP is a reasonable teaching choice. An internal high-volume streaming service may justify a different interface without changing the underlying ownership and retry questions.

Use GraphQL with a query-cost model

GraphQL lets a client request a structured selection of fields. That can reduce awkward over-fetching or repeated round trips for some client views. The server must still decide how those fields are resolved and authorized.

A nested query can trigger repeated database work if each parent object loads its children separately. Batching and request-scoped loading can reduce that pattern, but they must preserve authorization and avoid mixing data between callers. A small request body can describe a very expensive query.

Apply limits appropriate to the schema, such as pagination bounds, depth or complexity controls, time budgets and batching. Field-level access decisions matter when one object contains both public and private fields. A single HTTP endpoint is not a single authorization check for all possible operations.

Compare protocols by tracing a real screen. If the client needs twenty links with owner metadata, count requests, database queries, response bytes and cache opportunities for each candidate. The best design is the one whose costs and behavior fit that workload, not the one with the most fashionable label.

SELECT id, created_at, destination
FROM links
WHERE owner_id = :authenticated_owner
  AND (created_at, id) < (:last_time, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT :bounded_page_size;

rows = [9, 8, 7, 6]
first_page = rows[:2]
cursor = first_page[-1]
rows.insert(0, 10)
offset_page = rows[2:4]
cursor_page = [row for row in rows if row < cursor][:2]
print("first:", first_page)
print("offset:", offset_page)
print("cursor:", cursor_page)

first: [9, 8]
offset: [8, 7]
cursor: [7, 6]

04 Cheat sheet

Review the decisions

Use this table to explain the mechanism and its limit without memorizing a product name.

05 Where & why

Check the primary references

Use official references for protocol and implementation details. The questions here are teaching prompts.

06 Interview questions

Explain the reasoning out loud

Answer before opening the explanation. The questions progress from vocabulary to decisions and failure behavior.

07 Practice problems

Practice a new case

Solve the new cases before revealing hints. Complete worked solutions follow the set.

Worked solutions