**Pagination** refers to the practice of dividing a large dataset into smaller, more manageable subsets called pages and retrieving them one at a time. It is commonly used to improve the performance and user experience when dealing with large result sets in queries.

Pagination Tip

We recommend using pagination when the query result contains more than 100,000 rows or 100 MB of data.

When a query returns a large number of records, fetching all of them at once can be inefficient and resource-intensive. Pagination allows you to retrieve and display a specific subset of the results, typically based on a defined page size (e.g. 100 records per page).

Rockset implements a **cursor-based approach** to pagination where, instead of relying on offsets, a cursor is used as a reference point to identify the position from which to retrieve the next set of results.

With this approach, an initial query is executed to fetch all of the data at once and store it temporarily in object storage so that the result set can be revisited over time. As the initial query often scans through large datasets, we recommend using async queries with pagination to exceed the 2 minute default timeout on queries. With async queries, query SLA times default to 30 minutes and that time can be customized by the user.

There are several benefits to this pagination approach:

  • Reduced processing needs as you only query once

  • Improved latency for large result sets

  • Consistent analytics

Check out [this blog](🔗) to learn more about our pagination method.

Pagination Tip

If you are concerned about a pagination query impacting the performance of ingestion or other queries running on the same virtual instance, you should consider running pagination queries on a separate [Virtual Instance](🔗).

## Sending a Query Request

To use pagination, set a limit on the number of initial results using the query request parameter `max_initial_results`. For example, if your application displays results to users in groups of 1000, set `max_initial_results` to 1000.

Pagination Tip

If you expect your query to exceed the 2 minute query timeout, use an [async query](🔗) to run queries up to 30 minutes.

Example request:

`max_initial_results` must be a value between 0 and 100,000.

Example response:

The `results` collection contains the actual result set, while the `query_id` and `pagination` fields contain all the information necessary to fetch the next results page. In order to fetch the first set of results from the start, you can omit the cursor parameter and call the `/orgs/self/queries/{query_id}/pages?docs=[number of docs]` endpoint with the optional `docs` parameter.

## Fetching the Next Page of Results

Invoke the `/orgs/self/queries/{query_id}/pages?cursor={next_cursor}&docs=[number of docs]` endpoint to fetch the next page of results. Retrieve the values from the `query_id` and `next_cursor` fields from the response above. Then, pass them into the respective query parameters in the endpoint.

The `docs` parameter is optional. If you choose not to add a `docs` parameter, the default will be 10,000 documents. The maximum value for `docs` is 100,000.

Alternatively, you can use the `offset` query parameter, which specifies the offset from the cursor of the first document to be returned. The maximum value for `offset` is 1,000,000,000. `offset` will default to 0 if not specified.

Example request:

Or using the `offset` parameter:

Example response:

If there are no more documents after the current page, the value for `next_cursor` will be null.

## Obtaining the Status of Paginated Queries

Results of paginated queries will expire in 24 hours. To get the expiration time of a query, use the `GET /orgs/self/queries/{queryId}` endpoint.

Example request:

Example response:

Check out this [Office Hours video](🔗) from one of our solutions engineers to learn more about using Pagination on Rockset.