Start a project

Engineering · Mar 28, 2026

Postgres materialized views for real-time analytics

Sooner or later every product grows a dashboard, and sooner or later the query behind it gets slow. Before reaching for a separate analytics database, it is worth knowing what Postgres can already do with a materialized view.

What a materialized view is

A normal view is a saved query: every time you read it, Postgres runs the query again. A materialized view stores the result on disk like a table. Reading it is as fast as reading any table, because the expensive joins and aggregates have already been done.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', paid_at) AS day,
       account_id,
       sum(amount) AS revenue
FROM payments
GROUP BY 1, 2;

CREATE UNIQUE INDEX ON daily_revenue (day, account_id);

The catch: the data is as old as the last refresh

The view does not update itself. You refresh it, and how you refresh it matters:

  • REFRESH MATERIALIZED VIEW locks the view while it rebuilds. Readers wait.
  • REFRESH MATERIALIZED VIEW CONCURRENTLY lets readers keep reading the old data during the rebuild. It needs a unique index on the view, which is why the example creates one.

Either way the whole view is recomputed. If the underlying query takes forty seconds, every refresh costs forty seconds of database work.

Getting close to real time

“Real-time” on a dashboard usually means “fresh enough that nobody notices”. Three patterns get you there:

  • Refresh on a schedule. A job every minute or every five is enough for most reporting screens. Say so in the interface: “updated 2 minutes ago” is honest and users accept it.
  • Split history from today. Materialize everything up to midnight, and query only today’s rows live. Union the two. The live part stays small, so it stays fast.
  • Keep a summary table instead. When you need the numbers current to the second, a table you update yourself, in the same transaction as the write, does what a materialized view cannot.

When to stop

Materialized views are the right tool while a full refresh is cheap compared with how often you need it. Once the refresh takes longer than the interval between refreshes, move to incremental summary tables or a dedicated analytics store. Until then, a view and a scheduled job are far less to operate than a second database.

← All posts