Query Cache

Replace this file with a real project. The section headings below are the structure worth keeping — they’re what turns a screenshot into a case study.

The problem

Our dashboard endpoint fanned out to six services and re-ran the same three queries on every page load. At 40 requests/second it was fine. At 400 it was the reason the on-call rotation existed.

The obvious fix — cache the response — didn’t work, because the response was personalized. Two users hitting the same dashboard shared about 80% of the underlying data and none of the final payload.

What I chose, and why

I cached at the query layer rather than the response layer, keying on the normalized SQL plus its bound parameters. That meant the shared 80% became a cache hit for every user while the personalized 20% stayed uncached.

The alternative was a materialized view refreshed on a cron. I ruled it out because staleness would have been measured in minutes, and the product requirement was “a user who edits a widget sees the change immediately.”

Three constraints drove the design:

• Invalidation had to be correct, not clever. Writes publish table-level invalidation events; anything touching that table drops out of the cache. Coarse, occasionally wasteful, and never wrong.
• A cache miss had to cost nothing extra. The wrapper is a decorator around the existing query interface, so a miss is the original code path plus one Redis round trip.
• It had to be observable. Hit rate, key cardinality, and invalidation lag are exported per query shape.

The hard part

Cache stampedes. When a popular key expired, every in-flight request missed simultaneously and hit Postgres at once — the exact spike the cache was meant to prevent, now concentrated into a 50ms window.

The fix was single-flight: the first miss acquires a short-lived lock and does the work, and concurrent misses on the same key wait for that result instead of starting their own. Getting the lock timeout right took a while. Too short and the stampede came back; too long and one slow query stalled every request behind it. It landed at 2× the p99 of the wrapped query, computed per shape rather than as a global constant.

func (c *Cache) Get(ctx context.Context, key string, fn LoaderFunc) ([]byte, error) {
	if hit, err := c.redis.Get(ctx, key).Bytes(); err == nil {
		c.metrics.Hit(key)
		return hit, nil
	}

	// Collapse concurrent misses into one load.
	val, err, _ := c.group.Do(key, func() (any, error) {
		return fn(ctx)
	})
	if err != nil {
		return nil, err
	}

	c.redis.Set(ctx, key, val, c.ttlFor(key))
	return val.([]byte), nil
}

Results

Metric	Before	After
p50 latency	240ms	35ms
p99 latency	1.4s	90ms
Postgres CPU (peak)	78%	22%
Cache hit rate	—	91%

What I’d change

The table-level invalidation is blunter than it needs to be. A write to any row in events drops every cached query that reads events, which on a busy day means the hit rate sags for about a minute after each bulk import. Row-level invalidation via logical replication would fix it, and I’d reach for that before adding another Redis node.

I’d also skip the custom metrics layer. I wrote one before checking whether OpenTelemetry’s existing instrumentation covered it. It did.