Advanced Pagination: Designing Scalable Data Views

Pagination is more than a simple technique to divide a list into manageable parts. It's a multifaceted problem that encompasses performance optimization, data consistency, and user experience. At scale, implementing a robust, efficient pagination system is a challenging task, particularly when dealing with dynamic, constantly changing data.

In scenarios where you are dealing with extensive datasets, such as product catalogs, search results, chat logs, or activity feeds, sending the entire dataset to the client is not a viable solution. In these cases, we resort to pagination. However, the method we choose for implementing pagination will significantly affect the system's performance, data consistency, caching efficiency, user satisfaction, and even its security.

This article aims to provide a deep understanding of pagination, moving beyond the simple comparison of offset versus cursor. We will delve into the depths of pagination, starting from SQL design to API ergonomics, from frontend integration to ensuring reliability in a production environment.

Why Do We Need Pagination?

Even moderately sized data sets can become unmanageable without pagination.

Let's consider an e-commerce site with:

• 500,000 products
• 1,000 orders processed per day
• 100+ concurrent users

Sending 10,000 results in a single response:

• Overwhelms the frontend rendering process
• Consumes mobile data unnecessarily
• May trigger database timeouts or create memory pressure
• Negatively impacts perceived performance

By implementing pagination, we enforce bounded queries and incremental rendering. These techniques create 'windows' into large datasets, enabling various features such as:

• Navigation through search results
• Browsing through an admin dashboard
• Feed rendering
• Background preloading
• Incremental filtering

Key Pagination Strategies (with Deep Tradeoffs)

Offset-based Pagination

GET /products?offset=60&limit=20

This is a simple-to-understand and easy-to-implement strategy. It's supported out-of-the-box by SQL through the LIMIT and OFFSET keywords.

However, the downside of offset-based pagination is that the insertion or deletion of rows shifts everything, which can lead to:

• Duplicate results across different pages
• Entries being skipped altogether
• Inconsistencies in the state of the list

This approach is best suited for cases where the dataset is mostly static, such as archived logs or analytics data, and not real-time.

Page-based Pagination

GET /products?page=3&pageSize=50

This strategy adds user-friendly semantics to pagination and is well-suited for UI pagination controls (1, 2, 3...).

Despite having a better user experience than raw offsets, page-based pagination is still subject to the same limitations as offset-based pagination.

A useful design tip for implementing page-based pagination is to always return totalPages, totalCount, and currentPage in the response metadata. This information significantly enhances the user experience by providing context and control.

Cursor-based Pagination (a.k.a. Relay-style)

GET /posts?after=abcdef123&limit=25

In this strategy, after is a base64-encoded cursor (e.g., post ID or timestamp).

The API needs to generate and manage stable cursors, which can be a complex task. However, cursor-based pagination has several benefits:

• It is very consistent under mutation
• It is ideal for live feeds, messages, social timelines
• It encourages stateless client navigation

The drawbacks of cursor-based pagination include:

• It can be harder to debug
• It doesn't allow jumping to arbitrary pages (e.g., "page 30")
• It can break if cursor encoding/decoding logic is inconsistent

Cursor-based pagination is best suited for data sorted by time, ID, or relevance and is frequently updated.

Keyset Pagination

SELECT * FROM products
WHERE created_at < '2024-03-01T00:00:00Z'
ORDER BY created_at DESC
LIMIT 20;

Instead of offsetting by row number, keyset pagination paginates by value — typically a sort key like a timestamp or ID.

Keyset pagination has several benefits:

• It is extremely fast
• Its performance doesn't degrade on deep pages
• It is resistant to issues caused by deletion or insertion of rows

However, it has the downside of not being able to skip directly to a specific page, and it doesn't provide a total count.

Keyset pagination is used by high-performance feeds such as Facebook and Twitter.

Schema Design for Pagination

A good paginated API should always include:

• items: the data
• meta: pagination metadata

An example response could look like:

{
  "items": [...],
  "meta": {
    "totalCount": 1349,
    "page": 2,
    "pageSize": 50,
    "nextCursor": "abc123",
    "hasMore": true
  }
}

In GraphQL, you can design a paginated response like this:

query {
  products(first: 20, after: "abc123") {
    edges {
      node {
        id
        title
      }
      cursor
    }
    pageInfo {
      hasNextPage
      endCursor
    }
  }
}

Real-World Pagination Examples

GitHub

The GitHub GraphQL API uses cursor-based pagination (edges, pageInfo), which is crucial for repositories with over 100,000 issues. The cursor structure is opaque, reducing coupling between the client and the server.

Twitter

Twitter uses keyset pagination internally with message IDs to paginate tweets. This ensures consistency even as tweets are deleted or new ones are added between requests.

Shopify

Shopify's APIs for product, collection, and order listings use cursor-based pagination. Admin dashboards present paginated tables, while public storefronts often use infinite scrolling with cached cursors.

Frontend Considerations

When implementing pagination on the frontend, it's essential to:

• Use frameworks with first-class support for pagination, such as SWR, React Query, Relay, or TanStack Query
• Implement loading states that don't block the entire view
• Use intersection observers or scroll tracking for infinite scroll implementations
• Track cursors in the app state or URL params to allow users to resume their position
• Clearly display empty states and "end of list" indicators

Pitfalls and Gotchas

When implementing pagination, be aware of the following potential issues:

• Using offset-based pagination can result in skipped or duplicated data if rows are deleted
• Exposing raw database IDs could lead to security leaks
• Changing the sort order between requests could break pagination
• Failing to handle network failures could freeze the UI
• Mixing filters and sorts without resetting pagination could lead to a corrupted user experience

As a rule of thumb, pagination must be idempotent and stateless per request.

Conclusion: Paginate with Purpose

Pagination is not just a user experience consideration — it's a core API design principle. Properly implemented pagination:

• Protects backend systems from overload
• Enables infinite scaling
• Empowers frontend development teams
• Reduces user frustration with large datasets

Design your pagination system with care, test it thoroughly at scale, and clearly communicate its functionality in your documentation.

And always remember: no one wants to scroll past the first few pages of results anyway.