We kept hearing the same thing from customers: "We already know which queries need caching. Why do we need to go through the DBA to set it up?"
Fair point. So we built Query Hints:
Caching a query in Readyset used to require someone — usually a DBA or platform engineer — to connect to Readyset and run a CREATE CACHE statement. That works fine if you have a small, stable set of queries and an ops team with bandwidth. In practice, though, the bottleneck was never technical. It was organizational.
The developer who just wrote a slow query knows it should be cached. But that developer can't do anything about it; they have to file a request, wait for someone on the ops side to review it, and then wait for the change to roll out. In organizations with strict change management, that cycle takes days.
Meanwhile, the cache configuration lives in a completely separate system from the application code. Queries change. Columns get renamed. WHERE clauses get refactored. And nobody notices the cache is broken until latency spikes in production.
Query Hints let developers embed caching directives directly in their SQL, using the optimizer hint comment syntax with the rs prefix. No DDL, no admin connection, no separate deployment.
There are two directives.
SELECT id, name, email
FROM users
WHERE id = ?
First time Readyset sees this, it creates a shallow cache for the query. Every execution after that — hint or no hint — gets served from cache. Done.
You can also pass eviction and refresh options inline:
SELECT
u.id, u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.region = ?
GROUP BY u.id, u.username
For a complete list of options, check out the Shallow Cache documentation page and our announcement blog.
There are times when you need the latest data, not what's in cache. After a write, during a financial transaction, or when you're debugging whether the cache is returning the right thing. The SKIP CACHE hint routes that specific execution directly to the upstream database, bypassing both shallow and deep caches:
SELECT account_balance, last_transaction_at
FROM accounts
WHERE account_id = ?
This only affects the execution that carries the hint. Everyone else hitting the same query still gets the cached result. The cache stays active.
A few places where this comes in handy:
- After a write, when you need to read back the state you just changed.
- In consistency-sensitive workflows — think balance checks before a transfer, or inventory before a purchase.
- When you want to compare cached vs. uncached latency during a rollout, or when you need to verify that cache and upstream agree during an incident.
The two hints are independent. Different parts of your application can use different hints for the same query shape:
SELECT balance
FROM accounts WHERE account_id = ?;
SELECT balance
FROM accounts WHERE account_id = ?;
The hint sits inside a /*+ ... */ comment block. Databases that don't recognize the rs prefix just treat it as a regular comment and move on — MySQL and PostgreSQL both handle this fine.
Before computing the query fingerprint, Readyset strips the hint. So these two are the same query as far as cache matching goes:
SELECT id, name FROM users WHERE id = ?
SELECT id, name FROM users WHERE id = ?
The implication: once a cache is created, the hint has done its job. You can leave it in, or take it out — the cache sticks around either way.
The sequence for CREATE SHALLOW CACHE is straightforward:
- Developer adds the hint in the application code.
- App goes through the normal deploy pipeline.
- On first execution, Readyset parses the hint, creates the cache, and serves the result from upstream (because the cache is empty).
- From then on, all matching queries get served from cache.
When SKIP CACHE bypasses a cache, Readyset increments a counter metric (readyset_query_log_total_skip_cache) tagged with the query ID, cache type, and skip reason.. You can also run EXPLAIN LAST STATEMENT right after a skipped query — it'll show Query_destination: upstream, confirming the query was routed directly to the database.
Useful for confirming routing during development.
We were deliberate about making hints safe to adopt incrementally:
- A malformed hint (typo, wrong keyword) gets logged as a warning and the query runs against upstream as usual. No error returned to the application.
- If the query shape can't be cached, the hint is ignored. Again, no error.
- If the cache already exists, the hint is a no-op.
- If you remove the hint later, the cache doesn't go away.
The point is that adding a hint should never make things worse. Worst case, it does nothing.
If you're running Readyset, this works out of the box — no configuration required.
If you are not running Readyset yet, get started with:
bash -c "$(curl -sSL https://launch.readyset.io)"
Pick your slowest read query, add /*rs+ CREATE SHALLOW CACHE */, and experiment low latency on your queries. That's it.
Query Hints are GA in MySQL and soon will be available on PostgreSQL.
Readyset Team
Vinicius Grippa