View vs Materialized View in PostgreSQL: Key Differences and Usage

In PostgreSQL, a view is a virtual table that runs its query every time you access it, showing fresh data without storing results. A materialized view stores the query result physically and must be refreshed manually or on demand, offering faster reads but potentially stale data.

Quick Comparison

This table summarizes the main differences between view and materialized view in PostgreSQL.

Feature	View	Materialized View
Data Storage	No physical storage; query runs on access	Stores query result physically on disk
Data Freshness	Always shows current data	Data can be stale until refreshed
Performance	Slower for complex queries due to re-execution	Faster reads as data is precomputed
Refresh	Automatic on each access	Manual or scheduled refresh needed
Use Case	Simple, frequently changing data	Expensive queries where speed matters
Locking During Refresh	No locking issues	May lock during refresh depending on method

Key Differences

A view in PostgreSQL acts like a saved query. When you select from a view, PostgreSQL runs the underlying query each time, so you always get the latest data. Views do not store any data themselves, which means they use no extra disk space but can be slower if the query is complex or the underlying tables are large.

On the other hand, a materialized view stores the result of the query physically on disk. This means reading from a materialized view is much faster because it does not run the query every time. However, the data can become outdated because it only updates when you explicitly refresh it using commands like REFRESH MATERIALIZED VIEW. This makes materialized views ideal for expensive queries where speed is important and slight data delay is acceptable.

Another difference is locking behavior: refreshing a materialized view can lock it, blocking reads during the refresh, while views have no such locking since they run queries live. Choosing between them depends on your need for fresh data versus query speed.

Code Comparison

Here is how you create and use a simple view in PostgreSQL that shows total sales per customer.

sql

CREATE VIEW total_sales_view AS
SELECT customer_id, SUM(amount) AS total_sales
FROM sales
GROUP BY customer_id;

-- Query the view
SELECT * FROM total_sales_view;

Output

customer_id | total_sales
------------+------------
1           | 5000
2           | 3200
3           | 4500

Materialized View Equivalent

Here is how you create and use a materialized view for the same total sales per customer query. Note the need to refresh it to update data.

sql

CREATE MATERIALIZED VIEW total_sales_matview AS
SELECT customer_id, SUM(amount) AS total_sales
FROM sales
GROUP BY customer_id;

-- Query the materialized view
SELECT * FROM total_sales_matview;

-- Refresh to update data
REFRESH MATERIALIZED VIEW total_sales_matview;

Output

customer_id | total_sales
------------+------------
1           | 5000
2           | 3200
3           | 4500

When to Use Which

Choose a view when you need always up-to-date data and your queries are simple or fast enough to run on demand. Views are great for live dashboards or reports where data freshness is critical.

Choose a materialized view when your query is complex or slow, and you can tolerate some delay in data freshness. Materialized views speed up read performance by storing results but require manual or scheduled refreshes. They are ideal for large reports or analytics where query speed matters more than real-time data.

Key Takeaways

Views run their query every time and always show fresh data without storing results.

Materialized views store query results physically and must be refreshed to update data.

Use views for simple, frequently updated data and materialized views for expensive queries needing fast reads.

Refreshing materialized views can lock them temporarily, unlike views which have no locking.

Choosing depends on your need for data freshness versus query performance.