Series -- From Ad-Hoc SQL to a Semantic Layer: 0: Overview · 1: Ten Answers · 2: Why Not Indexes · 3: Metrics Model · 4: View Pipeline · 5: Schema Design · 6: Authorization · 7: Migration Sequence · 8: Stakeholder Alignment
The metrics model from the last post gave us one place to define what a business word means. Materialized views gave us a fast place to read it from. But a materialized view is a photograph, not a mirror. It shows the data as it was the last time something took the picture, and the whole speed advantage of the semantic layer rested on a fact that was easy to underestimate: those pictures only stay useful if something keeps retaking them.
I want to invert the Part 0 framing here, because the inversion is the point. The interesting problem was not how to invalidate the cache. It was how to design the refresh pipeline so that cache invalidation stopped being a separate problem at all. Once I saw that the refresh schedule and the cache freshness were the same question wearing two hats, most of the design fell out on its own. Getting there meant being honest about the failure modes of the word "schedule," which sounds solved right up until you ask what happens on the run that doesn't finish.
The refresh pipeline
The scheduler was Sidekiq-Cron, chosen for the least glamorous reason available: Sidekiq was already in the stack. We had workers, a Redis instance behind them, retry semantics, and a web UI that on-call already knew how to read. A dedicated scheduling process, or pg_cron inside the database, would have meant a new operational surface to solve a problem the existing one already covered.
The job itself was deliberately dumb. A single ViewRefreshJob iterated every registered Analytics::Metrics subclass and, for each one, ran REFRESH MATERIALIZED VIEW CONCURRENTLY against that metric's backing view. Because the metrics model already knew every view name (that was the whole point of the registry from Part 3), the refresh job did not maintain its own list of what to refresh. It asked the model, so a newly added metric showed up in the next refresh cycle for free.
The CONCURRENTLY flag was not decoration. A plain REFRESH MATERIALIZED VIEW takes an exclusive lock for the duration of the rebuild, so every report reading that view blocks until the refresh finishes. For a reporting layer that firms hit during business hours, that is a self-inflicted outage on a timer. CONCURRENTLY rebuilds the view alongside the old one and swaps it in, so reads keep hitting the last good copy the entire time. It costs more and requires a unique index on the view, but it buys the one property this layer could not live without: the refresh is invisible to the person reading a report.
Every run also logged its per-view duration to Datadog, which is what the monitoring later hung off of.
Why the background was the right place for the cost
The trade the whole architecture made was to move expense off the read path and onto the refresh path. A report reading from a materialized view answered in single-digit milliseconds, because it was reading a purpose-built table, not reconstructing an answer out of the OLTP schema on every request. The bespoke queries these views replaced took seconds, and they took those seconds while a user waited. Rebuilding a view is not free, but that cost landed in a background worker with nobody watching a spinner. The reports felt instant because the slow part had already happened.
What happens when a refresh fails
Here is the question that separates a schedule from a pipeline: what does the system do on the run that does not finish. A worker gets OOM-killed, a rebuild deadlocks, the database is briefly unreachable. The refresh fails. Then what.
The behavior we designed for, and got, was stale-and-silent. Postgres does not blank out a materialized view when a refresh fails; it keeps the last successful state. So a failed ViewRefreshJob left the view exactly as it was after the previous good refresh. The job failed loudly in Sidekiq, where the failure belonged, but no exception reached the report consumer. The report kept returning the last good data as if nothing had happened.
For this domain, that was the correct failure mode, on purpose. The consumers were billing reports, trust accounting, financial summaries. If a refresh fails at 6am, a firm looking at yesterday's numbers a few minutes into the delay is seeing data that is slightly stale but internally consistent and still trustworthy. Handing that same firm a stack trace where a number should be is strictly worse. Stale data a person can reason about beats an error a person cannot, so the pipeline was built to degrade toward the last known good answer rather than a visible break.
Silent staleness is only safe if it is not actually silent to the people running the system, and that is what the monitoring was for. Two signals, not one. The first was obvious: a Datadog alert on ViewRefreshJob failure, so a failed run paged someone. The second was subtler and more important. We tracked view age directly and alerted if any view had not refreshed within its expected cadence. That caught the failure modes the first one missed: the runs that never threw an exception because they never started, and the ones that hung past the window without technically failing. A job that fails is easy to see. A job that quietly stops running is the one that lets data rot, and the view-age check was the trap for exactly that.
The cache invalidation insight
Everything above sets up the decision I am most attached to on this project, because it is where two problems collapsed into one.
Sitting in front of the views was Redis, and the question was how a cached report answer knew when to stop trusting itself. I looked hard at two standard approaches and turned both down, for reasons that only become visible once you understand how the views actually refresh.
A fixed TTL, cache each answer for N minutes and then evict, has a blind spot that is structural, not tunable. A TTL knows how long it has been since the answer was cached. It knows nothing about when the underlying view last refreshed. Those are different clocks. A freshly refreshed view and a badly stale one, cached at the same moment, expire at the same moment, because the TTL cannot tell them apart. To guarantee freshness you would have to set the TTL short enough to chase the fastest possible refresh, throwing away most of the caching benefit to wait on a timer with no relationship to when the data actually changed.
Event-driven invalidation, bust the specific keys the instant the underlying data changes, is more precise on paper and was a worse fit in practice. Being precise about "when the data changes" means hooking every write path that could change it, across case, billing, trust, and financial domains. That is an N-domain coordination problem, and one missed write path means a report serving stale data with no signal that anything is wrong. It was at its most dangerous during the migration, exactly the window this whole series lives inside, because some legacy reports were still writing directly against the same tables the new model was reading. The domain boundaries were the least stable they would ever be, and this approach demanded I coordinate across all of them at precisely that moment. It was the right tool aimed at the wrong week.
So I keyed the cache to each view's own refresh timestamp instead. The cache key for a report answer included the refresh time of the view it was computed from, and the mechanism that follows is almost too simple to feel like a decision. When a view refreshes, its timestamp changes, so every key derived from it changes, so the next read is a cache miss that recomputes against fresh data. When a view does not refresh, whether on schedule or because a run failed, its timestamp does not move, the key stays valid, and the cache keeps serving the last good answer. A stale view produces a stale key produces a miss, with no invalidation code anywhere in the system. Freshness stopped being a second system I had to keep in sync with the refresh pipeline and became a side effect of the pipeline itself. Updating the data is what invalidates the cache, because the data's own freshness marker is baked into the key. The problem was never how to invalidate the cache; it was how to design the refresh so that invalidation was something the refresh already did.
On the hot paths, the billing and case reports that firms hit on essentially every login, this landed at a 65% cache hit rate. Those are the queries where the same handful of views get read over and over between refreshes, which is exactly the shape timestamp keying rewards: a stable key between refreshes means repeated hits, and the one guaranteed miss per refresh is the miss you want.
The shape of the whole thing
What I keep coming back to is that the failure behavior was layered on purpose. A failure in the refresh pipeline degrades gracefully: the view holds its last good state, the timestamp-keyed cache keeps serving from it, the view-age check catches the staleness before a user does, and the reader never sees an error in a report a firm is using to move client money.
None of the individual pieces here are exotic: Sidekiq, REFRESH MATERIALIZED VIEW CONCURRENTLY, a Redis key with a timestamp in it. The design was in how they compose. Put the expense in the background, let failure fall toward stale rather than broken, and derive the cache key from the same freshness signal the refresh already produces, and the pieces stop being three systems that have to agree with each other and become one system that only has to be right once.
Top comments (0)