Add a Read-Through Result Cache

Cache expensive query results keyed by a normalized query plus tenant, invalidate on writes, and set a TTL so stale data cannot persist after a missed invalidation.

~40 mincachingdatabasesperformance

Task

Add a read-through cache in front of expensive queries with correct invalidation and a safety TTL.

When to use this

A report query runs on every dashboard load, and simple TTL caching leaves obviously stale data after a write that should have refreshed it.

Prerequisites

A shared cache store
Write paths that can emit invalidation events

Steps

1. Build a cache key from the normalized query and the tenant or user scope.

Expected output: Different scopes never share a cache entry.

2. Read through the cache: return a hit, otherwise run the query and store the result.

Expected output: Repeat reads skip the database.

3. Invalidate on writes that affect the underlying data with a short TTL as a backstop.

Expected output: Writes refresh the cache and a missed event still expires.

4. Log cache hit ratio and latency to confirm the cache is earning its keep.

redis-cli --scan --pattern "q:tenant:acme:*" | head

Expected output: The cache is measured, not assumed.

Troubleshooting

Problem

Cache returns data from another tenant

Solution

Always include the tenant or user id in the key; never key only by query text.

Problem

Invalidation missed

Solution

Keep a short safety TTL so a missed invalidation still resolves within a bounded time.

Limitations

A result cache serves data that may be briefly stale, which is wrong for reads that must be exact.
Cache keys and invalidation add complexity and bugs; measure carefully before caching widely.

Context

Add a read-through cache in front of expensive queries with correct invalidation and a safety TTL. A report query runs on every dashboard load, and simple TTL caching leaves obviously stale data after a write that should have refreshed it.

Steps

Build a cache key from the normalized query and the tenant or user scope. Expected: Different scopes never share a cache entry.
Read through the cache: return a hit, otherwise run the query and store the result. Expected: Repeat reads skip the database.
Invalidate on writes that affect the underlying data with a short TTL as a backstop. Expected: Writes refresh the cache and a missed event still expires.
Log cache hit ratio and latency to confirm the cache is earning its keep. Expected: The cache is measured, not assumed.

const key = `q:${tenant}:${hash(normalizedSql)}`;
const cached = await cache.get(key);
if (cached) return JSON.parse(cached);
const rows = await db.query(sql);
await cache.set(key, JSON.stringify(rows), { ttl: 300 });
return rows;

Verify

Different scopes never share a cache entry.
Repeat reads skip the database.
Writes refresh the cache and a missed event still expires.
The cache is measured, not assumed.

Limits

A result cache serves data that may be briefly stale, which is wrong for reads that must be exact.
Cache keys and invalidation add complexity and bugs; measure carefully before caching widely.