Part 4 — Fast at scale › Chapter 32

Materialised views, pre-aggregation and caching

Trading freshness for speed on purpose, and the three questions to answer before you cache anything.

From the advert

“Reporting con SSRS e Power BI” — when the report takes forty seconds and the query cannot be made faster, the remaining move is to compute it earlier.

The trade you are making

🧒 Simple

Working out the total every time somebody asks is slow but always right. Writing the total on a card and handing over the card is instant but might be out of date. There is no third option — so the only real question is how out of date is allowed, and who decides.

🎓 The three questions, before you cache anything

How stale may it be? A dashboard tile: minutes. A stock level on a checkout page: zero. If the answer is “zero”, caching is not the tool.
What invalidates it? A schedule, a trigger, an event, or a TTL. If you cannot name the mechanism, you have not designed a cache — you have created a bug with a delay.
What happens when it is wrong? A slightly stale sales figure is fine. A stale permission check is a security incident. The blast radius decides how much machinery the invalidation deserves.

A plain view is not a cache

A plain view stores no data at all.

CREATE VIEW v_order_totals AS SELECT ... ;   -- a NAME for a query, and nothing more

What that means in practice

Reading the view runs its query, every time, at whatever it costs. A view is not a cache and never becomes one by being queried often.
What it does buy is a stable interface: the definition of “order total” lives in one place, and every report agrees with every other one.
Views nest, and nested views are how a five-table query quietly becomes a thirty-table one. Read the plan, not the view.

A view is expanded into whatever selects from it and re-executed every time. It is an excellent interface — it lets the tables underneath change without every report changing — and it does nothing at all for performance. People are surprised by this regularly enough that it is worth saying explicitly.

Materialised views

Engine	Feature	Refresh
PostgreSQL	`MATERIALIZED VIEW`	Manual: `REFRESH MATERIALIZED VIEW`. Add `CONCURRENTLY` (needs a unique index) to avoid locking readers
SQL Server	Indexed view	Synchronous — maintained inside the transaction that changes the base table
Oracle	`MATERIALIZED VIEW`	On commit, on demand, or incremental via a materialised view log
MySQL	None	A summary table plus a scheduled job or triggers

⚠ SQL Server's indexed view is a different bargain

It is always current, because every write to a base table updates it synchronously — so the cost lands on your writes, permanently, not on a refresh window. It also carries a long list of restrictions (SCHEMABINDING, no outer joins, no subqueries, COUNT_BIG(*) required with GROUP BY, deterministic expressions only). Knowing it is synchronous — and that PostgreSQL's is not — is the distinction that matters, because it decides which one is safe on a write-heavy table.

A materialised view does store data — which is exactly why it can be stale.

-- PostgreSQL: refreshed on a schedule, and readers are never blocked.
CREATE MATERIALIZED VIEW mv_monthly_sales AS          -- the result is STORED, not recomputed
SELECT date_trunc('month', o.placed_at) AS month,     -- the grain: one row per month…
       r.name                           AS region,    -- …per region
       COUNT(*)                         AS orders,    -- how many
       SUM(o.total_cents)               AS revenue_cents   -- and how much, in cents
FROM orders o                                         -- the fact table
JOIN customers c ON c.id = o.customer_id              -- …to reach the city…
JOIN cities   ci ON ci.id = c.city_id                 -- …and the region…
JOIN regions   r ON r.id = ci.region_id               -- …four tables, resolved once
WHERE o.status <> 'cancelled'                         -- cancellations are not revenue
GROUP BY 1, 2;                                        -- by the first two select columns

CREATE UNIQUE INDEX ON mv_monthly_sales (month, region);  -- REQUIRED by CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;  -- rebuild without blocking readers

The two words that decide whether this is safe on a live system

CONCURRENTLY keeps the view readable while it rebuilds. Without it, the refresh takes an exclusive lock and every dashboard hangs for its duration — which is how a “performance improvement” becomes an outage.
It requires a unique index on the view, because the refresh works out the difference row by row and needs to identify rows. That is why the index above is not optional.
The data is as old as the last refresh. Decide the staleness budget explicitly — five minutes, an hour, overnight — and put it in the name or the documentation, because someone will eventually ask why the number differs from the live query.
SQL Server's indexed views are the opposite trade: maintained synchronously, always current, and every writer pays for them on every write.

Summary tables, and the incremental refresh

A full refresh recomputes everything, which stops scaling once "everything" is two years of data. The pattern that does scale is to refresh only what changed:

Incremental refresh: recompute only the slice that changed.

-- Recompute only the affected slice, inside one transaction. Naturally idempotent,
-- so re-running it after a failure is safe. See chapter 20.
BEGIN;                                                      -- both statements, or neither
  DELETE FROM summary_daily_sales WHERE d = :day;           -- remove the old version of the day
  INSERT INTO summary_daily_sales (d, region, orders, revenue_cents)  -- and rebuild it
  SELECT date(placed_at), region, COUNT(*), SUM(total_cents)  -- the same aggregation as before
  FROM v_orders_geo                                         -- a view that joins the geography
  WHERE date(placed_at) = :day                              -- …restricted to one day…
    AND status <> 'cancelled'                               -- …and to real revenue
  GROUP BY 1, 2;                                            -- by day and region
COMMIT;                                                     -- readers see old or new, never half

Why this scales where a full refresh does not

REFRESH MATERIALIZED VIEW recomputes everything, and “everything” is two years of data. This touches one day, so its cost is constant as the table grows.
Delete-then-insert of a slice is idempotent by construction: re-running replaces the day with itself, so a failed job can simply be run again (chapter 20).
date(placed_at) = :day is not sargable — a function on the column. In a nightly job over one day's rows that is acceptable; if this table were large, the predicate would be a half-open range on the raw timestamp instead.
You are now maintaining a cache by hand. That is a real cost, and it is worth paying only when the query is genuinely too slow and genuinely tolerates being stale.

Always store when it was computed

A computed_at column costs nothing and answers the only question anyone asks about a summary table: “is this current?”. Show it on the report — “as at 06:00 today” — and stale data stops being a support ticket and becomes a stated property. That one line of UI saves more arguments than any amount of refresh engineering.

Maintained counters, and why they hurt

The maintained counter: correct, tempting, and a contention point you cannot index away.

-- Tempting: keep orders.total_cents in step with a trigger on order_lines.
-- Correct, and it serialises every concurrent write to the same order behind one
-- row lock — because they all update the same parent row. On a hot parent this
-- is a contention point you cannot index your way out of.

The trade, stated plainly

Every insert into order_lines now also updates one row of orders. Two concurrent line inserts for the same order both want that row's lock, so they queue — and the queue is invisible in any per-query report.
The alternative is computing the total on read, from an index that makes it cheap. Slower per read, and no write contention at all.
Which is right depends on the read/write ratio, and saying that — rather than “triggers are bad” — is the answer an interviewer is listening for.
If you do maintain a counter, the trigger must handle UPDATE and DELETE of lines too, not just INSERT. Most hand-written ones do not.

🎓 The standard fix: sharded counters

Instead of one row per counter, keep n rows and have each writer update a random one; read the counter as a SUM over the n. Writes spread across n locks, reads cost one small aggregate. It is the same idea behind PostgreSQL's advice to avoid a single hot row, and it is a good thing to know exists — although the honest first answer is usually “compute it on read, with the right index, until measurement says otherwise”.

Where to cache

Layer	Good at	Watch out for
Summary table / matview	Shared by every consumer, queryable, joinable, transactional	Refresh cost and lag
Redis / Memcached	Sub-millisecond key lookups, TTLs, cross-process	A second system to run and to invalidate; cache stampedes
Application memory	Free and instant	Per-process, so n instances hold n different stale copies
HTTP caching	Nothing reaches the database at all — the cheapest query	Only works for genuinely public, cacheable responses

⚠ Cache stampede

A popular cached value expires; a thousand concurrent requests all miss; a thousand identical expensive queries hit the database at once and it falls over — because the cache was working. Mitigations: a lock so only one request recomputes, serving stale while refreshing in the background, or jittered expiry so a batch of keys does not expire together. Naming this failure mode signals real production experience.

Caching trades freshness for speed. Decide the tolerated staleness first, in numbers.
If you cannot name the invalidation mechanism, you have not designed a cache.
A plain view is a name for a query and does nothing for performance.
PostgreSQL materialised views are refreshed manually; SQL Server indexed views are maintained synchronously on every write.
Refresh incrementally by slice. A full refresh stops scaling.
Store and display computed_at. Stale becomes a property, not a bug report.
Trigger-maintained counters serialise concurrent writes on the parent row.
Application-memory caches multiply by the number of instances.
Expiring a popular key can stampede the database. Use a lock, serve-stale, or jittered TTLs.

Interview questions this chapter answers

A report takes 40 seconds and the query is already optimal. What now?

Compute it earlier. I would ask how stale it may be — usually the answer is "this morning is fine" — then build a summary table refreshed incrementally by day, with a computed_at column shown on the report. A materialised view is the same idea with engine support; on SQL Server an indexed view is always current but moves the cost onto every write.

Difference between a view and a materialised view?

A view is a stored query, expanded and re-executed each time — it is an interface, not a cache. A materialised view stores the result, so reads are fast and the data is as fresh as the last refresh. PostgreSQL refreshes on demand; SQL Server's indexed views are maintained synchronously, which makes writes more expensive but removes staleness entirely.

What is a cache stampede?

A hot cached value expires and every concurrent request misses at once, so the database gets hundreds of identical expensive queries simultaneously. Fixes are a mutex so one request recomputes while the others wait, serving the stale value while refreshing in the background, or adding jitter to expiry times so keys do not expire together.