PushBackLog

Pagination Patterns

Advisory enforcement Complete by PushBackLog team
Topic: performance Topic: api-design Skillset: backend Skillset: fullstack Technology: generic Stage: execution Stage: review

Pagination Patterns

Status: Complete
Category: Performance
Default enforcement: Advisory
Author: PushBackLog team

Tags

  • Topic: performance, api-design
  • Skillset: backend, fullstack
  • Technology: generic
  • Stage: execution, review

Summary

Pagination limits the amount of data returned by any single API call or database query by splitting large result sets into pages. Without pagination, a query can unknowingly return millions of rows — causing memory exhaustion on the server, timeouts for the client, and unreadable UIs. Three main patterns exist — offset/limit, cursor-based, and keyset — each with different trade-offs around implementation simplicity, consistency, and performance at scale.

Rationale

Unbounded queries are an operational risk

An endpoint that returns all records without a limit is a latency spike waiting to happen. As the dataset grows, the query becomes slower, the response payload grows, mobile clients time out, and memory consumption on the API server grows proportionally. The fix at that point is expensive: production incidents, API-breaking changes, and migration effort. Pagination must be designed in from the start.

Each pattern fits a different use case

Offset/limit is simple and usable when result sets are small and strong consistency is acceptable. Cursor-based pagination (used by most social and content platforms) gives stable results under concurrent writes. Keyset pagination is the highest-performance option for large, indexed datasets where an ordered key is available.

Guidance

Offset / limit pagination

The simplest approach — skip N rows and return the next M:

-- Page 3 with page size 20:
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40;
// API response envelope
{
  "data": [...],
  "pagination": {
    "page": 3,
    "pageSize": 20,
    "total": 1420,
    "totalPages": 71
  }
}

Limitations:

  • Deep pagination is slow: OFFSET 10000 causes the DB to scan and discard 10,000 rows
  • Inconsistent under writes: records inserted/deleted between pages cause duplicates or gaps
  • Acceptable for: admin UIs, small datasets, reporting tools where exact page navigation is needed

Cursor-based pagination

Return an opaque cursor that encodes the position in the result set, not a numeric offset:

// Response includes a cursor for the next page
{
  "data": [...],
  "pagination": {
    "nextCursor": "eyJpZCI6IDEyMzQsICJjcmVhdGVkQXQiOiAiMjAyNC0wMS0xNVQxMDowMDowMFoifQ==",
    "hasNextPage": true
  }
}

// Client passes the cursor back
GET /api/posts?cursor=eyJpZCI6IDEy...&pageSize=20
// Server decodes the cursor and queries forward
const decoded = decodeCursor(cursor); // { id: 1234, createdAt: '2024-01-15T10:00:00Z' }

const rows = await db.query(`
  SELECT * FROM posts
  WHERE created_at < $1 OR (created_at = $1 AND id < $2)
  ORDER BY created_at DESC, id DESC
  LIMIT $3
`, [decoded.createdAt, decoded.id, pageSize + 1]); // +1 to detect hasNextPage

Advantages over offset:

  • Stable under concurrent inserts (no duplicate/missing records)
  • O(1) performance regardless of page depth (uses index, not scan)

Limitations:

  • Cannot jump to arbitrary pages — clients must navigate forward (sometimes backward)
  • Cursor must be treated as opaque — clients must not construct or parse them

Keyset pagination

A direct SQL variant of cursor-based pagination using a WHERE clause on a unique, indexed column:

-- First page:
SELECT * FROM posts ORDER BY id DESC LIMIT 20;

-- Next page (using last id from previous page = 9812):
SELECT * FROM posts WHERE id < 9812 ORDER BY id DESC LIMIT 20;

This is the fastest pattern for sequential pagination through large tables — it uses the index directly with no scanning.

Requirements:

  • Must have a unique, ordered, indexed column (auto-increment ID, ULID, or (created_at, id) composite)
  • Works for forward navigation; backward navigation requires storing previous cursors

Comparison

PatternSimplicityConsistent under writesDeep pagination
Offset/limitHighNoSlow (O(n))
Cursor-basedMediumYesFast (O(log n))
KeysetMediumYesFast (O(log n))

GraphQL relay-compatible cursor pagination

If building a GraphQL API, use the Relay Connection Specification:

type PostConnection {
  edges: [PostEdge!]!
  pageInfo: PageInfo!
}

type PostEdge {
  node: Post!
  cursor: String!
}

type PageInfo {
  hasPreviousPage: Boolean!
  hasNextPage: Boolean!
  startCursor: String
  endCursor: String
}

Guidelines

  • Always set a maximum page size — never allow pageSize=unbounded; apply a hard cap server-side (typically 100–1000 depending on payload size)
  • Default page sizes — choose sensible defaults (20–50); document the max
  • Cursor opacity — treat cursors as opaque strings; never document their internal structure in the API contract
  • Include total counts carefully — COUNT(*) on unindexed queries can be expensive; omit it from cursor-based APIs unless specifically needed