Choose what to cache
Requests are slow, and a cache could help. But a cache can hold different things: the raw file blocks a query reads, the table's metadata, the query's final result, a precomputed view, or even the fact that a key does not exist. The further an object is from the stored data, the more work a hit saves, and the more writes can make it wrong. Which object should you cache?
You will end with the object to cache, how its key is built, and how it is kept correct.
Before you choose
List what each entry depends on
Write down everything a cached entry is computed from: tables, rows, files, settings. If you cannot list it, you cannot invalidate it, and an expiry time (TTL) is your only tool. Price that before designing anything else.
Put input versions in the key where you can
If the inputs carry versions (snapshot IDs of a table, file hashes), build them into the cache key. A write then invalidates nothing: the old key simply stops being asked for, so a stale hit becomes impossible. The cost is unreachable entries, which eviction cleans up.
Check that the cache can pay at all
A cache pays only if each cached version is read more times than it costs to produce: reads per version > cost to compute ÷ saving per hit. Reads per version is read rate ÷ rate of writes that invalidate it. A dashboard costing 8 s to compute and saving 2 s per hit needs more than 4 reads per version: fine if its table changes every 5 minutes, hopeless if it changes every 10 seconds.
Why
What makes something cacheable is the ratio of reads to writes, not the number of reads. A million reads per second against data written a million times per second cannot be cached usefully.
Decide
Are the slow requests lookups for keys that do not exist?
For example: a crawler requesting article IDs that were never published, or a join probing for missing customers.
Why
Proving that something is absent is the most expensive read a system makes: every level, index or shard must be checked. Remembering the absence costs almost nothing.
What is the expensive part of a slow request?
Reading dataCache file blocks
CatalogCache metadata (the catalog round trip: schema, file lists, statistics)
ComputingGo to 3
Why
Cache the lowest object that still skips the expensive part: lower objects depend on less, so fewer writes make them wrong. If a result cache is being built because the catalog lookup is slow, the metadata is the thing to cache.
Does the same query repeat, byte for byte, between writes to its inputs?
A dashboard refreshing every minute over a table loaded hourly: yes. The same dashboard over a table ingesting every 10 seconds: no.
Why
A write to any input makes every cached result over that table unreachable at once, so a result cache can never hit more often than queries repeat between writes. No key design escapes that bound.
Can you declare the computation in advance, as a view the engine keeps up to date?
Why
A materialized view is a cache whose contents you declared. Because the engine knows how it was computed, it can often repair the view from the changes alone instead of recomputing it.
Do different queries ask for overlapping slices, so one cached answer could serve a narrower question?
Why
A fragment cache hits when a stored answer contains the one asked for, not only when the queries are identical. That recovers hits that exact matching loses, at the cost of deciding containment on every lookup.
Where you can land
File blocks
Use it when reading the data is the expensive part, or nothing higher pays.
Cache the bytes of files, keyed by file identity and offset. If files are never modified in place, they need no invalidation at all.
Before you build it
Key by content or immutable identity
With immutable, content-addressed files, an entry can never be stale, which removes the hardest part of caching.
Metadata
Use it when the catalog round trip (schema, file lists, statistics) dominates.
Usually the best deal of all: it depends on one table's commits, is read by every query, and is tiny.
Before you build it
Know what each kind of staleness costs
A stale schema produces wrong answers; a stale file list makes new data invisible; stale statistics produce bad plans. Bound each one separately.
Split the pointer from the body
Cache the immutable body (a snapshot's file list) without limit, and refresh only the small mutable pointer that says which snapshot is current.
Final results
Use it when computing is the expense and the same query repeats between writes.
The most work saved per hit, and the most fragile entry: it depends on every input table.
Before you build it
Build the whole key
The key is the normalized query and its parameters, everything the query reads that is not data (time zone, clock, session settings, the calling user), and the snapshot IDs of every input. Write literals as parameters: a date written into the SQL text makes one entry per date.
Security divides the hit rate
With row-level security, the result depends on who asks, so hit rate is divided by the number of distinct users. If that kills it: cache below the security boundary (scans, pre-filter aggregates) and apply the policy when reading.
Materialized view
Use it when the computation can be declared in advance.
Before you build it
Settle the algebra first
It decides whether the view can be refreshed from changes alone. SUM and COUNT can (a deletion is a subtraction). MIN and MAX cannot handle deletes without recomputing. AVG can, if the view stores sum and count, not the average. Exact distinct counts and medians cannot; mergeable sketches can, approximately. If your aggregate cannot remove values: choose deliberately between insert-only data, an auxiliary structure, a sketch, or full rebuilds.
Find the rebuild crossover
Incremental refresh does random work per changed row; a rebuild scans sequentially. It pays only while changes are a small share of the base, typically between a few percent and about 20%. Measure it, and measure again when your storage medium changes.
Count every input's writes
A view over several tables is invalidated by writes to any of them. Check that reads per version still exceed cost ÷ saving, using the sum of all inputs' write rates, not the one table you had in mind.
Fragments that answer narrower queries
Use it when queries ask overlapping slices of the same data.
Before you build it
Keep the lookup cheaper than a miss
Deciding whether a stored fragment contains the answer is a search. If that search costs close to recomputing, or grows with the cache past its budget, the cache costs more than it saves. Check the lookup cost against the cost of a miss, now and as the cache grows.
Negative entries
Use it when requests look for keys that do not exist.
Free to create (you learned the absence while missing) and cheap to keep. The dependency is a single, known key.
Before you build it
Give negatives their own capacity
The number of keys that do not exist is unbounded: a scanner or a retry loop can create them without limit and evict every useful entry. Cap them in a separate partition.
Invalidate on insert
Creating a key invalidates exactly one negative entry, so wire the insert path to delete it. Falling back to a short TTL instead accepts "I just registered and it says I do not exist"; if you do, name it as an organizational choice. For an absence check that never forgets, a Bloom filter does the same job with a small false-positive rate.