Materialized Views in Postgres: Cheap Reads, Expensive Refreshes

The dashboard started as a query. Then it became a slow query. Then someone wrapped it in a materialized view and the page became fast again. For two months, everyone was happy.

The pain returned when the refresh started taking longer than the reporting interval. Users saw stale numbers, refresh jobs overlapped, and the team learned the uncomfortable truth: materialized views make reads cheap by moving cost to refresh time.

That is not a flaw. It is the contract.

The framework: stale enough, cheap enough, simple enough

I use a materialized view when three things are true:

• The result can be stale for a known window.
• The full refresh is cheap enough to schedule safely.
• The query is simpler to rebuild than to maintain incrementally.

If any of those are false, a materialized view may still work, but it is no longer the default answer.

A materialized view is a stored result set

CREATE MATERIALIZED VIEW account_daily_revenue AS
SELECT
  date_trunc('day', paid_at) AS day,
  account_id,
  sum(amount_cents) AS revenue_cents,
  count(*) AS invoice_count
FROM invoices
WHERE status = 'paid'
GROUP BY 1, 2;

Unlike a normal view, the result is stored. That means you can index it.

CREATE UNIQUE INDEX account_daily_revenue_key
ON account_daily_revenue (day, account_id);

The index is not just performance decoration. It may be required if you want concurrent refreshes.

Refresh behavior is the production risk

REFRESH MATERIALIZED VIEW account_daily_revenue;

A regular refresh rebuilds the view and blocks reads from the materialized view while it refreshes. That may be fine for a nightly report. It is not fine for a dashboard users hit all day.

REFRESH MATERIALIZED VIEW CONCURRENTLY account_daily_revenue;

Concurrent refresh avoids blocking reads, but PostgreSQL requires a suitable unique index that covers all rows using plain column names. It also cannot be used on an unpopulated materialized view. The first load is a separate operational step.

CONCURRENTLY is not incremental refresh

This is the misconception that causes most materialized view disappointment. CONCURRENTLY changes the locking behavior. It does not mean PostgreSQL only processes changed rows.

If the underlying query scans a year of orders, the refresh still needs to evaluate that query. As the base data grows, refresh time grows unless the query can prune work.

Design the refresh window like a product feature

Do not hide the schedule in cron and call it done. Decide what users are allowed to see.

• Is five-minute stale data acceptable?
• Does the UI show the last refresh time?
• What happens if a refresh fails?
• Can two refreshes overlap?
• Does the refresh compete with production writes?

If the product cannot tolerate stale data, a materialized view is probably the wrong serving surface.

Protect refresh jobs from overlap

BEGIN;
SELECT pg_try_advisory_xact_lock(8200, 1) AS got_lock;
-- run refresh only if got_lock is true
COMMIT;

Overlapping refresh jobs are easy to create during deploys, retries, and manual maintenance. Make refresh singleton behavior explicit.

When summary tables are better

If only recent data changes, a summary table with incremental updates may be a better fit than rebuilding a full materialized view.

INSERT INTO account_daily_revenue_summary AS dst
  (day, account_id, revenue_cents, invoice_count)
SELECT date_trunc('day', paid_at), account_id, sum(amount_cents), count(*)
FROM invoices
WHERE id > $last_processed_invoice_id
GROUP BY 1, 2
ON CONFLICT (day, account_id)
DO UPDATE SET
  revenue_cents = dst.revenue_cents + EXCLUDED.revenue_cents,
  invoice_count = dst.invoice_count + EXCLUDED.invoice_count;

That is more code, but it makes the cost proportional to change instead of history.

What I monitor

• Refresh duration versus schedule interval.
• Last successful refresh timestamp.
• Rows in the materialized view.
• Temporary file usage during refresh.
• Lock waits involving the materialized view.
• Query plans for the refresh query as base tables grow.

The pragmatic default

Use materialized views for expensive reads where stale data is acceptable and full refresh stays cheap. Use concurrent refresh when readers must continue during refresh and you can provide the required unique index. Move to incremental summary tables when the refresh cost grows with history but the business change is small.

The mistake is not using materialized views. The mistake is selling them as automatic incremental aggregation.