DM
Technical reference

Pagination Cheatsheet

Efficiently navigate large and changing datasets

Must Know

sql

Offset Pagination

Simple, but deep pages get slower and changing data can shift results.

SELECT * FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;
sql

Cursor Pagination

Use a unique tie-breaker for stable ordering.

SELECT * FROM posts
WHERE (created_at, id) < ($cursorTime, $cursorId)
ORDER BY created_at DESC, id DESC
LIMIT 21;

Important Patterns

json

Page Response

Keep cursors opaque to clients.

{
  "data": [],
  "pageInfo": {
    "nextCursor": "opaque-value",
    "hasNextPage": true
  }
}
javascript

Limit + 1

Avoid a separate count query when total count is unnecessary.

fetch pageSize + 1 rows
hasNextPage = rows.length > pageSize
return rows.slice(0, pageSize)

Useful Recipes

javascript

Encode Cursor

Signing prevents clients from tampering with internal cursor data.

cursor = base64url(JSON.stringify({ createdAt, id }))
// Decode, validate schema, then bind as query parameters.

Bidirectional

Clearly define edge behavior and ordering.

after + first => forward
before + last => backward
reverse query order, then reverse results

Pitfalls & Production

sql

Index Match

The index should match filters and ordering.

CREATE INDEX posts_page_idx ON posts (created_at DESC, id DESC);

Consistency Rules

Changing filters between cursor requests can duplicate or skip data.

stable sort
unique tie-breaker
maximum page size
cursor version
filter values bound to cursor