How to Use Caching Mechanisms in Retool

Open a query (e.g., a slow aggregation query named 'getDailySummary'). Click the 'Advanced' tab in the query editor. Find the 'Cache the results' toggle and enable it. A 'Cache TTL (seconds)' field appears — set the duration you want results to be cached. A TTL of 300 (5 minutes) means the query result is served from cache for up to 5 minutes after the first run. For slow aggregate queries (taking 2-5 seconds), caching dramatically reduces load — subsequent runs return in milliseconds.

1// Query: getDailySummary
2// Advanced tab settings:
3// Cache the results: ON
4// Cache TTL (seconds): 300
5
6-- The underlying SQL (expensive aggregation)
7SELECT
8  DATE_TRUNC('day', created_at) AS day,
9  COUNT(*) AS orders,
10  SUM(amount) AS revenue
11FROM orders
12WHERE created_at >= NOW() - INTERVAL '90 days'
13GROUP BY 1
14ORDER BY 1;

Retool builds the cache key from three components: (1) the resource name (e.g., 'production-postgres'), (2) the query SQL/configuration template, and (3) the resolved values of all {{ }} parameters. If any parameter changes, the cache key changes and Retool makes a fresh database request. This means a query filtered by {{ select1.value }} will have separate cache entries for each distinct value of select1.value. Changing the date filter creates a new cache entry — the old TTL-valid entry is not reused.

After a query runs, check {{ queryName.servedFromCache }} — it's a boolean that is true when the result came from cache, false when fresh from the database. Use this to show a staleness indicator to users. Add a Text component near your chart or table: '{{ getDailySummary.servedFromCache ? "Cached data" : "Live data" }}'. You can also display the cache age by tracking when the query last ran using a Temporary State variable that stores Date.now() on each non-cached run.

1// Text component value for cache status indicator
2{{ getDailySummary.servedFromCache
3  ? '⚠️ Showing cached data'
4  : '✓ Live data' }}
5
6// CSS class for visual distinction
7{{ getDailySummary.servedFromCache
8  ? 'text-amber-600'
9  : 'text-green-600' }}

After a write operation (INSERT, UPDATE, DELETE), the cached data is stale. Call await queryName.invalidateCache() to clear the cache entry and force the next run to fetch fresh data. Chain this in your save button's event handler: first run the update query, then invalidate the read query's cache, then trigger the read query again. This ensures users see updated data immediately after a write.

1// JS Query: saveAndRefresh
2// Triggered by 'Save Changes' button
3
4// 1. Run the update
5await updateRecord.trigger();
6
7// 2. Invalidate the stale cache
8await getDailySummary.invalidateCache();
9
10// 3. Re-run the read query to get fresh data
11await getDailySummary.trigger();
12
13utils.showNotification({
14  title: 'Saved',
15  description: 'Record updated. Dashboard refreshed.',
16  notificationType: 'success',
17});

Because the cache is shared across all users, never enable caching on queries that filter by the current user's data using {{ retoolContext.currentUser.email }} or similar. If user A runs the query and it caches, user B will see user A's data when they run the same query within the TTL. Only cache queries returning shared data visible to all users (global aggregates, reference data, system-wide summaries).