Why Is My Retool Application Running Slowly?

1-- BEFORE: returns all rows (slow)
2SELECT * FROM orders ORDER BY created_at DESC;
3
4-- AFTER: server-side pagination (fast)
5SELECT * FROM orders
6ORDER BY created_at DESC
7LIMIT {{ table1.pageSize || 50 }}
8OFFSET {{ table1.paginationOffset || 0 }};
9
10-- Also need a count query for total pages:
11SELECT COUNT(*) as total FROM orders;
12
13-- In Table Inspector:
14-- Pagination: Enabled
15-- Page size: 50
16-- Server-side pagination: ON
17-- Total count query: {{ countOrders.data[0].total }}

1// Change trigger method:
2// In query settings → General → Trigger:
3// 'Automatic' → runs on load and on input changes
4// 'Manual' → only runs when explicitly triggered
5
6// For search queries: use 'Run when inputs change' on
7// a query that is bound to searchInput.value
8// This only re-runs when the search input changes,
9// not on every page load.
10
11// JS Query: initializePage (runs on load)
12// Instead of many automatic queries, run critical ones in parallel:
13await Promise.all([
14  getCriticalData.trigger(),
15  getCurrentUserInfo.trigger()
16]);
17// Non-critical queries run later on demand

1-- Diagnose slow query:
2EXPLAIN ANALYZE
3SELECT * FROM orders o
4JOIN customers c ON c.id = o.customer_id
5WHERE o.status = 'pending'
6AND o.created_at > NOW() - INTERVAL '30 days'
7ORDER BY o.created_at DESC;
8
9-- If EXPLAIN shows Sequential Scan on orders, add index:
10-- (Run this in your database, not in Retool)
11-- CREATE INDEX CONCURRENTLY idx_orders_status_created
12--   ON orders(status, created_at DESC);
13
14-- Add index for the JOIN:
15-- CREATE INDEX CONCURRENTLY idx_orders_customer_id
16--   ON orders(customer_id);

1// PROBLEM: Transformer runs on every state change
2// transformer1 does heavy computation
3// It re-runs whenever searchInput.value changes
4// This causes visible lag on every keystroke
5
6// SOLUTION: Move heavy work to a JS Query with manual trigger
7// JS Query: computeExpensiveReport (trigger: Manual)
8const data = getOrders.data || [];
9
10// Heavy computations
11const grouped = _.groupBy(data, 'customer_id');
12const enriched = Object.entries(grouped).map(([id, orders]) => ({
13  customerId: id,
14  orderCount: orders.length,
15  totalRevenue: _.sumBy(orders, 'amount'),
16  avgOrderValue: _.meanBy(orders, 'amount'),
17  lastOrder: _.maxBy(orders, 'created_at')?.created_at
18}));
19
20return _.orderBy(enriched, ['totalRevenue'], ['desc']);
21
22// Trigger this query only when needed:
23// - On page load (one time)
24// - After a data refresh
25// NOT on every UI interaction