System Design · Module 2 · Lesson 4 of 4 · 40 min

Cursor Pagination and Choosing an API Protocol

Q: Is base64 a security boundary for a cursor?

No. A client can often decode or alter it. Validate token structure and apply authorization and query bounds independently.

Q: Why add id after created_at?

Timestamps can tie. A unique tiebreaker gives every row a deterministic place in the ordering.

Q: Can a cursor guarantee a frozen export?

Not by itself. Updates and deletions can still change results; snapshot or version semantics require an additional contract.

Q: When is offset acceptable?

Small or largely static datasets and deliberate page-number navigation may make it useful. State its behavior under concurrent changes.

Q: Why can GraphQL overload a service?

A compact query can trigger many nested fetches or large result sets. Bound depth, cost, fan-out and output size according to the service design.

Build a deterministic paginated listing, handle timestamp ties, and choose REST gRPC or GraphQL from actual client requirements.

A continuation token must identify a position in a stable total order; it does not automatically create a snapshot.

01 The idea

The problem this lesson solves

The descending list is 9,8,7,6; return 9 and 8.

Prerequisite: earlier lessons introduce the vocabulary and shared examples used here. Follow the module order.

OffsetA count of ordered rows to skip before returning a page.

CursorA token describing where traversal should continue.

Total orderAn ordering with enough tiebreakers to compare every distinct result deterministically.

02 Worked example

Follow one worked example

Page 1: 9, 8 • New row: 10 arrives • OFFSET 2: 8, 7 repeats • Cursor < 8: 7, 6 continues.

Trace the example in order. Follow the same identities, units and state transitions through the diagram and the events below.

Read page oneThe descending list is 9,8,7,6; return 9 and 8.

Insert a new rowThe list becomes 10,9,8,7,6.

Apply an offsetSkipping two rows now returns 8,7 and duplicates 8.

Apply a cursorContinue below id 8 and return 7,6.

A continuation token must identify a position in a stable total order; it does not automatically create a snapshot.

03 The method

Mechanisms decisions and a guided tutorial

Work through the explanation in order, then apply it in the tutorial. Each comparison names a cost as well as a benefit.

Choice or concept	Purpose	Boundary or cost
Offset	Simple numbered pages	Insert/delete shifts the boundary
Cursor	Stable continuation position	Needs deterministic ordering
Snapshot traversal	Frozen result semantics	Requires explicit storage/version support
GraphQL / gRPC / REST	Different client contracts	All still need bounds and authorization

Paginate with a stable order

Offset pagination skips a number of rows. Insert a new row before page two and the offset can now point at a row already seen. Cursor pagination continues after a stable tuple such as (created_at, id), ordered descending. The unique id breaks timestamp ties. A query requests tuples less than the last returned tuple. This avoids offset shifting for new leading rows; it is not automatically a snapshot of a changing dataset. Authorization filters must apply on every page.

Choose the protocol for the caller

REST-style HTTP endpoints are convenient for browser-facing resources. gRPC supplies strongly defined service contracts and streaming, often useful between services. GraphQL lets a client request a particular data shape, but the server must still bound query cost and authorize fields. These choices do not remove retries, timeouts, schema evolution or authentication. Prefer the simplest contract that serves the actual clients.

See the moving-offset bug

A feed ordered by descending id contains 9,8,7,6. Page one returns 9,8. A new row 10 arrives before page two. OFFSET 2 now starts at 8, so the client sees 8 twice. Deleting a leading row can instead cause a skipped result.

A cursor after id 8 continues with ids less than 8, producing 7,6 despite the new row at the front. For real data use the order that the product promises, often created_at plus a unique id. Every page must repeat the same authorization and filter rules; the cursor is a position, not permission.

Timestamp ties and mutable order

If three rows share a timestamp, a timestamp-only cursor cannot distinguish them. Order by (created_at DESC, id DESC) and continue lexicographically below the last pair. This gives a deterministic boundary even when time resolution is coarse.

Changing a sort field during traversal can still move a row across the boundary. If the product requires a frozen export, introduce snapshot or version semantics rather than promising them from ordinary cursors. A token should be validated and bounded. Encoding a JSON token does not make it trusted or authorize its contents.

Protocol choice does not remove distributed problems

REST-style HTTP/JSON is easy to inspect and broadly usable by browsers. gRPC can suit typed internal service calls and streaming. GraphQL lets clients select fields, but the server must bound query cost, prevent excessive fan-out and authorize every relevant object and field.

Write the client constraint before choosing the mechanism: browser support, streaming, schema tooling, response shape or compatibility. Every option still needs timeouts, versioning, authentication and overload behavior. A complex query language is not a substitute for a paginated and bounded contract.

Guided tutorial

1. Trace inserts and deletes

Repeat the example with one leading row deleted instead of inserted.

2. Add a tiebreaker

Create three rows with the same timestamp and assign unique ids.

3. Write the continuation

Use the tuple predicate and a bounded page size.

4. Compare clients

Choose a protocol for a public browser listing and an internal streaming service, explaining one cost each.

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;

04 Cheat sheet

Review the decisions

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

Choice or concept	Remember	Check
Offset	Simple numbered pages	Insert/delete shifts the boundary
Cursor	Stable continuation position	Needs deterministic ordering
Snapshot traversal	Frozen result semantics	Requires explicit storage/version support
GraphQL / gRPC / REST	Different client contracts	All still need bounds and authorization

OffsetA count of ordered rows to skip before returning a page.

CursorA token describing where traversal should continue.

Total orderAn ordering with enough tiebreakers to compare every distinct result deterministically.

05 Where & why

Check the primary references

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

Primary reference

HTTP method semantics

Read HTTP method semantics

Primary reference

HTTP status codes

Read HTTP status codes

State the implementation version and the workload assumptions when defending your own design.

06 Interview questions

Explain the reasoning out loud

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

What does Offset mean?

A count of ordered rows to skip before returning a page.

What does Cursor mean?

A token describing where traversal should continue.

What does Total order mean?

An ordering with enough tiebreakers to compare every distinct result deterministically.

Is base64 a security boundary for a cursor?

No. A client can often decode or alter it. Validate token structure and apply authorization and query bounds independently.

Why add id after created_at?

Timestamps can tie. A unique tiebreaker gives every row a deterministic place in the ordering.

Can a cursor guarantee a frozen export?

Not by itself. Updates and deletions can still change results; snapshot or version semantics require an additional contract.

When is offset acceptable?

1. Choose a boundary

For a unique id ordering use id < 15, together with the same authorization and filters.

2. Break a timestamp tie

Add a unique stable id and include it in both ORDER BY and the continuation token.

3. Pagination insertion

The first page is 30,20. The new list is 40,30,20,10,5. OFFSET 2 returns 20,10 and duplicates 20. A cursor id less than 20 returns 10,5.

4. Timestamp ties

Order by created_at DESC, id DESC and continue with (created_at,id) less than the last returned tuple. Include both values in the cursor; validate its shape and apply the same ownership filter on every request.

5. Mutable sort

A cursor fixes a continuation boundary, but a mutable sort value can move a row to another position. Use snapshot/version semantics for a frozen traversal or document live-feed behavior.

6. Choose a contract

Authenticate on every page, use a deterministic tuple order, validate cursor shape and enforce page-size and time limits. A forged owner id inside the token must not override the authenticated identity.