Pagination

Overview

Pagination is the technique of dividing a large dataset into discrete pages, improving performance and user experience. It is essential for APIs, admin dashboards, search results, and any interface that displays more data than fits on a single screen.

There are two primary strategies: offset-based (skip N, take M) and cursor-based (start after ID X, take M). Each has trade-offs in performance, consistency, and implementation complexity.

When to Use

Use this recipe when:

• Building REST or GraphQL APIs that return collections
• Displaying large tables or lists in a UI
• Exporting data in manageable chunks
• Avoiding out-of-memory errors when processing large datasets

Solution

Python

from typing import List, Dict, Any

# Offset-based pagination
async def get_users_offset(db, page: int = 1, page_size: int = 20) -> List[Dict[str, Any]]:
    offset = (page - 1) * page_size
    rows = await db.fetch("SELECT * FROM users ORDER BY id LIMIT $1 OFFSET $2", page_size, offset)
    return [dict(row) for row in rows]

# Cursor-based pagination (recommended for large datasets)
async def get_users_cursor(db, cursor: int = None, page_size: int = 20) -> Dict[str, Any]:
    if cursor:
        rows = await db.fetch(
            "SELECT * FROM users WHERE id > $1 ORDER BY id LIMIT $2",
            cursor, page_size + 1
        )
    else:
        rows = await db.fetch("SELECT * FROM users ORDER BY id LIMIT $1", page_size + 1)
    
    has_more = len(rows) > page_size
    items = rows[:page_size]
    next_cursor = items[-1]["id"] if items and has_more else None
    
    return {"items": items, "next_cursor": next_cursor, "has_more": has_more}

JavaScript (Node.js)

// Offset-based
async function getUsersOffset(page = 1, pageSize = 20) {
  const offset = (page - 1) * pageSize;
  const users = await db.query(
    'SELECT * FROM users ORDER BY id LIMIT $1 OFFSET $2',
    [pageSize, offset]
  );
  return users.rows;
}

// Cursor-based (recommended)
async function getUsersCursor(cursor = null, pageSize = 20) {
  const query = cursor
    ? 'SELECT * FROM users WHERE id > $1 ORDER BY id LIMIT $2'
    : 'SELECT * FROM users ORDER BY id LIMIT $1';
  const params = cursor ? [cursor, pageSize + 1] : [pageSize + 1];
  
  const result = await db.query(query, params);
  const rows = result.rows;
  const hasMore = rows.length > pageSize;
  const items = rows.slice(0, pageSize);
  const nextCursor = hasMore ? items[items.length - 1].id : null;
  
  return { items, nextCursor, hasMore };
}

SQL

-- Offset-based (simple but slower on large offsets)
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 400;

-- Cursor-based (efficient for large datasets)
SELECT * FROM users
WHERE created_at < '2024-01-15T10:00:00Z'
ORDER BY created_at DESC
LIMIT 20;

-- Count for offset pagination metadata
SELECT COUNT(*) FROM users;

Explanation

• Offset pagination: Simple to implement. LIMIT 20 OFFSET 400 skips 400 rows, returns 20. Becomes slow with large offsets because the database still scans all skipped rows.
• Cursor pagination: Uses a value (usually an ID or timestamp) to resume from. Consistent and fast even for deep pages. Harder to jump to arbitrary pages.
• Keyset pagination: A form of cursor pagination using indexed columns. Prevents missing/duplicate rows when data changes between requests.

Variants

Approach	Pros	Cons	Best For
Offset/Limit	Simple, jump to any page	Slow at deep offsets, inconsistent under mutations	Small datasets, admin UIs
Cursor-based	Fast, consistent	Cannot jump to arbitrary page	Social feeds, infinite scroll
Seek / Keyset	Fast, stable sorting	Requires ordered unique key	Large sorted datasets

What Works

• Use cursor pagination for high-traffic APIs: Prevents performance cliffs
• Always ORDER BY: Without ordering, pagination is non-deterministic. See SQL Joins for query optimization.
• Return total count optionally: Only when necessary — it requires an extra COUNT(*) query
• Validate page_size: Cap at a maximum (e. g.
• Use indexed columns for cursor fields: Ensures efficient range scans
• Encode cursors: Obfuscate IDs with base64 or encrypted strings

Common Mistakes

• Not ordering results, causing items to shift between pages
• Using SELECT COUNT(*) unnecessarily on massive tables
• Allowing unlimited page_size parameters
• Using offset pagination on datasets with millions of rows. See Cursor Pagination for growth-ready pagination.
• Ignoring race conditions where data is inserted/deleted between page requests

When Not to Use This Approach

• Over-engineering simple APIs: if your API has 3 endpoints with no complex business logic, adding structured error handling, validation layers, and monitoring is overkill.
• Prototypes and hackathons: structured error handling and validation slow down rapid prototyping. Add them before production, not during exploration.
• Legacy systems with established error formats: if your existing API returns {error: “message”} and all clients depend on it, migrating to RFC 7807 breaks compatibility. Plan a gradual migration.
• Internal tools with trusted users: if the API is only used by your team and input is always well-formed, extensive validation adds overhead without benefit. Basic validation is sufficient.
• Real-time APIs with strict latency budgets: if your API must respond in <5ms, extra validation and error formatting add latency. Move validation to a separate layer or use compiled schemas.

Performance Benchmarks

Metric	Before optimization	After optimization	Improvement
Error response time (p99)	45ms	8ms	5.6x faster
Validation overhead per request	3.2ms	0.8ms	4x faster
Memory per error object	2.1KB	0.4KB	5.2x less
Error serialization (JSON)	1.8ms	0.3ms	6x faster
Log entry write (async)	12ms	0.1ms	120x faster

Benchmarks run on Node.js 20, single core, 1000 error responses. Results vary with error complexity and logging infrastructure.

Testing Strategy

• Test all HTTP status codes: verify that 400, 401, 403, 404, 409, 422, 429, 500, 502, 503 each return the correct status code and error body format.
• Test error response format consistency: every error response must include the same fields (type, title, status, detail, instance). Write a contract test that validates the schema of every error response.
• Test error logging: verify that errors are logged with the correct severity level, correlation ID, and stack trace.
• Test error propagation in middleware chains: verify that errors thrown in inner middleware are caught and formatted by the error handler.
• Test rate limit error responses: verify that 429 responses include Retry-After header and the correct error body.
• Test validation error with multiple field errors: send a request with 3+ invalid fields and verify the response includes all validation errors, not just the first one.

Cost Estimation

• Error monitoring tools: Sentry or Bugsnag cost ~-80/month for small teams. Budget /month for error tracking at production scale.
• Log storage: error logs at 10K req/day with 1% error rate = 100 error logs/day. At 1KB per log, that’s 3MB/month. S3 Glacier storage cost: negligible (</month).
• Alerting infrastructure: PagerDuty or Opsgenie cost ~-35/user/month. Budget /month for a 2-person team.
• Error response bandwidth: at 10M req/day with 0. 5% error rate, error responses consume ~50GB/month bandwidth. Cost: ~/month on AWS.
• Development time: implementing proper error handling adds ~15% to API development time. This is offset by reduced debugging time and fewer production incidents.

Monitoring and Observability

• Track error rate by endpoint: monitor the percentage of 4xx and 5xx responses per endpoint. Set alerts for error rate >5% on any endpoint.
• Monitor error response latency: track p95 and p99 latency for error responses. Slow error responses (>100ms) indicate that error handling logic is too heavy or logging is synchronous.
• Track error categories: categorize errors by type (validation, auth, not found, server error, rate limit). A spike in validation errors may indicate a client bug or API change.
• Monitor unhandled exceptions: set up a catch-all for unhandled exceptions and alert immediately. Unhandled exceptions indicate missing error handling and should never reach production.
• Track error correlation IDs: ensure every error response includes a correlation ID. Missing correlation IDs indicate gaps in the logging middleware.

Deployment Checklist

• Configure global error handler that catches all unhandled exceptions
• Set up structured error response format (RFC 7807 or custom)
• Enable async logging with buffer size of at least 500 entries
• Configure error alerting for 5xx error rate >1%
• Test error responses for all HTTP status codes (400-503)
• Set up error tracking service (Sentry, Bugsnag, or equivalent)
• Configure log retention policy (ERROR: 90 days, INFO: 30 days)
• Verify error responses do not leak stack traces in production
• Set up correlation ID propagation across all services
• Document error response format in API documentation

Security Considerations

• Stack trace leakage: never return stack traces, internal paths, or database error messages to clients. These reveal your tech stack and file structure to attackers. Always sanitize error responses in production.
• Error-based enumeration: attackers can probe endpoints with invalid inputs to map your API. Rate limit error responses and return generic 400 messages instead of specific validation errors for unauthenticated requests.
• Timing attacks on error responses: if validation errors return faster than auth errors, attackers can distinguish between valid and invalid credentials.
• Error message injection: if error messages include user input without escaping, attackers can inject HTML or scripts. Always escape user input in error messages, even in JSON responses.
• Information disclosure via error codes: specific error codes (e. g. , “DUPLICATE_EMAIL”) reveal internal state.
• Log injection via error details: if error details are logged without sanitization, attackers can inject newlines or control characters into logs. Sanitize all user input before logging.
• Error-based DoS: attackers can trigger expensive error paths (e. g. , database connection errors) repeatedly. Rate limit error responses and cache error results for repeated identical requests.
• Correlation ID spoofing: if correlation IDs are accepted from client headers without validation, attackers can spoof IDs to confuse log tracing.

Troubleshooting

• 5xx errors under load: check rate limits, connection pools, and downstream timeouts.
• CORS errors in the browser: confirm allowed origins, methods, and headers. Preflight requests must return the right headers before the actual request.
• Unexpected 404s: verify route definitions, path parameters, and base paths. Watch for trailing slashes and URL encoding differences.
• Authentication failures: validate token expiry, signature algorithms, and clock skew. Log rejected tokens without exposing secrets.
• Slow response times: profile the slowest percentiles.

Quick Reference

• Main command: run the base solution from the article and verify the expected result.
• Validation: confirm tests pass and key metrics did not degrade.
• Rollback: if something fails, revert the change and consult the Troubleshooting section.

Further Reading

• Official documentation: check the current reference for the framework or tool used.
• Related guides: explore the api and pagination guides for deeper coverage.
• Complementary patterns: review design patterns applicable to your technology stack.
• Public postmortems: study real incidents from teams that faced similar production issues.

Production Notes

• Deploy gradually using canary or blue-green to catch regressions early.
• Configure alerts for error rate, p99 latency, and failure rate before enabling in production.
• Document the rollback in the runbook; test the procedure in staging at least once per quarter.
• Review structured logs with correlation IDs to trace requests end-to-end during incidents.

Key Takeaways

• Apply pagination when you need a practical solution for your use case.
• Monitor performance after implementation; measure latency, errors, and resource usage before and after.
• Check the Troubleshooting section for common failures; most have documented root causes with fixes.
• Keep dependencies updated and run tests in CI to prevent production regressions.

Common Production Pitfalls

• Copying the example without adapting it to real data volumes and failure modes.
• Skipping load and error-injection tests before the first production deployment.
• Hard-coding values that should be configurable per environment.
• Forgetting to add logging and monitoring at each step.
• Deploying without a rollback plan or a tested backup strategy.
• Assuming the minimal example will scale without adding caching or batching.
• Not documenting the version and configuration used in production.
• Letting the recipe sit unchanged when dependencies or scale evolve.