Cursor-Based Pagination for Large Datasets

From Pages to Pointers: The Core Idea

Cursor-based pagination (also known as keyset pagination) abandons the concept of "page numbers" entirely. Instead of telling the server "give me page 5," the client says, "give me the next 10 items after the last one I saw."

This "last one I saw" is represented by a cursor, which is an opaque, stable pointer to a specific item in the sorted dataset. The client receives a cursor with each page of data and sends it back to the server to request the next page.

The diagram below contrasts this with the offset-based approach we discussed previously.

The flow is simple and powerful:

1. The client makes an initial request without a cursor.
2. The server returns the first page of results and a next_cursor pointing to the last item in that page.
3. For the next page, the client sends a request including that next_cursor.
4. The server uses the cursor to find its place in the dataset and returns the subsequent items, along with a new next_cursor.

This process is visualized clearly in the following illustration.

The Advantages: Performance and Consistency

So, why is this approach so much better? It directly solves the two main problems of offset pagination. The PlanetScale video below gives an excellent overview of the concepts and benefits.

Let's break down the advantages you just saw.

1. High Performance at Scale:
   Instead of using OFFSET, which forces the database to scan and discard millions of rows, cursor pagination uses a WHERE clause. A query looks like this:
   SELECT * FROM users WHERE created_at < 'cursor_value' ORDER BY created_at DESC LIMIT 10;
   If the created_at column is indexed, the database can use the index to instantly seek to the correct position and start reading the 10 rows it needs. This means fetching "page" 1,000,000 is just as fast as fetching page 1. There is no performance degradation as you go deeper into the results.

2. Stable and Consistent Data:
   The "shifting data" problem disappears. Because the query is anchored to a stable value from a specific record (the cursor), it doesn't matter if new records are added or deleted before that point. The starting point for the next page remains the same relative to the data the user has already seen, preventing missed or repeated items.

The main tradeoff is that you lose the ability to jump to an arbitrary page number (e.g., "go to page 50"). For use cases like infinite scrolling or "Load More" buttons, this is a perfectly acceptable limitation.

Implementing Cursor-Based Pagination in Go

Now for the practical part. Given your background in Go and SQL, we'll walk through a complete implementation. While the concept is simple, a robust implementation has several important details to get right.

The Prerequisite: A Deterministic Order

For pagination to be reliable, the order of results must be the same every time you run the query. As you know from your DBMS course, SQL tables have no inherent order. You must use an ORDER BY clause. For cursor pagination, the column(s) you order by must be unique and sequential.

• A created_at timestamp is a common choice, but what if two records have the exact same timestamp? You could miss records.
• The solution is to add a unique secondary sort column, like the primary key (id). This is called keyset pagination. The ORDER BY clause becomes ORDER BY created_at DESC, id DESC.

The article "Cursor-Based Pagination in Go" provides an excellent conceptual breakdown of the logic before diving into code.

A Complete Go Implementation

The article "Implementing cursor pagination in Golang" provides a fantastic, production-ready example using Go Fiber and GORM. It correctly implements keyset pagination with a composite key (created_at, id) and handles both forward and backward navigation.

The concepts from the Go article are universal. The video below, though using Python, provides an excellent visual walkthrough of the limit + 1 trick and the process of making the cursor opaque via encoding.

By combining these techniques, you get a robust and scalable pagination system:

• API Request: GET /books?limit=10&cursor=eyJjcm...ZXh0Ijp0cnVlfQ==
• Backend Logic:
  1. Base64-decode and JSON-unmarshal the cursor string to get the created_at and id of the last item.
  2. Query the database:

     SELECT * FROM books
     WHERE (created_at, id) < ('2022-12-17T17:14:47Z', '6e4d9295-6b26...')
     ORDER BY created_at DESC, id DESC
     LIMIT 11; -- (limit + 1)
     

  3. Check if 11 records were returned. If so, a next page exists.
  4. The next_cursor is generated from the 10th record.
  5. Return only the first 10 records in the response.
• API Response:

  {
    "data": [ ... 10 book objects ... ],
    "pagination": {
      "next_cursor": "eyJjcmVhdGVkX2F0Ijo...etc..."
    }
  }
  

Conclusion

In this lesson, you've moved beyond basic pagination to a scalable, production-grade solution. You've seen how cursor-based pagination solves the critical performance and consistency problems inherent in the offset method, a key piece of knowledge for any backend developer.

Key Takeaways:

• Core Mechanism: Cursor-based pagination uses a WHERE clause on an indexed, unique, and sequential key (often a composite key like (timestamp, id)) to find the starting point for the next page.
• Advantages: It offers excellent performance on large datasets by allowing the database to seek directly to the data. It is also resilient to changes in the dataset, preventing missed or repeated items.
• The Cursor: A cursor is an opaque token that points to the last record a user has seen. It should be encoded (e.g., Base64) to be treated as a black box by the client.
• Implementation: A common pattern is to query for limit + 1 records to efficiently check if a next page exists without a separate COUNT query.
• Tradeoff: The primary limitation is the inability to jump to arbitrary pages, making it ideal for infinite-scroll feeds but less so for interfaces requiring numbered page links.

Now that we've covered how to return large collections of data correctly, our next step is to consider what happens when things go wrong. In the next lesson, we will focus on designing a consistent API error response format, ensuring that clients can reliably handle issues like invalid input or server failures.