Pagination patterns (cursor, offset) in HLD - Deep Dive

Overview - Pagination patterns (cursor, offset)

What is it?

Pagination patterns are methods used to split large sets of data into smaller, manageable chunks called pages. Two common patterns are offset pagination and cursor pagination. Offset pagination uses page numbers and offsets to fetch data, while cursor pagination uses a unique marker to continue from the last item fetched. These patterns help systems deliver data efficiently without overwhelming users or servers.

Why it matters

Without pagination, systems would try to load all data at once, causing slow responses, high memory use, and poor user experience. Pagination ensures fast loading times and smooth navigation through data, especially in apps like social media feeds or product listings. It also helps servers handle many users without crashing or slowing down.

Where it fits

Before learning pagination patterns, you should understand basic data retrieval and database queries. After mastering pagination, you can explore advanced topics like caching, infinite scrolling, and real-time data updates.

Mental Model

Core Idea

Pagination patterns organize large data sets into small, easy-to-handle pages using either position-based offsets or unique markers to track progress.

Think of it like...

Imagine reading a book: offset pagination is like turning to a specific page number, while cursor pagination is like placing a bookmark to remember where you left off.

┌───────────────┐       ┌───────────────┐
│   Data Set    │──────▶│ Pagination    │
└───────────────┘       │  Patterns     │
                        ├───────────────┤
                        │ Offset        │
                        │ Pagination    │
                        ├───────────────┤
                        │ Cursor        │
                        │ Pagination    │
                        └───────────────┘

Build-Up - 6 Steps

1

Foundation

What is Pagination and Why Use It

Concept: Pagination breaks large data into smaller pages to improve performance and usability.

When you have thousands of items, showing them all at once is slow and confusing. Pagination divides data into pages, so users see a few items at a time. This makes apps faster and easier to use.

Result

Users get data in small chunks, improving speed and experience.

Understanding pagination is key to handling large data sets without overwhelming systems or users.

2

Foundation

Basics of Offset Pagination

Concept: Offset pagination uses page numbers and offsets to fetch data slices.

Offset pagination asks for data starting at a certain position. For example, page 3 with 10 items per page means starting at item 21 (offset 20). The system fetches items 21 to 30. This is simple and common in many apps.

Result

You can jump to any page by calculating the offset.

Knowing offset pagination helps you understand the simplest way to split data into pages.

3

Intermediate

Understanding Cursor Pagination

🤔Before reading on: do you think cursor pagination uses page numbers or unique markers? Commit to your answer.

Concept: Cursor pagination uses a unique marker from the last item to fetch the next set.

Instead of page numbers, cursor pagination remembers the last item's unique ID or timestamp. To get the next page, it asks for items after that marker. This avoids problems with data changes during paging.

Result

Pagination continues smoothly even if data changes between requests.

Understanding cursor pagination reveals how to handle dynamic data without errors or duplicates.

4

Intermediate

Comparing Offset and Cursor Pagination

🤔Before reading on: which pagination method do you think handles data changes better? Offset or cursor? Commit to your answer.

Concept: Offset is simple but can break with data changes; cursor is more complex but reliable.

Offset pagination can show duplicate or missing items if data changes between pages. Cursor pagination avoids this by using a stable marker. However, cursor pagination needs unique, ordered keys and is harder to implement.

Result

You learn trade-offs between simplicity and reliability in pagination.

Knowing the strengths and weaknesses of each pattern helps choose the right one for your system.

5

Advanced

Implementing Cursor Pagination in Practice

🤔Before reading on: do you think cursor pagination requires sorting data? Commit to your answer.

Concept: Cursor pagination requires consistent sorting and unique cursors to work correctly.

To implement cursor pagination, data must be sorted by a unique field like creation time or ID. The cursor is the last item's value in that order. The next query fetches items greater than this cursor. This ensures no overlaps or gaps.

Result

A robust pagination system that handles live data well.

Understanding sorting and unique cursors is essential for building reliable cursor pagination.

6

Expert

Scaling Pagination for High Traffic Systems

🤔Before reading on: do you think caching helps pagination performance? Commit to your answer.

Concept: Advanced pagination uses caching, indexing, and hybrid patterns to scale under heavy load.

High-traffic systems combine pagination with caching to reduce database load. Indexes speed up cursor queries. Sometimes, offset and cursor pagination are mixed to balance simplicity and performance. Monitoring and tuning are critical to avoid slow pages.

Result

Pagination systems that remain fast and reliable even with millions of users.

Knowing how to optimize pagination for scale prevents bottlenecks and poor user experience.

Under the Hood

Offset pagination works by calculating a starting position (offset) and fetching a fixed number of items from there. The database uses LIMIT and OFFSET clauses to retrieve data. Cursor pagination uses a unique cursor value from the last item, and queries for items greater than this cursor, often using indexed columns for speed. This avoids scanning skipped rows and handles data changes gracefully.

Why designed this way?

Offset pagination was designed for simplicity and easy jumping to any page. However, it struggles with data changes and performance on large offsets. Cursor pagination was introduced to solve these issues by using stable markers and indexes, trading off some simplicity for reliability and speed.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Client Request│──────▶│ Server Logic  │──────▶│ Database Query│
└───────────────┘       └───────────────┘       └───────────────┘
       │                       │                       │
       │ Offset or Cursor       │                       │
       │----------------------▶│                       │
       │                       │ Generate SQL Query    │
       │                       │----------------------▶│
       │                       │                       │
       │                       │  Return Data Slice    │
       │                       │◀----------------------│
       │ Return Page Data       │                       │
       │◀----------------------│                       │

Myth Busters - 4 Common Misconceptions

Quick: Does offset pagination always return consistent data if the database changes? Commit yes or no.

Common Belief: Offset pagination always returns the same data for the same page number.

Reality: If data changes between requests, offset pagination can return duplicates or skip items.

Why it matters: This causes confusing user experiences and data inconsistencies in apps.

Quick: Is cursor pagination always harder to implement than offset? Commit yes or no.

Common Belief: Cursor pagination is too complex and not worth using.

Reality: While cursor pagination is more complex, it is essential for reliable paging in dynamic data and large datasets.

Why it matters: Ignoring cursor pagination limits system scalability and data correctness.

Quick: Can cursor pagination jump directly to any page number? Commit yes or no.

Common Belief: Cursor pagination supports jumping to any arbitrary page like offset pagination.

Reality: Cursor pagination only supports moving forward or backward from a known position, not random page jumps.

Why it matters: Misunderstanding this leads to wrong UI designs and user frustration.

Quick: Does adding indexes always fix pagination performance? Commit yes or no.

Common Belief: Adding indexes solves all pagination performance problems.

Reality: Indexes help but large offsets still cause slow queries; cursor pagination or caching is needed for scale.

Why it matters: Relying only on indexes can cause unexpected slowdowns in production.

Expert Zone

1

Cursor pagination requires stable, unique, and monotonic sorting keys to avoid missing or repeating items.

2

Offset pagination can be optimized with keyset pagination hybrid approaches to balance usability and performance.

3

In distributed systems, cursor pagination helps maintain consistency across replicas better than offset.

When NOT to use

Avoid offset pagination for large or frequently changing datasets; prefer cursor pagination or keyset pagination. Avoid cursor pagination if your data lacks unique, ordered keys or if users need random page access; consider hybrid or caching strategies instead.

Production Patterns

Real-world systems use cursor pagination for infinite scrolling feeds (e.g., social media), offset pagination for simple admin dashboards, and combine caching layers with pagination to reduce database load. Monitoring query performance and user behavior guides pagination tuning.

Connections

Database Indexing

Pagination performance depends on efficient indexing of sorting keys.

Understanding indexing helps optimize cursor pagination queries for speed and scalability.

User Experience Design

Pagination patterns directly affect how users navigate and perceive data loads.

Knowing pagination helps design smooth, responsive interfaces that keep users engaged.

Supply Chain Logistics

Both pagination and logistics involve breaking large flows into manageable batches for efficiency.

Recognizing batch processing in logistics clarifies why pagination improves system throughput and reliability.

Common Pitfalls

#1 Using offset pagination on large, frequently updated datasets.

Wrong approach:

SELECT * FROM items ORDER BY created_at DESC LIMIT 10 OFFSET 1000000;

Correct approach:

SELECT * FROM items WHERE created_at < last_cursor_value ORDER BY created_at DESC LIMIT 10;

Root cause:

Misunderstanding that large offsets cause slow queries and inconsistent data when underlying data changes.

#2 Implementing cursor pagination without unique sorting keys.

Wrong approach:

Using a non-unique field like 'status' as cursor: WHERE status > last_status_value

Correct approach:

Using a unique timestamp or ID as cursor: WHERE created_at < last_created_at_value

Root cause:

Not realizing cursor pagination requires unique, ordered keys to avoid duplicates or missing items.

#3 Allowing users to jump to arbitrary pages with cursor pagination.

Wrong approach:

Providing UI input for page number and trying to map it to cursor queries.

Correct approach:

Designing UI for next/previous page navigation only, or combining with offset for random access.

Root cause:

Confusing offset's random access with cursor's sequential access model.

Key Takeaways

Pagination splits large data into smaller pages to improve performance and user experience.

Offset pagination uses page numbers and offsets but can be slow and inconsistent with changing data.

Cursor pagination uses unique markers to reliably page through data, especially in dynamic or large datasets.

Choosing the right pagination pattern depends on data size, update frequency, and user needs.

Advanced systems combine pagination with indexing, caching, and hybrid methods to scale efficiently.