Case Study 1 — The Dashboard That Got Fast, Then Got Stale

A materialized view rescued a slow dashboard — and then, weeks later, quietly served week-old numbers because nobody scheduled the refresh. The lesson: a matview is a cache, and a cache you don't invalidate is a bug waiting to happen.

Background

An executive dashboard had a "revenue by category, this quarter" tile backed by a five-table join with aggregation over millions of order_items. As the data grew, the tile took 15–20 seconds to load; executives refreshed the page and got spinners. The team's fix was textbook: cache the aggregate in a materialized view.

CREATE MATERIALIZED VIEW category_sales AS
SELECT c.category_id, c.name AS category,
       COUNT(DISTINCT o.order_id)       AS num_orders,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM categories c
JOIN products p     ON p.category_id = c.category_id
JOIN order_items oi ON oi.product_id = p.product_id
JOIN orders o       ON o.order_id    = oi.order_id
GROUP BY c.category_id, c.name;

CREATE UNIQUE INDEX ON category_sales (category_id);   -- enables CONCURRENTLY refresh

The dashboard now read a handful of pre-aggregated rows — sub-millisecond. Everyone was thrilled. The slow tile was fast.

What went wrong (weeks later)

For the first few days, the numbers looked current — because the team had run REFRESH manually a few times while testing. But there was no scheduled refresh. A materialized view stores a snapshot; it does not update when the base tables change. As new orders poured in, the matview kept showing the snapshot from the last manual refresh. Within a couple of weeks the dashboard was confidently displaying numbers that were days to weeks old, and a VP made a planning comment based on revenue that had actually grown 20% since the snapshot.

The failure mode was the worst kind: not an error, not a slow page, but a fast, wrong page. The very speed that made it trustworthy made the staleness invisible.

The fix

1. Schedule the refresh at a cadence matching how fresh the data must be. Using pg_cron (or an external scheduler):

-- Refresh hourly, without blocking dashboard reads (needs the unique index above)
SELECT cron.schedule('refresh_category_sales', '0 * * * *',
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY category_sales$$);

CONCURRENTLY lets the dashboard keep reading the old snapshot while the new one is computed, then swaps atomically — no read downtime (it requires the unique index, which they'd already added).

2. Show the data's age. They added a last_refreshed timestamp (a tiny one-row table the refresh updates) and displayed "as of 10:00" on the tile. Now any staleness is visible, and the speed-vs-freshness trade-off is honest rather than hidden.

3. Match cadence to need. Quarterly revenue doesn't need second-by-second freshness; hourly was plenty and cheap. A tile that did need real-time numbers stayed on a plain view (live, slightly slower) instead of a matview — the right tool for that requirement.

The analysis

  1. A materialized view is a cache. Caches trade freshness for speed. The cardinal rule of caching applies: you must invalidate (refresh) it, or it lies. An unrefreshed matview is not "a fast view" — it's a stale snapshot.

  2. Fast-and-wrong is more dangerous than slow. The slow original was at least correct; the matview was fast and silently outdated. Speed lent it false credibility. Always ask of a cached number: "how old is this?"

  3. Refresh must be scheduled, not manual. Manual refresh works in testing and fails in production (nobody remembers). Automate it (pg_cron, an app job, or a trigger-driven refresh) at a cadence tied to the freshness requirement.

  4. CONCURRENTLY avoids the refresh downtime — but needs a unique index. Plan for it when you create the matview (as the team did) so you're not blocked later.

  5. Surface the staleness. Displaying "as of

  6. Match the tool to the freshness need. View (live, recomputed) vs. materialized view (cached, refreshed) is a requirements decision: how stale can this be? Answer that first, then choose.

Discussion questions

  1. Why did the dashboard show old numbers despite the base tables being up to date?
  2. Why is "fast and wrong" arguably worse than "slow and right" here?
  3. What does REFRESH ... CONCURRENTLY provide, and what does it require?
  4. How would you decide the refresh cadence for a given tile? Give two examples with different needs.
  5. ⭐ Besides scheduled refresh, what are two other ways to keep a matview reasonably fresh (e.g., event-driven refresh)? What are their trade-offs?