Automatic Shallow Caching

Automatic shallow caching lets Readyset create a shallow cache for every previously-unseen SELECT it sees, with no CREATE CACHE statement and no /*rs+ ... */ hint required. The first time a query reaches Readyset it is proxied to the upstream database and a cache is created in the background; subsequent executions are served from that cache.

This is the lowest-friction way to onboard an existing application: point your client at Readyset, run your normal traffic, and caches accumulate as your queries arrive.

Enabling auto-caching

Pass --auto-cache when starting Readyset:

readyset --upstream-db-url=... --auto-cache ...

--auto-cache is shorthand for --cache-mode=shallow plus --query-caching=inrequestpath. It is mutually exclusive with both of those flags. Use it unless you specifically need a non-shallow cache mode or a different migration style.

How it works

1. First execution — Readyset receives a SELECT it has not seen before. It checks the query against the eligibility filter. If the query passes, Readyset proxies it to the upstream database, returns the result, and creates a shallow cache for the query.
2. Subsequent executions — Matching queries are served from the shallow cache, subject to the cache's TTL and refresh settings. See Shallow Caching for cache semantics.
3. Ineligible queries — Queries that fail the eligibility filter are proxied to upstream every time. No cache is created and no automatic retry is attempted.

The default cache parameters (TTL, coalescing, refresh) come from --default-ttl-ms and --default-coalesce-ms. To override them per query, use a /*rs+ CREATE SHALLOW CACHE ... */ hint instead. Explicit hints take precedence over the auto-cache trigger and bypass the eligibility filter.

Transaction handling

Auto-created caches default to the UNTIL WRITE transaction policy. Inside a transaction, the cache is consulted as long as the transaction has not yet observed a write. After the first INSERT, UPDATE, or DELETE, subsequent reads in that transaction are proxied upstream. The cache becomes available again at the next transaction boundary.

This default makes auto-caching compatible with 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). Without UNTIL WRITE, those clients would never see cache hits because every query would be inside a transaction.

To override the default for a specific query, use a /*rs+ CREATE SHALLOW CACHE ALWAYS */ hint (always serve from cache regardless of transaction state) or an explicit CREATE SHALLOW CACHE DDL statement with the keyword you want.

For read-your-writes outside of transactions, see the Read-your-writes concept page and --opportunistic-ryw-ms: when set, reads on a session bypass every cache for a configurable window after any write on that same session. The window is opportunistic, not a consistency guarantee, so review the concept page before enabling it.

Eligibility filter

Auto-caching is deliberately aggressive: every previously-unseen SELECT is a candidate. That is too aggressive for client and driver bootstrap traffic, which is structurally SELECT but should never be cached. The eligibility filter rejects three classes of query before a cache can be created. Ineligible queries are simply proxied to upstream; they do not produce errors.

The filter applies only to the implicit auto-cache trigger. Explicit CREATE CACHE statements and /*rs+ CREATE SHALLOW CACHE */ hints are user-initiated and bypass it.

System-schema references

A query is rejected if any table reference (including inside a CTE or subquery) targets a database system schema.

Examples that are filtered out:

SELECT * FROM information_schema.tables WHERE table_schema = 'app';
SELECT * FROM mysql.user;
SELECT * FROM pg_catalog.pg_class;
WITH t AS (SELECT * FROM pg_catalog.pg_class) SELECT * FROM t;

User and session variables

A query is rejected if it references a session variable, user variable, or any compound expression rooted in one. The most common case is MySQL clients probing server state on connect.

SELECT @@version;
SELECT @@global.max_connections;
SELECT @my_user_var;
SELECT id FROM users WHERE id = @target_id;

The check matches identifiers and compound identifiers whose first component starts with @, so both @var and @@scope.name forms are caught.

Non-deterministic functions

A query is rejected if it calls a function whose result depends on time, randomness, session state, server state, or external resources. Matching is case-insensitive and checks the last component of the function name, so both now() and pg_catalog.now() are caught.

Bare session keywords

A subset of the names above also reject the query when used as a bare identifier without parentheses. PostgreSQL accepts SELECT current_user (no ()) as a session-state reference, and the filter treats it the same way:

current_user, current_role, session_user, system_user, current_database, current_schema, current_catalog, current_date, current_time, current_timestamp, localtime, localtimestamp.

SELECT current_user;
SELECT current_timestamp;

Skipping the cache for a single query

To prevent a specific query from being auto-cached even though it would pass the eligibility filter, annotate it with the /*rs+ SKIP CACHE */ hint:

SELECT /*rs+ SKIP CACHE */ id, name FROM users WHERE id = ?;

SKIP CACHE routes the query directly to upstream and never creates a cache for it.

See also

• --auto-cache — the convenience flag that turns this mode on.
• Shallow Caching concept — what shallow caches are and how they refresh.
• Hint-based caches — per-query control with /*rs+ ... */ hints, including how to bypass auto-caching.