Full cache with hourly expiration causing stampedes
An insurer's claims_pipeline view joins three sources, including a REST lookup for adjusters, and has a median runtime of 85 seconds. Between 08:00 and 11:00, 350 underwriters open the same dashboard, which sends 12 distinct combinations of (reporting_month, region). The source ETL finishes at 05:30 and the data is static for the rest of the day.
The team enabled full caching on the view with an expiration of 60 minutes, using the internal Derby cache engine on each VDP node, JVM heap 16 GB. Result: at the top of every hour, 40-60 concurrent queries miss the cache together, all reach the sources, the source DBAs report CPU saturation, and users see 200-300 second responses. Between spikes, responses are 1-2 seconds. Three weeks in, the cache database file on one node reached 40 GB and queries began failing with disk errors.
Access is governed by a row filter that restricts each underwriter to their own portfolio.
1. Is full caching the right mode here, and how should expiration be configured? Justify against the actual workload.
Read the worked answer
Full caching is the right mechanism for 12 combinations of static data - but the expiration policy is wrong and is manufacturing the outage. A 60-minute expiration guarantees 24 synchronized cold starts a day. Every one of them re-runs an 85-second, three-source query while 40-60 users wait. The data only changes at 05:30, so the cache is being invalidated 23 times a day for no correctness benefit.
Correct configuration:
- Expiration aligned to the ETL, not to the wall clock: either a long expiry (24h) or no time-based expiry at all, with an explicit invalidation/refresh driven by a Scheduler cache-refresh job that runs immediately after the 05:30 load completes.
- Warm the cache before 08:00. Have the Scheduler job issue the 12 parameter combinations after the load, so the first underwriter at 08:00 hits a populated cache rather than the source. This converts a 40-60 user stampede into 12 controlled queries at a quiet time.
- Verify the 40 GB. Full caching stores one entry per distinct query. 12 combinations cannot produce 40 GB. Something else is being cached - ad-hoc reports, different column lists, different filter literals, or per-user predicates (see next answer). Cap or exclude those paths rather than buying disk.
Full cache is preferable to partial cache only while the query shape is predictable. If the parameter space is genuinely open, full cache degrades into an unbounded store and partial cache with cache indexes is the correct choice.
2. The row filter restricts each underwriter to their own portfolio. What does that do to cache reuse, and how do you handle it?
Read the worked answer
It destroys cache reuse for exactly the users who need it. Row filters are evaluated per user, so the SQL that reaches the cache is not the 12 dashboard combinations - it is the 12 combinations combined with each underwriter's portfolio predicate. With 350 underwriters, the cache sees hundreds or thousands of distinct statements, each stored separately as its own full-cache entry. That explains both the 40 GB cache file and the fact that the hourly stampede does not resolve: most requests miss regardless of expiry.
This is a policy-versus-performance conflict, not a tuning problem, so decide deliberately:
- Cache below the policy. Cache the underlying view that is not row-filtered (the aggregated claims data at portfolio level), and let the filtered view read from that cached layer. The expensive three-source join is computed once; the per-user filter is applied cheaply at the end. This is the usual correct answer.
- Cache above the policy only for roles without row filters - e.g. the actuarial role - and leave underwriters uncached.
- Reduce the portfolio dimension. If portfolios map to a few hundred values, partial cache with a cache index on the portfolio column can serve filtered queries from a shared cached result set.
Do not 'fix' it by removing the row filter to make caching work. Equally, do not assume a hit-rate dashboard reflects reality - measure distinct cache keys, not requests. Also remember the cache is per node: with two VDP nodes you pay twice unless you front it with a single caching layer.
3. Beyond caching, what acceleration and placement options would you evaluate, and what is the migration risk for the embedded Derby cache?
Read the worked answer
Cache engine placement. The embedded Derby cache is fine for development and wrong for a production tier: it lives inside the VDP server's file system, shares the host with the JVM and heap, has no independent backup or monitoring, and per-node files mean a node rebuild silently loses the cache. Move the cache database to an external, monitored database with adequate storage and indexes, sized for the expected entry count rather than grown reactively to 40 GB. The migration itself is low risk - the cache is disposable by definition, so you repoint the cache engine and let it rebuild - but do it during a quiet window and warm it immediately, because the first hours after the change are all misses.
Other levers:
- MPP/data movement. The sources are static after 05:30 and the join is expensive. Precomputing the joined result into a columnar MPP table after the ETL may remove the need to cache the join at all, leaving only the cheap per-user filter. That is often cheaper than 40 GB of cache and gives predictable response times.
- Fix the REST adjuster lookup. An 85-second median across three sources suggests the REST source is a latency outlier; caching or replicating a small, slowly changing lookup is a structural fix rather than a query-tuning exercise.
- Query shapes. Encourage the dashboard to send one canonical statement per combination - same column list, same literals - otherwise even a correct cache will miss.
Sequence: correct the expiry and warm the cache first (cheap, immediate), then address cache keys and policy placement, then evaluate data movement if latency is still unacceptable.