Materialized View Refresh — Eager, Lazy, Incremental, and Concurrent Strategies

What is a materialized view?#

A regular view re-runs its query every time you SELECT from it. A materialized view stores the result on disk. You get fast reads but the data can go stale.

The hard part is not creating the view — it is deciding when and how to refresh it.

Eager vs lazy refresh#

Eager (synchronous)#

Refresh the view every time the underlying data changes. The write transaction blocks until the view is updated.

INSERT INTO orders (...) → trigger → REFRESH MATERIALIZED VIEW order_summary

Pros: Data is always fresh. Zero staleness.

Cons: Write latency increases. Every INSERT pays the cost of rebuilding the view. Unacceptable for high-throughput tables.

Use when: The source table changes infrequently (config tables, lookup data) and freshness is critical.

Lazy (asynchronous)#

Refresh the view on a schedule or on demand. Writes are fast. Reads may return stale data.

-- Cron job every 5 minutes
REFRESH MATERIALIZED VIEW order_summary;

Pros: No write penalty. Predictable refresh cost.

Cons: Data is stale between refreshes. The staleness window equals your refresh interval.

Use when: Dashboards, analytics, reporting — anywhere "5 minutes old" is acceptable.

Incremental refresh#

A full refresh rebuilds the entire view from scratch. For a view over 100 million rows, that can take minutes.

Incremental refresh only processes rows that changed since the last refresh. Oracle calls this "fast refresh." PostgreSQL does not natively support it (yet).

How incremental refresh works#

1. A materialized view log (or change log) tracks INSERT/UPDATE/DELETE on the base tables
2. On refresh, the engine reads only the log entries since the last refresh
3. It applies the deltas to the materialized view
4. It truncates the processed log entries

-- Oracle syntax
CREATE MATERIALIZED VIEW LOG ON orders
  WITH ROWID, PRIMARY KEY (order_id, customer_id, total)
  INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW order_summary
  REFRESH FAST ON DEMAND
  AS SELECT customer_id, SUM(total) as total_spent
     FROM orders
     GROUP BY customer_id;

Limitations#

Not all queries support incremental refresh. Joins, DISTINCT, certain aggregations, and subqueries may force a full refresh. Always check the database documentation for supported patterns.

PostgreSQL workarounds#

PostgreSQL lacks native incremental refresh. Common approaches:

• pg_ivm extension — Incremental View Maintenance for PostgreSQL. Maintains views automatically on data changes.
• Manual delta tables — Use triggers to capture changes, then apply them to a regular table that mimics a materialized view.
• UPSERT pattern — Refresh into a staging table, then MERGE/UPSERT into the target.

Concurrent refresh (PostgreSQL)#

By default, REFRESH MATERIALIZED VIEW takes an exclusive lock. No one can read the view during refresh.

-- This blocks all reads until refresh completes
REFRESH MATERIALIZED VIEW order_summary;

-- This allows reads during refresh (requires a UNIQUE index)
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;

How CONCURRENTLY works#

1. PostgreSQL builds the new version of the view in a temporary location
2. It compares the new version with the old version (diff)
3. It applies only the changes (INSERT new rows, DELETE removed rows, UPDATE changed rows)
4. Readers continue querying the old version until the swap completes

Requirements#

• The view must have a UNIQUE index. Without it, PostgreSQL cannot diff the old and new versions.
• Concurrent refresh is slower than a regular refresh (it does extra comparison work).
• It uses more disk space temporarily (two copies of the view exist during refresh).

-- Required: create a unique index first
CREATE UNIQUE INDEX ON order_summary (customer_id);

-- Now concurrent refresh works
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;

When to use concurrent refresh#

Use it when the view is queried frequently and you cannot tolerate read downtime. Accept the slower refresh in exchange for zero read interruption.

Refresh scheduling strategies#

Time-based (cron)#

The simplest approach. Refresh every N minutes/hours.

*/5 * * * * psql -c "REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;"

Problem: You pay the refresh cost even if nothing changed.

Event-driven#

Trigger a refresh when the underlying data changes. Use a message queue or database notification.

-- Listen for changes
LISTEN order_changes;

-- In your application
ON notification → REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;

Problem: High-frequency changes can trigger too many refreshes. Add debouncing — collect changes for 30 seconds, then refresh once.

Hybrid#

Combine both. Event-driven refresh with a maximum frequency (no more than once per minute) plus a fallback cron job (at least once per hour) to catch missed events.

Stale data tradeoffs#

Every materialized view is a tradeoff between freshness and performance.

Communicating staleness#

Expose the staleness to consumers. Store the last refresh timestamp and return it with query results.

-- Track when the view was last refreshed
CREATE TABLE mv_metadata (
  view_name TEXT PRIMARY KEY,
  last_refreshed TIMESTAMPTZ
);

-- Update after each refresh
UPDATE mv_metadata
SET last_refreshed = NOW()
WHERE view_name = 'order_summary';

Your API can then return "data_as_of": "2026-03-29T10:05:00Z" so clients know exactly how fresh the data is.

Streaming materialized views#

Traditional materialized views are batch-oriented. Streaming materialized views update continuously as data arrives.

How they work#

A streaming engine (Materialize, RisingWave, ksqlDB) maintains the view incrementally in real time. Every INSERT/UPDATE/DELETE on the source is immediately reflected in the view.

-- Materialize syntax
CREATE MATERIALIZED VIEW live_order_summary AS
  SELECT customer_id, SUM(total) as total_spent, COUNT(*) as order_count
  FROM orders
  GROUP BY customer_id;
-- This view updates automatically as orders stream in

When streaming views make sense#

• Real-time dashboards — sub-second freshness requirements
• Alerting systems — trigger alerts the moment a threshold is crossed
• Feature stores — ML features that must reflect the latest data
• Event-driven architectures — views over Kafka topics

When they do not#

• Simple reporting — a cron refresh every 5 minutes is simpler and cheaper
• Complex ad-hoc queries — streaming engines optimize for pre-defined queries, not exploration
• Low data volume — the operational overhead of a streaming engine is not justified

Decision framework#

1. How fresh must the data be? Sub-second needs streaming. Minutes is fine with cron. Zero staleness needs eager refresh.
2. How large is the view? Small views can do full refresh. Large views need incremental or concurrent.
3. How often does the source change? High-frequency changes rule out eager refresh. Low-frequency changes make eager viable.
4. Can readers tolerate lock waits? No means you need CONCURRENTLY or streaming.
5. What database are you on? Oracle has mature incremental refresh. PostgreSQL needs workarounds or extensions. Streaming requires a dedicated engine.

Visualize your data architecture#

Map out your materialized views, source tables, and refresh flows — try Codelit to generate an interactive architecture diagram.

Key takeaways#

1. Eager refresh gives zero staleness but penalizes every write
2. Lazy refresh (cron) is simple and works for most analytics workloads
3. CONCURRENTLY in PostgreSQL avoids read locks but requires a unique index
4. Incremental refresh processes only deltas — essential for large views
5. Streaming materialized views provide sub-second freshness for real-time use cases
6. Always expose staleness metadata so consumers know how fresh their data is

Article #434 in the Codelit engineering series. Explore our full library of system design, infrastructure, and architecture guides at codelit.io.