VibeWeek
Home/Grow/API Pagination Patterns: Offset, Cursor, Keyset, and the Trade-offs That Matter at Scale

API Pagination Patterns: Offset, Cursor, Keyset, and the Trade-offs That Matter at Scale

⬅️ Day 6: Grow Overview

If you're running a SaaS in 2026, every API endpoint that returns a list of resources needs pagination. Most founders default to ?page=1&limit=50 (offset pagination) on day one because every framework supports it, then six months later discover that page 1000 takes 30 seconds, the second-page-loaded-twice bug haunts them, and bulk-export from their API is impossible because ?page=2000 returns the wrong rows when new data is created.

A working pagination strategy answers: which pagination type fits the use case (offset / cursor / keyset / page-token), how do we handle stable iteration when data changes mid-pagination, what's the page-size limit, and how do we structure the response. Done well, pagination is invisible — clients iterate efficiently; new data inserts don't break in-flight pagination. Done badly, every list endpoint is a performance trap and clients build hacks to work around it.

This guide is the implementation playbook for pagination — picking the right pattern, response design, edge cases (consistency during inserts), and the discipline that prevents the "page 1000 is slow" class of bugs.

The Three Pagination Patterns

Each has different trade-offs. Pick deliberately.

Help me understand pagination patterns.

**Pattern 1: Offset / Limit (most common, often wrong)**

GET /api/orders?page=1&limit=50 GET /api/orders?offset=0&limit=50


DB: `SELECT * FROM orders ORDER BY created_at DESC LIMIT 50 OFFSET 0`

Pros:
- Simple to implement
- Random access (jump to page 47)
- Easy to display "page X of Y" UI

Cons:
- **Slow at scale**: OFFSET 10000 = scan 10000 rows then return 50
- **Inconsistent during inserts**: new row inserted; page 2 includes some rows from page 1
- **No stable iteration**: cursor doesn''t exist; can''t resume mid-stream

Best for: small datasets (<10K rows); admin UIs; never for high-throughput APIs.

**Pattern 2: Cursor-based (modern default)**

GET /api/orders?cursor=abc123&limit=50

Response: { "data": [...], "next_cursor": "def456", "has_more": true }


DB: `SELECT * FROM orders WHERE id > 'last_id' ORDER BY id LIMIT 50`

Pros:
- **Fast at scale**: index lookup instead of OFFSET scan
- **Stable during inserts**: cursor anchored to specific row
- **Resumable**: can pause + resume

Cons:
- No random access (can''t jump to "page 47")
- Slightly more complex to implement
- Cursor is opaque (clients can''t inspect)

Best for: most modern APIs; infinite-scroll UIs; programmatic clients.

**Pattern 3: Keyset / Seek (cursor variant)**

GET /api/orders?after_created_at=2026-04-30T10:00:00Z&limit=50


DB: `SELECT * FROM orders WHERE created_at > $1 ORDER BY created_at LIMIT 50`

Pros:
- Same as cursor but uses natural keys (timestamps, IDs)
- Self-describing (client can read the key)
- Sometimes simpler

Cons:
- Need a unique-or-stable sort key
- Ties must be handled carefully

Best for: when natural key is good enough (event streams; time-ordered).

**Pattern 4: Page Token (Google-style)**

GET /api/orders?pageToken=xyz789&pageSize=50

Response: { "data": [...], "nextPageToken": "abc123" }


This is essentially cursor-based with Google''s naming. Same trade-offs.

**The pick**:

For most modern SaaS APIs in 2026:
- Public API: cursor-based (most flexible)
- Internal admin: offset (random access useful)
- Time-series / events: keyset by timestamp
- Bulk export: cursor with no expiration

For my system:
- List endpoints inventory
- Current pagination per endpoint
- Use case per endpoint

Output:
1. The endpoint inventory
2. The pattern per endpoint
3. The migration plan

The biggest unforced error: offset pagination on every endpoint by default. Works fine on day 1; breaks on day 365. The fix: cursor-based for any list that might grow past 1K rows. Cheaper to do right early than retrofit.

Why Offset Breaks at Scale

Understand the failure mode.

Help me understand why offset is slow.

The Postgres execution:

```sql
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 50 OFFSET 100000;

Postgres must:

  1. Scan the index in created_at order
  2. Skip the first 100,000 rows
  3. Return rows 100,001 through 100,050

Skipping requires reading. Reading 100K rows = slow. The cost grows linearly with offset.

Performance numbers:

Offset Time (typical)
0 1-5ms
1,000 10-50ms
10,000 100-500ms
100,000 1-10s
1,000,000 30s-timeout

Why cursor is fast:

SELECT * FROM orders
WHERE id > 'last_seen_id'
ORDER BY id
LIMIT 50;

Postgres uses index to seek directly to last_seen_id + 1; reads 50 rows; done.

Always O(log n) regardless of dataset size.

The "deep pagination" anti-pattern:

Some clients hit ?page=1000 to "export all data." With offset:

  • Each page request scans more
  • Total cost: O(n²) for full export
  • Disk / CPU pegged

With cursor:

  • Each page is O(log n) seek
  • Total cost: O(n) for full export
  • Reasonable

The "consistency" problem with offset:

Time T1: client requests ?page=1&limit=50 — gets orders 1-50 Time T2: 50 new orders are created Time T3: client requests ?page=2&limit=50 — gets orders 1-50 (the new ones; previous "page 1" now starts at row 51)

Result: client sees same orders twice. Or if rows are deleted: skips some.

Cursor avoids this: cursor anchored to specific row, not arbitrary position.

For my API:

  • Endpoints that paginate
  • Likely scale (1K? 1M? 100M?)
  • Migration cost

Output:

  1. The scale assessment
  2. The "how soon will this break?"
  3. The migration priority

The biggest scaling mistake: **assuming "we''ll fix it when it breaks."** Production users with deep pagination requests cause real-time outage. Customers can''t export their data. The fix: cursor pagination from day one for endpoints that might grow.

## Implementing Cursor Pagination

The mechanics matter. Get them right.

Help me implement cursor pagination.

The approach:

Step 1: Pick a sort field

Cursor pagination requires a stable sort. Options:

  • Primary key (id) — simplest if monotonic
  • Timestamp + id (composite for ties)
  • Custom sort key

For Postgres with UUIDs: use created_at + id (composite cursor).

Step 2: Encode the cursor

Cursor should be opaque to clients (they shouldn''t parse). Encode:

function encodeCursor(row): string {
  const data = { created_at: row.created_at, id: row.id };
  return Buffer.from(JSON.stringify(data)).toString('base64url');
}

function decodeCursor(cursor: string) {
  return JSON.parse(Buffer.from(cursor, 'base64url').toString());
}

Now cursor looks like eyJjcmVhdGVkX2F0IjoiMjAyNi0wNC0zMCIsImlkIjoiYWJjIn0 — opaque blob.

Step 3: Query with cursor

async function getOrders(cursor?: string, limit: number = 50) {
  const decoded = cursor ? decodeCursor(cursor) : null;

  const query = decoded
    ? db.query(`
        SELECT * FROM orders
        WHERE (created_at, id) < ($1, $2)
        ORDER BY created_at DESC, id DESC
        LIMIT $3
      `, [decoded.created_at, decoded.id, limit + 1])
    : db.query(`
        SELECT * FROM orders
        ORDER BY created_at DESC, id DESC
        LIMIT $1
      `, [limit + 1]);

  const rows = await query;

  // Fetched limit+1 to check has_more
  const hasMore = rows.length > limit;
  const data = rows.slice(0, limit);

  const nextCursor = hasMore && data.length > 0
    ? encodeCursor(data[data.length - 1])
    : null;

  return { data, nextCursor, hasMore };
}

Step 4: Index for cursor performance

Postgres needs a multi-column index for the WHERE clause:

CREATE INDEX idx_orders_pagination
ON orders (created_at DESC, id DESC);

Per database-indexing-strategy-chat: match the ORDER BY.

Step 5: Filter compatibility

When clients filter (e.g., status=open), the cursor only makes sense within that filter:

GET /api/orders?status=open&cursor=abc&limit=50

DB:

SELECT * FROM orders
WHERE status = 'open'
  AND (created_at, id) < ($cursor_created_at, $cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT $limit;

Index needed:

CREATE INDEX idx_orders_status_pagination
ON orders (status, created_at DESC, id DESC);

For each filterable field combination: separate index OR use covering composite.

Step 6: Total count handling

Cursor pagination doesn''t naturally provide "total count" (would require full scan).

Options:

  • Don''t provide it (most modern APIs)
  • Provide approximate count (Postgres pg_class.reltuples)
  • Provide on-demand via separate endpoint

The response format:

{
  "data": [...50 orders...],
  "pagination": {
    "next_cursor": "eyJjcmVhdGVkX2F0Ijo...",
    "has_more": true,
    "limit": 50
  }
}

Some APIs include "previous_cursor" for backward navigation; many don''t bother.

The "first page" handling:

First page: no cursor. Last page: next_cursor: null.

Client iterates until next_cursor is null.

For my API:

  • Cursor encoding strategy
  • Index plan
  • Response format

Output:

  1. The cursor implementation
  2. The index plan
  3. The API contract

The biggest cursor-implementation mistake: **using single-column sort.** `created_at` ties happen; rows with same timestamp can be skipped or duplicated. The fix: composite cursor (timestamp + id) for tie-breaking.

## Page Size Limits

Don''t let clients request huge pages. Cap.

Help me set page-size limits.

The defaults:

Endpoint type Default Max
List orders 50 100
List events 100 1000
List users 50 100
Bulk export 1000 10000

Why limits matter:

Without limit:

  • Client requests ?limit=1000000
  • Server tries to load 1M rows
  • OOM; timeout; bad UX

Implementation:

const requestedLimit = parseInt(req.query.limit) || DEFAULT_LIMIT;
const limit = Math.min(requestedLimit, MAX_LIMIT);

if (requestedLimit > MAX_LIMIT) {
  // Optionally: return warning header
  res.setHeader('X-Limit-Capped', `${requestedLimit} -> ${limit}`);
}

The "tier-based limits" pattern:

Different limits per pricing tier:

  • Free: max 50 per request
  • Pro: max 500
  • Business: max 1000
  • Enterprise: max 10000

Per pricing-packaging-tier-design.

Bulk export accommodations:

For bulk-export use cases:

  • Higher max limit (1000-10000)
  • Or: dedicated /api/exports endpoint with async download
  • Or: data export to S3 / customer''s storage

Don''t force bulk-export through normal pagination.

The "rate limit" interaction:

Per rate-limiting-abuse-chat:

  • Rate limit = X req/sec
  • Page size = 50
  • Effective throughput = 50 × X items/sec

Tune both together:

  • Bigger pages: fewer requests; same total
  • Smaller pages: more requests; same total
  • Right balance: smooth and predictable

For my API:

  • Default limits per endpoint
  • Max limits per endpoint
  • Tier-based variations

Output:

  1. The limit table
  2. The implementation
  3. The bulk-export approach

The biggest limit mistake: **no max limit.** "Just trust the client" — until one client requests a million rows; OOM; outage. The fix: hard max per endpoint; tier-based for paying tiers.

## Total Counts and "Page X of Y" UI

If you need traditional pagination UI, plan for it.

Help me handle total counts.

The "page X of Y" UI need:

Some UIs require:

  • Total record count
  • Total pages
  • "Page 47 of 1,238"

Cursor pagination doesn''t natively provide these. Options:

Option A: Don''t provide totals (modern)

Modern APIs increasingly don''t provide totals:

  • "Loading more..." infinite scroll
  • "Page X of Y" replaced by relative ("Older / Newer")
  • Saves DB cost; cleaner UX

GitHub, Stripe, Twitter (X) all dropped totals.

Option B: Approximate count from Postgres stats

SELECT reltuples::BIGINT FROM pg_class WHERE relname = 'orders';

Returns Postgres''s estimate (updated by ANALYZE). Good enough for UI hints; cheap.

Option C: Cached count refresh

Periodically run SELECT COUNT(*) FROM orders and cache:

const cachedCount = await getCachedCount('orders'); // refreshed every 5 min

Acceptable when count doesn''t need to be exact.

Option D: Real-time exact count (don''t)

SELECT COUNT(*) FROM orders WHERE filter = X;

Slow on large tables. Avoid except small datasets.

Option E: Maintain separate counter table

CREATE TABLE row_counts (table_name TEXT, count BIGINT);

-- Trigger or app-level: increment on insert; decrement on delete

Costs write performance; gives exact count.

The "for tenants only" pattern:

For multi-tenant SaaS, exact count per tenant is usually feasible:

SELECT COUNT(*) FROM orders WHERE tenant_id = $1;

Per-tenant counts are small enough; partial index on tenant_id helps.

The decision:

Use case Approach
Modern API No totals (cursor only)
Admin UI with "page X of Y" Approximate from pg_class
Per-tenant exact COUNT(*) with index
Real-time exact at scale Counter table

For my API:

  • Total-count needed?
  • Approach
  • Caching strategy

Output:

  1. The count strategy
  2. The implementation
  3. The performance plan

The biggest count mistake: **`COUNT(*)` on a 100M-row table per request.** 30-second responses; database CPU pegged. The fix: don''t count; use approximate; cache aggressively.

## Stability During Inserts (the Subtle Bug)

What happens when data changes mid-pagination? Plan for it.

Help me handle pagination during inserts.

The bug:

Time T0: client requests page 1; gets rows A, B, C (sorted by created_at DESC) Time T1: row D is inserted (now at top) Time T2: client requests page 2; gets... A, B, C? or D, A, B?

With offset:

  • Page 1 (offset 0): D, A, B
  • Page 2 (offset 3): nothing (only 4 total rows; we showed A B C as page 1 and "page 2" should be empty)
  • Or: A B C duplicates from page 1

With cursor:

  • Page 1: D, A, B (no, wait — they got A, B, C originally)
  • Page 1 already received A, B, C; cursor is at C
  • Page 2: stuff older than C — D doesn''t appear (D is newer than C)

Cursor "skips" D. From the client''s view: they iterated past A, B, C; never see D.

Is this OK?

Depends on use case:

Real-time "live feed":

Snapshot iteration:

  • Sometimes want consistency at start time
  • Use cursor; new inserts after start aren''t included
  • Common for export use cases

"Most recent":

  • Just rerun query for "first page"; fresh data shown

The "snapshot ID" pattern:

For consistent iteration:

// Start: get current max ID
const startMaxId = await db.query('SELECT MAX(id) FROM orders');

// Iterate with that as upper bound
SELECT * FROM orders
WHERE id <= $1 AND id > $cursor
ORDER BY id DESC
LIMIT 50
-- $1 = startMaxId

Now iteration is consistent; new inserts not included.

The "deletion" gotcha:

Cursor based on id: if row at cursor is deleted, cursor still works (no gap visible).

Cursor based on created_at + id: same behavior.

But if the cursor row is deleted AND no others have that timestamp: pagination still works (next-greater row is returned).

The "modification" semi-gotcha:

Cursor based on created_at. Row at cursor has created_at updated to a new time.

  • If row.created_at moves earlier (less common): cursor-based query might skip it (already past)
  • If row.created_at moves later: similarly might skip

Best practice: don''t modify the sort field. Or use created_at (immutable) instead of updated_at.

For my API:

  • Insert / delete frequency
  • Consistency needs (snapshot vs live)
  • Sort field stability

Output:

  1. The consistency requirement
  2. The sort-field choice
  3. The snapshot pattern (if needed)

The biggest consistency mistake: **sorting by `updated_at` for pagination.** Rows shift when modified; pagination misses or duplicates. The fix: sort by immutable field (`created_at` + `id`); use `updated_at` for filtering only.

## Response Format Standards

Consistent response format makes APIs easier to use.

Help me design pagination response format.

The standard structure:

{
  "data": [
    { "id": "1", ... },
    { "id": "2", ... }
  ],
  "pagination": {
    "next_cursor": "eyJ...",
    "has_more": true,
    "limit": 50
  }
}

Fields explained:

  • data: array of records (always plural)
  • pagination.next_cursor: cursor for next page; null if last page
  • pagination.has_more: bool indicating more data
  • pagination.limit: echo of requested/applied limit

Optional fields:

  • pagination.previous_cursor: for backward navigation (rarely used)
  • pagination.total_count: if computed (and you decided to provide)
  • pagination.estimated_count: approximate count

Stripe-style naming:

{
  "object": "list",
  "data": [...],
  "has_more": true,
  "url": "/v1/charges"
}

Stripe uses has_more at root level; cursor is implicit (last item''s ID).

Google-style naming:

{
  "items": [...],
  "nextPageToken": "abc123"
}

pageToken instead of cursor; pageSize instead of limit.

REST API conventions:

HTTP/1.1 200 OK
Link: <https://api.example.com/orders?cursor=def>; rel="next",
      <https://api.example.com/orders?cursor=abc>; rel="prev"

[items...]

Link header for navigation (used by GitHub).

The "consistency across endpoints" rule:

Whatever format you pick: use it on EVERY list endpoint.

Anti-pattern: /orders returns {data, next_cursor} while /users returns {items, next_page_token}. Confusing.

For my API:

  • Format choice
  • Field naming convention
  • Header usage

Output:

  1. The response format
  2. The naming convention
  3. The consistency check

The biggest format mistake: **inconsistent across endpoints.** Each endpoint had a different "I think we should return..." Different naming; different metadata. Clients have to special-case. The fix: standard response format applied to all list endpoints.

## Test for Pagination Correctness

Pagination bugs are subtle. Test deliberately.

Help me test pagination.

The tests:

1. Single page works

test('returns first page of orders', async () => {
  const r = await api.get('/orders?limit=10');
  expect(r.body.data.length).toBe(10);
  expect(r.body.pagination.has_more).toBe(true);
});

2. Cursor advances correctly

test('cursor returns next page', async () => {
  const r1 = await api.get('/orders?limit=10');
  const r2 = await api.get(`/orders?cursor=${r1.body.pagination.next_cursor}&limit=10`);

  expect(r2.body.data[0].id).not.toBe(r1.body.data[0].id);
  // No overlap
});

3. Last page handled correctly

test('last page returns null cursor', async () => {
  // Iterate to end
  let cursor = null;
  while (true) {
    const r = await api.get(`/orders?cursor=${cursor || ''}&limit=100`);
    if (!r.body.pagination.has_more) {
      expect(r.body.pagination.next_cursor).toBe(null);
      break;
    }
    cursor = r.body.pagination.next_cursor;
  }
});

4. No duplicates across pages

test('no duplicate IDs across pages', async () => {
  const allIds = new Set();
  let cursor = null;
  let total = 0;

  while (true) {
    const r = await api.get(`/orders?cursor=${cursor || ''}&limit=50`);
    for (const item of r.body.data) {
      expect(allIds.has(item.id)).toBe(false);
      allIds.add(item.id);
      total++;
    }
    if (!r.body.pagination.has_more) break;
    cursor = r.body.pagination.next_cursor;
  }

  expect(total).toBe(allIds.size);
});

5. Limit cap enforced

test('limit capped at maximum', async () => {
  const r = await api.get('/orders?limit=99999');
  expect(r.body.data.length).toBeLessThanOrEqual(MAX_LIMIT);
});

6. Filter + cursor compatibility

test('filter applied with cursor', async () => {
  const r1 = await api.get('/orders?status=open&limit=10');
  const r2 = await api.get(`/orders?status=open&cursor=${r1.body.pagination.next_cursor}&limit=10`);

  // All items match filter
  for (const item of [...r1.body.data, ...r2.body.data]) {
    expect(item.status).toBe('open');
  }
});

7. Empty result

test('empty result when no matches', async () => {
  const r = await api.get('/orders?status=nonexistent');
  expect(r.body.data).toEqual([]);
  expect(r.body.pagination.has_more).toBe(false);
});

8. Tenant isolation

test('cursor from tenant A cannot leak tenant B data', async () => {
  // Create cursor for tenant A
  const cursorA = ...;

  // Use as tenant B
  const r = await apiAsTenantB.get(`/orders?cursor=${cursorA}`);

  // Should return tenant B''s data only (or error)
  for (const item of r.body.data) {
    expect(item.tenant_id).toBe(tenantB.id);
  }
});

For my testing:

  • Test coverage
  • CI integration

Output:

  1. The test plan
  2. The CI integration

The biggest testing mistake: **only testing happy path.** Iteration ends; cursor handling for filters; tenant isolation. The fix: explicit test for each pagination edge case.

## Avoid Common Pitfalls

Recognizable failure patterns.

The pagination mistake checklist.

Mistake 1: Offset by default

  • Slow at scale
  • Fix: cursor-based for any growth-list

Mistake 2: No max limit

  • OOM risk
  • Fix: hard max per endpoint

Mistake 3: COUNT(*) on every request

  • Slow on large tables
  • Fix: skip totals or approximate

Mistake 4: Single-column cursor (with ties)

  • Rows at same timestamp skipped
  • Fix: composite cursor (timestamp + id)

Mistake 5: Sort by mutable field

  • Rows shift; duplicates / skips
  • Fix: sort by immutable

Mistake 6: Inconsistent response format

  • Each endpoint different
  • Fix: standard format

Mistake 7: No index for cursor query

  • Slow despite cursor
  • Fix: composite index on (filter, sort)

Mistake 8: Cursor leaks tenant data

  • Tenant A cursor used for tenant B
  • Fix: validate cursor against tenant context

Mistake 9: Cursor expires unpredictably

  • Long-running export interrupted
  • Fix: stable cursors; or expire with notice

Mistake 10: Bulk-export through paginated API

  • Customer wants 1M rows; uses page=1 to page=20000
  • Fix: dedicated bulk-export endpoint

The quality checklist:

  • Cursor pagination on growth-lists
  • Max limit enforced
  • No COUNT(*) on hot path
  • Composite cursor (timestamp + id)
  • Immutable sort field
  • Standard response format
  • Indexes for cursor queries
  • Tenant-isolation respected
  • Bulk-export endpoint separate
  • Edge-case tests

For my API:

  • Audit
  • Top 3 fixes

Output:

  1. Audit results
  2. Top 3 fixes
  3. The "v2 pagination" plan

The single most-common mistake: **assuming offset works "for now."** It works in dev (small data); fails in production (real scale). The fix: cursor-based for growth-lists from day one. Modest implementation cost; massive scaling benefit.

---

## What "Done" Looks Like

A working pagination system in 2026 has:

- Cursor-based for any list endpoint that grows
- Composite cursor (timestamp + id) for tie-breaking
- Sort by immutable field (`created_at`, not `updated_at`)
- Hard max limit per endpoint
- Indexes matching cursor queries
- Standard response format across all list endpoints
- No COUNT(*) on hot path
- Tenant isolation in cursor validation
- Dedicated bulk-export endpoint for large exports
- Edge-case test coverage

The hidden cost of weak pagination: **performance degradation that scales with success.** The more data customers have, the slower their API calls. Bulk export becomes impossible. Customers complain. The fix is a refactor that touches every list endpoint. Cheap to do right early; expensive to retrofit. Cursor-based pagination from day one is the easy choice.

## See Also

- [Public API](public-api-chat.md) — broader API design
- [API Versioning](api-versioning-chat.md) — versioning paginated APIs
- [API Keys](api-keys-chat.md) — auth on paginated endpoints
- [Rate Limiting & Abuse](rate-limiting-abuse-chat.md) — limits + pagination interact
- [Database Indexing Strategy](database-indexing-strategy-chat.md) — index for pagination
- [Caching Strategies](caching-strategies-chat.md) — cache paginated responses
- [Multi-Tenancy](multi-tenancy-chat.md) — tenant-aware cursors
- [Idempotency Patterns](idempotency-patterns-chat.md) — adjacent
- [Outbound Webhooks](outbound-webhooks-chat.md) — webhook list endpoints
- [Bulk Operations](bulk-operations-chat.md) — bulk-export pattern
- [Search](search-chat.md) — paginating search results
- [GraphQL vs REST API Design](graphql-vs-rest-api-design-chat.md) — Relay-style cursor convention
- [VibeReference: Database Providers](https://www.vibereference.com/backend-and-data/database-providers) — DB choice
- [VibeReference: Postgres](https://www.vibereference.com/backend-and-data/postgres) — Postgres
- [LaunchWeek: Pricing Packaging & Tier Design](https://www.launchweek.com/1-position/pricing-packaging-tier-design) — tier-based limits

[⬅️ Day 6: Grow Overview](README.md)