Caching & Materialized Reads for Stale-Tolerant Workloads

Most read paths that use replicas eventually acquire a second staleness source, and often a third. A cache goes in front to cut latency; a materialized view goes underneath to cut computation. Each is a reasonable decision in isolation, and together they produce a read path whose actual freshness nobody has calculated. The operational problem this page addresses is that staleness composes: the layers add, they do not overlap, and the total is routinely several times what the team believes it is.

This is the applied side of replication lag and consistency management. Lag measurement tells you how stale one replica is; this page is about what happens to that number after you put two more layers on top of it, and how to keep the composed total inside a budget you have actually written down.

Concept and Scope: Three Sources of Age

Every value your read path returns has an age, and that age is the sum of three independent contributions.

Replication lag — the interval between the transaction committing on the primary and the replica having replayed it. Typically milliseconds, occasionally seconds, unbounded during a batch job.

Materialization interval — for a materialized view or a rollup table, the time since the last refresh. This is usually the largest of the three and the most predictable, because you schedule it.

Cache residency — how long the value has been sitting in the cache since it was written there. Bounded above by the TTL, and added to whatever age the value already had when it was cached.

The trap is that all three are usually reasoned about separately, by different people, at different times. The replica has a 500 ms lag budget. The materialized view refreshes every 5 minutes. The cache TTL is 60 seconds. Each is defensible; the composed path can serve a value 6 minutes and 0.5 seconds old, and no dashboard shows that number anywhere.

A useful distinction: bounded staleness means you can state a maximum age and enforce it; unbounded staleness means you cannot. A cache with a TTL is bounded. A cache invalidated only by application events is unbounded, because a missed invalidation persists forever. Almost every serious cache-correctness incident traces back to a layer that was assumed bounded and was not.

Scope: this page covers read paths that may serve slightly old data. Read paths that must not — a user reading back their own write, a balance check before a transfer — belong to read-your-writes consistency and should not be cached at all without an explicit watermark check.

Mechanism Deep-Dive: Where Each Layer Gets Its Freshness

A cache read from a replica inherits the replica’s lag

The subtlety that catches teams is when the age starts counting. A cache entry’s TTL starts at the moment of writing, but the value’s age started at the commit that produced it. If the read that populated the cache came from a replica two seconds behind, the entry begins life two seconds old.

# Wrong: TTL is treated as the value's whole age budget.
def get_profile(uid):
    if (v := cache.get(f"profile:{uid}")) is not None:
        return v
    v = replica.query("SELECT * FROM profiles WHERE id = %s", uid)
    cache.set(f"profile:{uid}", v, ttl=60)          # v may already be 2 s old
    return v

# Better: carry the observed age with the value and shorten the TTL to fit
# the remaining budget. Freshness becomes an explicit, inspectable quantity.
BUDGET_S = 60

def get_profile(uid):
    if (e := cache.get(f"profile:{uid}")) is not None:
        return e["value"], time.time() - e["as_of"]
    row, lag_s = replica.query_with_lag(
        "SELECT * FROM profiles WHERE id = %s", uid)
    remaining = max(BUDGET_S - lag_s, 1)            # never below one second
    cache.set(f"profile:{uid}",
              {"value": row, "as_of": time.time() - lag_s},
              ttl=remaining)
    return row, lag_s

The second version costs one extra field and turns an unmeasurable property into a measurable one. as_of is the commit-time the value reflects, so any caller — or any dashboard — can compute the true age rather than the cache-residency time.

Invalidation must be driven from the primary

An invalidation that races with a repopulation from a lagging replica leaves the cache holding stale data with a fresh TTL, which is the worst of both worlds: bounded staleness has silently become unbounded.

The failure sequence is precise. A write commits on the primary. The application invalidates the cache key. A concurrent read misses the cache, queries a replica that has not yet replayed the write, and repopulates the key with the pre-write value and a full TTL. The invalidation happened and the stale value survives.

Two mechanisms close this:

# Mechanism 1 — delete-after-lag. Invalidate twice: immediately, and again
# after the replication lag window has certainly passed.
def invalidate(key, lag_budget_s=2.0):
    cache.delete(key)
    schedule_after(lag_budget_s, lambda: cache.delete(key))

# Mechanism 2 — version gate. The primary's commit bumps a version that any
# repopulation must match, so a value read from a stale replica cannot win.
def repopulate(key, uid):
    want = primary.query("SELECT xmin FROM profiles WHERE id = %s", uid)
    row, seen = replica.query_with_version(
        "SELECT *, xmin FROM profiles WHERE id = %s", uid)
    if seen != want:
        return row              # serve it, but do NOT cache a version we know is behind
    cache.set(key, row, ttl=BUDGET_S)
    return row

Mechanism 1 is cheap and usually sufficient; the second delete costs one operation and closes the window for any lag under the budget. Mechanism 2 is exact but pays a primary round trip on every cache miss, which frequently defeats the reason the cache exists. Choose the first unless correctness genuinely requires the second.

Materialized views refresh on the primary and arrive by replication

REFRESH MATERIALIZED VIEW is a write. It cannot run on a standby. The refreshed contents reach replicas through normal WAL replay, which means a materialized view on a replica is always at least one replication interval behind its own refresh — and the refresh itself is a large write that can increase lag while it replays.

-- CONCURRENTLY avoids the ACCESS EXCLUSIVE lock, so readers on the primary
-- and on replicas are not blocked. It requires a unique index on the view.
CREATE UNIQUE INDEX IF NOT EXISTS mv_daily_totals_pk
  ON mv_daily_totals (account_id, day);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_totals;

-- Record when the refresh completed so readers can compute the view's age
-- instead of assuming it matches the schedule.
INSERT INTO mv_refresh_log (view_name, refreshed_at)
VALUES ('mv_daily_totals', clock_timestamp());

Without CONCURRENTLY, the refresh takes an ACCESS EXCLUSIVE lock. When that lock is replayed on a hot standby it conflicts with any query reading the view, and those queries are cancelled — the mechanism described in detecting hot standby query cancellations on Postgres replicas. A nightly non-concurrent refresh is a scheduled outage for every replica reader of that view.

The refresh log matters more than it looks. Without it, the view’s age is inferred from the cron schedule, which is exactly the quantity that becomes wrong when a refresh is slow, skipped, or still running.

Trade-off Comparison

Always give an event-invalidated cache a TTL as well, even a long one. The TTL is not the freshness mechanism — the invalidation is — but it converts “stale forever” into “stale for at most one TTL”, and that difference is what separates a degradation from an incident.

Configuration Runbook

A composed read path with an explicit budget

# read_path.py — one place where the whole budget is stated and enforced.
# Every layer subtracts from the same number; nothing gets its own budget.
TOTAL_BUDGET_S = 30.0

def read_dashboard(account_id):
    spent = 0.0

    entry = cache.get(f"dash:{account_id}")
    if entry and (age := time.time() - entry["as_of"]) < TOTAL_BUDGET_S:
        return entry["value"], age                    # inside budget: serve it

    # Cache miss or over budget — go to the data, accounting as we go.
    view_age = mv_age_seconds("mv_daily_totals")      # from mv_refresh_log
    spent += view_age
    if spent > TOTAL_BUDGET_S:
        # The view alone has consumed the budget: nothing downstream can fix
        # this. Serve from the primary or fail loudly — never cache it.
        return primary.query(LIVE_SQL, account_id), 0.0

    row, lag_s = replica.query_with_lag(VIEW_SQL, account_id)
    spent += lag_s
    if spent > TOTAL_BUDGET_S:
        return primary.query(LIVE_SQL, account_id), 0.0

    cache.set(f"dash:{account_id}",
              {"value": row, "as_of": time.time() - spent},
              ttl=max(TOTAL_BUDGET_S - spent, 1))
    return row, spent

The structure is the point: a single budget, decremented by each layer, with an escape hatch to the primary when the budget is exhausted before the cheapest path can be used. This makes the staleness question answerable at any moment rather than a matter of adding up configuration files.

Refresh scheduling that does not fight replication

# Refresh windows chosen against replication behaviour, not just business hours.
materialized_views:
  - name: mv_daily_totals
    refresh: "*/5 * * * *"
    concurrently: true          # required: replicas read this view continuously
    skip_if_lag_above_ms: 1000  # do not add a large write while replicas are behind
    alert_if_age_exceeds_s: 900 # 3× the interval — catches silent refresh failure
  - name: mv_monthly_rollup
    refresh: "17 3 * * *"       # off-peak; :17 avoids the top-of-hour job pile-up
    concurrently: true
    alert_if_age_exceeds_s: 172800

skip_if_lag_above_ms prevents the compounding failure where a refresh runs while replicas are already behind, adds a large write to the WAL stream, and pushes them further behind — the mechanism behind many “the dashboard job takes down the read tier” incidents.

Monitoring and Alerting Signals

The instinct is to monitor cache hit rate. Hit rate tells you about cost, not about correctness — a 99% hit rate on stale data is worse than a 60% hit rate on fresh data. Monitor age instead.

# 1. Observed age of served values, at the tail. This is the real SLI.
histogram_quantile(0.99, sum by (le, path) (rate(read_value_age_seconds_bucket[5m])))

# 2. Budget consumption — what fraction of the stated budget the path uses.
histogram_quantile(0.99, sum by (le, path) (rate(read_value_age_seconds_bucket[5m])))
  / on(path) group_left read_path_budget_seconds > 0.8

# 3. Materialized view age against its own schedule. Catches a silently
#    failing or overrunning refresh, which the cron exit code often does not.
time() - mv_last_refresh_timestamp_seconds{view="mv_daily_totals"} > 900

# 4. Repopulation-from-stale rate — how often a cache write used a replica
#    read that was already outside the per-read lag allowance.
rate(cache_set_from_stale_replica_total[5m]) > 0

# 5. Refresh replay impact: replica lag attributable to the refresh window.
max_over_time(pg_replication_lag_seconds[10m])
  and on() (hour() == 3)

Signal 1 requires instrumenting the read path to emit the age it computed — the as_of field from the runbook above. That instrumentation is the difference between knowing your staleness and estimating it from configuration, and it is a few lines of code. Signal 4 is the direct measurement of the invalidation race; a non-zero rate means the race is being lost, and the second-delete mechanism is the fix.

Failure Modes and Recovery Steps

1. Compounded staleness exceeds the budget nobody wrote down. Diagnosis: users report data older than any single layer’s setting allows; signal 1 shows a p99 far above expectation. Recovery: shorten the largest term, which is almost always the materialization interval rather than the cache TTL. Prevention: a single budget decremented by each layer, as in the runbook.

2. Lost invalidation race repopulates stale data. Diagnosis: a specific key serves pre-write data for a full TTL after a write, reproducibly under load and never in a quiet environment. Recovery: delete the key manually, then deploy the scheduled second delete. Prevention: delete-after-lag on every invalidation path.

3. Non-concurrent refresh cancels replica queries. Diagnosis: ERROR: canceling statement due to conflict with recovery clustered exactly at refresh time. Recovery: switch to REFRESH ... CONCURRENTLY, which needs a unique index on the view. Prevention: make CONCURRENTLY the default for any view a replica reads.

4. Refresh job fails silently and the view ages out. Diagnosis: the data looks plausible but is hours old; the cron job reports success because the failure was inside a transaction that rolled back. Recovery: refresh manually and backfill. Prevention: signal 3 — alert on the view’s observed age from the refresh log, never on the scheduler’s exit code.

5. Refresh worsens replication lag, which worsens everything downstream. Diagnosis: replica lag spikes during the refresh window and the cache begins caching values that were already stale. Recovery: stagger or skip the refresh. Prevention: skip_if_lag_above_ms, and schedule large refreshes away from other batch writes — the interaction is the same one described in graceful degradation when all replicas exceed the lag budget.

Production-Readiness Checklist

• A single staleness budget is written down per read path, in seconds
• Every layer’s contribution is accounted against that one budget, not its own
• Cache entries carry an as_of commit-time, not just a TTL
• TTL is shortened by the lag observed at population time
• Every event-invalidated cache also has a TTL as a bound of last resort
• Invalidation is driven from the primary and repeated after the lag window
• All materialized views read by replicas refresh CONCURRENTLY
• A refresh log records completion times; view age is alerted from it
• Large refreshes skip or defer when replica lag is already elevated
• Served value age is exported as a histogram and treated as the SLI

In This Section

• Redis Cache Versus Read Replica for Hot Read Paths — when to add a cache, when to add a replica, and why the answer is rarely both at once.
• Refreshing Materialized Views on Read Replicas — why the refresh runs on the primary, and how to keep it from cancelling replica queries.
• Cache Invalidation That Survives Replication Lag — the race in detail, with both fixes and their costs.
• Serving Bounded-Staleness Reads with an Explicit Freshness Budget — turning “reasonably fresh” into a number the code enforces.

FAQ

Related

← Back to Replication Lag & Consistency Management

• Eventual Consistency Patterns for Read-Heavy Workloads — the design patterns that make a stale-tolerant read path acceptable to users.
• Routing Queries Based on Data Freshness Requirements — choosing a node by freshness, the layer beneath everything on this page.
• Detecting and Handling Replication Lag in Real-Time — measuring the lag term that every other term is added to.
• Detecting Hot Standby Query Cancellations on Postgres Replicas — the cancellation mechanism a non-concurrent refresh triggers.