Lorixen.
EN

Slow dashboard? Precompute it with a PostgreSQL materialized view

Short answer: If your admin dashboard runs the same heavy totals over months of orders every time someone opens it, compute those totals once and read the stored result. In PostgreSQL, a materialized view does exactly that: it saves the output of a query like a table, and you refresh it on a schedule, such as every 15 minutes or nightly. Use REFRESH MATERIALIZED VIEW CONCURRENTLY with a unique index so people can keep reading while it updates, and show a "last updated" time on the dashboard so nobody mistakes it for live data.

The problem with live totals

Dashboards often start as a few queries: revenue by month, top products, orders by region. With a few thousand orders, they're instant. Two years later, with many more orders, each page load scans and groups everything again. The dashboard gets slow, and because several staff open it each morning, the database does the same heavy work over and over, slowing the shop or app for customers too.

Indexes help with finding rows, but they don't remove the cost of adding up hundreds of thousands of them. For figures that don't need to be accurate to the second, precomputing is the better answer.

What a materialized view is

A normal view is a saved query that runs every time you select from it. PostgreSQL's documentation explains that a materialized view also stores its query like a view, but persists the results in a table-like form. Reading from it returns the stored data directly, like reading from a table.

The trade-off: it doesn't update itself. You run REFRESH MATERIALIZED VIEW to recalculate it, and the documentation notes that you can't update it directly the way you can a table.

Building one for a dashboard

Take a typical "sales by day and product category" query. As a materialized view:

CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) AS day,
       category_id,
       count(*) AS orders,
       sum(total_cents) AS revenue_cents
FROM orders
WHERE status = 'paid'
GROUP BY 1, 2;

CREATE UNIQUE INDEX ON daily_sales (day, category_id);

Your dashboard then reads from daily_sales, which has one row per day and category instead of every order.

Refreshing without blocking readers

The reference page for REFRESH MATERIALIZED VIEW says a refresh completely replaces the view's contents. By default it can block other connections trying to read the view while it runs. The CONCURRENTLY option refreshes without locking out concurrent reads.

Conditions from the same page:

  • CONCURRENTLY needs at least one unique index on the view that uses only column names and covers all rows. That's why the example adds one.
  • The view must already be populated.
  • Only one refresh can run against a view at a time.
  • A concurrent refresh may be faster when few rows change; a plain refresh tends to use fewer resources when many rows change.

For most dashboards, CONCURRENTLY is the right default, because staff can keep using the page.

Scheduling the refresh

Pick a refresh frequency that matches how the figures are used:

  • Nightly for monthly and yearly reports.
  • Every 15 to 60 minutes for daily operations dashboards.
  • On demand after a bulk import, triggered by the import job.

Run the refresh from your app's background job system, a scheduled task on the server, or a scheduler extension if your database host offers one. Log how long each refresh takes so you notice when it starts creeping up.

Be honest about freshness

Show "Figures updated at 10:15" on the dashboard. Without it, someone will compare today's total with the live order list, see a difference and lose trust in the report. If certain numbers must be live, such as today's order count, keep those as small direct queries and precompute only the heavy history.

When to use something else

  • Application caching suits results that are cheap to recompute and used by many people, but invalidation logic lives in your code.
  • A summary table you update yourself suits figures that must stay current, by adding to totals as each order is paid. It's more code and more ways to drift.
  • A separate reporting database or warehouse suits heavy analysis across many sources.

A materialized view sits in the middle: little code, predictable freshness, all in the database you already run.

Checking the numbers match

Before switching the dashboard over, run the old live query and the new view side by side for a few days and compare the results after each refresh. Differences usually come from time zones in the date grouping, orders that change status after being counted, or filters that were applied in the page code rather than the query. Fix those first, so the change doesn't quietly alter the figures people rely on.

Things to watch

  • Refreshes use database resources. Schedule heavy ones outside busy hours.
  • If the underlying query changes, you need to recreate the view.
  • Grant read access to the view for dashboard users, rather than to the raw orders table, if you want to limit what they can query.
  • Keep refunds and cancellations in mind; the view only reflects them after the next refresh.

A worked example

An illustrative Hamilton food wholesaler's admin dashboard took several seconds to load each morning as staff checked sales by product range. The page ran four aggregate queries over every order since the business started. The developer moved the two heaviest into materialized views with unique indexes, refreshed them concurrently every 30 minutes from the existing job queue, and added "updated at" labels. The dashboard became quick to load, and the database stopped slowing down at 8am when everyone opened it.

FAQ

Is a materialized view the same as a cache?

It's a cache that lives in the database and is refreshed by a query. You control when it updates.

Can I index a materialized view?

Yes, like a table. A unique index is required for concurrent refreshes.

What happens to reads during a plain refresh?

They can be blocked until it finishes. Use CONCURRENTLY to avoid that.

Does MySQL have the same feature?

Not as a built-in equivalent. Teams usually use summary tables refreshed by a scheduled job instead.

If your admin dashboard or reports are getting slower every month, tell us what they show and we'll suggest what to precompute.

Sources

WhatsAppBook a call