Optimize queries with server-side pagination
Learn how to implement server-side pagination of data.
When building apps using large data sets, it's important to limit how much data your queries return. Returning large sets of data at once can affect performance in your apps, especially when joining multiple tables in complex queries.
Server-side pagination is a best practice to optimize queries by returning only the results necessary to populate a given view. When new data is needed to populate the view, the query is triggered again and the server retrieves the next set of results.
The demo app shows examples of server-side pagination using List View, Table, and Pagination components.
Write queries that use server-side pagination
Most databases and APIs support server-side pagination but can vary on how it is supported. Retool enables you to make use of commonly used patterns or parameters like limits, offsets, and cursors. Check your data source to determine how it best enables server-side pagination.
SQL queries
In SQL, you can implement server-size pagination by adding a limit and offset to your query. Limit tells the query to only return the given number of rows, and offset indicates the amount of rows to skip before returning rows.
For example, the following uses the table1 page size (i.e., rows currently displayed) to limit the number of rows returned and the table's built-in offset as the offset.
select id, first_name, last_name, email from customer where first_name ilike {{ '%' + textinput1.value + '%' }}
order by id
limit {{ table1.pagination.pageSize }}
offset {{ table1.pagination.offset }}
API requests
API requests often accept parameters to specify a subset of data to return. The following demo app shows server-side pagination with the Pagination and List View components and uses _limit and _page REST API parameters.
In the REST API in the demo, the _limit parameter determines the number of results to return—in this example, the number of rows in the List View. The _page parameter determines the page to begin returning results—in this example, the current page in view.
GET https://retoolapi.dev/HZKN0g/data?_page={{ pagination1.value }}&_limit={{ listView1.instances }}
Some APIs use cursor-based pagination, which uses an identifier (a cursor) in a data set to segment records before or after the cursor. In the API request, you specify a given cursor and use the result set to determine which cursor to use in the next query.
Query to retrieve total row counts
With client-side pagination, the client can access an entire data set at once and use its count to determine how many pages a view requires. With server-side pagination, only a subset of data is accessible at once, so you'll need another query to retrieve the total number of rows in your data set. Depending on your app, you might use this query directly or as part of a calculation to determine how many pages your view needs.
For example, to determine a Pagination component's Page count, divide the total row count by the number of items in a single view.
Be sure any filters used in your pagination query are also used in the total row count query, so each returns the same number of rows.
Server-side pagination with the Table component
The Table component has built-in support for server-side pagination. In the Add-ons section of the Inspector, select Pagination, then select the box next to Enable server side pagination. You can then choose to paginate using Limit offset based, Cursor based, or GraphQL Relay cursor based pagination types.
Each server-side pagination type requires the Total row count field.
Limit and offset
Table includes the following properties to support limit offset-based pagination.
| Property | Description |
|---|---|
pagination.pageSize | The number of records of the current page in view. |
pagination.offset | The offset of the current page in view. |
You can use these properties in the limit and offset clauses of your queries.
Cursor-based
Table includes the following properties to support cursor-based pagination.
| Property | Description |
|---|---|
pagination.pageSize | The number of records of the current page in view. |
pagination.afterCursor | A cursor representing the next page, read from the Next cursor value. |
Use these properties to dynamically update API requests with the correct cursor and limit. For example, a request to the Stripe API might use afterCursor and pageSize as parameters for the limit and starting_after fields.
GET https://api.stripe.com/v1/customers?limit={{ table1.pagination.pageSize }}&{{ table.pagination.afterCursor ? 'starting_after' : null }}={{ table1.pagination.afterCursor }}
For a Table showing five rows per page, with cus_4QFOF3xrvBT2nU as the ID of the last customer on the current page, the request might evaluate to:
GET https://api.stripe.com/v1/customers?limit=5&starting_after=cus_4QFOF3xrvBT2nU
Use the updated table data to specify the values for the Previous cursor, Next cursor, and Has next page fields in Inspect > Pagination. For example, using the Stripe API, Next cursor is the ID of the last customer in the table.
GraphQL Relay cursor
Retool supports cursor-based pagination using Relay's GraphQL server specification. For more information, see the Relay GraphQL Cursor Connections documentation.
GraphQL queries accept the first, last, after, and before arguments to identify the data to retrieve. You can determine whether to include first or last parameters by checking the table's afterCursor property.
To specify values for Previous cursor and Next cursor, use the cursor field from the API response. To specify a value for Has next page, use the pageInfo.hasNextPage field from the API response.
