Should this dashboard use a materialized view?

The situation

The sales dashboard shows revenue by month and category. It runs the report on every page load and takes almost two seconds. An AI assistant opened a pull request to fix it.

-- migration
CREATE MATERIALIZED VIEW monthly_revenue_mv AS
SELECT date_trunc('month', o.ordered_at) AS month, p.category,
       sum(i.quantity * i.unit_price) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
GROUP BY 1, 2;

# crontab
* * * * *  psql -c "REFRESH MATERIALIZED VIEW monthly_revenue_mv"

CREATE UNIQUE INDEX ON monthly_revenue_mv (month, category);

How to decide

Prefer a plain view or an index first, unless the query is expensive even when written well. A view costs nothing to keep correct; check the query plan before adding a cache.

Prefer a materialized view when the result is small, expensive to compute, read far more often than the data changes, and allowed to be minutes or hours old.

Prefer a summary table updated as data arrives when the numbers must be close to live, or a full recompute would take longer than the freshness you need.

Whatever you choose, make freshness visible. Record when each refresh succeeded, alert when it is late, and show users the time the data is from.

Check yourself

1. What two changes would you make to the pull request’s refresh, at minimum?

   Show answer

   Add a unique index on (month, category) and refresh CONCURRENTLY, so readers are not blocked; and record and alert on refresh success, so a broken refresh is noticed.

   I could explain this without looking

2. Why does refreshing every minute get worse as the business grows, even if the report has only 72 rows?

   Show answer

   A refresh reruns the full query over all orders, so its cost grows with the order tables. Once it takes longer than the interval, refreshes queue up, because only one can run at a time.

   I could explain this without looking

3. When is a summary table a better choice than a materialized view?

   Show answer

   When the numbers must be nearly live, or a full recompute is too slow for the freshness required. A summary table updated as each order arrives does work proportional to the new data.

   I could explain this without looking