Materialized View

A materialized view is a view whose query result is physically stored on disk, unlike a regular view which re-executes its query every time. This trades storage for read performance — ideal for expensive aggregations, joins, or OLAP queries that don’t need real-time data.

Syntax (PostgreSQL)

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', sale_date) AS month,
       product_id,
       SUM(amount) AS total,
       COUNT(*) AS num_orders
FROM sales
GROUP BY 1, 2;

Refreshing

The data becomes stale as the underlying tables change. Refresh manually:

-- Full refresh (blocks reads during refresh)
REFRESH MATERIALIZED VIEW monthly_sales;
 
-- Concurrent refresh (allows reads during refresh, requires UNIQUE index)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

Automate with a cron job, trigger, or application logic depending on staleness tolerance.

Regular View vs Materialized View

Aspect	Regular View	Materialized View
Storage	None (virtual)	Physical (on disk)
Query execution	Every access	Only on refresh
Read speed	Same as underlying query	Fast (pre-computed)
Data freshness	Always current	Stale between refreshes
Index support	No (indexes on base tables)	Yes (can create indexes on the MV)
Write-through	Some views are updatable	Not updatable

Use Cases

• Dashboard aggregations — Pre-compute daily/weekly/monthly summaries
• Expensive joins — Cache the result of multi-table joins
• Data Warehousing — Summary tables for OLAP queries
• Search acceleration — Pre-join and denormalize for Full Text Search or filtering

Availability

DBMS	Support
PostgreSQL	Full support with CONCURRENTLY option
Oracle	Full support with automatic refresh options
SQL Server	Called “Indexed Views”
MySQL	Not natively supported (simulate with table + trigger)
SQLite	Not supported