📄Pagination Strategies

Pick pagination by UX need: random page jumps, stable feeds, or incremental sync.

📌 Key IdeaThere is no one best strategy. Offset/page-number is easiest for back-office grids. Cursor/keyset is best for large, append-heavy feeds. Since-id/time-window is ideal for polling and sync endpoints.

🧩

Problem it solves

Large list APIs become slow or inconsistent if pagination strategy does not match access pattern.
Concurrent inserts/updates can cause duplicate or missing records across pages with naive pagination.

✨

Why It Matters

Directly affects API latency, DB load, and user experience for feeds and admin grids.
Wrong choice creates scaling pain (deep offset scans) and correctness confusion in clients.

🖼

Visual

📄 Pagination strategy map

Page-number / Offset
  page=101,size=20 -> OFFSET 2000
  ✅ jump to page N
  ⚠ deep pages get expensive

Cursor / Keyset
  after=base64(created_at,id)
  WHERE (created_at,id) < (?,?)
  ✅ stable under writes + fast at scale
  ⚠ no true "go to page 101"

Time-window / Since-id
  since=2026-05-01T00:00:00Z (or last_id)
  ✅ incremental sync / polling APIs

Choose strategy from access pattern first, then SQL shape and index design.

✅

When to Use

Page-number / Offset when users must jump to page N and datasets are moderate.
Cursor / Keyset for infinite scroll, timelines, event logs, chat history.
Since-id / Time-window for mobile sync, webhooks catch-up, CDC-like pull APIs.
Use deterministic sorting with a unique tie-breaker (e.g., created_at, id).

🚫

When NOT to Use

Offset for deep pages on very large tables (scan + discard cost).
Cursor without stable ordering or unique tie-breaker.
Cursor if product strictly needs arbitrary "jump to page 187".

💻

Code Variations

1️⃣ Page-number / Offset (simple admin grids)java

int page = 3, size = 20;
int offset = (page - 1) * size;

String sql = """
SELECT id, email, created_at
FROM users
ORDER BY created_at DESC
LIMIT ? OFFSET ?
""";

Great developer ergonomics, but deep offsets become slow.

2️⃣ Keyset (raw cursor fields in request)java

String sql = """
SELECT id, created_at, body
FROM posts
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT ?
""";

Range scan on indexed sort keys; stable even when new rows arrive.

3️⃣ Cursor token (API-safe keyset)java

record Cursor(Instant createdAt, long id) {}

Cursor c = decode(afterToken); // base64 + signature/HMAC
List<Post> rows = repo.fetchAfter(c.createdAt(), c.id(), limit);
String next = encode(new Cursor(rows.getLast().createdAt(), rows.getLast().id()));

Hide DB fields from clients; sign token to prevent tampering.

4️⃣ Since-id / time-window (incremental sync)java

String sql = """
SELECT id, updated_at, payload
FROM orders
WHERE updated_at > ?
ORDER BY updated_at ASC, id ASC
LIMIT ?
""";

Best for "give me changes since last checkpoint".

📊

Pagination strategy comparison

Aspect	Page-number / Offset	Cursor / Keyset	Since-id / Time-window
Jump to page N	✅ Native	❌ Not natural	❌ Not applicable
Deep-page performance	❌ Degrades with large OFFSET	✅ Near constant	✅ Near constant
Consistency under concurrent writes	⚠ Can skip/duplicate	✅ Stable with tie-breaker	✅ Stable for monotonic checkpoints
Client complexity	✅ Lowest	⚠ Medium (token handling)	⚠ Medium (checkpoint handling)
Best UX fit	Tables with explicit pages	Infinite scroll / feeds	Sync / polling APIs

🔀

Common Confusions Cleared Up

Cursor vs Keyset

In practice they are the same core idea. Keyset describes the SQL predicate (WHERE key < lastKey). Cursor usually means wrapping those keys into an opaque API token.

Offset vs Cursor

Offset optimizes for human page navigation; cursor optimizes for backend scalability and consistency. Pick based on UX, not just SQL taste.

🎤

Interview Q&A

🧾

Quick Cheat Sheet

OffsetBest for page jumps; weak deep-page performance.

Cursor/KeysetBest default for feeds and large ordered sets.

Since-id/timeBest for incremental sync and polling.

RuleSort must be deterministic + indexed.

API outputitems, nextCursor/nextSince, hasMore.

🔗