Prompt
how to cache database query results without stale data
Latest observation
You generally can’t have perfect caching and never-stale data at the same time. The practical goal is to make data fresh enough while keeping performance high.
Here are the main patterns to avoid stale data:
1) Use short TTLs
Cache query results for a short time only.
- Example: 5–30 seconds for highly dynamic data
- Example: 5–15 minutes for mostly static data
Pros: simple
Cons: data can still be stale until TTL expires
2) Invalidate cache on writes
Whenever data changes in the database, explicitly evict or update the related cache entries.
- On
INSERT,UPDATE,DELETE, remove affected keys - Or update the cached value directly if you can do it safely
Pros: much fresher than TTL alone
Cons: harder to implement, especially with complex queries
3) Cache by entity, not by query when possible
Instead of caching whole query results, cache individual records:
- Cache
user:123 - Cache
product:456
Then build query results from cached entities.
Pros: easier invalidation on a single record change
Cons: more complex query assembly
4) Use versioning
Attach a version number to cached data.
- Store a global version for a table or dataset
- Include version in cache key, e.g.
orders:v17:status=paid - On write, increment the version
This makes old cache entries automatically irrelevant.
Pros: simple invalidation strategy
Cons: old cache entries may remain until evicted
5) Read-through / cache-aside pattern
Common pattern:
- App checks cache
- If miss, read DB
- Store result in cache
- Return result
Then combine with TTL and invalidation.
Pros: widely used, flexible
Cons: stale data possible unless invalidated
6) Use database change streams / pub-sub
If your database supports it:
- Listen for changes via CDC, triggers, binlog, WAL, etc.
- Publish invalidation events to cache nodes
- Remove or refresh affected entries
Pros: very fresh cache invalidation
Cons: more infrastructure complexity
7) Cache only “safe” data
Avoid caching queries that must always be current, such as:
- account balances
- inventory for critical checkout flows
- permission checks
- financial transactions
For these, either don’t cache or use very short TTL plus strict invalidation.
Good practical approach
A common reliable setup is:
- Cache-aside
- Short TTL
- Invalidate on writes
- Version keys for broad invalidation
- Do not cache critical strongly-consistent reads
Example
For a list of active products:
- Cache key:
products:active:v12 - TTL: 60 seconds
- On product update affecting active status:
- bump version from
v12tov13 - or delete
products:active:v12
- bump version from
This prevents serving old results for long.
Rule of thumb
- If freshness matters most: minimize caching or use write-triggered invalidation
- If performance matters most: use TTL + invalidation
- If both matter: use versioned cache keys and event-based invalidation
If you want, I can show:
- a language-agnostic design, or
- code examples in Python, Node.js, or Java.