Twelve rows, ten times a second
A support desk's queue screen shows twelve rows: the open tickets for each of four teams at three priorities, and beside each count the age of the oldest ticket still waiting. Three hundred agents keep the screen open and it reloads every 30 seconds, so the query behind it runs ten times a second. It joins tickets to each ticket's current assignment and to the customer's support plan, filters what is open, and groups the rest. On a Monday morning each run takes over a second, and the primary database spends most of its time on this one query.
Every run computes the same twelve rows. Between two runs a tenth of a second apart, a ticket or two may have changed, and the database redoes the whole join to find out.
A Materialized View stores the query's result instead, shaped exactly as the readers want it: a queue_summary table with one row per team and priority, holding the count and the creation time of the oldest waiting ticket, from which the screen computes its age. The screen reads twelve rows by key. tickets and the two tables it joins stay the source of truth; the view is a copy derived from them, and the expensive work runs once per refresh or per change rather than once per request. It trades storage and freshness for fast reads, and the pattern is mostly about freshness: who keeps the copy current, and how far behind it may be.
The projector turns changes in the source tables into the twelve rows the screen reads. The queue service never runs the join, and the view can be rebuilt from the source at any time.
Who keeps the copy current
Whatever updates the view is its projector, and there are three common ways to run one.
Inside the database. In PostgreSQL, CREATE MATERIALIZED VIEW queue_summary AS SELECT … runs the query and stores its result like a table, which can be indexed like one. PostgreSQL does not keep it current: the contents change only when REFRESH MATERIALIZED VIEW queue_summary reruns the whole query, usually from a scheduled job. A plain refresh locks out readers of the view until it finishes, so a screen that reloads meanwhile waits for it. REFRESH MATERIALIZED VIEW CONCURRENTLY lets reads continue against the old contents while it computes the new result and applies only the rows that differ. It needs a unique index on plain columns of the view, here on team and priority, and a plain refresh is usually quicker when many rows change. Refreshed every minute, the join runs once instead of six hundred times, and the view can be a minute behind plus the length of the refresh, the screen up to 30 s more.
On a schedule, but only what changed. A job finds the tickets, assignments and plans changed since its last run and recomputes only the team and priority rows they touch, the old team's row as well as the new one's when a ticket was reassigned. The hard part is "changed since": a filter on an updated_at column misses deleted rows and can miss rows committed late, the gaps Change data capture opens with.
On each change. The projector follows changes to tickets, assignments and plans as they commit, from events their services publish or from change data capture, and adjusts the rows they touch: a ticket opened adds one to its row, a ticket closed takes one off, a reassignment moves one from one team's row to another's. The view is now usually seconds behind, and two new problems appear. A projector that applies a "ticket closed" event twice, after a crash and a redelivery, takes one off twice, so it must recognise a change it has already applied; Idempotent Consumer describes the usual ways. And changes to one ticket must be applied in the order they happened, or a reassignment followed by a close is counted against the wrong team.
The oldest waiting time shows where this stops being simple. A count follows from each change, one more or one fewer; a minimum does not. When Billing's oldest urgent ticket closes, nothing in that event says which ticket is now the oldest, so the projector has to ask the source, here through an index on team, priority and creation time. Anything that depends on rows other than the one that changed needs a query back to the source, or extra state in the projector.
How old is this number?
Every way of keeping the view leaves it behind the source, and the screen cannot tell by looking. An agent closes the last urgent Billing ticket and the screen still says 1 until the next refresh: that is the price of the pattern. A refresh job that dies on a Friday is worse, because the screen then shows Friday's numbers all weekend and they look exactly like current ones.
So the view carries its own age. The refreshed query can include now() AS refreshed_at, and a projector applying changes can store the commit time up to which it has applied every change. The screen shows "updated 40 s ago" and greys the numbers out past five minutes. On a quiet night an event-driven view receives no changes and looks stale while being current; a heartbeat change written to the source every few seconds tells the two apart. Then decide per reader what age is acceptable. The queue screen can be a minute behind; the check that refuses to assign an agent more tickets than their limit reads tickets directly, because a stale count there makes a wrong decision rather than an old display; it checks and assigns in one transaction, with the agent's row locked, and every assignment path goes through it.
Disposable by design
Three weeks after launch, Billing's urgent row says 214 and a direct count over tickets says 171. A projector bug counted reassigned tickets twice. The view owns no fact of its own, so rather than hunting for the bad rows, rebuild it: fill a new table from one consistent snapshot of tickets, assignments and plans, tied to a position in the change stream the projector follows, apply every change after that position, and switch readers to it once it has caught up. A scheduled comparison of a few rows against the direct query is cheap insurance that finds the next drift before an agent does.
A view can only be rebuilt from what the source still holds. If a second view counts tickets closed per day over the last year, and closed tickets are purged after 90 days, that view has become the only copy of nine months of history, and it can no longer be thrown away. Where the source is a log of every change, as in Event Sourcing, rebuilding means replaying that history.
Copies with other names
The read model of CQRS is usually a materialized view, a view built from another service's events is one way Database per Service answers cross-service queries, and a warehouse's rollup table is one too (Analytics data: OLTP vs OLAP & the warehouse). Neighbouring copies follow other rules: an Index Table is keyed for lookups by another field, a cache in Cache-Aside is filled by readers on a miss and expires or is invalidated, and an extra column in the same table is the denormalisation weighed in Normalisation & denormalisation.
Draw the copy and its owner
The queue screen fits the Materialized View challenge's palette: a Data Store for tickets in its write shape, a Projector fed by Events, a Materialized View named for the query it answers, and a Client reading the view with no edge to the store. The brief also asks you to note that the view is disposable and rebuildable from the source; a label such as "rebuild from source" on the edge from the store to the projector says it. A good run makes clear how fresh the view is and when it refreshes.