⚡ Speeding Up Dashboards With Postgres Materialized Views and AI

The dashboard nobody wanted to load was running the same eight-way join and three aggregates on every page view, against tables that only changed a few times an hour. Every executive who opened it triggered a full re-computation of numbers that had not moved since the last person looked. This is the textbook case for a materialized view: pre-compute the expensive result once, store it like a table, and let every reader hit the cached version. The query that took twelve seconds becomes a millisecond index lookup. The catch is that a materialized view is a snapshot, not a live view — it is only as fresh as your last refresh, and choosing what to materialize and how often to refresh is a judgment call about staleness. AI is a good thinking partner for that judgment, but it cannot tell you how stale your business can tolerate. That number comes from you.

Here is how I decide what to materialize, set up concurrent refreshes that do not block readers, and verify the speedup is real.

Regular view versus materialized view

A regular VIEW is just a stored query — it runs the underlying SQL every time you select from it, so it is always fresh and never faster than the query it wraps. A MATERIALIZED VIEW runs the query once, stores the result physically, and serves reads from that stored copy until you refresh it. You trade freshness for speed. That trade is only worth making when two things are true: the underlying query is genuinely expensive, and your readers can tolerate data that is minutes or hours old rather than instantaneous.

Dashboards almost always fit. Nobody needs a quarterly-revenue tile to be accurate to the second; they need it to load. Operational alerting, on the other hand, usually does not fit, because acting on stale data defeats the purpose.

Creating and indexing one

The creation is straightforward. The detail that matters for production is the unique index, which I will explain in a moment.

CREATE MATERIALIZED VIEW dashboard_daily_revenue AS
SELECT
  date_trunc('day', placed_at) AS day,
  region_id,
  count(*)                     AS orders,
  sum(total_cents) / 100.0     AS revenue
FROM orders
JOIN customers USING (customer_id)
GROUP BY 1, 2
WITH DATA;

-- A unique index is what makes concurrent refresh possible:
CREATE UNIQUE INDEX dashboard_daily_revenue_pk
  ON dashboard_daily_revenue (day, region_id);

The view now behaves like a table for reads — you can index it, query it, and join it. The columns you would put in the dashboard’s WHERE and ORDER BY deserve indexes just as they would on a base table, because the materialized view is read like one.

Why REFRESH CONCURRENTLY needs a unique index

The default refresh is the problem you have to design around.

-- The naive refresh: rebuilds the data, but takes an
-- ACCESS EXCLUSIVE lock — readers are blocked until it finishes.
REFRESH MATERIALIZED VIEW dashboard_daily_revenue;

-- The production refresh: readers keep querying the old data
-- throughout, and the swap is transactional.
REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_daily_revenue;

A plain REFRESH takes an ACCESS EXCLUSIVE lock for its whole duration, so every dashboard reader is blocked while it runs — which on a heavy view means the dashboard freezes exactly when the data is being updated. REFRESH ... CONCURRENTLY instead computes the new result and applies the diff while readers continue to see the old snapshot, holding only a lighter lock. The price of admission is a unique index on the view: Postgres needs it to match old rows to new rows during the diff. Forget the unique index and CONCURRENTLY errors out. This is the single most common materialized-view mistake I see, so I treat the unique index as part of the view definition, not an optional extra.

Scheduling the refresh

A materialized view does not refresh itself; something has to call REFRESH. The simplest approach is a scheduled job — pg_cron inside the database, or an external scheduler.

-- With pg_cron, refresh every 15 minutes:
SELECT cron.schedule(
  'refresh_daily_revenue',
  '*/15 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_daily_revenue$$
);

The interval is the staleness decision in disguise. Refresh every fifteen minutes and your dashboard is at most fifteen minutes behind reality, at the cost of running the expensive query four times an hour whether or not the data changed. Refresh hourly and you spend less but tolerate more lag. There is no universal right answer; there is only the freshness your users actually need weighed against the cost of recomputation.

Where AI helps you choose

The judgment call — what to materialize, and how often — is where I bring in AI as an advisor. It is good at laying out the tradeoff structure and proposing a starting point, which I then sanity-check against the real refresh cost and the real freshness requirement.

Prompt: I have a dashboard with five tiles. Four read from a 12-second aggregation over an orders table that gets a few hundred inserts an hour. The fifth tile shows “orders in the last 5 minutes.” Should I materialize all of this, and what refresh interval makes sense?

Output: Split it. The four tiles built on the 12-second aggregation are ideal materialized-view candidates — the query is expensive and the underlying data moves slowly, so a single materialized view refreshed on a schedule will serve all four from one cached result. Given a few hundred inserts an hour, a 10-15 minute REFRESH ... CONCURRENTLY interval is a reasonable starting point; tighten it only if users complain about lag. Do not materialize the fifth tile: “last 5 minutes” demands near-real-time data, and any refresh interval long enough to be worth caching would make it wrong. Leave that one as a live query, ideally backed by an index on the timestamp. Verify by measuring the refresh duration — if it exceeds the interval, you’ll fall behind.

That answer captures the real discipline: materialize the slow-and-stable, leave the fast-and-fresh alone, and check that refresh time fits inside the interval. I confirm the refresh duration myself before trusting the schedule.

Verify the speedup, do not assume it

The whole point is performance, so I prove it. I time the dashboard query against the materialized view and compare it to the original, and I confirm the view’s own indexes are being used.

EXPLAIN (ANALYZE, BUFFERS)
SELECT day, region_id, revenue
FROM dashboard_daily_revenue
WHERE day >= current_date - 30
ORDER BY day;

If that plan shows an index scan on the materialized view returning in a millisecond or two, the work paid off. If it shows a sequential scan over the whole view, the view is built but unindexed for this access pattern, and you have moved the problem rather than solved it. Reading these plans well is its own skill — the slow query EXPLAIN triage prompt helps you hand a plan to an AI and get the suspicious nodes ranked.

When a materialized view is the wrong tool

Materialized views are not free. They consume disk, they go stale, and the refresh itself does real I/O. If the base query is slow because it is missing an index, fix the index first — materializing a poorly-indexed query just hides the underlying problem and locks you into refresh overhead forever. The index advisor prompt is the right first check. And if the data needs to be genuinely live, no refresh interval will save you, and you want a live view or a well-indexed direct query instead.

Used in the right spot, though, a materialized view is one of the highest-leverage tools in Postgres: it turns a brutal dashboard query into an instant read. Let AI help you reason about the staleness tradeoff, always add the unique index so you can refresh concurrently, and prove the speedup with EXPLAIN ANALYZE rather than assuming it. For more in this vein, the Postgres category collects the related performance guides.