Cursor Pagination

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.

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.

Your app sends the query with getCursor=true. The broker runs it on the servers like any other query.
The broker saves the full result to its response store and gives it an ID (requestId).
The broker returns the first page, the requestId, and the total row count.
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.
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 for the settings involved.

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.

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"}'

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):

{
  "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.

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.

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:

Call the same broker directly: send every page fetch to the broker named in brokerHost and brokerPort in the first response.
Use the StarTree proxy URL: send all calls through the proxy 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.

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:

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.

Get Started

Ingestion

External Tables

Query Data

Manage Data

Observability

Visualize Data

Cluster Operations

Manage Security

Release Notes

Reference

Overview

How it works

Prerequisites

Paging through results

Run the query and open a cursor

Fetch the next pages

Stop at the total

Clean up (optional)

Parameters

Request parameters

Broker configuration

Good to know

​Overview

​How it works

​Prerequisites

​Paging through results

​Run the query and open a cursor

​Fetch the next pages

​Stop at the total

​Clean up (optional)

​Parameters

​Request parameters

​Broker configuration

​Good to know

Overview

How it works

Prerequisites

Paging through results

Run the query and open a cursor

Fetch the next pages

Stop at the total

Clean up (optional)

Parameters

Request parameters

Broker configuration

Good to know