# How to Paginate Data in a Retool Table

- Tool: Retool
- Difficulty: Beginner
- Time required: 15-20 min
- Compatibility: Retool Cloud and Self-hosted
- Last updated: March 2026

## TL;DR

Enable pagination in the Retool Table Inspector under Interaction → Pagination. For client-side pagination, just toggle it on. For server-side pagination, use table1.page and table1.pageSize in a SQL LIMIT/OFFSET query with 'Run when inputs change' enabled. Always show the total count with a separate COUNT query for proper navigation.

## Client-Side vs Server-Side Pagination in Retool

Retool tables support two pagination modes. Client-side pagination loads all data from the query at once and divides it into pages in the browser. This is simple to set up and works well for datasets up to a few thousand rows. Server-side pagination fetches only one page of data at a time from the database, which is necessary for large tables (10,000+ rows) where loading everything at once would be slow and memory-intensive.

This tutorial covers both approaches, the table1.page and table1.pageSize properties you use for server-side implementation, how to display total row counts, and how to handle edge cases like jumping to a specific page.

## Before you start

- A Retool app with a Table component
- For server-side pagination: a SQL database resource
- Basic familiarity with SQL LIMIT/OFFSET syntax

## Step-by-step guide

### 1. Enable Client-Side Pagination

Select your Table component. In the Inspector, go to the Interaction section and find 'Pagination type'. Set it to 'Client-side' (or 'Pagination' depending on your Retool version). Configure the page size — common values are 10, 25, or 50 rows per page.

Client-side pagination loads all query results into the browser and divides them into pages. No changes to your SQL query are needed. This works well for tables with fewer than a few thousand rows.

**Expected result:** Pagination navigation appears at the bottom of the table with Previous/Next buttons and page numbers.

### 2. Switch to Server-Side Pagination Mode

For large datasets, switch to server-side pagination. In the Table Inspector, set 'Pagination type' to 'Server-side'. This tells Retool the table is paginated server-side — Retool will no longer try to paginate client-side data.

With server-side pagination enabled, you must update your SQL query to use LIMIT and OFFSET based on the current page. The table will show the navigation controls but relies on your query to return the correct page of data.

**Expected result:** The table shows pagination controls but now expects your query to return only the current page's data.

### 3. Add LIMIT/OFFSET to Your SQL Query

With server-side pagination enabled, update your SQL query to use table1.page and table1.pageSize. The page property is 1-based (first page = 1), so the OFFSET formula is (page - 1) * pageSize.

Enable 'Run when inputs change' in the query's Advanced settings so it re-runs when the user navigates to a different page.

```
-- PostgreSQL example with server-side pagination:
SELECT
  id,
  first_name,
  last_name,
  email,
  status,
  created_at
FROM users
WHERE
  ({{ searchInput.value || '' }} = ''
   OR first_name ILIKE {{ '%' + (searchInput.value || '') + '%' }}
   OR last_name  ILIKE {{ '%' + (searchInput.value || '') + '%' }})
ORDER BY created_at DESC
LIMIT {{ table1.pageSize || 25 }}
OFFSET {{ ((table1.page || 1) - 1) * (table1.pageSize || 25) }};

-- MySQL / SQLite equivalent:
-- LIMIT 25 OFFSET 0  (page 1)
-- LIMIT 25 OFFSET 25 (page 2)
-- LIMIT 25 OFFSET 50 (page 3)
```

**Expected result:** The query fetches only 25 rows per page, dramatically reducing initial load time for large tables.

### 4. Get the Total Row Count for Pagination Display

Server-side pagination needs a total count to display 'Page 2 of 47' style navigation. Create a second SQL query named 'getUsersCount' that returns the total number of matching rows. Apply the same WHERE filters as your main query but use COUNT(*) instead of SELECT.

In the Table Inspector, set the 'Total rows' property to {{ getUsersCount.data[0].count }} so the table knows how many pages to show in the navigation.

```
-- SQL Query: getUsersCount
-- Must use same WHERE filters as main data query
SELECT COUNT(*) AS count
FROM users
WHERE
  ({{ searchInput.value || '' }} = ''
   OR first_name ILIKE {{ '%' + (searchInput.value || '') + '%' }}
   OR last_name  ILIKE {{ '%' + (searchInput.value || '') + '%' }});

-- In Table Inspector → Interaction → Total rows:
-- {{ parseInt(getUsersCount.data[0].count) || 0 }}
```

**Expected result:** The table pagination shows accurate page counts like 'Page 2 of 47' instead of just Previous/Next buttons.

### 5. Configure Page Size Controls

Add a Select component to let users control how many rows appear per page. Name it 'pageSizeSelect' and configure static options: [{label: '10', value: 10}, {label: '25', value: 25}, {label: '50', value: 50}, {label: '100', value: 100}].

If your table supports dynamic page size, bind the Table Inspector's 'Page size' to {{ pageSizeSelect.value }} instead of a hardcoded number. The SQL query already uses {{ table1.pageSize }} so it will adapt automatically.

```
// pageSizeSelect options (static):
// [{ label: '10 per page', value: 10 },
//  { label: '25 per page', value: 25 },
//  { label: '50 per page', value: 50 },
//  { label: '100 per page', value: 100 }]

// Table Inspector → Interaction → Page size:
// {{ pageSizeSelect.value || 25 }}

// When page size changes, reset to page 1 to avoid empty pages:
// pageSizeSelect event handler → Run JS Query:
await table1.setCurrentPage(1);
// Note: table1.setCurrentPage() is async
```

**Expected result:** Users can change the number of rows per page and the table re-queries accordingly.

### 6. Handle Pagination Reset When Filters Change

When a user applies a filter, the total number of rows changes and the current page may no longer be valid. Reset to page 1 whenever a filter changes. Add a JS Query that resets the page and re-runs the count query, triggered from filter component event handlers.

```
// JS Query: resetPaginationOnFilterChange
// Triggered by searchInput onChange and statusFilter onChange

// Reset table to page 1:
await table1.setCurrentPage(1);

// Retool will automatically re-run queries with 'Run when inputs change'
// since table1.page just changed to 1 (from whatever it was)

// If count query doesn't auto-run, trigger it:
await getUsersCount.trigger();

// Event handler setup:
// searchInput → Change → Run JS Query: resetPaginationOnFilterChange
```

**Expected result:** Applying a filter always returns the user to page 1 with the correct total count.

### 7. Show Loading State During Page Transitions

Add a loading indicator so users see feedback during server-side page transitions. Use query1.isFetching in a component's 'Disabled' or 'Loading' property. You can also show a spinner using a Spinner component with its 'Visible' property bound to {{ query1.isFetching }}.

```
// Spinner component visibility:
{{ query1.isFetching }}

// Disable 'Next Page' button while loading:
{{ query1.isFetching }}

// Show row count info:
{{ !query1.isFetching
   ? 'Showing ' + ((table1.page - 1) * table1.pageSize + 1) +
     '-' + Math.min(table1.page * table1.pageSize,
       parseInt(getUsersCount.data[0]?.count || 0)) +
     ' of ' + (getUsersCount.data[0]?.count || '?')
   : 'Loading...'
}}
```

**Expected result:** Users see a loading indicator during page transitions and a clear 'Showing X-Y of Z' row count.

## Complete code example

File: `SQL Query: getUsersPaginated`

```javascript
-- Query 1: getUsersPaginated
-- Enable: 'Run when inputs change'
SELECT
  id,
  first_name,
  last_name,
  email,
  department,
  status,
  revenue,
  created_at
FROM users
WHERE
  (
    {{ searchInput.value || '' }} = ''
    OR first_name ILIKE {{ '%' + (searchInput.value || '') + '%' }}
    OR last_name  ILIKE {{ '%' + (searchInput.value || '') + '%' }}
    OR email      ILIKE {{ '%' + (searchInput.value || '') + '%' }}
  )
  AND (
    {{ statusFilter.value || '' }} = ''
    OR status = {{ statusFilter.value }}
  )
ORDER BY
  created_at DESC
LIMIT  {{ table1.pageSize || 25 }}
OFFSET {{ ((table1.page || 1) - 1) * (table1.pageSize || 25) }};

-- Query 2: getUsersCount
-- Enable: 'Run when inputs change'
SELECT COUNT(*) AS count
FROM users
WHERE
  (
    {{ searchInput.value || '' }} = ''
    OR first_name ILIKE {{ '%' + (searchInput.value || '') + '%' }}
    OR last_name  ILIKE {{ '%' + (searchInput.value || '') + '%' }}
    OR email      ILIKE {{ '%' + (searchInput.value || '') + '%' }}
  )
  AND (
    {{ statusFilter.value || '' }} = ''
    OR status = {{ statusFilter.value }}
  );

-- Table Inspector settings:
-- Pagination type: Server-side
-- Page size: {{ pageSizeSelect.value || 25 }}
-- Total rows: {{ parseInt(getUsersCount.data[0]?.count) || 0 }}
```

## Common mistakes

- **Using client-side pagination on a table with 50,000 rows, causing slow loads** — undefined Fix: Switch to server-side pagination in the Table Inspector and add LIMIT/OFFSET to your SQL query referencing table1.page and table1.pageSize. Load only the current page of data.
- **Server-side pagination shows '?' for total pages because Total rows is not configured** — undefined Fix: Create a separate COUNT(*) query with the same WHERE conditions and bind its result to the Table Inspector's 'Total rows' field: {{ parseInt(countQuery.data[0].count) || 0 }}.
- **Not resetting to page 1 when filters change, leaving the user on an empty page** — undefined Fix: Add an event handler on filter components that calls await table1.setCurrentPage(1) whenever a filter changes. This ensures the user always sees the first page of filtered results.
- **Forgetting that table1.page is 1-based, calculating OFFSET as table1.page * pageSize** — undefined Fix: The correct OFFSET formula is (table1.page - 1) * table1.pageSize. Using table1.page * pageSize skips the first page of results.

## Best practices

- Use server-side pagination for any table with more than 5,000 rows — loading all rows client-side causes slow initial loads and memory issues.
- Always pair server-side pagination with a COUNT query using the same WHERE filters to show accurate total page counts.
- Reset to page 1 whenever filters change — staying on page 5 when the filtered result has only 2 pages causes an empty table.
- Use fallback values in LIMIT/OFFSET expressions: {{ table1.pageSize || 25 }} and {{ (table1.page || 1) }} to prevent SQL errors on initial page load.
- Enable 'Run when inputs change' on pagination queries — without it, navigating pages doesn't trigger a re-query.
- For PostgreSQL, wrap COUNT(*) results in parseInt() when binding to 'Total rows' since COUNT returns a string.
- Consider prefetching the next page in a background JS Query for smoother UX on large datasets.

## Frequently asked questions

### What is the difference between client-side and server-side pagination in Retool?

Client-side pagination loads all data into the browser at once and splits it into pages in JavaScript — simple to set up but impractical for large datasets. Server-side pagination fetches only one page from the database at a time using LIMIT/OFFSET, which is efficient for large tables. Use client-side for up to ~5,000 rows; use server-side for anything larger.

### Why does my Retool table show 'Page 1 of ?' with server-side pagination?

The table needs a total row count to compute the number of pages. Create a separate SQL query with COUNT(*) (using the same WHERE filters as your data query) and bind its result to the Table Inspector's 'Total rows' field: {{ parseInt(countQuery.data[0].count) || 0 }}. PostgreSQL returns COUNT as a string, so parseInt() is needed.

### How do I access the current page number in a Retool SQL query?

Use {{ table1.page }} for the current page number (1-based) and {{ table1.pageSize }} for the rows per page. The OFFSET formula is {{ ((table1.page || 1) - 1) * (table1.pageSize || 25) }}. The fallback values (|| 1 and || 25) handle the initial state before the table renders.

### How do I programmatically navigate to a specific page in a Retool table?

Use await table1.setCurrentPage(pageNumber) in a JS Query. This method is async, so use await before reading table1.page if you need the updated value. For example, to jump to page 1 after a filter change: await table1.setCurrentPage(1).

---

Source: https://www.rapidevelopers.com/retool-tutorial/how-to-paginate-data-in-a-retool-table
© RapidDev — https://www.rapidevelopers.com/retool-tutorial/how-to-paginate-data-in-a-retool-table