Find Cache Misses and Stampede Risk

Event contract

Fields the query expects

Field	Type	Why it exists
timestamp_utc	Timestamp	Cache lookup completion time in UTC.
cache_key_pattern	Utf8	Bounded pattern such as product:{id}, never a raw key.
cache_hit	Boolean	Whether the lookup returned a cached value.
concurrent_misses	Int64	Observed in-flight fills for the same pattern.
backend_duration_ms	Float64	Fallback duration; zero for a hit.
environment	Utf8	Deployment environment.

DataFusion SQL

Copy the query

sql

SELECT
  cache_key_pattern,
  COUNT(*) AS requests,
  SUM(CASE WHEN cache_hit THEN 1 ELSE 0 END) AS hits,
  SUM(CASE WHEN cache_hit THEN 0 ELSE 1 END) AS misses,
  100.0 * SUM(CASE WHEN cache_hit THEN 0 ELSE 1 END)
    / NULLIF(COUNT(*), 0) AS miss_rate_pct,
  MAX(concurrent_misses) AS maximum_concurrent_misses,
  AVG(CASE WHEN cache_hit THEN NULL ELSE backend_duration_ms END)
    AS average_backend_duration_ms
FROM cache_request_events
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
  AND environment = 'production'
GROUP BY cache_key_pattern
ORDER BY maximum_concurrent_misses DESC, cache_key_pattern;

This read-only query is planned and executed against an empty typed table with Apache DataFusion 45.2.0. The deterministic sample output is synthetic and reviewed separately; validate field types, thresholds, and business definitions against your own data. Read the testing methodology.

Query result

Cache miss and stampede indicators

Product lookups combine a 50% miss rate with 31 concurrent fills and the higher backend cost.

Compare a numeric result field

product:{id}

50%

comparison 31%

feature-flags:{account}

12.5%

comparison 7%

cache_key_pattern	requests	hits	misses	miss_rate_pct	maximum_concurrent_misses	average_backend_duration_ms
product:{id}	10	5	5	50	31	232
feature-flags:{account}	8	7	1	12.5	7	75

Synthetic example output. Run the query against your own event schema and thresholds before using it for operational decisions.

Cache miss and stampede indicators: static chart of synthetic miss_rate_pct values from the Find Cache Misses and Stampede Risk example result — Indexable SVG of the deterministic example output. Download it for an article, runbook, or design review with attribution.

Reproduce the example

Download the public fixture

The JSON bundle includes the typed event contract, reproducible input rows, exact SQL, expected output, review notes, and engine version. The CSV contains the displayed result.

Run in browser JSON fixture Expected output CSV Static SVG chart

How the SQL works

1The grouping key is a bounded application-defined pattern rather than a high-cardinality raw cache key.
2Miss rate captures efficiency while maximum concurrent misses exposes burst behavior.
3Backend duration is averaged only for misses because hits do not execute the fallback.

Edge cases to decide

Define concurrent_misses consistently at lookup time or fill start.
Split cold-start deployments from steady-state traffic when cache warming is expected.
Use percentiles rather than averages when backend duration has a heavy tail.

Recommended dashboard

Scatter plot: miss_rate_pct versus maximum_concurrent_misses
Trend: misses and backend duration by cache-key pattern
Table: top bounded patterns by backend work

Alert guidance

Alert when concurrent misses and backend duration rise together for a material request volume.

Read alert setup

Put the recipe to work

Related instrumentation and guides

Infrastructure monitoring Infrastructure recipes Database saturation

Continue the analysis

Run it on real events

Create a table, adapt the fields, and save the result

Start free, send structured events, and use the query result as a chart, shared dashboard widget, or alert input.

Get an API key