Pagination patterns (cursor, offset) in HLD - System Design Guide

Problem Statement

When a system returns large lists of data, sending all results at once overloads the client and server, causing slow responses and crashes. Without proper pagination, users face long wait times and cannot efficiently navigate through data, especially as the dataset grows.

Solution

Pagination breaks large data into smaller chunks sent one at a time. Offset pagination uses page numbers and skips records, while cursor pagination uses a unique marker from the last item to fetch the next set. This approach reduces load, improves response times, and allows smooth navigation through data.

Architecture

Client

→API Server

↓

Client

→API Server

The diagram shows two flows: offset pagination uses page number to skip records in the database query; cursor pagination uses a unique cursor value to fetch the next set of records after that cursor.

Trade-offs

✓ Pros

→

Offset pagination is simple to implement and understand using page numbers.

→

Cursor pagination provides consistent performance even with large datasets.

→

Cursor pagination avoids duplicate or missing records when data changes during pagination.

→

Offset pagination works well for small to medium datasets with stable data.

✗ Cons

→

Offset pagination can become slow with large offsets because the database must scan and skip many rows.

→

Offset pagination can return inconsistent results if data changes between requests.

→

Cursor pagination requires a unique, ordered column and more complex client logic to manage cursors.

Use offset pagination for small datasets or when users need direct page access (e.g., page 5). Use cursor pagination for large, frequently changing datasets where consistent and efficient pagination is critical.

Avoid offset pagination when dataset size exceeds tens of thousands of records or data changes rapidly. Avoid cursor pagination when the dataset lacks a unique, sequential column or when simple page numbers suffice.

Real World Examples

Twitter

Uses cursor pagination to load tweets in user timelines efficiently, ensuring smooth scrolling without missing or duplicating tweets.

GitHub

Uses cursor pagination in their API to handle large lists of repositories and commits, providing consistent results despite frequent data changes.

Amazon

Uses offset pagination for product search results where users expect to jump to specific pages.

Alternatives

Keyset Pagination

Similar to cursor pagination but uses multiple columns as keys for ordering and filtering.

Use when: Use when sorting by multiple columns is required and cursor pagination with a single column is insufficient.

Seek Method Pagination

Uses WHERE clauses with comparison operators on indexed columns instead of OFFSET.

Use when: Use when offset pagination is too slow and cursor pagination is not feasible.

Summary

Pagination breaks large data into manageable chunks to improve performance and user experience.

Offset pagination uses page numbers and skips records but can be slow and inconsistent with large or changing data.

Cursor pagination uses a unique marker to fetch the next set, providing better performance and consistency for large, dynamic datasets.