Materialized Views, Summary Tables and CQRS Read Models

In one line

A materialized view stores a query's result so expensive aggregations become cheap reads. PostgreSQL refreshes them fully (REFRESH MATERIALIZED VIEW CONCURRENTLY keeps them readable); incremental maintenance uses summary tables updated by triggers, jobs or event consumers. CQRS generalises this: writes go to a normalized model, reads come from projections built for each screen.

0/4 · 0%

Think of it like this

A newspaper's league table. Nobody recomputes it from every match whenever someone looks; it's updated after each round and printed. Readers accept that it reflects the last update.

Key ideas

01
CREATE MATERIALIZED VIEW ... AS SELECT ... stores rows; REFRESH MATERIALIZED VIEW mv recomputes everything and locks out readers; REFRESH ... CONCURRENTLY (needs a unique index) builds a new result and applies the diff while readers continue.
02
PostgreSQL has no built-in incremental refresh; full refresh cost grows with data. For large, frequently updated aggregates, maintain summary tables incrementally with INSERT ... ON CONFLICT DO UPDATE from triggers, batch jobs or event consumers.
03
Staleness is a product decision: "top sellers updated every 5 minutes" is fine; account balances are not.
04
CQRS read models: an event consumer builds denormalised tables or documents per query (order history with product names and shipment status). They're rebuildable from the event log or source tables, and each can live in the best store for it (Postgres table, Redis, Elasticsearch).
05
Rebuilds: version the projection (order_view_v2), rebuild in the background from the start of the stream or a snapshot, then switch reads. Consumers must be idempotent and ordered per aggregate.

Code & diagrams

mv.sqlsql

CREATE MATERIALIZED VIEW product_sales_30d AS
SELECT oi.product_id, sum(oi.qty) AS units, sum(oi.qty * oi.unit_price) AS revenue
FROM order_item oi JOIN orders o ON o.id = oi.order_id
WHERE o.created_at >= now() - interval '30 days'
GROUP BY oi.product_id;

CREATE UNIQUE INDEX ON product_sales_30d (product_id);   -- required for CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY product_sales_30d; -- readers keep working

-- scheduled every 5 minutes with pg_cron
SELECT cron.schedule('refresh-sales', '*/5 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY product_sales_30d');

cqrs.mermaiddiagram

Rendering diagram…

Interview problem

The problem

Merchant dashboard over 2 billion orders

Merchants view today's sales, last 30 days by day, and top products. Queries on raw orders take 20 s for large merchants. Data may be up to 1 minute stale. Design the read model.

When it breaks

REFRESH MATERIALIZED VIEW without CONCURRENTLY during business hours

What you see

It takes an ACCESS EXCLUSIVE lock; dashboard queries block for the whole refresh.

Fix & prevent

Use CONCURRENTLY with a unique index, or swap between two tables; move large aggregates to incremental summaries.

Explain it without notes

What's the difference between a materialized view and a summary table?

Practice

Make an idempotent consumer update merchant_daily_sales from OrderPlaced events.

Trade-offs

↔
Read models make reads fast and simple, at the cost of staleness, extra storage and pipelines that must be rebuildable.

Done when you can

I can build materialized views, incremental summary tables and CQRS projections with a rebuild plan.

Command Palette