Caching Queries

Once your application is sending SQL queries through Readyset Cache, you can see which queries are cacheable. Deep caches can be created only once all tables have finished the initial snapshotting process.

Before attempting to create a deep cache, check Readyset Cache's overall snapshotting progress by connecting a SQL shell to Readyset Cache and running the following custom SQL command:

SHOW READYSET STATUS;

Look for the row labeled Snapshot Status -- it should be either In Progress or Completed.

name            |   value
----------------+----------
Snapshot Status | Completed
(1 row)

Once you see the status is Completed Readyset Cache is ready to cache queries.

If you are unsure what queries to cache, you can run diagnostics with both Postgres and MySQL to ascertain costly queries.

1. Creating a Cache

It's important to note that Readyset Cache sits between your application and your database. SQL traffic must pass through Readyset Cache so that it can cache desired queries. You can check to see that an application is successfully connected to Readyset Cache by using the Readyset Cache shell and running the commands show proxied queries (you should see queries) or show connections (you should see two connections - one being you in the Readyset Cache shell and the other being your application).

Once you've identified queries that can benefit from caching with Readyset Cache, use Readyset Cache's custom SQL commands to check if the queries are supported and then to cache supported queries in Readyset Cache.

2. Checking query support

To view all queries that Readyset Cache has proxied to the upstream database and check if they can be cached in Readyset Cache, connect to Readyset Cache via the shell and run:

SHOW PROXIED QUERIES;

This command returns a virtual table with four columns:

• QueryID: A unique identifier for the query.
• Proxied Query: The text of the query being proxied.
• Readyset Cache supported: Whether or not Readyset Cache can cache the query.
  • If the value is pending, check again until you see yes or no. If the value remains pending for more than 15 seconds, the query is unsupported.
  • If the value is yes, Readyset Cache can cache the query.
  • If the value is no, Readyset Cache cannot cache the query.
• Count: The number of times Readyset Cache has seen this query.

3. Cache queries

Readyset Cache supports two kinds of caches:

• Deep caches use streaming dataflow to incrementally maintain query results from your database's replication stream, so cached results stay up to date automatically. They require the initial snapshot to be complete. See Deep Caching.
• Shallow caches are TTL-based: they store query results in memory and serve them until they expire, without building a materialized view. They support a broader range of queries and do not depend on snapshotting. See Shallow Caching.

Deep caches

To create a deep cache, use:

CREATE DEEP CACHE [<name>] [WITH (<option>[, <option>...])] FROM <query>;

• The DEEP modifier builds a materialized view in the dataflow graph. With no modifier, the cache type follows the server's --cache-mode setting, which defaults to shallow, so specify DEEP explicitly to create a deep cache regardless of that setting.
• <name> is optional. If a cache is not named, Readyset Cache automatically assigns an identifier.
• <query> is the full text of the query or the unique identifier (i.e. query_id) assigned to the query by Readyset Cache, as seen in output of SHOW PROXIED QUERIES.
• WITH (...) carries an optional comma-separated list of options. The most common ones are ALWAYS and UNTIL WRITE, which control how the cached query is served when the connection is inside a transaction (for example, an ORM that wraps every statement in BEGIN/COMMIT). With no option, the cached query is proxied upstream for the entire transaction. WITH (UNTIL WRITE) serves the cached query from Readyset Cache until the transaction observes a write, after which it is proxied upstream for the rest of that transaction. WITH (ALWAYS) serves the cached query from Readyset Cache regardless of transaction state. See the Command Reference for the full option list.

For example, to cache from the query SELECT "words".* FROM "words" WHERE ("words"."id" = $1) ORDER BY "words"."id" ASC:

CREATE DEEP CACHE FROM SELECT "words".* FROM "words" WHERE ("words"."id" = $1) ORDER BY "words"."id" ASC;

You can also grab the query ID from show proxied queries and use that identifier to cache a query. For instance, running show proxied queries shows this table:

      query_id      |                     query                     | readyset_supported | count
--------------------+-----------------------------------------------+--------------------+-------
 q_bb6bd342f17bd8cd | SELECT                                       +| yes                |   219
                    |   *                                          +|                    |
                    | FROM                                         +|                    |
                    |   "employees"."employee"                     +|                    |
                    | WHERE                                        +|                    |
                    |   ("last_name" = $1)                          |                    |

You can create a cache for the select query above with its ID (q_bb6bd342f17bd8cd) like so:

CREATE DEEP CACHE FROM q_bb6bd342f17bd8cd;

This makes it easier to create caches from a list of queries found in show proxied queries.

Shallow caches

To create a shallow cache:

CREATE SHALLOW CACHE [<name>] [WITH (POLICY TTL <n> {SECONDS | MILLISECONDS | MS}[, <option>...])] FROM <query>;

For full syntax and options, see Shallow Caches in the command reference.

4. View cached queries

To show all queries that have been cached, use:

SHOW CACHES;

To show a specific cached query, use:

SHOW CACHES where query_id = <query ID>;

This command returns a virtual table with 2 columns:

• Name: The name assigned to the query by the user, or the ID assigned to the query by Readyset Cache.
• Query Text: The SQL source of the query. This is the canonical structure of the query, not the original SQL passed to Readyset Cache.

5. Remove cached queries

To remove a cache from Readyset Cache, use:

DROP CACHE <id>;

• <id> is either the name assigned to the query by the user or the ID assigned to the query by Readyset Cache, as seen in the output of SHOW CACHES.

After removing a query from Readyset Cache, any instances of this query will be proxied to the upstream database.