API Response Pagination Patterns: Architecture, Implementation, and Production Trade-offs

ng for deep navigation.

Core Solution

Implementing robust pagination requires moving beyond simple SQL translation to a holistic API design involving contract definition, sorting stability, and metadata management.

Step 1: Define the Pagination Contract

Standardize the response structure to decouple clients from internal implementation details. Use opaque cursors or sortable field values rather than exposing database IDs directly.

// src/types/pagination.ts

export interface PaginationMeta {
  hasNext: boolean;
  nextCursor?: string;
  // Avoid total count in standard responses; use optional query param if absolutely necessary
  totalCount?: number; 
}

export interface PaginatedResponse<T> {
  data: T[];
  meta: PaginationMeta;
  links: {
    next?: string;
    prev?: string;
    first?: string;
  };
}

Step 2: Implement Keyset Pagination (Recommended for most APIs)

Keyset pagination uses the values of the last row to fetch the next batch. It requires a stable sort order, typically enforced by a unique column (like id) as a tie-breaker.

Architecture Rationale: Keyset is preferred for its simplicity and performance. It avoids the overhead of encoding/decoding cursors and is natively supported by most query builders.

// src/repositories/user.repository.ts
import { db } from './db';

interface UserKeyset {
  id: number;
  createdAt: Date;
}

export async function getUsersKeyset(
  limit: number,
  cursor?: UserKeyset
): Promise<{ users: any[]; nextCursor?: UserKeyset }> {
  const query = db.selectFrom('users')
    .selectAll()
    .orderBy('createdAt', 'desc')
    .orderBy('id', 'desc') // Mandatory tie-breaker for stability
    .limit(limit + 1); // Fetch one extra to determine hasNext

  if (cursor) {
    // Keyset logic: (createdAt < cursor.createdAt) OR (createdAt = cursor.createdAt AND id < cursor.id)
    query.where((eb) => eb.or([
      eb('createdAt', '<', cursor.createdAt),
      eb.and([
        eb('createdAt', '=', cursor.createdAt),
        eb('id', '<', cursor.id)
      ])
    ]));
  }

  const results = await query.execute();
  
  // Check for extra row
  const hasNext = results.length > limit;
  const users = hasNext ? results.slice(0, limit) : results;
  
  const nextCursor = hasNext 
    ? { id: users[users.length - 1].id, createdAt: users[users.length - 1].createdAt } 
    : undefined;

  return { users, nextCursor };
}

Step 3: Implement Cursor Pagination (For Complex Sorts)

When sorting by non-unique fields or composite keys that cannot be easily exposed, use Cursor pagination. The cursor is an opaque string containing encoded sort values.

Architecture Rationale: Cursors hide implementation details and allow pagination on any sort configuration without leaking sensitive IDs. They are essential for social feeds or search results with multi-field sorting.

// src/utils/cursor.ts
import { Buffer } from 'buffer';

export function encodeCursor(values: Record<string, any>): string {
  const payload = JSON.stringify(values);
  return Buffer.from(payload).toString('base64url');
}

export function decodeCursor<T>(cursor: string): T {
  const payload = Buffer.from(cursor, 'base64url').toString();
  return JSON.parse(payload) as T;
}

// src/repositories/post.repository.ts
export async function getPostsCursor(
  limit: number,
  cursor?: string
): Promise<{ posts: any[]; nextCursor?: string }> {
  const sortValues = cursor ? decodeCursor<Record<string, any>>(cursor) : null;
  
  const query = db.selectFrom('posts')
    .selectAll()
    .orderBy('score', 'desc')
    .orderBy('id', 'desc')
    .limit(limit + 1);

  if (sortValues) {
    // Dynamic WHERE clause construction based on decoded cursor values
    // Implementation depends on your query builder's ability to handle dynamic conditions
    query.where((eb) => eb.or([
      eb('score', '<', sortValues.score),
      eb.and([
        eb('score', '=', sortValues.score),
        eb('id', '<', sortValues.id)
      ])
    ]));
  }

  const results = await query.execute();
  const hasNext = results.length > limit;
  const posts = hasNext ? results.slice(0, limit) : results;

  const nextCursor = hasNext 
    ? encodeCursor({ score: posts[posts.length - 1].score, id: posts[posts.length - 1].id }) 
    : undefined;

  return { posts, nextCursor };
}

Step 4: HTTP Header Integration

Use Link headers for navigation to keep the response body clean and adhere to RFC 5988.

HTTP/1.1 200 OK
Content-Type: application/json
Link: <https://api.example.com/v1/users?limit=20&cursor=eyJpZCI6MTAwfQ>; rel="next",
      <https://api.example.com/v1/users?limit=20&cursor=eyJpZCI6MTB9>; rel="prev"

Pitfall Guide

1. Unstable Sort Orders

Mistake: Sorting by a field that is not unique (e.g., ORDER BY status) without a tie-breaker. Impact: Rows with identical sort values may appear in different orders across requests, causing duplicates or missing items. Fix: Always append a unique column (e.g., id) to the ORDER BY clause.

2. The total_count Performance Killer

Mistake: Calculating SELECT COUNT(*) on every paginated request. Impact: On large tables, this forces a full index scan, doubling query time and increasing I/O load. Fix: Remove total_count by default. If required for UX, use approximate counts (e.g., PostgreSQL statistics) or calculate it asynchronously via a separate endpoint.

3. Leaking Internal Identifiers in Cursors

Mistake: Encoding raw database IDs or sequential integers in cursors. Impact: Clients can reverse-engineer cursors to access unauthorized data or guess record counts. Fix: Use opaque encoding. If security is paramount, sign cursors with a HMAC or use encrypted tokens.

4. Missing max_limit Enforcement

Mistake: Allowing clients to request limit=10000. Impact: Large pages consume excessive memory, cause timeouts, and degrade database performance. Fix: Enforce a server-side max_limit. If the client requests more, cap the response or return a 400 error.

5. Offset Pagination on Mutable Collections

Mistake: Using Offset on streams or feeds where data is frequently inserted/deleted. Impact: "Drifting" data causes users to see the same item twice or skip items entirely. Fix: Use Keyset or Cursor pagination for any dataset with write activity.

6. Inconsistent Page Sizes

Mistake: Returning fewer items than requested at the end of the dataset without signaling termination correctly. Impact: Clients may loop indefinitely trying to fetch the next page. Fix: Always return hasNext: false or omit the next link when the result set is smaller than the limit.

7. N+1 Pagination Queries

Mistake: Fetching pages in a loop within application code rather than using efficient SQL pagination. Impact: Massive latency and connection pool exhaustion. Fix: Push pagination logic to the database layer. Ensure the query plan uses an index seek, not a sequential scan.

Production Bundle

Action Checklist

• Audit Endpoints: Identify all endpoints using offset/page parameters and flag them for migration if dataset size > 10k.
• Enforce Limits: Implement middleware to validate limit parameters against a defined MAX_LIMIT.
• Add Tie-Breakers: Review all ORDER BY clauses to ensure a unique column is included for sort stability.
• Remove Total Counts: Strip totalCount from default responses; migrate to hasNext boolean.
• Implement Link Headers: Add Link headers to responses for standard navigation support.
• Secure Cursors: Ensure cursors are opaque and do not expose sensitive internal state.
• Load Test Deep Pages: Verify latency remains constant when testing page 10,000+ in staging.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Public Feed / Social Graph	Cursor	Handles composite sorts; opaque tokens; constant latency.	Low Infra, Med Dev
Admin Dashboard (Static Data)	Offset	Supports random page navigation; data is rarely mutated.	Low Dev
High-Scale API (User List)	Keyset	Simplest high-performance pattern; native DB support.	Low Dev, Low Infra
Search Results	Cursor	Flexibility for dynamic sorting; prevents data drift.	Low Infra, Med Dev
Export / Bulk Download	Keyset	Efficient streaming of large datasets without drift.	Low Infra

Configuration Template

Pagination Middleware (Express/Fastify Style):

// src/middleware/pagination.ts

export function paginationMiddleware(req: any, res: any, next: any) {
  const MAX_LIMIT = 100;
  const DEFAULT_LIMIT = 20;

  let limit = parseInt(req.query.limit as string, 10) || DEFAULT_LIMIT;
  const cursor = req.query.cursor as string | undefined;

  // Enforce max limit
  if (limit > MAX_LIMIT) {
    limit = MAX_LIMIT;
  }

  // Validate cursor format (basic check)
  if (cursor) {
    try {
      Buffer.from(cursor, 'base64url');
    } catch {
      return res.status(400).json({ error: 'Invalid cursor format' });
    }
  }

  req.pagination = { limit, cursor };
  next();
}

Response Builder:

// src/utils/response-builder.ts

export function buildPaginatedResponse<T>(
  items: T[],
  nextCursor?: string,
  baseUrl: string,
  currentParams: Record<string, any>
): PaginatedResponse<T> {
  const hasNext = items.length > 0 && nextCursor !== undefined;
  
  const buildUrl = (cursor?: string) => {
    const params = new URLSearchParams(currentParams);
    if (cursor) params.set('cursor', cursor);
    return `${baseUrl}?${params.toString()}`;
  };

  return {
    data: items,
    meta: { hasNext, nextCursor },
    links: {
      next: hasNext ? buildUrl(nextCursor) : undefined,
      first: buildUrl(),
    }
  };
}

Quick Start Guide

1. Define Sort Contract: Identify the sort fields and ensure a unique tie-breaker exists in your schema.
2. Add Query Parameters: Accept limit (default 20, max 100) and cursor (optional string) in your route handlers.
3. Implement Keyset Logic: Modify your repository query to use WHERE (sort_col, id) > (cursor_val, cursor_id) instead of offset.
4. Fetch Extra Row: Request limit + 1 rows to determine if more data exists without a separate count query.
5. Encode and Return: If an extra row exists, encode the sort values of the last valid row into a cursor, slice the array, and return the response with the next link.