> ## Documentation Index
> Fetch the complete documentation index at: https://docs.startree.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Cursor Pagination

> Run a query once and read its results page by page from the broker's response store.

Cursor pagination lets you run a query once and then read its results page by page. The broker saves the full result after the first API call. Each later page is read from that saved copy, so the query is never run again.

> [!NOTE]
>
> **At a glance**
>
> * **First API call:** `POST /query/sql?getCursor=true&numRows=N` runs the query and returns page 1 plus a `requestId`.
> * **Subsequent calls:** `GET /responseStore/{requestId}/results?offset=X&numRows=N` returns any page you ask for.
> * **Cleanup:** stored results expire and are deleted automatically.

## Overview

Without cursors, paging through a large result usually means sending the same query again and again with a different `LIMIT` and `OFFSET`. Each of those calls makes Pinot do the full query work again. With cursors, the broker runs the query one time, saves the full result, and hands you pages of it on request.

Use cursor pagination when:

* A query returns more rows than you want to send to a client in one response.
* You are building a UI with "next page" or "load more" controls.
* You are exporting or processing a large result in batches.
* You want every page to come from the same snapshot of results.

## How it works

The broker runs your query once, saves the whole result in its response store, and then serves pages from that saved copy. Servers are only involved in the first API call.

```mermaid theme={null}
sequenceDiagram
    participant App as Your app
    participant Broker
    participant Servers
    participant Store as Response store
    Note over App,Store: First API call
    App->>Broker: 1. POST query, getCursor=true
    Broker->>Servers: 2. Run the query
    Servers-->>Broker: 3. All result rows
    Broker->>Store: 4. Save full result
    Broker-->>App: 5. Page 1 + requestId
    Note over App,Store: Subsequent calls (no query re-run)
    App->>Broker: 6. GET results?offset=X
    Broker->>Store: 7. Read rows from X
    Store-->>Broker: 8. Requested rows
    Broker-->>App: 9. Next page
```

1. Your app sends the query with `getCursor=true`. The broker runs it on the servers like any other query.
2. The broker saves the full result to its response store and gives it an ID (`requestId`).
3. The broker returns the first page, the `requestId`, and the total row count.
4. For each later page, your app asks the same broker for rows starting at an `offset`. The broker reads them from the stored result. The query is not run again.
5. After the expiration time, the broker deletes the stored result automatically.

## Prerequisites

Cursor pagination must be enabled on your environment's brokers. Ask StarTree Support to enable it.

Support can also tune the page size and how long stored results are kept. See [Broker configuration](#broker-configuration) for the settings involved.

> [!NOTE]
>
> **How expiration and the cleaner work together.** A stored result expires after the `expiration` time, and the cleaner deletes expired results on its next run. For example, with a 15-minute expiration and a cleaner that runs every 10 minutes, a result is removed between 15 and 25 minutes after the query. Ask for an expiration longer than your clients need to page through a result.

## Paging through results

A first API call opens the cursor, and subsequent calls fetch the remaining pages. In all examples, replace `xxx.startree.cloud` with your broker URL, and `<YOUR_TOKEN>`, `<your_table>` and `<REQUEST_ID>` with your own values.

The examples use a result of 17 rows read with a page size of 10. Page 1 returns rows 0 to 9 and page 2 returns the remaining 7.

### Run the query and open a cursor

Send the query with `POST /query/sql`, adding `getCursor=true` and your page size `numRows` as URL parameters. Put the SQL in the JSON body as usual.

```bash theme={null}
curl -X POST "https://xxx.startree.cloud/query/sql?getCursor=true&numRows=10" \
  -H "Authorization: Bearer <YOUR_TOKEN>" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM <your_table> LIMIT 20"}'
```

> [!WARNING]
>
> `getCursor` and `numRows` must go in the URL. They are not query options, so putting them inside `queryOptions` in the body does nothing.

The response holds the first page of data in `resultTable`, plus the cursor fields:

| Field | What it tells you |
| :- | :- |
| `resultTable` | The data for this page. It holds `dataSchema` and `rows`. |
| `resultTable.dataSchema` | The result's columns: `columnNames` lists their names and `columnDataTypes` lists their types (for example `LONG`, `STRING`, `DOUBLE`). |
| `resultTable.rows` | The rows in this page, one array per row, with values in the same order as `columnNames`. |
| `requestId` | The cursor's ID. You use it to fetch every later page. |
| `numRowsResultSet` | Total number of rows in the full result. |
| `offset` | Position of the first row in this page (`0` for the first page). |
| `numRows` | Number of rows actually returned in this page. |
| `expirationTimeMs` | When the stored result expires, as epoch milliseconds. |
| `brokerHost` / `brokerPort` | The broker that ran the query and holds the stored result. |

A first-page response with a page size of 3 looks like this (trimmed):

```json theme={null}
{
  "resultTable": {
    "dataSchema": {
      "columnNames": ["<column_1>", "<column_2>", "<column_3>"],
      "columnDataTypes": ["LONG", "STRING", "DOUBLE"]
    },
    "rows": [
      [101, "value-a", 1.7],
      [102, "value-b", 10.39],
      [103, "value-c", 14.04]
    ]
  },
  "requestId": "1234567890000000001",
  "numRowsResultSet": 17,
  "offset": 0,
  "numRows": 3,
  "expirationTimeMs": 1792760944761,
  "brokerHost": "pinot-broker-xxx",
  "brokerPort": 8099
}
```

### Fetch the next pages

Call `GET /responseStore/{requestId}/results` with the `offset` of the first row you want and the page size in `numRows`.

```bash theme={null}
curl "https://xxx.startree.cloud/responseStore/<REQUEST_ID>/results?offset=10&numRows=10" \
  -H "Authorization: Bearer <YOUR_TOKEN>"

# Returns "offset": 10 and "numRows": 7, the last 7 of the 17 rows.
```

To walk through the whole result, increase `offset` by your page size each time (0, 10, 20, ...) until `offset` reaches `numRowsResultSet`. The last page may hold fewer rows than you asked for, and its `numRows` field shows the actual count.

> [!WARNING]
>
> **Stay on the same broker.** The stored result lives only on the broker that ran the query. Other brokers do not have it, and a call that lands on a different broker fails with "not found". Use one of these two options for every follow-up call:
>
> 1. **Call the same broker directly:** send every page fetch to the broker named in `brokerHost` and `brokerPort` in the first response.
> 2. **Use the StarTree proxy URL:** send all calls through the [proxy](https://docs.startree.ai/corecapabilities/query_data/query_interfaces/pinot-proxy.md) broker URL. The proxy routes calls with the same `requestId` to the same broker.

### Stop at the total

An `offset` equal to or larger than `numRowsResultSet` returns an error, not an empty page. With 17 stored rows, valid offsets are 0 to 16.

```bash theme={null}
curl "https://xxx.startree.cloud/responseStore/<REQUEST_ID>/results?offset=18&numRows=2" \
  -H "Authorization: Bearer <YOUR_TOKEN>"

# Response:
# Offset 18 should be lesser than totalRecords 17
```

The same error comes back for `offset=17`, because it equals the total, and the broker returns it as an HTTP 500 error. Stop paging once `offset` reaches `numRowsResultSet`. An offset inside the range with a large `numRows` is fine: it simply returns the rows that are left.

### Clean up (optional)

Stored results are deleted automatically after they expire. To free the space right away when you are done, delete the cursor:

```bash theme={null}
curl -X DELETE "https://xxx.startree.cloud/responseStore/<REQUEST_ID>" \
  -H "Authorization: Bearer <YOUR_TOKEN>"
```

## Parameters

There are two kinds of settings: request parameters you send with each call, and broker configuration that sets the page size and storage behavior.

### Request parameters

| Parameter | Used in | What it does |
| :- | :- | :- |
| `getCursor` | `/query/sql` | `true` turns on cursor mode. |
| `numRows` | `/query/sql`, `/responseStore/{requestId}/results` | Page size: rows returned per call. |
| `offset` | `/responseStore/{requestId}/results` | Row to start from, beginning at 0. |
| `requestId` | `/responseStore/{requestId}/results` | ID of the stored result, from the `/query/sql` response. |

### Broker configuration

These are the cursor settings a broker reads. To change any of them, contact StarTree Support.

| Parameter | Format | What it does and why it matters |
| :- | :- | :- |
| `pinot.broker.cursor.fetch.rows` | Number | Default page size when a request does not send `numRows`. Set it to the page size most of your clients use. |
| `pinot.broker.cursor.response.store.type` | `file` | Which response store keeps the results. `file` writes them to the folder set by `file.data.dir`. |
| `pinot.broker.cursor.response.store.expiration` | Duration, for example `15m`, `2h`, `1d` | How long a stored result stays readable. Make it longer than the time a client needs to read every page. Shorter values free disk sooner. |
| `pinot.broker.cursor.response.store.cleaner.frequencyPeriod` | Duration, greater than 0 | How often each broker deletes expired results. A shorter period removes them sooner after they expire. |
| `pinot.broker.cursor.response.store.cleaner.initialDelay` | Duration, 0 or more | How long a broker waits after starting before its first cleanup run. Rarely needs changing. |
| `pinot.broker.cursor.response.store.file.data.dir` | URI with a scheme | Where stored results are kept, one folder per `requestId`. Must include a scheme, such as `file:///path/to/cursors`. A bare path like `/path/to/cursors` stops the broker from starting. |
| `pinot.broker.cursor.response.store.file.temp.dir` | Local path | Local staging folder used while a result is being written, before it is copied to `data.dir`. Keep it outside `data.dir`. |
| `pinot.broker.cursor.response.store.file.extension` | Text | File suffix for the stored result files. Rarely needs changing. |

Durations accept the units `d`, `h`, `m` and `s`, and combinations such as `1h30m`.

## Good to know

* **Results expire.** A stored result lives for the `expiration` time, and may stay up to one more cleaner cycle before it is deleted. After that, run the query again to get a new cursor.
* **Check broker disk size.** If your cursor QPS is very high, check the broker disk size. Stored results stay on the broker's disk until they expire, so a high volume of cursor queries could fill the disk.
* **Pick a sensible page size.** Each page fetch re-reads the stored result, so very small pages over a very large result are slower. Pages of hundreds or thousands of rows work better than pages of 10.
* **The query still runs in full.** Cursors page the delivery of results, not the query itself. The query's `LIMIT` and the broker's memory limits still apply, and only read (`SELECT`) queries can open a cursor.
* **Results are a snapshot.** Every page comes from the result saved when the query ran, so new data ingested later does not appear until you run the query again.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.