Materialized Views

A materialized view stores the result of a SELECT query as cached data. Unlike regular views (which re-execute the query on every access), materialized views serve from precomputed results and can be refreshed on demand, on a schedule, or automatically after each commit.

Creating a Materialized View

CREATE MATERIALIZED VIEW monthly_revenue AS
  SELECT month, SUM(amount) AS total
  FROM orders
  GROUP BY month;

The view is populated immediately on creation. Subsequent reads return the cached result without re-executing the underlying query.

Refreshing

Manual Refresh

Re-execute the defining query and replace the cached data:

REFRESH MATERIALIZED VIEW monthly_revenue;

Periodic Refresh

Create a view that refreshes automatically on a timer:

CREATE MATERIALIZED VIEW hourly_stats
  REFRESH EVERY 3600
AS
  SELECT status, COUNT(*) AS cnt
  FROM orders
  GROUP BY status;

The REFRESH EVERY <seconds> clause schedules a background task that re-executes the query at the specified interval.

On-Commit Refresh

Create a view that refreshes after every transaction commit:

CREATE MATERIALIZED VIEW live_totals
  REFRESH ON COMMIT
AS
  SELECT category, SUM(price) AS total
  FROM products
  GROUP BY category;

On-commit views are always up-to-date but add overhead to every write transaction. Use this strategy for dashboards that require real-time accuracy on small-to-medium tables.

Refresh Strategies Summary

Strategy	Trigger	Trade-off
Manual	REFRESH MATERIALIZED VIEW	Full control; data may be stale
Periodic	Background timer	Bounded staleness; no write overhead
On Commit	After each transaction commit	Always fresh; adds commit latency

Querying

Query a materialized view exactly like a table:

SELECT * FROM monthly_revenue WHERE month = '2025-03';

Dropping

DROP MATERIALIZED VIEW monthly_revenue;
DROP MATERIALIZED VIEW IF EXISTS monthly_revenue;

Monitoring

The virtual table __pdb_stat_materialized_views exposes metadata for all materialized views:

SELECT * FROM __pdb_stat_materialized_views;

Column	Type	Description
name	TEXT	View name
query	TEXT	The defining SELECT statement
strategy	TEXT	Refresh strategy (MANUAL, EVERY Ns, ON COMMIT)
last_refreshed	BIGINT	Epoch microseconds of last successful refresh
next_refresh	BIGINT	Epoch microseconds of next scheduled refresh
row_count	BIGINT	Number of rows in the cached result
is_populated	TEXT	Whether the view has been populated at least once

Refresh Semantics

Refreshes are atomic from the perspective of concurrent readers — queries never see a partially-updated view. Periodic views are refreshed when their next-refresh timestamp elapses; ON COMMIT views refresh after each transaction that touches the underlying tables.