40 Efficient Pagination Resolvers for Large Datasets

title: 40 Efficient Pagination Resolvers for Large Datasets
date: 2024-06-12
tags: [pagination, backend, graphQL, REST, performance, scalable-architecture]

Pagination is one of those classic challenges we face whenever we need to display large datasets with optimal performance. As more applications scale up, pagination is no longer just about splitting data into pages—it’s also about preserving backend service performance, the user experience, and server resource usage. In this article, I’ll break down various pagination resolver strategies, especially in the context of GraphQL and REST APIs, and share 40 efficient tips and techniques for handling large datasets. We’ll cover best practices, with Python (Django) and Node.js (Express) code examples, along with real-world case studies from the industry.

A Common Problem: Pagination on Large-Scale Data

In the digital era, modern applications—whether e-commerce, social media, or SaaS—inevitably deal with millions of records in the database. Using the wrong pagination technique can lead to:

• Slow queries
• High database load
• API latency
• A degraded user experience

We’ll break down modern solutions to this classic problem.

Types of Pagination

Before diving into the 40 tips, it’s essential to understand the three main pagination patterns:

We’ll focus on efficient pagination, especially Cursor and Seek.

Architecture: How a Pagination Resolver Works

Before discussing the techniques, let’s look at the workflow of a pagination resolver, both in GraphQL and REST.

flowchart TD
  Client -->|Request: page/cursor| API
  API --> Resolver
  Resolver -->|Query with params| Database
  Database -->|Results| Resolver
  Resolver -->|Data+Meta Info| API
  API -->|Response JSON| Client

flowchart TD
  Client -->|Request: page/cursor| API
  API --> Resolver
  Resolver -->|Query with params| Database
  Database -->|Results| Resolver
  Resolver -->|Data+Meta Info| API
  API -->|Response JSON| Client

In the diagram above, the Resolver is the brain of the pagination operation—where you filter, sort, and generate metadata (hasNextPage, total, and so on).

40 Efficient Tips and Techniques for Pagination Resolvers on Large Datasets

1-10: A Solid Foundation Design

1. Use Cursor-based Pagination for Data > 10K
   Avoid offset. Cursor is more consistent (especially when inserts/deletes happen).

2. Index the Pagination Field
   Make sure the column used as the cursor is already indexed in the database.

3. Query Selective Fields
   Select only the fields you need; don’t SELECT *.

4. Limit the Query Result
   Cap the data sent per page, e.g. a maximum of 50–100 rows.

5. Don’t Calculate the Total in the Main Query
   Separate the data query from the total count; execute them asynchronously when possible.

6. Use UUID as the Cursor
   Avoid increment numbers; use a UUID for security and uniqueness.

7. Optimize the Sort Order
   Always give the query an explicit ORDER BY that matches the cursor.

8. Return PageInfo Metadata
   Add next/previous info, a hasNext boolean, and so on.

9. Avoid Pagination for Static Data
   Return the entire dataset if it has fewer than 100 records.

10. Cache Pagination Queries
    Cache the results of popular pages.

11-20: Query Techniques Across Different Platforms

11. Use a JSONB Index Query in PostgreSQL
12. Implement Limit-Offset in Redis for Simple Lists
13. Range Scan in MongoDB using a field index
14. Elasticsearch Search After—use the sort key as the cursor
15. Sharding Pagination for horizontally clustered databases
16. Avoid DISTINCT When Unnecessary in your query
17. Async Query (Promise.all) for Metadata in Node.js
18. Use EXPLAIN on pagination queries for troubleshooting
19. Preload Associations Selectively (GraphQL resolver)
20. Paginate Queries on an Aggregate Table

21-30: Code Simulations and Real-World Case Studies

Offset (inefficient for >10K rows)

1// Node.js Express - Not recommended for large datasets
2app.get('/users', async (req, res) => {
3  const { page = 1, limit = 20 } = req.query;
4  const offset = (page - 1) * limit;
5  const users = await User.find().skip(offset).limit(limit);
6  res.json(users);
7});

Cursor (Highly Efficient)

 1# Django/Python GraphQL
 2def resolve_users(parent, info, after=None, first=20):
 3    query = User.objects.all().order_by('created_at')
 4    if after:
 5        query = query.filter(created_at__gt=after)
 6    items = list(query[:first])
 7    end_cursor = items[-1].created_at if items else None
 8    return {
 9        "edges": items,
10        "pageInfo": {
11            "endCursor": end_cursor,
12            "hasNextPage": len(items) == first
13        }
14    }

• Effective on large tables; it only loads data “since” the cursor position.

Seek Method (Streaming/Big Data)

1-- MySQL seek, only works for a sorted primary key field
2SELECT * FROM log_table
3WHERE id > LAST_SEEN_ID
4ORDER BY id
5LIMIT 100;

31-40: Production Tips and Edge Cases

31. Implement Rate Limiting on the Pagination API
    So clients can’t scrape all the data at once.

32. Paginate Consistently on Joined Tables
    Use a composite cursor when data comes from joined tables.

33. Rotate the Cursor in Encrypted Form
    For security, prevent any data leakage to clients.

34. Lazy Loading on the Frontend
    Combine it with infinite scroll.

35. Resumable Pagination
    Save the cursor position in case the user reconnects.

36. Paginate Together with Filtering and Search
    Filter the data first, then apply the cursor.

37. Load Test Your Pagination
    Simulate high-concurrency queries.

38. Error Handling for Expired Cursors
    Catch cases where the data has already been deleted.

39. Pagination with Multi-field Sorting
    The cursor can take the form of a pair: (created_at, id)

40. Monitor Slow Queries for Pagination Endpoints
    Tools: APM, logs to catch slow queries on these endpoints.

Example Simulation: 5 Million Rows

Suppose we have a transactions table with 5 million records. Let’s compare the performance of offset vs cursor.

The result: cursor-based pagination is far more efficient on both relational and NoSQL databases.

Conclusion

Pagination looks simple—but the demands of modern applications require us to be efficient and scalable. By adopting the 40 tips above, you’ll be far better prepared to handle large datasets without compromising API performance.

If you’re building a resolver for GraphQL/REST, use a combination of cursor-based pagination and query optimization tricks to keep the user experience at its best. Always monitor and test performance, because “the bigger the data, the bigger the challenge!”

I hope this helps—don’t hesitate to share your favorite pagination technique in the comments! 🚀