Caching in Retool

Caching in Retool is treated in several different ways, and allows users to better manage their data. By caching your query and request data, you can speed up the performance and load speeds of an application dramatically.

Queries are cached server-side inside of the Retool database connector. Whether a query is routed to your database or returns cached results is based on the inputs. If the exact same query is run using the same inputs within the Cache duration lifespan, the cached result is returned rather than querying the resource.

A common example of caching in Retool would be for SQL or database queries that return a very large amount of results. Querying your database for 5,000+ rows of data can be very resource-intensive for your application. Caching the results frees up the time and resources that the query would originally have used.

🚧

Retool's JavaScript editor runs in a sandbox. This prevents the window object from being accessible within a query or function.

Enable query caching

Check Cache the results of this query in the Advanced tab of a query to enable caching. You must specify the number of seconds the query should be cached for (e.g., 3600 for one hour).

You can programmatically determine if a cached query result was returned by using query1.servedFromCache. If it is true, a cached result was served. You can also do this to determine the cache's time-to-live using query1.cacheTtl.

Invalidate the cache

You can manually or programmatically invalidate the cache.

  • Manually: Click Invalidate cache in the Query editor.
  • Programmatically: Use the invalidateCache JavaScript method. You can also use an event handler to invalidate the cache by selecting the Control query action and Clear cache method.

The cache can also be invalidated if TTL (Time to Live) time limit runs out.

Redis caching for on-premise deployment

📘

This information only applies to on-premise deployments of Retool.

Without a Redis-connected resource, the cache is stored on the local file system of the Retool server. The cache can be invalidated if the Retool worker stops running as this may cause in-memory and file system caches to be wiped.

With a Redis-connected resource, the cache is additionally stored within Redis, and automatically set up once connected. This enables greater flexibility around caching behavior and also gives you visibility on how data is stored.

Refer to our documentation on environment variables to view Redis-specific variable information. Once set up and connected, Retool automatically leverages the Redis instance as a caching layer for queries with caching enabled.

Connect Retool to Redis on AWS ElastiCache

  1. Log in to the AWS console for your appropriate region.
  2. Create an Amazon ElastiCache cluster.
  3. Choose Redis as your cluster engine. Do not enable cluster mode. Cluster mode will handle spreading keys across multiple shards. By default, the Redis shard should be able to store 13GB of data which is substantial.

  1. Enter the name and description of the cluster. Leave the rest of the fields as-is.
  • Port: 6379 by default. A custom port can be specified as an additional layer of protection though for most use cases is not required.
  • Node type: Large by default. Automatically set to be able to store 13GB of data.
  • Multi-AZ: True by default. Multi-AZ is especially useful when we have strong high availability requirements for the use case (many users, critical workflow). Redis is usually highly available even without this feature as long as we replicate to at least 2 nodes.
  • Number of Replicas: 2 by default. This will replicate the data into 2 other nodes as well.

👍

In the event that the primary node for the cluster fails, Multi-AZ ensures that it follows protocol to assign a new primary immediately (taking the read replica with the fastest recorded latency times). For small or medium use cases, or for testing purposes, you can set this to false. You can change this retroactively by modifying the replication group.

  1. Advanced Redis Settings: Ensure we are under the same VPC for this Redis instance as the instance where Retool is deployed. Otherwise, the two will not be able to communicate. For Multi-AZs, we need to provide two subnets.

  1. Data Management: Enable automatic backups. This is optional but highly recommended. If you experience unexplained issues, you can retrieve the previous backup.

  2. Create Cluster.

Monitor the Redis instance

  1. Log in to the Retool instance (or create a new one in the same VPC as both Retool and Redis) and enter the following commands.
sudo wget http://download.redis.io/redis-stable.tar.gz

sudo tar xvzf redis-stable.tar.gz

cd redis-stable

sudo CC=clang make BUILD_TLS=yes # only if TLS is enabled
  1. Read the Redis instance by running either of the two commands below. The cluster endpoint should be the reader hostname. The port number should be the port number entered above (by default, it is 6379).
# If no password is setup, run:
redis-cli -h <cluster-endpoint> -c -p <port number> 

# Otherwise...
redis-cli -h <cluster-endpoint> --tls -a <your-password> -p <port number>
  1. Once in the Redis CLI, run some get and set commands to verify expected behavior. Running the INFO command displays stats for the Redis cluster.

Cache sharing between users

By default, the cache is shared across users in the same organization. If two users run a query with the same requests—even with different auth tokens—the cached results are still returned.

Administrators can disable Cache queries across all users in the Advanced section of Organization Settings to perform caching on a per-user basis instead.


Did this page help you?