Refresh a Materialized View without Blocking Reads

Build a materialized view for an expensive aggregate, refresh it concurrently, and manage the unique index that concurrent refresh requires.

~35 minpostgresqlperformancedata

Task

Serve a heavy aggregate from a materialized view and refresh it without locking out readers.

When to use this

A dashboard re-runs a costly join and aggregate on every load, adding load to production and returning inconsistent numbers while it runs.

Prerequisites

PostgreSQL or a database with materialized views
A unique index on the view
A refresh schedule

Steps

1. Create the materialized view with the expensive query.

Expected output: The aggregate is computed once and stored.

2. Add a unique index so CONCURRENTLY refresh is possible.

Expected output: Refreshes do not block readers.

3. Refresh with REFRESH MATERIALIZED VIEW CONCURRENTLY on a schedule.

Expected output: Readers keep querying the old snapshot during refresh.

4. Document the staleness window and monitor refresh duration and failures.

psql -c "REFRESH MATERIALIZED VIEW CONCURRENTLY sales_daily;"

Expected output: Consumers know how old the data may be.

Troubleshooting

Problem

CONCURRENTLY fails without a unique index

Solution

Create the required unique index; a missing one forces a full lock.

Problem

Refresh takes too long

Solution

Incrementally maintain a summary table, or refresh less often and cache the result further.

Limitations

Materialized views serve stale data by design, so they are unsuitable when reads must reflect the latest write.
CONCURRENTLY refresh is slower and requires a unique index and sufficient free space.

Context

Serve a heavy aggregate from a materialized view and refresh it without locking out readers. A dashboard re-runs a costly join and aggregate on every load, adding load to production and returning inconsistent numbers while it runs.

Steps

Create the materialized view with the expensive query. Expected: The aggregate is computed once and stored.
Add a unique index so CONCURRENTLY refresh is possible. Expected: Refreshes do not block readers.
Refresh with REFRESH MATERIALIZED VIEW CONCURRENTLY on a schedule. Expected: Readers keep querying the old snapshot during refresh.
Document the staleness window and monitor refresh duration and failures. Expected: Consumers know how old the data may be.

CREATE MATERIALIZED VIEW sales_daily AS
SELECT date_trunc('day', created_at) AS day, sum(total) AS revenue
FROM orders GROUP BY 1;
CREATE UNIQUE INDEX ON sales_daily (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_daily;

Verify

The aggregate is computed once and stored.
Refreshes do not block readers.
Readers keep querying the old snapshot during refresh.
Consumers know how old the data may be.

Limits

Materialized views serve stale data by design, so they are unsuitable when reads must reflect the latest write.
CONCURRENTLY refresh is slower and requires a unique index and sufficient free space.