Materialized Views in Postgres: Cheap Reads, Expensive Refreshes

A materialized view is a query whose result is stored as a table. Reads against it are fast — they are just reads from the materialized table. Writes don't update it; only an explicit REFRESH does.

For expensive queries that run frequently against data that changes slowly, materialized views are a good answer. For everything else, they are a footgun. Knowing the difference is mostly knowing how often the data changes versus how often the query runs.

The shape

CREATE MATERIALIZED VIEW customer_lifetime_value AS
SELECT
  customer_id,
  count(*) AS order_count,
  sum(total_cents) AS lifetime_cents,
  max(created_at) AS last_order_at
FROM orders
GROUP BY customer_id;

CREATE UNIQUE INDEX customer_lifetime_value_pk
  ON customer_lifetime_value (customer_id);

Queries against customer_lifetime_value return pre-aggregated results. Fast.

The data in the view does not update when orders changes. To pick up new orders:

REFRESH MATERIALIZED VIEW customer_lifetime_value;

This re-runs the original query and replaces the materialized data. During refresh, the view is locked — readers wait.

CONCURRENTLY: refresh without blocking reads

The non-blocking refresh:

REFRESH MATERIALIZED VIEW CONCURRENTLY customer_lifetime_value;

Requires a unique index on the materialized view (the unique index above). The refresh computes the new state, then applies the diff atomically. Readers see either the old or new state, never partial.

This is the right form for production. The non-CONCURRENTLY version is faster but takes an exclusive lock for the duration.

When materialized views earn their keep

The right shape:

• The query is expensive (seconds to compute).
• The query runs frequently (many times per minute).
• The underlying data changes slowly (hourly or less often).
• Some staleness is acceptable.

Under these conditions, the math works out: refresh cost amortized over many cheap reads is much less than computing the query each time.

The canonical fit: dashboards. "Top 100 customers by lifetime value" is expensive to compute, refreshed hourly, queried thousands of times per hour. Materialized view is perfect.

When they are wrong

The data changes faster than you can refresh. A materialized view that takes 10 minutes to refresh, on data that changes every minute, is essentially never current.

Reads are not actually frequent enough. If the query only runs a few times per day, the refresh cost outweighs the savings. Just compute on demand.

The query is hard to make incremental. A materialized view's refresh is a full re-execution. There is no built-in way to update only the changed parts. For very large views, the refresh itself becomes a load problem.

Strict consistency is required. Materialized views are stale by definition. "My write should be visible immediately" does not work with them.

Refresh scheduling

The refresh has to happen somehow. Three patterns:

1. Cron / scheduled job. A cron job runs REFRESH MATERIALIZED VIEW on a schedule. Simple, predictable, easy to monitor.

# crontab
0 * * * * psql -c 'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_lifetime_value;'

For most cases, this is the right answer. Hourly or daily refreshes cover the vast majority of use cases.

2. pg_cron extension. Same idea but inside Postgres:

SELECT cron.schedule('refresh-mv-customers', '0 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY customer_lifetime_value$$);

Useful when the OS does not have a robust cron and the database does.

3. Trigger-based. A trigger on the source table fires a refresh after writes.

CREATE OR REPLACE FUNCTION refresh_mv_trigger()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify('refresh_mv', 'customer_lifetime_value');
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER refresh_after_insert
AFTER INSERT ON orders
FOR EACH STATEMENT EXECUTE FUNCTION refresh_mv_trigger();

A listener picks up the notify and refreshes. Useful for low-volume writes; bad for high-volume because every write triggers a refresh that takes longer than the next write.

Common failure modes

Forgotten refresh. The view is created, then nobody schedules the refresh. Users see stale data for months.

Always set up the refresh schedule when creating the view. A view without a refresh schedule is worse than no view because it produces wrong answers.

Refresh takes longer than the schedule interval. Hourly refresh that takes 90 minutes. Refreshes pile up. Eventually the database is doing nothing but refreshing.

Monitor refresh duration. If it gets close to the schedule interval, either the refresh is too expensive or the schedule is too aggressive.

The view's underlying query is slower than people remember. If the underlying query is hostile to make fast (multiple JOINs, large GROUP BY), the refresh is itself a heavy operation. Materialized views can hide this complexity, making it forgettable.

Keep the materialized view's query as simple as practical. If the query is essentially "join everything," the refresh cost is the join cost, multiplied by however often you refresh.

A real example

A dashboard query was the slowest endpoint on the application. The query:

SELECT
  date_trunc('day', o.created_at) AS day,
  c.country,
  count(*) AS orders,
  sum(o.total_cents) AS revenue_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > now() - interval '30 days'
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

This ran in 8 seconds against 50M orders. Dashboard hit it every page load.

Fix:

CREATE MATERIALIZED VIEW daily_orders_by_country AS
SELECT
  date_trunc('day', o.created_at) AS day,
  c.country,
  count(*) AS orders,
  sum(o.total_cents) AS revenue_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > now() - interval '30 days'
GROUP BY 1, 2;

CREATE UNIQUE INDEX daily_orders_pk ON daily_orders_by_country (day, country);

-- Refresh every 30 minutes
SELECT cron.schedule('refresh-daily-orders', '*/30 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY daily_orders_by_country$$);

Dashboard latency: 50ms. Up-to-30-minutes-stale data, which the product team agreed was fine.

What I commit to with materialized views

1. The refresh is scheduled at creation time.
2. The refresh duration is monitored. Alerts on "refresh took longer than expected."
3. CONCURRENTLY is used for all refreshes (with the unique index).
4. The staleness window is documented in code comments. Stakeholders know what they are getting.
5. Periodic review: is this view still needed? Is the staleness still acceptable?

Materialized views are powerful but easy to forget about. The discipline is what makes them useful long-term.