Readyset Docs

This section provides a reference for the built-in SQL commands for Readyset.

Cache Management

`CREATE CACHE`

Create a new cache entry for a specific SELECT statement or <query id>.

Command :

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

The optional DEEP or SHALLOW modifier selects the cache type:

DEEP builds a materialized view in the dataflow graph.
SHALLOW stores results in memory and serves them until a TTL expires, without building a materialized view. See Shallow Caching for the conceptual overview and examples for sample DDL.
With no modifier, the cache type follows the server's --cache-mode setting, which defaults to shallow.

The WITH (...) clause carries a comma-separated list of options. Available options:

ALWAYS — serve from cache inside transactions.
UNTIL WRITE — serve from cache for read-only-so-far transactions.
POLICY TTL, REFRESH, REFRESH EVERY — TTL and refresh behavior for shallow caches.
COALESCE — coalesce concurrent upstream fetches for shallow caches.
ADAPTIVE — adjust per-entry refresh intervals to observed data change rates.

ALWAYS and UNTIL WRITE are mutually exclusive. They control how the cached query is served when the connection is inside a transaction (for example, when a client driver wraps every statement in BEGIN/COMMIT). With no keyword, the cached query is proxied upstream for the entire transaction.

`UNTIL WRITE`

Serves the cached query from Readyset for read-only-so-far transactions, and proxies upstream once the transaction observes a write. This is the right choice for client drivers that wrap every statement in an implicit transaction (for example, Python drivers running with autocommit=False, JDBC defaults, or ORMs that do not expose autocommit). Reads stay served from Readyset while the transaction is read-only; writes and reads-after-writes are routed upstream so they observe the write set.

UNTIL WRITE is also the default policy for caches created automatically by --auto-cache and by hint-based auto-creation when no policy keyword is supplied.

`ALWAYS`

When specified, the cached query is served from Readyset even inside a transaction. Use this for queries that should never be proxied upstream once cached, including the bucket function which requires ALWAYS.

For the broader picture, including read-your-writes outside of transactions, see Read-your-writes.

`POLICY TTL`

Shallow caches only. The lifetime of each cache entry. Once an entry has not been accessed for this duration, it is considered stale and will be refreshed on the next access.

If omitted, Readyset uses the server default configured by --default-ttl-ms.

`REFRESH`

Shallow caches only. When a request hits a cache entry that is at least n old (in the specified unit), Readyset preemptively issues a background refresh against upstream. The current request receives the existing cached value immediately; the updated result is available for subsequent requests.

If REFRESH is omitted entirely (no REFRESH or REFRESH EVERY clause), Readyset applies an implicit refresh interval equal to half the TTL.

Must be less than the TTL.

`REFRESH EVERY`

Shallow caches only. Readyset proactively re-executes the query against upstream on a fixed schedule, regardless of whether any client has requested it. Automatic refreshes continue as long as the cache entry has not been evicted due to TTL expiration.

If the query takes longer to execute than the refresh interval, multiple refreshes for the same key can be in-flight concurrently. This produces consistently fresh results at the cost of higher database query volume. See database load considerations for guidance on tuning refresh settings.

Must be less than the TTL.

Units can be mixed freely within a single statement (e.g., TTL in seconds and refresh in milliseconds).

`COALESCE`

Shallow caches only. The window during which concurrent requests for the same query and parameter values are coalesced on a cache miss. Only the first request is sent to upstream; the rest wait for its result. This prevents a thundering herd from hitting upstream simultaneously.

If omitted, Readyset uses the server default configured by --default-coalesce-ms. Set to 0 to disable coalescing for this cache.

`ADAPTIVE`

Shallow caches only. Adjusts each cache entry's refresh interval to the rate at which its data is observed to change: entries whose results change between refreshes are refreshed more often, and entries whose results stay the same return to the configured interval. The extra upstream load from shortened intervals is capped by --shallow-adaptive-max-extra-load-percent. See Adaptive refresh for details.

Caches created by automatic shallow caching have this option enabled implicitly.

`<name>`

An optional name for the cache. If omitted, Readyset assigns an identifier automatically. The name can be used with DROP CACHE and SHOW CACHES.

Result: On success, returns a single row with the following columns:

Column	Description
`query_id`	Unique query identifier (e.g., `q_5986a67d37048328`)
`name`	Name of the cache (user-specified or auto-generated from the query hash)
`query`	The cached SQL query
`cache_type`	Type of cache created: `deep` or `shallow`

For examples covering each combination of options, see the examples below.

`DROP CACHE`

Drop a cache entry based on a <query id> from SHOW CACHES.

Command : DROP CACHE <query id>

`DROP ALL CACHES`

Drop all cache entries. The optional DEEP or SHALLOW modifier restricts the operation to caches of that type; with no modifier, all caches are dropped.

Command : DROP ALL [DEEP | SHALLOW] CACHES

`DROP ALL PROXIED QUERIES`

Clear the list of proxied queries shown by SHOW PROXIED QUERIES.

Command : DROP ALL PROXIED QUERIES

Shallow Caches

Shallow caches store result sets in memory and serve them until a TTL expires, without building a materialized view. They are created with CREATE CACHE using the SHALLOW modifier and the POLICY, REFRESH, and COALESCE options documented above. For the conceptual overview, see Shallow Caching.

Examples

Cache with a 30-second TTL and default refresh behavior (implicit on-access refresh at half the TTL):

CREATE SHALLOW CACHE WITH (POLICY TTL 30 SECONDS) FROM
  SELECT * FROM products WHERE category_id = $1;

Cache with a 60-second TTL and a 10-second on-access refresh:

CREATE SHALLOW CACHE WITH (POLICY TTL 60 SECONDS REFRESH 10 SECONDS) FROM
  SELECT * FROM sessions WHERE user_id = $1;

Cache with a 60-second TTL and a proactive 15-second scheduled refresh:

CREATE SHALLOW CACHE WITH (POLICY TTL 60 SECONDS REFRESH EVERY 15 SECONDS) FROM
  SELECT count(*) FROM orders WHERE status = $1;

Cache with coalescing disabled and a name:

CREATE SHALLOW CACHE my_cache WITH (POLICY TTL 30 SECONDS, COALESCE 0 SECONDS) FROM
  SELECT * FROM inventory WHERE sku = $1;

Cache with sub-second TTL and mixed units:

CREATE SHALLOW CACHE WITH (POLICY TTL 5 SECONDS REFRESH 500 MILLISECONDS) FROM
  SELECT * FROM leaderboard WHERE game_id = $1;

Cache with a scheduled refresh whose per-entry intervals adapt to observed data change rates:

CREATE SHALLOW CACHE WITH (POLICY TTL 60 SECONDS REFRESH EVERY 15 SECONDS, ADAPTIVE) FROM
  SELECT count(*) FROM orders WHERE status = $1;

Cache that serves read-only-so-far transactions but proxies upstream after the first write in a transaction:

CREATE SHALLOW CACHE WITH (UNTIL WRITE) FROM
  SELECT * FROM users WHERE id = $1;

Metrics

The following Prometheus metrics are available for shallow caches. Each metric includes a query_id label identifying the cache.

Metric	Type	Description
`readyset_shallow_shallow_result_hit{query_id=...}`	Counter	Number of requests served from the cache
`readyset_shallow_shallow_result_miss{query_id=...}`	Counter	Number of requests that missed the cache and went to upstream
`readyset_shallow_shallow_result_refresh{query_id=...}`	Counter	Number of background refreshes dispatched for a cache entry
`readyset_shallow_shallow_refresh_changed{query_id=...}`	Counter	Number of refreshes of an adaptive cache that returned data different from the cached entry
`readyset_shallow_shallow_refresh_unchanged{query_id=...}`	Counter	Number of refreshes of an adaptive cache that returned the same data as the cached entry
`readyset_shallow_shallow_refresh_queue_exceeded`	Counter	Number of refresh requests dropped because all refresh workers were busy; see `--shallow-refresh-workers`
`readyset_shallow_shallow_refresh_query_time_us{query_id=...}`	Histogram	Time in microseconds spent executing the upstream query during a refresh; only recorded for successful queries
`readyset_shallow_shallow_evict_memory`	Counter	Number of cache entries evicted due to memory pressure

`SHOW` Commands

`SHOW CACHES`

Display a list of cached queries.

Command : SHOW [DEEP | SHALLOW] CACHES [WHERE query_id = <query id>]

`SHOW PROXIED QUERIES`

Display a list of proxied queries to upstream alongside with a status indicating if Readyset can cache the query. Optionally, users can filter only SUPPORTED queries.

Command :

SHOW PROXIED [SUPPORTED] [DEEP | SHALLOW] QUERIES
  [WHERE query_id = <query id>]
  [LIMIT <n>]

The DEEP or SHALLOW modifier restricts the listing to queries Readyset would route to that cache type. WHERE query_id = <query id> returns just that one entry; LIMIT <n> caps the number of rows returned.

`SHOW CONNECTIONS`

Display a list of client connections.

Command : SHOW CONNECTIONS

`SHOW READYSET STATUS`

Display current Readyset server status.

Command : SHOW READYSET STATUS

`SHOW READYSET STATUS ADAPTER`

Display current Readyset adapter status.

Command : SHOW READYSET STATUS ADAPTER

`SHOW READYSET MIGRATION STATUS`

Display the migration status for a specific migration id.

Command : SHOW READYSET MIGRATION STATUS <migration id>

`SHOW READYSET VERSION`

Display information about the current Readyset version.

Command : SHOW READYSET VERSION

`SHOW READYSET TABLES`

Display the list of replicated tables and their statuses. Optionally, the ALL keyword extends the listing to include non-replicated tables from the upstream.

Command : SHOW READYSET [ALL] TABLES

`SHOW READYSET RSA PUBLIC KEY`

Display the RSA public key used by Readyset for caching_sha2_password full-authentication exchanges. Clients that connect over a non-TLS channel need this key to encrypt their password before sending it to the server.

The result is a single row with one column (Caching_sha2_password_rsa_public_key) containing the PEM-encoded public key.

Command : SHOW READYSET RSA PUBLIC KEY

See MySQL Authentication for more information on caching_sha2_password.

`SHOW REPLAY PATHS`

List active replay paths in the dataflow graph.

Command : SHOW REPLAY PATHS

`EXPLAIN` Commands

`EXPLAIN CACHES`

List all CREATE CACHE statements that have been executed, for the purpose of exporting them.

Command : EXPLAIN CACHES

`EXPLAIN CREATE CACHE`

Display information about how Readyset will interpret a specific query, including whether the query is supported or not.

Command : EXPLAIN CREATE [DEEP | SHALLOW] CACHE FROM <query>

`EXPLAIN DOMAINS`

List domains and the worker they're running on.

Command : EXPLAIN DOMAINS

`EXPLAIN GRAPHVIZ`

Print a graphviz representation of the current query graph to stdout. SIMPLIFIED produces a less detailed graph; FOR CACHE <query id> scopes the output to the subgraph supporting one cache.

Command : EXPLAIN [SIMPLIFIED] GRAPHVIZ [FOR CACHE <query id>]

`EXPLAIN MATERIALIZATIONS`

List and give information about all materializations in the graph.

Command : EXPLAIN MATERIALIZATIONS [FOR CACHE <query id>]

`EXPLAIN LAST STATEMENT`

Provides metadata about the last statement that was executed.

Command : EXPLAIN LAST STATEMENT

`ALTER READYSET` Commands

`ALTER READYSET STOP REPLICATION`

Stop the replication stream from the upstream database. While stopped, Readyset continues to serve cached data but does not receive new changes. Use this as the first step in a failover procedure.

Returns an error if replication is already stopped.

Command : ALTER READYSET STOP REPLICATION

`ALTER READYSET START REPLICATION`

Resume the replication stream after it has been stopped with ALTER READYSET STOP REPLICATION. Readyset reconnects to the upstream database (or the new upstream set via ALTER READYSET CHANGE CDC) and begins replicating from the stored replication position.

Returns an error if replication is already running.

Command : ALTER READYSET START REPLICATION

`ALTER READYSET SET REPLICATION POSITION`

Set the replication position for all tables. Replication must be stopped before running this command. The position format depends on the database:

MySQL binlog file and position: 'binlog.000003:154'
MySQL GTID: 'uuid:1-10' or a comma-separated GTID set
PostgreSQL LSN: '0/16B3748'

Switching between MySQL position types (binlog file/position and GTID) is allowed. Switching between MySQL and PostgreSQL is not.

For PostgreSQL failover, this command is not needed. See Failover for details.

Command : ALTER READYSET SET REPLICATION POSITION '<position>'

`ALTER READYSET CHANGE CDC`

Change the CDC (Change Data Capture) connection URL that Readyset uses for replication. Replication must be stopped before running this command. Use this during failover to point Readyset at a new upstream source.

The <url> must be a valid database connection URL whose protocol matches the current dialect (e.g. mysql://<user>:<password>@host/db or postgresql://<user>:<password>@host/db).

Command : ALTER READYSET CHANGE CDC TO '<url>'

`ALTER READYSET CHANGE UPSTREAM`

Dynamically switch the upstream database that Readyset connects to, without restarting. All client connections are closed when the upstream changes; clients must reconnect, after which they will be routed to the new upstream.

The <url> must be a valid database connection URL whose protocol matches the configured dialect (e.g. mysql://<user>:<password>@host/db or postgresql://<user>:<password>@host/db).

This command is only available when replication is disabled, i.e. in shallow caching only mode.

Command : ALTER READYSET CHANGE UPSTREAM TO '<url>'

`ALTER READYSET ADD USER`

Add a user to the set of users allowed to authenticate with Readyset. Requires authentication to be enabled. The change is persisted, so it survives restarts and overrides the --allowed-users bootstrap, and the new user can connect immediately.

The list of allowed users can be inspected by querying the readyset.users virtual relation, for example SELECT user FROM readyset.users (on PostgreSQL, quote the column: SELECT "user" FROM readyset.users). Only usernames are returned; passwords are never exposed.

Command : ALTER READYSET ADD USER '<user>' PASSWORD '<password>'

`ALTER READYSET MODIFY USER`

Rotate the password of an existing allowed user. Requires authentication to be enabled. The user derived from --upstream-db-url cannot be modified, since that identity backs Readyset's upstream connectivity.

Command : ALTER READYSET MODIFY USER '<user>' PASSWORD '<password>'

`ALTER READYSET DROP USER`

Remove a user from the set of users allowed to authenticate with Readyset. Requires authentication to be enabled. The user derived from --upstream-db-url cannot be dropped.

Command : ALTER READYSET DROP USER '<user>'

`ALTER READYSET RESNAPSHOT TABLE`

Resnapshot one specific table.

Command : ALTER READYSET RESNAPSHOT TABLE [db.table]

`ALTER READYSET ADD TABLES`

Add new tables to Readyset that have either been excluded from --replication-tables-ignore or omitted from --replication-tables. The command accepts a single table or a list of table comma-delimited.

The same change should be adjusted in the .cnf in order to make it persistent across restarts.

Command : ALTER READYSET ADD TABLES [db.table, db.table2]

`ALTER READYSET ENTER MAINTENANCE MODE`

Enter maintenance mode is meant to inform external tools like ProxySQL that Readyset is in maintenance mode and it should stop sending queries to Readyset.

Command : ALTER READYSET ENTER MAINTENANCE MODE

`ALTER READYSET EXIT MAINTENANCE MODE`

Exit maintenance mode is meant to inform external tools like ProxySQL that Readyset is no longer in maintenance mode and it should resume sending queries to Readyset.

Command : ALTER READYSET EXIT MAINTENANCE MODE

`ALTER READYSET SET LOG LEVEL`

Dynamically change the log level of a running Readyset process without restarting it. The new level applies immediately to the adapter or server connected to and is not persisted across restarts. To make the change permanent, also update --log-level (or the LOG_LEVEL environment variable).

The directive string accepts the same syntax as --log-level: a single severity (ERROR, WARN, INFO, DEBUG, TRACE), or a comma-separated list of per-module directives such as info,readyset_server=debug. See the tracing library docs for the full directive syntax.

ALTER READYSET SET LOG LEVEL only affects the process serving the connection. In a deployment with multiple adapters and a separate server, run the command against each process whose log level you want to change.

Command : ALTER READYSET SET LOG LEVEL '<directives>'

Examples:

ALTER READYSET SET LOG LEVEL 'debug';
ALTER READYSET SET LOG LEVEL 'info,readyset_server=debug,replicators=trace';

MCP Commands

Used to administer Bearer tokens for the embedded MCP HTTP endpoint. See the MCP Server page for the full user guide.

`CREATE MCP TOKEN`

Create a new MCP Bearer token and return its raw value. The value is shown exactly once. Only the SHA-256 hash is stored.

Command : CREATE MCP TOKEN '<name>' [WITH SCOPE {read_only | cache_admin | full}] [EXPIRES '<rfc3339-datetime>']

Examples:

CREATE MCP TOKEN 'claude-desktop';
CREATE MCP TOKEN 'claude-desktop' WITH SCOPE cache_admin;
CREATE MCP TOKEN 'claude-desktop' WITH SCOPE full EXPIRES '2027-01-01T00:00:00Z';

`SHOW MCP TOKENS`

List all stored MCP tokens with their name, scope, creation timestamp, and expiration. Raw values and hashes are not returned.

Command : SHOW MCP TOKENS

`ALTER MCP TOKEN`

Change the expiration on an existing token without rotating its value. Use this to extend a soon-to-expire token or remove the expiration entirely.

Command : ALTER MCP TOKEN '<name>' SET {EXPIRES '<rfc3339-datetime>' | NEVER EXPIRES}

Examples:

ALTER MCP TOKEN 'claude-desktop' SET EXPIRES '2028-01-01T00:00:00Z';
ALTER MCP TOKEN 'claude-desktop' SET NEVER EXPIRES;

`DROP MCP TOKEN`

Immediately revoke an MCP token. Requests using the dropped token receive HTTP 401 on the next call.

Command : DROP MCP TOKEN '<name>'

Command Reference

On this page