# How to Handle Large Datasets in Retool

- Tool: Retool
- Difficulty: Intermediate
- Time required: 25-30 min
- Compatibility: Retool Cloud and Self-hosted
- Last updated: March 2026

## TL;DR

Handle large datasets in Retool by using server-side pagination (LIMIT/OFFSET with table1.page), adding SQL indexes on filtered/sorted columns, computing aggregations in SQL rather than transformers, setting appropriate caching with 'Cache results' in query settings, and avoiding loading full datasets client-side. Never use SELECT * on large tables.

## Performance Patterns for Retool Apps with Large Datasets

Retool apps start showing performance issues when queries return more than 5,000-10,000 rows. The browser slows down rendering large tables, transformers take longer to process, and users experience significant lag. The solution is a combination of server-side strategies (pagination, indexes, aggregation) and client-side strategies (caching, progressive loading).

This tutorial covers the most impactful optimizations: switching to server-side pagination, writing efficient SQL, using aggregations to summarize data before it reaches Retool, implementing query result caching, and lazy loading patterns for secondary data. RapidDev works with teams building enterprise Retool tools that handle millions of rows — these are the patterns that make the difference.

## Before you start

- A Retool app with a Table component showing performance issues
- Access to a SQL database with large tables
- Basic SQL knowledge (indexes, EXPLAIN, CTEs)
- Understanding of Retool's query panel and transformer pattern

## Step-by-step guide

### 1. Diagnose the Performance Bottleneck

Before optimizing, identify where the slowness is. Open your browser's DevTools Network tab while running the slow query. Check the query response time and response size. If the response is several MB or takes more than 3 seconds, the bottleneck is the query itself (too many rows or slow SQL). If the query is fast but the table renders slowly, the bottleneck is client-side rendering.

In Retool's query editor, click 'Run' and note the execution time shown in the results panel. A well-optimized query for a paginated table should take under 500ms.

**Expected result:** You identify whether the bottleneck is query execution time or client-side rendering.

### 2. Replace SELECT * with Explicit Column Lists

Never use SELECT * on large tables. Fetching all columns increases the response payload size, wastes bandwidth, and can include large text/JSON columns you don't need. Always list only the columns your UI actually displays.

This single change can reduce query response time by 50-80% on tables with text, JSON, or binary columns.

```
-- BEFORE (fetches all columns including large ones):
SELECT * FROM orders
WHERE status = {{ statusFilter.value }}
ORDER BY created_at DESC;

-- AFTER (only columns shown in the table):
SELECT
  id,
  user_id,
  order_number,
  status,
  total_amount,
  created_at
FROM orders
WHERE
  ({{ statusFilter.value || '' }} = '' OR status = {{ statusFilter.value }})
ORDER BY created_at DESC
LIMIT {{ table1.pageSize || 50 }}
OFFSET {{ ((table1.page || 1) - 1) * (table1.pageSize || 50) }};
```

**Expected result:** Query response time and payload size are significantly reduced.

### 3. Implement Server-Side Pagination

For tables with more than 5,000 rows, server-side pagination is non-negotiable. Set the Table component's Pagination type to 'Server-side' in the Inspector. Add LIMIT and OFFSET to your SQL query using table1.page and table1.pageSize. Create a separate COUNT query for total row display.

This pattern limits every query to at most 50-100 rows, keeping the browser snappy regardless of database size.

```
-- Main paginated query:
SELECT id, user_id, order_number, status, total_amount, created_at
FROM orders
WHERE
  ({{ statusFilter.value || '' }} = '' OR status = {{ statusFilter.value }})
  AND ({{ searchInput.value || '' }} = ''
       OR order_number ILIKE {{ '%' + (searchInput.value || '') + '%' }})
ORDER BY created_at DESC
LIMIT {{ table1.pageSize || 50 }}
OFFSET {{ ((table1.page || 1) - 1) * (table1.pageSize || 50) }};

-- Count query (same WHERE, no LIMIT):
SELECT COUNT(*) AS total
FROM orders
WHERE
  ({{ statusFilter.value || '' }} = '' OR status = {{ statusFilter.value }})
  AND ({{ searchInput.value || '' }} = ''
       OR order_number ILIKE {{ '%' + (searchInput.value || '') + '%' }});
```

**Expected result:** The table loads the first 50 rows instantly and users navigate pages for more data.

### 4. Add Database Indexes on Filtered and Sorted Columns

If your paginated queries still take more than 500ms, add indexes on the columns used in WHERE and ORDER BY clauses. This is a database-level optimization that reduces query time from seconds to milliseconds on large tables.

For the common pattern of filtering by status + sorting by created_at, a composite index covers both operations in one index scan.

```
-- Add indexes for your common query patterns:

-- For WHERE status = ? ORDER BY created_at DESC:
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC);

-- For text search (ILIKE) on order_number:
CREATE INDEX CONCURRENTLY idx_orders_order_number_gin
  ON orders USING gin(order_number gin_trgm_ops);
-- Requires: CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- For date range queries on created_at:
CREATE INDEX CONCURRENTLY idx_orders_created_at
  ON orders (created_at DESC);

-- Check if indexes are being used:
EXPLAIN ANALYZE
SELECT id FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 50;
```

**Expected result:** Filtered and sorted queries execute in under 100ms even on tables with millions of rows.

### 5. Compute Aggregations Server-Side Instead of Client-Side

For charts and KPI cards showing totals, averages, or counts, compute these in SQL rather than in a Retool transformer. SQL aggregations run on the database with full index support — loading 100,000 rows into a transformer to sum them is 1000x slower than a single SQL COUNT/SUM query.

Replace transformer-based aggregations with dedicated SQL aggregate queries for each KPI.

```
-- BEFORE: bad pattern - loading 100k rows to sum in transformer:
-- SELECT amount FROM orders WHERE created_at > '2024-01-01';
-- Transformer: return data.reduce((sum, r) => sum + r.amount, 0);

-- AFTER: compute in SQL:
SELECT
  COUNT(*) AS order_count,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_value,
  COUNT(*) FILTER (WHERE status = 'pending') AS pending_count,
  SUM(amount) FILTER (WHERE created_at >= DATE_TRUNC('month', NOW()))
    AS mtd_revenue
FROM orders
WHERE created_at >= '2024-01-01';

-- Access in components:
-- {{ getOrderStats.data[0].total_revenue }}
-- {{ getOrderStats.data[0].pending_count }}
```

**Expected result:** KPI queries return a single row with pre-computed aggregates in milliseconds.

### 6. Cache Query Results to Avoid Redundant Re-fetches

For data that doesn't change frequently (reference tables, configuration data, aggregated stats), enable query result caching. In the query's Advanced settings, set 'Cache results' to 'Yes' and configure the TTL (time to live). Cached results are returned immediately from memory on subsequent runs without hitting the database.

Common use cases for caching: dropdown options lists, product catalogs, configuration tables, and daily summary stats.

```
// Query Advanced settings:
// Cache results: Yes
// TTL: 300 (5 minutes) for frequently-checked data
//       3600 (1 hour) for reference data like product categories
//       86400 (1 day) for configuration tables

// To manually invalidate a cached query, run it explicitly:
await cachedQuery.trigger();
// This re-runs the query even if cached results are fresh

// Show when cache was last refreshed:
{{ cachedQuery.lastSuccessfulRunTime
   ? 'Last updated: ' + moment(cachedQuery.lastSuccessfulRunTime).fromNow()
   : 'Loading...' }}
```

**Expected result:** Reference data queries return instantly from cache without database round-trips on repeated loads.

### 7. Use 'Run when inputs change' Strategically

Queries with 'Run when inputs change' re-execute every time any referenced component value changes. This is powerful but can cause excessive database queries when multiple filters change rapidly (e.g. a user typing in a search box triggers a query on every keystroke).

Mitigate this by: (1) adding a Debounce setting on text input components (Inspector → Interaction → Debounce ms), and (2) using a 'Search' button instead of auto-triggering for expensive queries.

```
// Text Input Inspector → Interaction settings:
// Debounce: 500 (ms)
// This delays the onChange event by 500ms, so typing 'john'
// fires only one query instead of four.

// For very expensive queries, disable 'Run when inputs change'
// and use a Search button instead:
// Button event handler → Run query: getResults

// JS Query to trigger all relevant queries together:
await getUsersPaginated.trigger();
await getUsersCount.trigger();
await table1.setCurrentPage(1); // Reset to page 1 on new search
```

**Expected result:** Database queries fire at most once per search interaction rather than on every keystroke.

### 8. Lazy Load Secondary Data (Detail Panel Pattern)

Don't load all data upfront. For a master-detail layout, load only summary data for the table list view. Load the full detail record only when a user selects a row. This pattern dramatically reduces initial page load time.

Set the detail query to NOT run on page load. Instead, trigger it from the table's 'Row selected' event handler.

```
// Detail query: getUserDetail
// Do NOT enable 'Run on page load'
SELECT
  u.*,
  -- Include expensive joined data only for the selected user
  json_agg(o.*) AS orders,
  json_agg(a.*) AS audit_log
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN audit_log a ON a.entity_id = u.id
WHERE u.id = {{ table1.selectedRow.id }}
GROUP BY u.id;

// Table1 event handler:
// Event: Row selected
// Action: Run query → getUserDetail

// This loads full detail data only when a row is clicked,
// not for all 1000 rows in the list view.
```

**Expected result:** The list table loads instantly; detail data loads only when a row is selected.

## Complete code example

File: `SQL Query: getOrdersOptimized`

```javascript
-- Fully optimized paginated query for a large orders table
-- Assumes indexes on: (status, created_at), (order_number gin_trgm)

-- Main list query: getOrdersPaginated
-- Enable: Run when inputs change
SELECT
  o.id,
  o.order_number,
  o.status,
  o.total_amount,
  o.created_at,
  u.name AS customer_name,  -- join only needed fields
  u.email AS customer_email
FROM orders o
INNER JOIN users u ON u.id = o.user_id
WHERE
  (
    {{ statusFilter.value || '' }} = ''
    OR o.status = {{ statusFilter.value }}
  )
  AND
  (
    {{ searchInput.value || '' }} = ''
    OR o.order_number ILIKE {{ '%' + (searchInput.value || '') + '%' }}
    OR u.name ILIKE {{ '%' + (searchInput.value || '') + '%' }}
  )
  AND
  (
    {{ !dateRangeFilter.value.start }}
    OR o.created_at >= {{ dateRangeFilter.value.start }}::date
  )
  AND
  (
    {{ !dateRangeFilter.value.end }}
    OR o.created_at < {{ dateRangeFilter.value.end }}::date + INTERVAL '1 day'
  )
ORDER BY o.created_at DESC
LIMIT  {{ table1.pageSize || 50 }}
OFFSET {{ ((table1.page || 1) - 1) * (table1.pageSize || 50) }};

-- Count query: getOrdersCount
-- Same WHERE conditions, no LIMIT
SELECT COUNT(*) AS total
FROM orders o
INNER JOIN users u ON u.id = o.user_id
WHERE
  ({{ statusFilter.value || '' }} = '' OR o.status = {{ statusFilter.value }})
  AND ({{ searchInput.value || '' }} = ''
       OR o.order_number ILIKE {{ '%' + (searchInput.value || '') + '%' }})
  AND ({{ !dateRangeFilter.value.start }}
       OR o.created_at >= {{ dateRangeFilter.value.start }}::date)
  AND ({{ !dateRangeFilter.value.end }}
       OR o.created_at < {{ dateRangeFilter.value.end }}::date + INTERVAL '1 day');

-- Stats query (cached, TTL 300s): getOrderStats
-- Runs once and caches; refresh with a manual 'Refresh' button
SELECT
  COUNT(*) AS total_orders,
  SUM(total_amount) AS total_revenue,
  COUNT(*) FILTER (WHERE status = 'pending') AS pending_count,
  AVG(total_amount) AS avg_order_value
FROM orders
WHERE created_at >= DATE_TRUNC('month', NOW());
```

## Common mistakes

- **Loading 100,000 rows into a Retool transformer to compute a sum** — undefined Fix: Use a dedicated SQL aggregate query: SELECT SUM(amount) FROM orders. Let the database compute aggregations — it is orders of magnitude faster than JavaScript reduce() on large arrays.
- **Using SELECT * on a large table that has JSON/text columns with megabytes of data** — undefined Fix: Always specify exactly which columns you need. A row with a large JSON column can be 10KB+ — loading 1000 such rows is 10MB just for one query.
- **Text search input triggering a full-table LIKE scan on every keystroke** — undefined Fix: Add a 500ms debounce in the Text Input's Interaction settings. Also add a trigram GIN index (pg_trgm extension) for PostgreSQL ILIKE searches to use indexes instead of sequential scans.
- **Loading all data for a master-detail view upfront for all rows** — undefined Fix: Only load summary columns in the list table. Trigger a separate detail query when a row is selected using the table's 'Row selected' event handler. This is the lazy loading pattern.

## Best practices

- Never use SELECT * on large tables — always list only the columns your UI needs to minimize response payload size.
- Use server-side pagination (LIMIT/OFFSET with table1.page) for any table with more than 5,000 rows.
- Add composite database indexes on columns used in WHERE filters and ORDER BY clauses — this is the highest-impact optimization for slow paginated queries.
- Compute aggregations (SUM, COUNT, AVG) in SQL with a dedicated aggregate query rather than loading all rows into a transformer.
- Add a 300-500ms debounce on text search inputs to prevent a database query on every keystroke.
- Use lazy loading for detail panel data — trigger the detail query from a row-select event handler rather than on page load.
- Cache reference data queries (lookup tables, configuration) with a TTL of 5-60 minutes to avoid redundant database round-trips.

## Frequently asked questions

### How many rows can a Retool table handle before it becomes slow?

Client-side rendered tables start showing noticeable lag at around 5,000-10,000 rows. At 50,000+ rows, the browser can become unresponsive. Switch to server-side pagination at 5,000 rows or whenever initial page load takes more than 2 seconds. With proper server-side pagination, your database can have billions of rows with no impact on Retool performance.

### Should I compute aggregations in a Retool transformer or in SQL?

Always in SQL for large datasets. A SQL SUM(amount) query on 1 million rows takes milliseconds using database indexes. The equivalent transformer (loading 1M rows into JS and using .reduce()) would take seconds and consume significant browser memory. Use transformers only for aggregations over small datasets that are already loaded (under 1,000 rows).

### How do I add a trigram index in PostgreSQL for fast ILIKE search?

Run: CREATE EXTENSION IF NOT EXISTS pg_trgm; then CREATE INDEX CONCURRENTLY idx_tablename_column_trgm ON tablename USING gin(column_name gin_trgm_ops); This enables PostgreSQL to use an index for ILIKE '%search%' queries, which otherwise require a sequential table scan.

### Does Retool cache query results between page loads?

Retool does not persist cache across browser sessions by default. The 'Cache results' setting caches within the current session — subsequent runs within the TTL window return the cached result without a database round-trip. When the user refreshes the browser, the cache is cleared and queries run fresh.

---

Source: https://www.rapidevelopers.com/retool-tutorial/how-to-handle-large-datasets-in-retool
© RapidDev — https://www.rapidevelopers.com/retool-tutorial/how-to-handle-large-datasets-in-retool